Redshift SQL按固定30天窗口滚动求和全年访问量方案咨询

核心逻辑 #

以2021-01-28为首个周期起始日，计算每条日数据距离起始日的天数差，每30天划分为一个周期，共取12个周期后分组汇总访问量即可。

基础实现（无空周期补0） #

假设你的日粒度统计表名为daily_views，包含date（日期类型）、views（数值类型）两个字段，直接执行以下SQL：

WITH period_tag AS (
    SELECT
        views,
        -- 计算周期序号，从1开始计数
        FLOOR(DATEDIFF(day, '2021-01-28'::DATE, date) / 30) + 1 AS series
    FROM daily_views
    -- 过滤12个周期覆盖的日期范围，排除无效数据
    WHERE date >= '2021-01-28'::DATE
      AND date < DATEADD(day, 30 * 12, '2021-01-28'::DATE)
)
SELECT
    series,
    SUM(views) AS views
FROM period_tag
GROUP BY series
ORDER BY series;

扩展实现（空周期补0） #

如果需要保留没有访问数据的周期，显示访问量为0，先生成1-12的连续周期序列再关联统计结果：

WITH -- 生成1-12连续周期序号
all_series AS (
    SELECT generate_series(1, 12) AS series
),
period_stats AS (
    SELECT
        FLOOR(DATEDIFF(day, '2021-01-28'::DATE, date) / 30) + 1 AS series,
        SUM(views) AS views
    FROM daily_views
    WHERE date >= '2021-01-28'::DATE
      AND date < DATEADD(day, 360, '2021-01-28'::DATE)
    GROUP BY series
)
SELECT
    a.series,
    COALESCE(p.views, 0) AS views
FROM all_series a
LEFT JOIN period_stats p ON a.series = p.series
ORDER BY a.series;

可选补充（展示周期起止日期） #

如果需要在结果中明确每个周期的时间范围，在输出字段中添加周期起止计算逻辑即可：

WITH period_tag AS (
    SELECT
        views,
        FLOOR(DATEDIFF(day, '2021-01-28'::DATE, date) / 30) + 1 AS series
    FROM daily_views
    WHERE date >= '2021-01-28'::DATE
      AND date < DATEADD(day, 360, '2021-01-28'::DATE)
)
SELECT
    series,
    DATEADD(day, (series - 1) * 30, '2021-01-28'::DATE) AS period_start,
    DATEADD(day, series * 30 - 1, '2021-01-28'::DATE) AS period_end,
    SUM(views) AS views
FROM period_tag
GROUP BY series
ORDER BY series;

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