基于日期条件的30天滚动销售额求和优化方案咨询

需求说明 #

需要对数据表中的销售额进行计算，得到每个日期及之后30天的销售额总和。

原始数据 #

期望结果 #

新增total 30d（30天总计）列，统计当前日期至30天后的销售额总和：

说明：以2021-08-08为例，统计该日期至30天后（2021-09-07）的销售额，即14+35+35=84。

当前低效实现 #

用户当前使用的查询通过笛卡尔积关联计算，数据量较大时效率低下：

with temp as (
    select date, sum(sales) as sales_total
from test_table
group by 1,2
)
, temp2 as (
SELECT a.date, SUM(b.sales_total) total
FROM temp a, temp b
WHERE b.date >= a.date AND b.date <= a.date + interval '30' day
GROUP BY a.date
)
select a.date, a.sales, b.total 
from test_table a 
JOIN temp2 b on a.date = b.date

优化方案：使用窗口函数 #

利用窗口函数的范围窗口特性，可避免笛卡尔积，大幅提升查询效率。以下以PostgreSQL为例实现：

优先方案：先聚合日期再计算 #

先合并同一日期的销售额，减少后续计算量：

WITH daily_sales AS (
    SELECT 
        date,
        SUM(sales) AS daily_total
    FROM test_table
    GROUP BY date
)
SELECT 
    t.date,
    t.sales,
    ds.rolling_30d_total AS "total 30d（30天总计）"
FROM test_table t
JOIN (
    SELECT 
        date,
        SUM(daily_total) OVER (
            ORDER BY date
            RANGE BETWEEN CURRENT ROW AND INTERVAL '30 days' FOLLOWING
        ) AS rolling_30d_total
    FROM daily_sales
) ds ON t.date = ds.date
ORDER BY t.date;

简化方案：直接在原表计算 #

如果不需要提前聚合日期，也可直接在原表上使用窗口函数（性能略逊于优先方案）：

SELECT 
    date,
    sales,
    SUM(sales) OVER (
        ORDER BY date
        RANGE BETWEEN CURRENT ROW AND INTERVAL '30 days' FOLLOWING
    ) AS "total 30d（30天总计）"
FROM test_table
ORDER BY date;

关键优化点 #

• 避免笛卡尔积关联，窗口函数通过一次扫描完成计算，时间复杂度从O(n²)降至O(n log n)
• 先按日期聚合销售额，减少窗口函数处理的行数，进一步提升性能
• 确保date字段存在索引，可大幅加速窗口函数的排序和范围计算

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