如何用PostgreSQL计算数据的3日滚动求和？

假设有一个名为values的表，结构及数据如下：

ID   day         value
 1   2023-09-21     37
 2   2023-09-21     14
 3   2023-09-22     42
 4   2023-09-23      9
 5   2023-09-23     10
 6   2023-09-23     11
 7   2023-09-24     14
 8   2023-09-24     16
 9   2023-09-25      6
10   2023-09-25      4
11   2023-09-26     17
12   2023-09-27     22
13   2023-09-28     31
14   2023-09-28      9

需要生成每日的3日滚动求和，即计算当前日期及过去2天的总值，预期结果如下：

day         total
2023-09-21     51  (21日 = 37+14)
2023-09-22     93  (21日 + 42)
2023-09-23    123  (21日 + 22日 + (9+10+11))
2023-09-24    102  (22日 + 23日 + (14+16))
2023-09-25     70  (23日 + 24日 + (6+4))
2023-09-26     57  (24日 + 25日 + 17)
2023-09-27     49  (25日 + 26日 + 22)
2023-09-28     79  (26日 + 27日 + (31+9))

目前已能通过以下SQL计算每日合计：

SELECT day, sum(value) as daily_total
FROM values
GROUP by day
ORDER BY day

得到结果：

day   daily_total
2023-09-21     51
2023-09-22     42
2023-09-23     30
2023-09-24     30
2023-09-25     10
2023-09-26     17
2023-09-27     22
2023-09-28     40

使用新版本PostgreSQL，尝试过窗口函数和分区范围等方法，但始终无法实现目标滚动求和，求解决办法。

可以基于每日合计的结果，使用PostgreSQL的范围窗口函数来实现3日滚动求和，核心是通过RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW定义窗口范围（当前日期及过去2天）。

方法一：基于每日合计计算 #

完整SQL语句如下：

WITH daily_totals AS (
    SELECT day, sum(value) AS daily_total
    FROM values
    GROUP BY day
    ORDER BY day
)
SELECT 
    day,
    SUM(daily_total) OVER (
        ORDER BY day 
        RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
    ) AS total
FROM daily_totals
ORDER BY day;

逻辑说明 #

1. CTE daily_totals：先计算每日的合计值，和你已实现的逻辑一致。
2. 窗口范围定义：RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW指定窗口包含当前日期以及往前推2天的所有数据，正好覆盖3天的范围。
3. 滚动求和：对窗口内的daily_total求和，得到每日的3日滚动总值。

执行后会得到预期结果：

day         total
2023-09-21     51
2023-09-22     93
2023-09-23    123
2023-09-24    102
2023-09-25     70
2023-09-26     57
2023-09-27     49
2023-09-28     79

方法二：直接基于原表计算 #

如果不需要单独保留每日合计，也可以直接在原表上使用窗口函数，写法如下：

SELECT 
    DISTINCT day,
    SUM(value) OVER (
        ORDER BY day 
        RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW
    ) AS total
FROM values
ORDER BY day;

这种写法会直接对过去3天内的所有value行求和，结果和方法一完全一致。

内容的提问来源于stack exchange，提问作者dave