求助：DENSE_RANK()统计活跃月数时，日期间隔后计数未正确重置

你的核心问题是用DENSE_RANK分区时选错了字段，导致间隔后的计数没能从1重新开始。正确的做法是先把连续活跃的月份划分为同一个组，再在每个组内从1开始计数。

修正步骤拆解 #

1. 计算相邻日期间隔，标记新组起点：算出每个记录和上一条的间隔月数，当间隔超过1个月（或你定义的活跃中断阈值），或是该ID的第一条记录时，标记为新组的开始。
2. 生成活跃组ID：通过累积求和标记值，把连续活跃的月份归为同一个组。
3. 组内生成连续计数：按组分区，对日期排序后用ROW_NUMBER()生成从1开始的活跃月数。

修正后的SQL代码 #

WITH step1 AS (
    SELECT
        GP,
        ID,
        Date,
        Age,
        -- 计算与上一条记录的间隔月数，首行返回NULL
        MONTHS_BETWEEN(Date, LAG(Date) OVER (PARTITION BY GP, ID ORDER BY Date ASC)) AS date_diff,
        -- 标记新组：首行 或 间隔超过1个月时为1，否则为0
        CASE
            WHEN LAG(Date) OVER (PARTITION BY GP, ID ORDER BY Date ASC) IS NULL THEN 1
            WHEN MONTHS_BETWEEN(Date, LAG(Date) OVER (PARTITION BY GP, ID ORDER BY Date ASC)) > 1 THEN 1
            ELSE 0
        END AS new_group_flag
    FROM TABLE2
),
step2 AS (
    SELECT
        *,
        -- 累积求和生成组ID，连续活跃的月份会有相同的group_id
        SUM(new_group_flag) OVER (PARTITION BY GP, ID ORDER BY Date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM step1
)
SELECT
    GP,
    ID,
    Date,
    -- 每个组内从1开始计数
    ROW_NUMBER() OVER (PARTITION BY GP, ID, group_id ORDER BY Date ASC) AS Active_MOS
FROM step2;

代码说明 #

• 原代码用DENSE_RANK()并分区DATEDIFF是错误的，因为DATEDIFF是单条记录的间隔值，无法用来划分连续活跃组。
• 用SUM(new_group_flag)累积生成的group_id，能把所有连续活跃的月份归为一组，中断后的月份会生成新的group_id。
• 最后用ROW_NUMBER()在每个组内计数，就能实现间隔后从1重新开始的效果。

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