基于Datetime列识别日期区间排名，筛选活动间隔超3个月的成员

需求说明 #

现有数据表包含成员ID（header 1）、所属组（header 2）、活跃时间（Create Date）三个字段，需要：

• 识别同一成员同一组内，活跃日期间隔超过3个月的记录
• 为连续活跃周期（间隔不超3个月）内的记录分配相同Rank，间隔超3个月时Rank递增

目标结果示例 #

解决方案（SQL实现） #

通用SQL版本 #

WITH ranked_records AS (
    SELECT 
        "header 1",
        "header 2",
        "Create Date",
        -- 获取上一条同组同成员的活跃时间，计算月份差
        CASE
            WHEN LAG("Create Date") OVER (PARTITION BY "header 1", "header 2" ORDER BY "Create Date") IS NOT NULL
            THEN ABS(DATEDIFF(MONTH, LAG("Create Date") OVER (PARTITION BY "header 1", "header 2" ORDER BY "Create Date"), "Create Date"))
            ELSE 0
        END AS month_diff,
        -- 标记是否触发新Rank
        CASE 
            WHEN LAG("Create Date") OVER (PARTITION BY "header 1", "header 2" ORDER BY "Create Date") IS NULL
            THEN 0
            WHEN ABS(DATEDIFF(MONTH, LAG("Create Date") OVER (PARTITION BY "header 1", "header 2" ORDER BY "Create Date"), "Create Date")) > 3
            THEN 1
            ELSE 0
        END AS is_new_rank
    FROM your_table_name
)
SELECT 
    "header 1",
    "header 2",
    "Create Date",
    -- 累积求和生成Rank，初始值为1
    1 + SUM(is_new_rank) OVER (PARTITION BY "header 1", "header 2" ORDER BY "Create Date") AS Rank
FROM ranked_records
ORDER BY "header 1", "header 2", "Create Date";

数据库适配说明 #

不同数据库的日期差函数略有不同，可按需调整：

• MySQL：用TIMESTAMPDIFF(MONTH, prev_date, curr_date)替代DATEDIFF(MONTH, ...)
• PostgreSQL：用EXTRACT(MONTH FROM AGE(curr_date, prev_date))计算月份差
• Oracle：用MONTHS_BETWEEN(curr_date, prev_date)获取月份差

逻辑说明 #

1. 用LAG()窗口函数按成员+组分组、按时间排序，获取每条记录的上一条活跃时间
2. 计算当前记录与上一条的月份差，判断是否超过3个月，标记为is_new_rank（1表示需要递增Rank，0表示不需要）
3. 用SUM() OVER()累积求和标记值，再加1得到最终Rank——第一条记录默认Rank为1，之后每遇到一次超3个月的间隔，Rank自动加1

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