CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsdate-range
Mike Montesines
Mike Montesines

Reputation: 161

Weekly total sums

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

Upvotes: 1

Views: 1452

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656291

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():

Upvotes: 4

Gavin
Gavin

Reputation: 6460

Using this function 

CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS
$$
  SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date;
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

AND generate_series (from 8.4 onwards) we can create the date partitions.

SELECT wk.wk_start, 
       CAST(
            CASE (extract(month from wk.wk_start) = extract(month from wk.wk_start + interval '6 days'))
            WHEN true THEN wk.wk_start + interval '6 days'
            ELSE last_day(wk.wk_start)
            END 
           AS date) AS wk_end
  FROM
    (SELECT CAST(generate_series('2012-05-01'::date,'2012-05-31'::date,interval '1 week') AS date) AS wk_start) AS wk;

Then putting it together with the data

CREATE TABLE my_tab(mydate date,total integer);
INSERT INTO my_tab 
values    
('2012-05-12'::date,12),
('2012-05-14'::date,8),
('2012-05-13'::date,4),
('2012-05-12'::date,12),
('2012-05-15'::date,2),
('2012-05-17'::date,1),
('2012-05-18'::date,1),
('2012-05-21'::date,1),
('2012-05-25'::date,1); 

WITH month_by_week AS
    (SELECT wk.wk_start, 
       CAST(
            CASE (extract(month from wk.wk_start) = extract(month from wk.wk_start + interval '6 days'))
            WHEN true THEN wk.wk_start + interval '6 days'
            ELSE last_day(wk.wk_start)
            END 
           AS date) AS wk_end
  FROM
    (SELECT CAST(generate_series('2012-05-01'::date,'2012-05-31'::date,interval '1 week') AS date) AS wk_start) AS wk
  )
SELECT month_by_week.wk_start,
       month_by_week.wk_end,
       SUM(COALESCE(mt.total,0))
  FROM month_by_week 
       LEFT JOIN my_tab mt ON  mt.mydate BETWEEN month_by_week.wk_start AND month_by_week.wk_end
 GROUP BY month_by_week.wk_start,
          month_by_week.wk_end
 ORDER BY month_by_week.wk_start;

Upvotes: 0

Related Questions