Question 283271 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Make SQL Query With Multiple Date Ranges

I have a table that keeps track of runs. It has the following columns:

ID distance run_time run_date
1 10 01:35:00 2023-12-10
2 10 01:48:00 2023-12-16
3 10 01:25:00 2023-12-17
4 10 01:41:00 2023-12-23

I want to calculate the average pace per mile (in the format mm:ss) for the current week and the week before it. Here is the query to calculate the average pace for one week:

select to_char(sum(run_time) / sum(distance), 'mi:ss')
  from runs
 where run_date between run_date1 and run_date2;

Is there a way to do this with just one query and guarantee that the first row of the results corresponds to the first week (the second row corresponds to the second week and so on)?

This is the best that I could come up with:

   select to_char(sum(run_time) / sum(distance), 'mi:ss')
      from runs
     where run_date between run_date1 and run_date2 union 
  select to_char(sum(run_time) / sum(distance), 'mi:ss')
      from runs
     where run_date between run_date3 and run_date4

Is there a neater way to write this? What if I want to calculate the next 4 weeks? Can we use the in operator to simplify this?

Tags:

2

Answers


  1. 0

    If your week is defined as the same with yearly weeks, you can use date_part to extract the week count and group the rows before calculating the average pace.

    select date_part('year', run_date) as year,
    date_part('week', run_date) as week,
    to_char(sum(run_time) / sum(distance), 'mi:ss') as average_pace
  from runs
  where run_date between '2023-12-10' and '2023-12-17'
  group by year, week;

    This will give you the following result:

    year week average_pace
    2023 49 09:30
    2023 50 09:39

    Fiddle link: https://dbfiddle.uk/u9NPRrsL

    Login or Signup to reply.
  2. 0

    The generate_series function makes your problem easy to answer:

    In the below example, all the records between 100 days ago to 100 days in the future will be grouped per week, Monday being the first day of the week.
    If you want to hardcode a date range instead, simply replace the parameters in generate_series by timestamps (or by dates that you cast to timestamp).

    WITH Weeks(Day) AS (    
SELECT generate_series(current_timestamp - interval '100 days', current_timestamp + interval '100 days', '1 week')::date 
    + 1 - date_part('dow', current_date)::integer /* Sets Monday as first day of the week */
)
SELECT from_date, to_date, sum(run_time) / sum(distance) AS average_pace
  FROM runs
  JOIN (SELECT DAY, LEAD(Day) OVER (ORDER BY Day) - 1 FROM Weeks) W(from_date, to_date)
  ON run_date BETWEEN from_date and to_date
GROUP BY from_date, to_date
ORDER BY from_date, to_date

    You can also group your record not according to a Monday-Sunday logic but rather using what your runs table contains.

    WITH Weeks(Day) AS (    
SELECT generate_series((SELECT MIN(run_date) FROM runs) , (SELECT MAX(run_date) + 6 FROM runs), '1 week')::date 
)
SELECT from_date, to_date, sum(run_time) / sum(distance) AS average_pace
  FROM runs
  JOIN (SELECT DAY, LEAD(Day) OVER (ORDER BY Day) - 1 FROM Weeks) W(from_date, to_date)
  ON run_date BETWEEN from_date and to_date
GROUP BY from_date, to_date
ORDER BY from_date

    Additional notes:

    First, in case you did not notice, I subtract 1 to every upper bound of the week. This is because BETWEEN includes both the lower and the upper bounds. So with BETWEEN, a run taking place the last day of a week would also be considered to take place the first day of the next week.
    Instead of run_date BETWEEN from_date and to_date - 1, you could use run_date >= from_date and run_date < to_date from the same result.

    Also, the part of the query calculating W is simply here to grab 2 of the consecutive dates returned by generate_series. There is a slightly more compact way of expressing the same idea:

    SELECT from_date, (from_date - 1 + interval '1 week')::date AS to_date, sum(run_time) / sum(distance) AS average_pace
  FROM runs
  JOIN (
   SELECT generate_series(current_timestamp - interval '100 days', current_timestamp + interval '100 days', '1 week')::date
) W(from_date)
ON run_date BETWEEN from_date and from_date - 1 + interval '1 week'
GROUP BY from_date
ORDER BY from_date

    However, I find this less convenient: if you want to change the grouping e.g. from 1 to 2 weeks, you must make sure to do it in 3 places (inside SELECT, subquery and ON) of the query instead of one.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search