如何高效实现含SalesId维度的两日期间Running total销售SQL查询

要实现保留SalesId维度的同时计算符合预期的销售累计值，核心是调整窗口函数的**分区（PARTITION BY）和排序（ORDER BY）**规则——不要按SalesId分区，而是按时间维度（如年份）分区，再按销售日期排序来累计。

优化后的SQL查询 #

WITH sales_summary AS (
    SELECT 
        SalesId,
        SUM(Sales) AS number_of_sales, 
        Sales_DATE AS SalesDate,
        ADD_MONTHS(Sales_DATE, -12) AS SalesDatePrevYear
    FROM DWH.L_SALES
    -- 可选：添加日期范围过滤，只处理目标区间数据，提升效率
    -- WHERE Sales_DATE BETWEEN '20200101' AND '20220301'
    GROUP BY SalesId, Sales_DATE 
)
SELECT 
    SalesId,
    number_of_sales,
    SUM(number_of_sales) OVER (
        PARTITION BY EXTRACT(YEAR FROM SalesDate) 
        ORDER BY SalesDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS "running total of sales",
    SalesDate,
    SalesDatePrevYear
FROM sales_summary
ORDER BY SalesDate;

关键说明 #

1. 原累计始终为1的原因
   若按SalesId分区做累计，每个SalesId对应唯一的SalesDate分组，窗口内仅1条数据，因此累计值始终等于当前行的number_of_sales。

2. 窗口函数逻辑解析

   • PARTITION BY EXTRACT(YEAR FROM SalesDate)：按销售日期的年份分区，确保不同年份的累计值独立计算（如2020年的记录不会和2022年的累计叠加）。
   • ORDER BY SalesDate：在每个年份分区内，按日期先后顺序累计销售数量。
   • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：明确累计范围是从分区第一条数据到当前行，部分数据库默认此规则，但显式声明更清晰。

3. 性能优化建议

   • 给Sales_DATE字段创建索引，加速GROUP BY和窗口函数的排序操作。
   • 新增WHERE子句过滤目标日期区间，减少需要处理的数据量。

执行结果 #

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