如何对单年度交易日期分别计算过去90天的value累计求和？

需要处理一整年的交易数据，为每个交易日期计算该日期往前推90天范围内的所有交易value总和，输出需包含原交易日期、往前推90天的日期（命名为date_diff），以及对应的总和值。

示例输入数据 #

预期输出 #

解决方案 #

方法1：使用窗口函数（推荐，高效处理大数据） #

利用窗口函数的范围聚合功能，直接计算每个日期的90天滑动窗口总和。不同数据库的日期语法略有差异，以下是主流数据库的实现：

PostgreSQL/BigQuery

SELECT
    transaction_date,
    transaction_date - INTERVAL '90 days' AS date_diff,
    SUM(value) OVER (
        ORDER BY transaction_date
        RANGE BETWEEN INTERVAL '90 days' PRECEDING AND CURRENT ROW
    ) AS rolling_90d_value_sum
FROM transactions
ORDER BY transaction_date;

MySQL

SELECT
    transaction_date,
    DATE_SUB(transaction_date, INTERVAL 90 DAY) AS date_diff,
    SUM(value) OVER (
        ORDER BY transaction_date
        RANGE BETWEEN INTERVAL 90 DAY PRECEDING AND CURRENT ROW
    ) AS rolling_90d_value_sum
FROM transactions
ORDER BY transaction_date;

方法2：自连接（兼容老版本数据库） #

如果数据库不支持窗口函数，可以用自连接筛选日期范围后聚合：

SELECT
    t1.transaction_date,
    DATE_SUB(t1.transaction_date, INTERVAL 90 DAY) AS date_diff,
    SUM(t2.value) AS rolling_90d_value_sum
FROM transactions t1
LEFT JOIN transactions t2
    ON t2.transaction_date BETWEEN DATE_SUB(t1.transaction_date, INTERVAL 90 DAY) AND t1.transaction_date
GROUP BY t1.transaction_date, date_diff
ORDER BY t1.transaction_date;

注意：确保transaction_date字段是日期类型，如果是字符串需先转换为日期格式（比如STR_TO_DATE(transaction_date, '%d-%m-%Y')），避免日期计算错误。

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