基于Datetime列按分组及≥1个月间隔划分Rank的技术需求

现有成员数据包含MID、Group、File Type及活跃时间Create_Date，需按MID、Group、File Type分组，当组内连续Create_Date间隔≥1个月时，为该组内的日期范围划分Rank。

输入示例 #

期望输出 #

实现方案（SQL） #

这是典型的间隙与岛屿问题，核心是识别分组内的日期连续区间，当相邻日期间隔≥1个月时开启新的Rank，可通过窗口函数实现：

代码示例 #

WITH date_diff AS (
    SELECT
        MID,
        "Group",
        "File Type",
        Create_Date,
        -- 计算当前日期与组内上一行日期的月份差
        DATEDIFF(month, LAG(Create_Date) OVER (PARTITION BY MID, "Group", "File Type" ORDER BY Create_Date), Create_Date) AS month_gap
    FROM your_table_name
),
group_flags AS (
    SELECT
        *,
        -- 首次行或间隔≥1个月时标记为新组起点
        CASE WHEN month_gap IS NULL OR month_gap >= 1 THEN 1 ELSE 0 END AS is_new_rank
    FROM date_diff
)
SELECT
    MID,
    "Group",
    "File Type",
    Create_Date,
    -- 累加新组标记得到最终Rank
    SUM(is_new_rank) OVER (ORDER BY Create_Date) AS Rank
FROM group_flags
ORDER BY Create_Date;

注意事项 #

• 不同数据库的日期差函数语法不同：
  • PostgreSQL 使用 DATE_PART('month', AGE(Create_Date, LAG(Create_Date) ...))
  • Oracle 使用 MONTHS_BETWEEN(Create_Date, LAG(Create_Date) ...)
  • 需根据实际使用的数据库调整日期差计算逻辑。

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