sql - 当某些月份没有记录时如何创建每月运行总和

Question

目标是在月度摘要中绘制随时间推移创建的订单总量

WITH monthly_sums AS (
  SELECT
    date_trunc('month', created_at) AS month,
    sum(count(created_at)) OVER (ORDER BY date_trunc('month', created_at)) AS sum
  FROM orders
  GROUP BY date_trunc('month', created_at)
)
SELECT
  to_char(date_range.month, 'Month') AS month,
  COALESCE(monthly_sums.sum, 0) AS total
FROM generate_series('2014-10-01'::date, CURRENT_DATE, '1 month') date_range(month)
LEFT OUTER JOIN monthly_sums
ON monthly_sums.month = date_range.month;

哪个返回:

   month   | total
-----------+-------
 October   |     0
 November  |     0
 December  |     0
 January   |     1
 February  |     0     <-- should be 1
 March     |     3
(6 rows)

从 monthly_sums 返回中选择:

        month        | sum
---------------------+-----
 2015-01-01 00:00:00 |   1
                     |         <-- no records created in February
 2015-03-01 00:00:00 |   3
                     |   3
(3 rows)

问题是 2 月份没有订单，因此总数合并为 0。我如何更改或重新考虑此查询以获得所需的结果？

Answer 1

我不熟悉此构造的 postgresql 语法，但所有 SQL 引擎高效实现此类操作的一般模式始终如下:

1. 按月汇总；
2. 从完整的所需周期集左连接到 (1) 中的聚合，将缺失的总和合并为零；
3. 按期间执行运行求和。

使用 CTE 或子查询依次构建 1、2 和 3。