如何编写SQL查询标记成员是否连续3个月居住同一地址

问题背景 #

现有存储成员地址历史的Addresses表，字段包括ID、FILE_MONTH、ADDRESS，数据示例如下：

需求是生成每个成员一行的结果集，新增SAME_ADDRESS_3_MONTHS标记列，标记成员是否连续3个月居住同一地址。预期结果：

原使用ROW_NUMBER()的CTE查询存在两个问题：返回每个成员多行记录，且错误统计了非连续月份的地址次数，无法满足需求。

正确SQL实现方案 #

核心思路是：先识别同一成员同一地址下的连续月份段，再统计每个段的连续月份数，最后判断每个成员是否存在长度≥3的连续段。

WITH address_groups AS (
    SELECT 
        ID,
        FILE_MONTH,
        ADDRESS,
        -- 判断当前月份与上一个同地址月份是否连续
        CASE 
            WHEN LAG(FILE_MONTH) OVER (PARTITION BY ID, ADDRESS ORDER BY FILE_MONTH) = FILE_MONTH - 1 THEN 0
            ELSE 1
        END AS is_new_group,
        -- 为连续的同地址月份生成分组ID
        SUM(CASE 
                WHEN LAG(FILE_MONTH) OVER (PARTITION BY ID, ADDRESS ORDER BY FILE_MONTH) = FILE_MONTH - 1 THEN 0
                ELSE 1
            END) OVER (PARTITION BY ID, ADDRESS ORDER BY FILE_MONTH) AS group_id
    FROM Addresses
    -- 按需调整时间范围，示例取2025年及以后的数据
    WHERE FILE_MONTH >= '202501'
),
group_counts AS (
    SELECT 
        ID,
        ADDRESS,
        group_id,
        COUNT(*) AS consecutive_months
    FROM address_groups
    GROUP BY ID, ADDRESS, group_id
)
SELECT 
    ID,
    CASE 
        WHEN MAX(consecutive_months) >= 3 THEN 'Y'
        ELSE 'N'
    END AS SAME_ADDRESS_3_MONTHS
FROM group_counts
GROUP BY ID
ORDER BY ID;

代码解释 #

1. address_groups CTE：

   • 用LAG()函数获取同一成员同一地址的上一条记录月份，判断当前月份与上月是否连续（差值为1）。
   • 通过累加is_new_group的值，为每一段连续的同地址月份生成唯一group_id。

2. group_counts CTE：

   • 按ID、ADDRESS、group_id分组，统计每个连续地址段的月份数。

3. 最终查询：

   • 按ID聚合，判断该成员是否存在连续月份数≥3的地址段，生成标记列。

结果验证 #

执行上述SQL后，将得到符合预期的结果：仅ID为5555555的成员标记为'Y'，其余成员标记为'N'。

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