SQL实现同表总累计、近7天、近30天销售额列计算

问题根源 #

你当前使用的ROWS BETWEEN 6 PRECEDING AND CURRENT ROW是按物理行偏移量划定统计范围，只有当每个ID每天恰好1条记录时，行偏移和日偏移才会等价；单日多笔交易时，行计数和自然日计数不匹配，就会出现统计错误。
要实现按自然日范围的滑动统计，需要将窗口帧类型从ROWS替换为RANGE——RANGE基于排序字段的逻辑值范围匹配记录，而非物理行位置。 #

标准实现方案（支持SQL:2003标准窗口函数的数据库） #

适用于SQL Server 2022+、PostgreSQL 11+、BigQuery、Spark SQL、ClickHouse等主流新版本数据库，全量累计逻辑保留原有写法即可，近7天、近30天累计改用RANGE+日期间隔定义帧范围：

SELECT
    ID
    ,DATE
    ,SALES
    -- 全量历史累计销售额，原有逻辑无需调整
    ,SUM(SALES) OVER (PARTITION BY ID ORDER BY DATE) AS CS
    -- 近7自然日累计（含当日，覆盖当前日期往前6天的所有交易）
    ,SUM(SALES) OVER (
        PARTITION BY ID 
        ORDER BY CAST(DATE AS DATE)
        RANGE BETWEEN INTERVAL '6 day' PRECEDING AND CURRENT ROW
    ) AS CS7
    -- 近30自然日累计（含当日，覆盖当前日期往前29天的所有交易）
    ,SUM(SALES) OVER (
        PARTITION BY ID 
        ORDER BY CAST(DATE AS DATE)
        RANGE BETWEEN INTERVAL '29 day' PRECEDING AND CURRENT ROW
    ) AS CS30
FROM SALESTABLE

注意事项 #

• 如果DATE字段本身是不带时分秒的DATE类型，可以省略CAST(DATE AS DATE)转换；如果是带时间的DATETIME/TIMESTAMP类型，必须先转为纯日期类型，否则会按时间戳精确差值计算，导致跨天统计错误。
• 该写法会自动覆盖同一天的多笔交易，只要日期落在统计区间内，无论同天有多少条记录都会被纳入求和范围，完全匹配你给出的样例预期结果。

老版本数据库兼容方案 #

如果使用的数据库版本不支持RANGE后接INTERVAL间隔（如SQL Server 2019及更早版本、MySQL 5.x），可以通过自关联+条件聚合实现，在(ID, DATE)字段建联合索引的前提下，性能可以支撑亿级数据量查询：

SELECT
    t1.ID
    ,t1.DATE
    ,t1.SALES
    ,SUM(t2.SALES) AS CS
    ,SUM(CASE WHEN CAST(t2.DATE AS DATE) >= DATEADD(day, -6, CAST(t1.DATE AS DATE)) THEN t2.SALES ELSE 0 END) AS CS7
    ,SUM(CASE WHEN CAST(t2.DATE AS DATE) >= DATEADD(day, -29, CAST(t1.DATE AS DATE)) THEN t2.SALES ELSE 0 END) AS CS30
FROM SALESTABLE t1
LEFT JOIN SALESTABLE t2
    ON t1.ID = t2.ID
    AND CAST(t2.DATE AS DATE) <= CAST(t1.DATE AS DATE)
GROUP BY t1.ID, t1.DATE, t1.SALES

不同数据库日期函数适配 #

• MySQL：将DATEADD(day, -6, CAST(t1.DATE AS DATE))替换为DATE_SUB(CAST(t1.DATE AS DATE), INTERVAL 6 DAY)
• Oracle：将日期偏移部分替换为CAST(t1.DATE AS DATE) - 6即可

结果校验 #

对照你提供的样例数据：

• 2022-06-14的首笔9元交易，距离上一笔交易日期6月3日间隔11天，超出7天范围，因此CS7值为9，和预期一致
• 2022-06-15的5元交易，仅统计6月14、15两日的交易，CS7为9+5=14，和预期一致
• 2022-06-14的第二笔10元交易，统计14日两笔+15日一笔的总和，CS7为9+5+10=24，和预期一致
• 全量累计CS会按日期顺序累加所有历史交易，和原有脚本返回结果完全一致

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