Вопрос по date-range, sql, postgresql, aggregate-functions – Итоговые суммы за неделю

1

У меня есть таблица в базе данных PostgreSQL, содержащая даты и общее количество в день.

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 

Теперь мне нужно получить итоги за неделю для заданного диапазона дат.
Ex. Я хочу получить еженедельные итоги от2012-05-01 вплоть до2012-05-31.

Я смотрю на этот вывод:

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

Ваш Ответ

2   ответа
0

функция

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;

И генерировать_серии (начиная с 8.4) мы можем создавать разделы даты.

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;

Затем положить его вместе с данными

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;
3

any заданный диапазон дат:

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$;

Вызов:

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

Generating time series between two dates in PostgreSQL

Похожие вопросы