SAS中统计ID对应成员最近连续月份数的技术实现问询

需求说明 #

统计每个ID对应的成员最近一段连续月份的计数，若ID的月份记录存在间隔，仅输出最近一段连续月份的总数。

基础信息 #

• 数据集名称：dataset.one
• 样本数据：

• 期望输出：

实现代码（SQL通用版，兼容Hive/Spark SQL等） #

采用经典的「间隙与岛屿」算法实现，代码如下：

WITH t1 AS (
    -- 第一步：将字符串格式月份转成标准日期，生成连续组标记
    SELECT 
        ID,
        Month,
        TO_DATE(Month, 'ddMMMyyyy') AS month_dt,
        ADD_MONTHS(TO_DATE(Month, 'ddMMMyyyy'), -ROW_NUMBER() OVER(PARTITION BY ID ORDER BY TO_DATE(Month, 'ddMMMyyyy') ASC)) AS group_flag
    FROM dataset.one
),
t2 AS (
    -- 第二步：统计每个连续组的月份数和组内最大日期
    SELECT 
        ID,
        group_flag,
        COUNT(*) AS months_ct,
        MAX(month_dt) AS max_dt_of_group
    FROM t1
    GROUP BY ID, group_flag
)
-- 第三步：取每个ID最新的连续组的月份数作为结果
SELECT 
    ID,
    months_ct
FROM t2
QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY max_dt_of_group DESC) = 1;

逻辑说明 #

1. 连续组标记逻辑：对每个ID的月份按时间升序排序后，用当前月份减去排序序号对应的月份数，得到的group_flag相同的记录属于同一连续月份段。比如2021年2月是ID72的第5条记录，减5个月得到2020年9月；2021年3月是第6条记录，减6个月同样得到2020年9月，因此属于同一连续组。
2. 最新组筛选逻辑：每个连续组的最大日期越晚，说明是越新的连续段，取最大日期对应的组的计数，即为要求的最近一段连续月份数。

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