单查询实现设备指定日期固定总和及近1/3天滚动求和

问题分析 #

你的原SQL存在两个核心问题：

• 未筛选目标日期范围（2024-10-03至2024-10-05），导致结果包含所有日期；
• 未实现「10月3-5日num_visits固定总和」的需求，窗口函数无法直接生成跨行的固定值。

修正后的SQL语句 #

WITH device_total AS (
    -- 计算每个设备在10月3-5日的num_visits总和
    SELECT 
        device,
        SUM(num_visits) AS total_3_5
    FROM tbl
    WHERE day BETWEEN '2024-10-03' AND '2024-10-05'
    GROUP BY device
)
SELECT 
    t.day,
    t.country,
    t.device,
    dt.total_3_5, -- 10月3-5日的固定总和
    -- 前1天滚动求和（取前一天的num_visits）
    SUM(t.num_visits) OVER (PARTITION BY t.device ORDER BY t.day ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_1day_sum,
    -- 前3天滚动求和（取往前3天的num_visits总和）
    SUM(t.num_visits) OVER (PARTITION BY t.device ORDER BY t.day ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS prev_3days_sum
FROM tbl t
JOIN device_total dt ON t.device = dt.device
WHERE t.day BETWEEN '2024-10-03' AND '2024-10-05'
ORDER BY t.device, t.day;

代码解释 #

1. CTE device_total：单独计算每个设备在目标日期内的总访问量，确保后续每行都能引用这个固定值。
2. 主查询筛选目标日期：通过WHERE子句只保留2024-10-03至2024-10-05的数据，解决结果范围不符的问题。
3. 前1天滚动求和：使用ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING精准取当前日期的前一行数据求和，也就是前一天的访问量。
4. 前3天滚动求和：使用ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING取当前日期往前3天（不含当天）的所有数据求和，实现3天滚动统计。

注意事项 #

• 确保表中day字段是日期类型，避免字符串匹配错误；
• 如果存在日期缺失的情况，可考虑用GENERATE_SERIES（PostgreSQL）或DATEADD+递归（MySQL）生成连续日期，再关联原表补全缺失数据，保证滚动求和的准确性。

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