在Impala SQL中统计连续重复事件至首次不满足的次数

需求拆解 #

按「最近购买颜色」分组，统计从第一条记录开始连续匹配「使用颜色=最近购买颜色」的次数，首次出现不匹配后，后续所有记录均保持该最大计数值；若第一条记录就不匹配，整组计数值为0。

解决方案 #

通过多阶段窗口函数实现，核心思路是先定位每组首次不匹配的时间点，再基于该时间点计算有效累计数并固化后续值：

WITH step1 AS (
    SELECT 
        *,
        -- 标记当前行是否满足匹配条件
        CASE WHEN 使用颜色 = 最近购买颜色 THEN 1 ELSE 0 END AS is_match,
        -- 找出每组中首次出现不匹配的下单时间，无则取极大值（确保后续逻辑兼容）
        MIN(CASE WHEN 使用颜色 != 最近购买颜色 THEN 下单时间 ELSE NULL END) OVER (PARTITION BY 最近购买颜色) AS first_mismatch_time
    FROM your_table
    ORDER BY 最近购买颜色, 下单时间
),
step2 AS (
    SELECT 
        *,
        -- 按时间顺序累加匹配次数（仅在首次不匹配前有效）
        SUM(is_match) OVER (PARTITION BY 最近购买颜色 ORDER BY 下单时间 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS temp_count,
        -- 计算每组的最大有效计数值（首次不匹配前的累计次数）
        MAX(CASE WHEN 下单时间 < first_mismatch_time THEN temp_count ELSE 0 END) OVER (PARTITION BY 最近购买颜色) AS max_valid_count
    FROM step1
)
SELECT 
    下单时间,
    最近购买颜色,
    使用颜色,
    -- 生成最终结果
    CASE 
        WHEN max_valid_count = 0 THEN 0
        WHEN 下单时间 < first_mismatch_time THEN temp_count
        ELSE max_valid_count
    END AS 预期结果
FROM step2
ORDER BY 最近购买颜色, 下单时间;

逻辑说明 #

1. step1：

   • 用is_match标记每行是否匹配；
   • 用窗口函数MIN() OVER (PARTITION BY 最近购买颜色)定位每组第一个不匹配的时间点，为后续划分有效计数区间做准备。

2. step2：

   • 用SUM() OVER ()按时间顺序累加匹配次数，得到实时累计的temp_count；
   • 用MAX() OVER ()提取首次不匹配前的最大累计数，作为整组后续记录的固定计数值。

3. 最终查询：

   • 若max_valid_count为0（说明第一条记录就不匹配），直接返回0；
   • 若当前行在首次不匹配之前，返回实时累计的temp_count；
   • 首次不匹配之后的所有行，返回固定的max_valid_count。

示例验证 #

将示例数据代入上述SQL，会输出与预期完全一致的结果：

• Red组：前3行累计到3，第4行开始保持3；
• Green组：第一条就不匹配，所有行返回0。

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