如何编写SQL实现连续同维度记录合并为日期区间的加载逻辑

需求说明 #

需要将按日期存储的用户地点明细数据，按ID+Name维度，把连续相同Place的记录合并为单条，取连续段最早日期为From Date、最晚日期为To Date，最终输出结构为ID、Name、Place、From Date、To Date的数据集。

样例源数据 #

期望输出结果 #

核心实现逻辑 #

这是典型的SQL *间隙与岛屿（Gaps and Islands）*问题，通过窗口函数生成两组行号做差，即可自动识别连续相同值的分段：

• 第一组行号：按ID、Name分区，按Date升序排序，给每个用户的所有记录生成连续递增的全局行号
• 第二组行号：按ID、Name、Place分区，按Date升序排序，给每个用户同个地点下的记录生成组内连续递增的行号
• 两组行号做差得到分组标记：同一连续相同地点的分段内，两组行号同步递增、差值固定；地点发生切换时，组内行号会重置从1开始计数，差值发生跳变，自然区分不同连续段
• 最后按ID、Name、Place+分组标记聚合，取分段内最小、最大日期即为起止日期

可直接运行的SQL代码 #

以下写法适用于MySQL 8.0+、PostgreSQL、Hive、Spark SQL、SQL Server等所有支持标准窗口函数的数据库，假设源表名为user_place_raw：

WITH base_with_rn AS (
    SELECT
        ID,
        Name,
        Place,
        `Date`,
        -- 生成用户维度全局行号
        ROW_NUMBER() OVER (PARTITION BY ID, Name ORDER BY `Date`) AS rn_user,
        -- 生成用户+地点维度组内行号
        ROW_NUMBER() OVER (PARTITION BY ID, Name, Place ORDER BY `Date`) AS rn_user_place
    FROM user_place_raw
)
SELECT
    ID,
    Name,
    Place,
    MIN(`Date`) AS `From Date`,
    MAX(`Date`) AS `To Date`
FROM base_with_rn
GROUP BY
    ID,
    Name,
    Place,
    rn_user - rn_user_place -- 连续段分组标记
ORDER BY
    `From Date`,
    ID;

注意事项 #

• 若使用不支持窗口函数的老版本MySQL（5.x版本），需要通过自定义用户变量实现相同的行号计算逻辑，执行性能会低于窗口函数写法
• Date字段建议使用标准DATE/DATETIME类型存储，避免字符串格式日期排序时出现顺序错乱
• 如果存在同一用户同一天多条同地点记录，可先对源表按ID、Name、Place、Date去重后再计算，避免结果异常

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