如何在Redshift中对含缺失日期的数据集计算滚动求和？

核心结论：无需循环，用SQL生成完整序列+关联聚合更高效 #

不用写循环，直接通过SQL生成完整日期范围 + 补全所有分组的日期组合，再结合窗口函数就能实现需求，比循环执行单日期查询的效率高得多。

具体实现步骤 #

1. 生成连续的日期序列

先获取表中的最小和最大日期，生成该范围内的所有连续日期：

WITH date_range AS (
    SELECT generate_series(
        (SELECT MIN(to_date(event_date, 'yyyy-mm-dd')) FROM schema_a.table_b),
        (SELECT MAX(to_date(event_date, 'yyyy-mm-dd')) FROM schema_a.table_b),
        INTERVAL '1 day'
    ) AS calc_date
),

2. 补全所有(col_a, col_b, 日期)组合

取出表中唯一的col_a和col_b组合，与日期序列做笛卡尔积，确保每个分组的每一天都有记录：

full_groups AS (
    SELECT DISTINCT tb.col_a, tb.col_b, dr.calc_date
    FROM schema_a.table_b tb
    CROSS JOIN date_range dr
),

3. 填充缺失日期的value为0

将完整组合与原表关联，用COALESCE把缺失的value设为0：

filled_data AS (
    SELECT 
        fg.col_a,
        fg.col_b,
        fg.calc_date,
        COALESCE(tb.value, 0) AS value
    FROM full_groups fg
    LEFT JOIN schema_a.table_b tb
        ON fg.col_a = tb.col_a
        AND fg.col_b = tb.col_b
        AND to_date(tb.event_date, 'yyyy-mm-dd') = fg.calc_date
)

4. 窗口函数计算7天滚动求和

按col_a和col_b分组，日期排序，用窗口函数计算过去7天的求和（可根据你的原SQL逻辑调整窗口范围）：

SELECT
    col_a,
    col_b,
    calc_date AS foo,
    SUM(value) OVER (
        PARTITION BY col_a, col_b
        ORDER BY calc_date
        -- 对应原SQL的"近7天（不含当天）"逻辑：12-20到12-25共6天
        RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT DAY
        -- 如果需要严格排除当天，改成：RANGE BETWEEN INTERVAL '7 days' PRECEDING AND INTERVAL '1 day' PRECEDING
    ) AS rolling_7d_sum
FROM filled_data
ORDER BY col_a, col_b, calc_date;

和Pandas实现的对比 #

Pandas里确实操作更简洁：把日期转成datetime类型，设置col_a, col_b, date为多重索引，用resample('D')补全日期，填充缺失值为0后调用rolling(7).sum()即可。但SQL方案适合直接在数据库端处理大数据量，无需将数据拉到本地，性能优势更明显。

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