过去 X 个月内的 PostgreSQL 累计计数

Question

给出下表:

CREATE TABLE cnts(
  user_id INT,
  month_d DATE,
  cnt INT
)

我想查询每个 (user_id, month_d) 对最近 6 个月的累计计数。我可以通过以下 JOIN 来完成:

SELECT
  S1.month_d AS "month_d",
  S1.user_id AS "user_id",
  SUM(S2.cnt) AS "last_6_months_cnt"
FROM cnts S1
LEFT JOIN cnts S2 ON S1.user_id = S2.user_id
                 AND (S2.month_d BETWEEN (S1.month_d - INTERVAL '5 MONTH') AND S1.month_d)
GROUP BY 1, 2
ORDER BY 2, 1;

但我想知道这是否可以通过窗口函数解决？

示例数据:

INSERT INTO cnts(user_id, month_d, cnt) VALUES 
(1, '2013-01-01', 2),
(1, '2013-04-01', 2),
(1, '2013-07-01', 2),
(1, '2013-10-01', 2),

(2, '2013-01-01', 2),
(2, '2013-04-01', 2),
(2, '2013-07-01', 2),
(2, '2013-10-01', 2)
;

预期结果(来自上面的连接):

  month_d   | user_id | last_6_months_cnt 
------------+---------+-------------------
 2013-01-01 |       1 |                 2
 2013-04-01 |       1 |                 4
 2013-07-01 |       1 |                 4
 2013-10-01 |       1 |                 4
 2013-01-01 |       2 |                 2
 2013-04-01 |       2 |                 4
 2013-07-01 |       2 |                 4
 2013-10-01 |       2 |                 4

Answer 1

PostgreSQL 12 及更新版本

更新:PostgreSQL 12 和更新版本现在支持 RANGE 窗口。

正确的方法是在 RANGE (INTERVAL '6' MONTH) PRECEDING 上使用窗口:

demo=> SELECT month_d, user_id, 
              SUM(cnt) OVER (PARTITION BY user_id ORDER BY month_d RANGE INTERVAL '6' MONTH PRECEDING)
       FROM cnts ORDER BY 2,1;

  month_d   | user_id | sum 
------------+---------+-----
 2013-01-01 |       1 |   2
 2013-04-01 |       1 |   4
 2013-07-01 |       1 |   6
 2013-10-01 |       1 |   6
 2013-01-01 |       2 |   2
 2013-04-01 |       2 |   4
 2013-07-01 |       2 |   6
 2013-10-01 |       2 |   6
(8 rows)

PostgreSQL 11 及更早版本

在 PostgreSQL 11 或更早的 RANGE 窗口还不支持，所以查询会失败:

regress=> SELECT month_d, user_id, 
          SUM(cnt) OVER (PARTITION BY user_id ORDER BY month_d RANGE INTERVAL '6' MONTH PRECEDING) 
          FROM cnts ORDER BY 2,1;
ERROR:  RANGE PRECEDING is only supported with UNBOUNDED
LINE 1: ...(cnt) OVER (PARTITION BY user_id ORDER BY month_d RANGE INTE...

否则，您将通过 generate_series 的连接返回，并且在多个用户 ID 上执行此操作很麻烦。我怀疑您的自连接方法比尝试使用基于 ROWS 的窗口在 sum 上执行此操作要好得多。您必须将整个日期范围的 generate_series 与所有不同 uid 的集合交叉连接，然后将其与 cnts 表进行左外部连接，使用 sum 在一个窗口上，然后过滤掉具有空计数的行。不用说，这是一种比简单的自连接更折磨人的做事方式。

---

对于您的示例数据，以下查询将产生与上面所示相同的结果:

-- This query is totally wrong and only works because of overly simple sample data
SELECT 
  month_d, user_id, 
  SUM(cnt) OVER (PARTITION BY user_id ORDER BY month_d ROWS 1 PRECEDING)
FROM cnts
ORDER BY 2,1;

然而，这是完全错误的。我展示它主要是为了说明示例数据不足以进行可靠测试，因为结果完全匹配完全靠运气。在六个月的范围内，您的 sample 都没有超过两个 sample 。示例数据很好，但您需要考虑极端情况，就像编写单元测试时一样。您应该拥有不在同一日期开始和停止、具有不同计数等的 uid。