MySQL中计算用户最近连续Flag=1的次数及起止日期

要提取每个Client+User分组下最近一次连续Flag=1的次数、起始日期和结束日期，可以通过以下步骤实现：

步骤1：标记连续Flag=1的分组 #

首先用窗口函数识别连续的Flag=1片段，为每个片段分配唯一的组ID：

WITH consecutive_groups AS (
    SELECT 
        Client,
        User,
        Date,
        Flag,
        -- 标记连续1的分组：当前Flag为1且前一个不是1，或是第一个1时，生成新分组标记
        SUM(CASE WHEN Flag = 1 AND COALESCE(LAG(Flag) OVER (PARTITION BY Client, User ORDER BY Date), 0) != 1 THEN 1 ELSE 0 END) 
            OVER (PARTITION BY Client, User ORDER BY Date) AS group_id
    FROM db.tbl
),

步骤2：计算每个连续段的统计信息 #

对每个连续的Flag=1片段，计算其起始日期、结束日期和总次数：

group_stats AS (
    SELECT 
        Client,
        User,
        group_id,
        MIN(Date) AS start_date,
        MAX(Date) AS end_date,
        COUNT(*) AS consecutive_count
    FROM consecutive_groups
    WHERE Flag = 1  -- 仅处理Flag=1的行
    GROUP BY Client, User, group_id
)

步骤3：筛选每个用户的最新连续段 #

在每个Client+User分组中，取最大的group_id（对应最新的连续段），得到最终结果：

SELECT 
    Client,
    User,
    start_date,
    end_date,
    consecutive_count
FROM group_stats
WHERE (Client, User, group_id) IN (
    SELECT Client, User, MAX(group_id)
    FROM group_stats
    GROUP BY Client, User
)
ORDER BY Client, User;

补充说明 #

• 如果某个用户没有任何Flag=1的记录，上述查询不会返回该用户的数据；若需包含这类用户并显示0或NULL，可以通过左连接原表实现。
• COALESCE函数用于处理每个Client+User的第一条记录（此时LAG(Flag)返回NULL，视为0），确保第一个1能被正确标记为新分组。

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