Conditional Windows Function应用：计算非活跃周的活跃金额均值

给定如下数据集：

需针对所有Inactive_Streak不为0的记录，计算该记录对应的前序连续活跃周期中Amount的均值，输出字段包括：Login、Week_date、Active_Streak（对应前序活跃周期的最大Active_Streak）、Inactive_Streak、AVG_active_Amount，期望输出如下：

以下是基于窗口函数的实现步骤：

完整SQL代码 #

WITH sorted_data AS (
    SELECT 
        *,
        -- 标记连续活跃周期组：从非活跃切换到活跃时，组号递增
        SUM(CASE WHEN Inactive_Streak = 0 AND LAG(Inactive_Streak, 1, 1) OVER (PARTITION BY Login ORDER BY Week_date) != 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Login ORDER BY Week_date) AS active_group
    FROM your_table
),
active_group_stats AS (
    SELECT 
        Login,
        active_group,
        AVG(Amount) AS avg_amount,
        MAX(Active_Streak) AS max_active_streak
    FROM sorted_data
    WHERE Inactive_Streak = 0
    GROUP BY Login, active_group
),
inactive_with_group AS (
    SELECT 
        s.*,
        -- 定位当前非活跃记录对应的前一个活跃周期组
        LAST_VALUE(CASE WHEN Inactive_Streak = 0 THEN active_group END) OVER (PARTITION BY Login ORDER BY Week_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS prev_active_group,
        -- 标记连续非活跃周期的最后一条记录（匹配期望输出的结果范围）
        CASE WHEN LEAD(Inactive_Streak, 1, 0) OVER (PARTITION BY Login ORDER BY Week_date) = 0 THEN 1 ELSE 0 END AS is_last_inactive
    FROM sorted_data s
    WHERE Inactive_Streak != 0
)
SELECT 
    i.Login,
    i.Week_date,
    a.max_active_streak AS Active_Streak,
    i.Inactive_Streak,
    ROUND(a.avg_amount, 2) AS AVG_active_Amount
FROM inactive_with_group i
JOIN active_group_stats a 
    ON i.Login = a.Login AND i.prev_active_group = a.active_group
WHERE is_last_inactive = 1
ORDER BY i.Week_date;

逻辑解释 #

1. sorted_data：按用户和日期排序，通过窗口函数标记每个连续活跃周期的组号，每次从非活跃状态切换到活跃状态时，组号自动递增。
2. active_group_stats：对每个活跃周期组，计算Amount的均值和该组的最大Active_Streak，为后续非活跃记录提供关联统计值。
3. inactive_with_group：筛选所有非活跃记录，用LAST_VALUE()定位当前非活跃记录对应的前一个活跃组，同时用LEAD()标记每个连续非活跃周期的最后一条记录（匹配期望输出的结果范围）。
4. 最终查询：关联活跃周期统计值，过滤出连续非活跃周期的最后一条记录，输出指定字段并保留两位小数。

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