如何编写SQL实现带周期性的窗口求和查询？

测试数据 #

WITH temp_data AS (SELECT '1' as customer_id, 0 as weekday, 1 as trip_count
UNION ALL
SELECT '1' as customer_id, 1 as weekday, 8 as trip_count
UNION ALL
SELECT '1' as customer_id, 2 as weekday, 5 as trip_count
UNION ALL
SELECT '1' as customer_id, 3 as weekday, 10 as trip_count
UNION ALL
SELECT '1' as customer_id, 4 as weekday, 6 as trip_count
UNION ALL
SELECT '1' as customer_id, 5 as weekday, 5 as trip_count
UNION ALL
SELECT '1' as customer_id, 6 as weekday, 3 as trip_count
UNION ALL
SELECT '2' as customer_id, 0 as weekday, 5 as trip_count
UNION ALL
SELECT '2' as customer_id, 1 as weekday, 4 as trip_count
UNION ALL
SELECT '2' as customer_id, 2 as weekday, 9 as trip_count
UNION ALL
SELECT '2' as customer_id, 3 as weekday, 7 as trip_count
UNION ALL
SELECT '2' as customer_id, 4 as weekday, 6 as trip_count
UNION ALL
SELECT '2' as customer_id, 5 as weekday, 10 as trip_count
UNION ALL
SELECT '2' as customer_id, 6 as weekday, 9 as trip_count
)

需求 #

为每个客户的每个工作日计算环形5天窗口内的总行程数：

• 常规日期（如工作日3）：求和范围为前后各2天，即工作日1、2、3、4、5
• 边缘日期（如工作日1）：因一周是7天，需循环取数，求和范围为工作日6、0、1、2、3
  预期total_value列结果为：[22, 27, 30, 34, 29, 25, 23, 22, 27, 30, 34, 29, 25, 23]

原SQL问题分析 #

原方案试图通过eff_weekday分区+行偏移窗口实现，但行偏移是基于数据排序后的物理位置，无法处理跨一周首尾的环形场景（比如工作日1无法取到前面的工作日6），因此结果不符合预期。

正确实现方案 #

方案1：自连接+模运算判断窗口范围 #

利用自连接关联同一客户的所有工作日，通过模运算判断目标工作日是否在当前日的环形5天窗口内，再求和：

WITH temp_data AS (
    SELECT '1' as customer_id, 0 as weekday, 1 as trip_count UNION ALL
    SELECT '1' as customer_id, 1 as weekday, 8 as trip_count UNION ALL
    SELECT '1' as customer_id, 2 as weekday, 5 as trip_count UNION ALL
    SELECT '1' as customer_id, 3 as weekday, 10 as trip_count UNION ALL
    SELECT '1' as customer_id, 4 as weekday, 6 as trip_count UNION ALL
    SELECT '1' as customer_id, 5 as weekday, 5 as trip_count UNION ALL
    SELECT '1' as customer_id, 6 as weekday, 3 as trip_count UNION ALL
    SELECT '2' as customer_id, 0 as weekday, 5 as trip_count UNION ALL
    SELECT '2' as customer_id, 1 as weekday, 4 as trip_count UNION ALL
    SELECT '2' as customer_id, 2 as weekday, 9 as trip_count UNION ALL
    SELECT '2' as customer_id, 3 as weekday, 7 as trip_count UNION ALL
    SELECT '2' as customer_id, 4 as weekday, 6 as trip_count UNION ALL
    SELECT '2' as customer_id, 5 as weekday, 10 as trip_count UNION ALL
    SELECT '2' as customer_id, 6 as weekday, 9 as trip_count
)
SELECT
    t1.customer_id,
    t1.weekday,
    t1.trip_count,
    SUM(t2.trip_count) AS total_value
FROM temp_data t1
JOIN temp_data t2 ON t1.customer_id = t2.customer_id
WHERE (t2.weekday - t1.weekday + 7) % 7 <= 2 
   OR (t2.weekday - t1.weekday + 7) % 7 >= 5
GROUP BY t1.customer_id, t1.weekday, t1.trip_count
ORDER BY t1.customer_id, t1.weekday;

方案说明

• 自连接确保只关联同一客户的数据
• 条件(t2.weekday - t1.weekday +7) %7将差值转换为0-6的正数，判断该值是否为0、1、2（当前日及后2天）或5、6（前2天，对应环形的倒数第2、1天），精准覆盖5天窗口
• 分组求和得到每个工作日的环形窗口总行程数

方案2：扩展数据+范围窗口（适用于支持范围窗口的SQL引擎） #

通过复制数据并偏移7天，将环形窗口转换为连续的数值范围，再用范围窗口函数求和：

WITH temp_data AS (
    SELECT '1' as customer_id, 0 as weekday, 1 as trip_count UNION ALL
    SELECT '1' as customer_id, 1 as weekday, 8 as trip_count UNION ALL
    SELECT '1' as customer_id, 2 as weekday, 5 as trip_count UNION ALL
    SELECT '1' as customer_id, 3 as weekday, 10 as trip_count UNION ALL
    SELECT '1' as customer_id, 4 as weekday, 6 as trip_count UNION ALL
    SELECT '1' as customer_id, 5 as weekday, 5 as trip_count UNION ALL
    SELECT '1' as customer_id, 6 as weekday, 3 as trip_count UNION ALL
    SELECT '2' as customer_id, 0 as weekday, 5 as trip_count UNION ALL
    SELECT '2' as customer_id, 1 as weekday, 4 as trip_count UNION ALL
    SELECT '2' as customer_id, 2 as weekday, 9 as trip_count UNION ALL
    SELECT '2' as customer_id, 3 as weekday, 7 as trip_count UNION ALL
    SELECT '2' as customer_id, 4 as weekday, 6 as trip_count UNION ALL
    SELECT '2' as customer_id, 5 as weekday, 10 as trip_count UNION ALL
    SELECT '2' as customer_id, 6 as weekday, 9 as trip_count
),
extended_data AS (
    -- 原数据
    SELECT customer_id, weekday, trip_count FROM temp_data
    -- 复制数据并偏移7天，模拟环形扩展
    UNION ALL
    SELECT customer_id, weekday + 7, trip_count FROM temp_data
)
SELECT 
    customer_id,
    weekday AS original_weekday,
    trip_count,
    SUM(trip_count) OVER (
        PARTITION BY customer_id
        ORDER BY weekday
        -- 取当前日前后各2天的数值范围
        RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS total_value
FROM extended_data
-- 只保留原工作日的结果
WHERE weekday < 7
ORDER BY customer_id, original_weekday;

方案说明

• 扩展数据将原工作日0-6复制为7-13，让环形的首尾变成连续的数值（比如原工作日6后面跟着7（原0）、8（原1））
• 范围窗口RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING基于数值范围求和，自动覆盖环形场景
• 最后筛选出原工作日（<7）的结果，得到预期值

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