按时间顺序统计客户Wait连续次数并更新至ContactStats表

源表 Customer #

统计规则 #

需要统计每个ID的wait-streak（等待连续次数），规则如下：

• 从ID的最新行开始，若Action为Wait，则wait-streak = 1
• 若同一ID的下一条时间顺序行的Action也为Wait，则wait-streak加1
• 按时间顺序遍历该ID的所有行，连续则持续累加
• 若存在多段Wait连续次数，需找出最大次数，并保留最新段的次数

疑问与最终要求 #

• 统计时需同时考虑日期和时间字段，合并为新的日期时间字段更简便，可避免多字段排序的复杂逻辑，降低出错概率。
• 最终结果需写入新表ContactStats：
  • 最大连续次数存入BiggestWaitStreak
  • 最新连续次数存入NewestWaitStreak
  • 若最新行为是Wait则NewestIsWait设为1

目标表 ContactStats #

WITH CustomerWithDT AS (
    -- 合并日期和时间为标准datetime字段，统一排序逻辑
    SELECT 
        ID,
        Action,
        STR_TO_DATE(CONCAT(日期, ' ', 时间), '%d.%m.%Y %H:%i') AS event_datetime
    FROM Customer
),
StreakGroups AS (
    -- 为每个ID的连续Wait行为分组：倒序排序时，非Wait行为触发分组ID递增
    SELECT 
        ID,
        Action,
        event_datetime,
        SUM(CASE WHEN Action != 'Wait' THEN 1 ELSE 0 END) OVER (
            PARTITION BY ID 
            ORDER BY event_datetime DESC
        ) AS streak_group
    FROM CustomerWithDT
),
StreakStats AS (
    -- 统计每个Wait分组的连续次数，并标记是否为最新分组
    SELECT 
        ID,
        streak_group,
        COUNT(*) AS streak_count,
        CASE WHEN streak_group = 0 THEN 1 ELSE 0 END AS is_newest_group
    FROM StreakGroups
    WHERE Action = 'Wait'
    GROUP BY ID, streak_group
),
IDStats AS (
    -- 聚合每个ID的最终统计结果
    SELECT 
        s.ID,
        MAX(s.streak_count) AS BiggestWaitStreak,
        COALESCE(MAX(CASE WHEN s.is_newest_group = 1 THEN s.streak_count END), 0) AS NewestWaitStreak,
        CASE WHEN c.Action = 'Wait' THEN 1 ELSE 0 END AS NewestIsWait
    FROM StreakStats s
    LEFT JOIN (
        -- 获取每个ID的最新行为
        SELECT ID, Action
        FROM CustomerWithDT
        WHERE (ID, event_datetime) IN (
            SELECT ID, MAX(event_datetime)
            FROM CustomerWithDT
            GROUP BY ID
        )
    ) c ON s.ID = c.ID
    GROUP BY s.ID, c.Action
)
-- 将结果写入ContactStats表（支持插入或更新）
INSERT INTO ContactStats (ID, BiggestWaitStreak, NewestWaitStreak, NewestIsWait)
SELECT ID, BiggestWaitStreak, NewestWaitStreak, NewestIsWait
FROM IDStats
ON DUPLICATE KEY UPDATE
    BiggestWaitStreak = VALUES(BiggestWaitStreak),
    NewestWaitStreak = VALUES(NewestWaitStreak),
    NewestIsWait = VALUES(NewestIsWait);

关键步骤说明 #

1. 日期时间合并：用STR_TO_DATE将原表的日期和时间拼接转换为标准datetime类型，若使用SQL Server，可替换为CONVERT(DATETIME, CONCAT(日期, ' ', 时间), 104)（104对应dd.mm.yyyy格式）。
2. 连续分组标记：通过倒序排序后的累计非Wait行为，把连续的Wait归为同一分组，非Wait行为会生成新的分组ID。
3. 分组统计：对每个Wait分组计算连续次数，同时标记最新的分组（倒序后的第一个分组，即streak_group=0）。
4. 结果聚合：提取每个ID的最大连续次数、最新分组的连续次数，结合最新行为判断NewestIsWait，最终写入目标表。

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