在BigQuery中计算30日内工作日与周末的维修量平均值

要同时获取当前行对应类型和相反类型的过去30天平均维修量，你需要放弃原SQL中PARTITION BY day_type的分区逻辑，改用条件聚合+滑动窗口的方式，在同一个日期窗口内筛选出目标类型的记录进行计算。

示例SQL代码 #

假设你的day_type字段只有两类取值：'工作日'和'周末'（假期已被标记为'周末'），可以用以下语句实现需求：

SELECT
    WORK_DT,
    day_type,
    Completed_Repairs,
    -- 当前类型的过去30天平均维修量
    AVG(CASE WHEN day_type = t.day_type THEN Completed_Repairs END) OVER(
        ORDER BY UNIX_DATE(WORK_DT) 
        RANGE BETWEEN 30 PRECEDING AND CURRENT ROW
    ) AS same_type_30d_avg,
    -- 相反类型的过去30天平均维修量
    AVG(CASE 
        WHEN t.day_type = '工作日' AND day_type = '周末' THEN Completed_Repairs
        WHEN t.day_type = '周末' AND day_type = '工作日' THEN Completed_Repairs
        ELSE NULL 
    END) OVER(
        ORDER BY UNIX_DATE(WORK_DT) 
        RANGE BETWEEN 30 PRECEDING AND CURRENT ROW
    ) AS opposite_type_30d_avg
FROM your_table t;

逻辑说明 #

1. 原SQL的PARTITION BY day_type会将数据拆分为两个独立分区，每个分区内只有同类型的日期，因此无法计算相反类型的平均值。
2. 改用无分区的滑动窗口（按WORK_DT排序，范围为过去30天到当前行），通过CASE WHEN语句在窗口内筛选出需要计算的目标类型记录：
   • same_type_30d_avg：仅统计窗口内与当前行day_type一致的记录的维修量平均值
   • opposite_type_30d_avg：仅统计窗口内与当前行day_type相反的记录的维修量平均值
3. 如果你的day_type有其他潜在取值，可以调整CASE条件的判断逻辑，确保只筛选出目标两类数据。

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