基于行生成日期范围，日期断档则新增行的SQL实现需求

需求说明：
将按日存储的User_ID、SPOT记录转换为以下格式：

• 当User_ID或SPOT发生变化时，生成新行
• 当同一User_ID、SPOT的日期出现断档时，生成新行
• 相同User_ID、SPOT且日期连续的记录，合并为一行并显示开始日期和结束日期

实现代码（通用SQL方言，适配PostgreSQL/MySQL等） #

WITH ranked_data AS (
    SELECT 
        User_ID,
        SPOT,
        Date,
        -- 判断当前记录是否属于新分组：日期不连续/用户/点位变化时标记为1
        CASE 
            WHEN LAG(Date) OVER (PARTITION BY User_ID, SPOT ORDER BY Date) = Date - INTERVAL '1 day'
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM your_table_name
),
grouped_data AS (
    SELECT 
        User_ID,
        SPOT,
        Date,
        -- 累计求和生成唯一分组ID，连续区间的记录会共享同一ID
        SUM(is_new_group) OVER (PARTITION BY User_ID, SPOT ORDER BY Date) AS group_id
    FROM ranked_data
)
SELECT 
    User_ID,
    SPOT,
    MIN(Date) AS Start_Date,
    MAX(Date) AS End_Date
FROM grouped_data
GROUP BY User_ID, SPOT, group_id
ORDER BY User_ID, SPOT, Start_Date;

代码说明 #

1. ranked_data 阶段：通过LAG()窗口函数获取同用户、同点位的上一条记录日期，判断当前日期是否与前一天连续。如果不连续，标记为新分组的起始。
2. grouped_data 阶段：对标记的新分组标识做累计求和，为每个连续区间生成唯一的group_id。
3. 最终聚合：按用户、点位、分组ID聚合，取每组的最小日期作为开始日期，最大日期作为结束日期，得到合并后的结果。

适配MySQL的小调整 #

如果使用MySQL，日期差的判断语句需要修改为：

WHEN LAG(Date) OVER (PARTITION BY User_ID, SPOT ORDER BY Date) = DATE_SUB(Date, INTERVAL 1 DAY)

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