如何识别拥有连续6个月及以上注册期的ID？max+partition by id方法无效

数据片段 #

问题分析 #

你之前用MAX()结合PARTITION BY ID的思路无法解决问题，因为MAX()只能获取单个字段的极值（比如最晚的年份），无法判断时间区间的连续性。要解决这个问题，核心是先把每个ID的连续注册期划分成独立的组，再统计每组的长度，筛选出存在长度≥6的组的ID。

解决方案 #

以下是通用SQL实现，利用窗口函数划分连续组并统计长度：

方法1：通过日期连续性判断（更准确，适配整月周期） #

WITH ranked_data AS (
    SELECT 
        ID,
        start_dt,
        end_dt,
        -- 判断当前周期的起始日是否是上一个周期结束日的次日，是则连续，否则开启新组
        CASE WHEN DATE_ADD(LAG(end_dt) OVER (PARTITION BY ID ORDER BY start_dt), INTERVAL 1 DAY) = start_dt 
             THEN 0 
             ELSE 1 
        END AS is_new_group
    FROM your_table
),
grouped_data AS (
    SELECT 
        ID,
        start_dt,
        end_dt,
        -- 累计求和生成连续组ID，每次遇到新组时累加1
        SUM(is_new_group) OVER (PARTITION BY ID ORDER BY start_dt) AS group_id
    FROM ranked_data
),
group_length AS (
    SELECT 
        ID,
        group_id,
        COUNT(*) AS consecutive_months
    FROM grouped_data
    GROUP BY ID, group_id
)
-- 筛选出存在连续6个月及以上组的ID
SELECT DISTINCT ID
FROM group_length
WHERE consecutive_months >= 6;

方法2：通过月份差判断（适用于严格按月连续的场景） #

如果你的数据保证每个周期都是整月，也可以通过计算月份间隔判断连续：

WITH ranked_data AS (
    SELECT 
        ID,
        start_dt,
        end_dt,
        -- 计算当前记录与上一条记录的月份差（跨年份需乘以12）
        EXTRACT(MONTH FROM start_dt) - EXTRACT(MONTH FROM LAG(start_dt) OVER (PARTITION BY ID ORDER BY start_dt)) 
        + 12*(EXTRACT(YEAR FROM start_dt) - EXTRACT(YEAR FROM LAG(start_dt) OVER (PARTITION BY ID ORDER BY start_dt))) AS month_diff
    FROM your_table
),
grouped_data AS (
    SELECT 
        ID,
        start_dt,
        end_dt,
        -- 月份差不为1时开启新组，累计求和生成组ID
        SUM(CASE WHEN month_diff = 1 OR month_diff IS NULL THEN 0 ELSE 1 END) OVER (PARTITION BY ID ORDER BY start_dt) AS group_id
    FROM ranked_data
),
group_length AS (
    SELECT 
        ID,
        group_id,
        COUNT(*) AS consecutive_months
    FROM grouped_data
    GROUP BY ID, group_id
)
SELECT DISTINCT ID
FROM group_length
WHERE consecutive_months >= 6;

代码说明 #

1. ranked_data：给每个ID的记录按时间排序，标记是否需要开启新的连续组。
2. grouped_data：通过累计求和生成连续组的唯一标识，同一连续周期的记录会被分到同一个group_id下。
3. group_length：统计每个ID每个连续组的月份数量。
4. 最后筛选出存在至少一个连续组长度≥6的ID。

运行上述代码后，会返回ID=1（因为它有2015年1-6月连续6个月的注册期），ID=2则不会被选中。

内容的提问来源于stack exchange，提问作者Prajwal Mani Pradhan