如何用Window Function统计任意日期连续3天活跃的用户数

要统计指定日期当天连续3天活跃的用户数，核心是识别用户在该日期及前两日均有活跃记录，这里最适合使用**LAG()**窗口函数（也可反向用LEAD()实现）。

实现逻辑与示例 #

方法1：用LAG()精准匹配日期

1. 按user_id分组，对每个用户的活跃日期升序排序
2. 通过LAG()窗口函数获取该用户前1天、前2天的活跃日期
3. 筛选出当前日期为目标日期，且前两日日期连续的用户，去重后统计数量

以统计2022-11-03的用户数为例，SQL代码（MySQL环境）：

SELECT COUNT(DISTINCT user_id) AS consecutive_active_users
FROM (
    SELECT 
        user_id,
        active_date,
        -- 获取前1天的活跃日期
        LAG(active_date, 1) OVER (PARTITION BY user_id ORDER BY active_date) AS prev_1_day,
        -- 获取前2天的活跃日期
        LAG(active_date, 2) OVER (PARTITION BY user_id ORDER BY active_date) AS prev_2_day
    FROM your_table_name
) t
WHERE active_date = '2022-11-03'
  AND prev_1_day = DATE_SUB('2022-11-03', INTERVAL 1 DAY)
  AND prev_2_day = DATE_SUB('2022-11-03', INTERVAL 2 DAY);

方法2：用ROW_NUMBER()分组识别连续周期

也可通过ROW_NUMBER()将连续活跃的日期归为同一分组，再筛选包含目标日期且分组内天数≥3的用户：

SELECT COUNT(DISTINCT user_id) AS consecutive_active_users
FROM (
    SELECT 
        user_id,
        active_date,
        -- 用日期减去行号，连续日期会得到相同的分组键
        DATE_SUB(active_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY active_date) DAY) AS group_key
    FROM your_table_name
) t
WHERE active_date BETWEEN DATE_SUB('2022-11-03', INTERVAL 2 DAY) AND '2022-11-03'
GROUP BY user_id, group_key
HAVING COUNT(*) = 3;

两种方法中，LAG()更贴合需求，能直接定位目标日期的前两日活跃记录；ROW_NUMBER()则适合批量识别所有连续活跃的时间段。

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