嘿，这个问题在用户行为分析场景里十分常见！要基于你提供的数据集计算每个用户从最早日期开始的连续活跃天数，这里有两种高效的实现方案，你可以根据实际情况选择：

方案一：通用方案（不依赖现有rank/day_diff字段） #

如果你的数据集后续可能缺失rank或day_diff字段，这个通用方案会更稳妥。核心思路是通过窗口函数识别日期的间断点，将连续的日期划分为同一组，再统计每组的天数。

完整SQL代码 #

WITH session_date_gaps AS (
    -- 第一步：计算每个用户当前日期与前一天的间隔
    SELECT 
        user_id,
        day_session,
        DATEDIFF(day_session, LAG(day_session) OVER (PARTITION BY user_id ORDER BY day_session)) AS date_interval
    FROM your_table_name  -- 替换成你的实际表名
),
continuous_groups AS (
    -- 第二步：为连续的日期序列生成唯一分组标识
    SELECT 
        user_id,
        day_session,
        SUM(CASE WHEN date_interval != 1 OR date_interval IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY user_id ORDER BY day_session) AS group_id
    FROM session_date_gaps
)
-- 第三步：统计每个用户每个连续组的起始日期、结束日期和连续天数
SELECT 
    user_id,
    MIN(day_session) AS continuous_start_date,
    MAX(day_session) AS continuous_end_date,
    DATEDIFF(MAX(day_session), MIN(day_session)) + 1 AS continuous_days
FROM continuous_groups
GROUP BY user_id, group_id
ORDER BY user_id, continuous_start_date;

代码解释 #

• session_date_gaps：使用LAG()窗口函数获取每个用户上一条记录的活跃日期，再用DATEDIFF()计算当前日期与前一天的间隔。连续日期的间隔为1，间断时间隔大于1，第一条记录的间隔为NULL。
• continuous_groups：通过累加间断点（间隔不为1或为NULL的情况）生成分组ID，同一个连续日期序列的分组ID相同。
• 最终统计：按用户和分组ID聚合，计算每组的起始/结束日期，以及连续天数（结束日期减起始日期加1，因为要包含首尾两天）。

方案二：利用现有rank字段优化 #

既然你的数据集已经有rank字段（按用户日期排序的序号），可以利用这个字段简化计算，效率更高。

完整SQL代码 #

SELECT 
    user_id,
    MIN(day_session) AS continuous_start_date,
    MAX(day_session) AS continuous_end_date,
    DATEDIFF(MAX(day_session), MIN(day_session)) + 1 AS continuous_days
FROM (
    SELECT 
        user_id,
        day_session,
        -- 核心：同一连续序列的日期减去(rank-1)天后会得到相同基准日期
        DATE_SUB(day_session, INTERVAL (rank - 1) DAY) AS group_key
    FROM your_table_name  -- 替换成你的实际表名
) AS grouped_data
GROUP BY user_id, group_key
ORDER BY user_id, continuous_start_date;

代码解释 #

• 对于连续的日期，rank每增加1，日期也增加1天，因此day_session - INTERVAL (rank-1) DAY会得到该连续序列的起始基准日期，同一个连续序列的基准日期完全相同。
• 按用户和基准日期分组后，就能直接统计每个连续序列的起始、结束日期和天数。

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