Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

I have a table in a PostgreSQL database containing dates and a total count per day.

mydate       total
2012-05-12      12 
2012-05-14       8 
2012-05-13       4 
2012-05-12      12 
2012-05-15       2 
2012-05-17       1 
2012-05-18       1 
2012-05-21       1 
2012-05-25       1 

Now I need to get the weekly totals for a given date range.
Ex. I want to get the weekly totals from 2012-05-01 up to 2012-05-31.

I'm looking at this output:

2012-05-01 2012-05-07   0
2012-05-08 2012-05-14  36
2012-05-15 2012-05-22   5
2012-05-23 2012-05-29   1
2012-05-30 2012-05-31   0
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
936 views
Welcome To Ask or Share your Answers For Others

1 Answer

This works for any given date range:

CREATE FUNCTION f_tbl_weekly_sumtotals(_range_start date, _range_end date)
  RETURNS TABLE (week_start date, week_end date, sum_total bigint)
  LANGUAGE sql AS
$func$
SELECT w.week_start, w.week_end, COALESCE(sum(t.total), 0)
FROM  (
   SELECT week_start::date, LEAST(week_start::date + 6, _range_end) AS week_end
   FROM   generate_series(_range_start::timestamp
                        , _range_end::timestamp
                        , interval '1 week') week_start
   ) w
LEFT   JOIN tbl t ON t.mydate BETWEEN w.week_start and w.week_end
GROUP  BY w.week_start, w.week_end
ORDER  BY w.week_start
$func$;

Call:

SELECT * FROM f_tbl_weekly_sumtotals('2012-05-01', '2012-05-31');

Major points

  • I wrapped it in a function for convenience, so the date range has to be provided once only.

  • The subquery w produces the series of weeks starting from the first day of the given date range. The upper bound is capped with LEAST to stay within the upper bound of the given date range.

  • Then LEFT JOIN to the data table (tbl in my example) to keep all weeks in the result, even where no data rows are found.

  • The rest should be obvious. COALESCE to output 0 instead of NULL for empty weeks.

  • Data types have to match, I assumed mydate date and total int for lack of information. (The sum() of an int is bigint.)

  • Explanation for my particular use of generate_series():


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...