如何编写PSQL查询计算deaths表每行对应过去90天累计死亡人数

核心思路 #

可以通过两种方案实现：SQL标准支持的滑动窗口函数方案，以及兼容性更强的自关联方案，均能实现按每行日期计算往前推90天的累计死亡人数。

方案1：滑动窗口函数方案（推荐，适合PostgreSQL、Oracle、Spark SQL等支持标准SQL窗口帧的数据库） #

直接使用窗口函数的RANGE滑动帧限定日期范围：

SELECT 
    Date,
    Deaths,
    SUM(Deaths) OVER (
        ORDER BY Date ASC
        RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
    ) AS rolling_90d_deaths
FROM deaths
ORDER BY Date DESC;

方案2：自关联方案（兼容性更强，适合MySQL等对窗口帧范围支持有限的数据库） #

通过表自关联限定日期范围后聚合求和：

SELECT 
    t1.Date,
    t1.Deaths,
    SUM(t2.Deaths) AS rolling_90d_deaths
FROM deaths t1
LEFT JOIN deaths t2 
ON t2.Date >= DATE_SUB(t1.Date, INTERVAL 90 DAY) AND t2.Date <= t1.Date
GROUP BY t1.Date, t1.Deaths
ORDER BY t1.Date DESC;

验证说明 #

针对你给出的示例数据，所有日期都在90天区间内，查询后2021-08-19对应的rolling_90d_deaths结果为481，和你预期的求和结果一致。

注意事项 #

• 请确保表中的Date字段为日期类型，若为字符串类型需要先转换为日期格式再做计算
• 若表中存在同一日期的多条记录，需要先按日期维度聚合单日死亡总人数，再计算滚动累计

内容的提问来源于stack exchange，提问作者Diego Collao Alcayaga