sql - 每周总金额

Question

我在 PostgreSQL 数据库中有一个表，其中包含日期和每天的总计数。

mydate       total2012-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 

现在我需要获取给定日期范围内的每周总计。
前任。我想获取从 2012-05-01 到 2012-05-31 的每周总计。

我正在查看这个输出:

2012-05-01 2012-05-07   02012-05-08 2012-05-14  362012-05-15 2012-05-22   52012-05-23 2012-05-29   12012-05-30 2012-05-31   0

Answer 1

这适用于任何给定的日期范围:

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');

要点

• 为方便起见，我将其包装在函数中，因此日期范围只需提供一次。

• 子查询 w 生成从给定日期范围的第一天开始的周系列。上限以 LEAST 为上限，以保持在给定日期范围的上限内。

• 然后 LEFT JOIN 到数据表(在我的示例中为 tbl)以在结果中保留 所有 周，即使在哪里没有找到数据行。

• 其余的应该是显而易见的。 COALESCE 为空周输出 0 而不是 NULL。

• 数据类型必须匹配，由于缺乏信息，我假定为 mydate date 和 total int。 (int 的 sum() 是 bigint。)

• 关于我对 generate_series() 的特殊使用的说明:

  • Generating time series between two dates in PostgreSQL