如何按时间阈值将成员-日期行分组为多Episode（剧集）？

示例数据 #

需求说明 #

按Member分组，以每组内的日期为基础，每间隔30天划分一个新的Episode：从该组第一条记录的日期开始，30天内的记录归为Episode 1；当某条记录与上一条记录的间隔超过30天时，开启新的Episode，以此类推，支持任意数量的Episode分组。此前的SQL仅能处理最多两个Episode的场景，无法适配多Episode需求。

解决方案 #

方案一：基于相邻记录间隔的动态分组（匹配示例逻辑） #

该方案通过判断当前记录与上一条记录的日期间隔，自动递增Episode编号，完全适配多Episode场景：

WITH ranked_data AS (
    SELECT 
        Date,
        Member,
        ROW_NUMBER() OVER (PARTITION BY Member ORDER BY CAST(Date AS DATE)) AS rn,
        CAST(Date AS DATE) AS dt
    FROM your_table_name
),
episode_calculation AS (
    SELECT 
        Date,
        Member,
        dt,
        rn,
        DATEDIFF(day, LAG(dt) OVER (PARTITION BY Member ORDER BY dt), dt) AS days_since_prev
    FROM ranked_data
),
final_episode AS (
    SELECT 
        Date,
        Member,
        SUM(CASE 
                WHEN rn = 1 THEN 1
                WHEN days_since_prev > 30 THEN 1
                ELSE 0
            END) OVER (PARTITION BY Member ORDER BY rn) AS Episode
    FROM episode_calculation
)
UPDATE t
SET t.Episode = f.Episode
FROM your_table_name t
JOIN final_episode f ON t.Date = f.Date AND t.Member = f.Member;

逻辑说明

1. ranked_data：按成员分组，对日期升序排序，同时将文本格式的日期转换为日期类型，方便后续计算。
2. episode_calculation：计算当前记录与上一条记录的日期间隔天数。
3. final_episode：通过累计求和生成Episode编号——第一条记录默认是Episode 1，后续只要与上一条记录间隔超过30天，就将Episode编号加1，自动生成连续的多Episode分组。

方案二：基于组内最早日期的周期分组 #

如果需求是从组内最早日期开始，每30天固定划分一个Episode，可使用更简洁的整数除法逻辑：

WITH member_dates AS (
    SELECT 
        Member,
        CAST(Date AS DATE) AS dt,
        DATEDIFF(day, MIN(CAST(Date AS DATE)) OVER (PARTITION BY Member), CAST(Date AS DATE)) AS days_since_first
    FROM your_table_name
),
episode_groups AS (
    SELECT 
        Member,
        dt,
        (days_since_first / 30) + 1 AS Episode
    FROM member_dates
)
UPDATE t
SET t.Episode = eg.Episode
FROM your_table_name t
JOIN episode_groups eg ON t.Member = eg.Member AND CAST(t.Date AS DATE) = eg.dt;

逻辑说明

计算每个日期与组内最早日期的间隔天数，用间隔天数除以30取整数（0-29天为0，30-59天为1，以此类推），加1后得到对应的Episode编号，适合固定周期的分组需求。

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