SQL按日期差分组未得预期结果，需合并连续同酒店入住记录

原始业务数据表 #

预期输出结果 #

问题说明 #

直接按CaseNo, DESTCODE, HotelName分组并取min(CheckInDate)、max(CheckOutDate)的方式，会错误合并间隔其他酒店的同酒店记录，无法得到预期结果。需要实现仅合并连续入住的同酒店记录，非连续的同酒店记录保持独立的逻辑。

解决方案 #

这是典型的"连续相同分组"问题，可通过窗口函数标记连续分组后聚合实现：

WITH ranked_data AS (
    SELECT 
        *,
        -- 标记连续同酒店的分组：上一条记录的酒店与当前相同，且上一条退房日期等于当前入住日期则归为同一组
        SUM(CASE 
            WHEN LAG(HotelName) OVER (PARTITION BY CaseNo ORDER BY CheckInDate) = HotelName 
                 AND LAG(CheckOutDate) OVER (PARTITION BY CaseNo ORDER BY CheckInDate) = CheckInDate
            THEN 0 
            ELSE 1 
        END) OVER (PARTITION BY CaseNo ORDER BY CheckInDate) AS group_id
    FROM your_table_name -- 替换为实际表名
)
SELECT 
    CaseNo,
    DESTCODE,
    HotelName,
    MIN(CheckInDate) AS CheckInDate,
    MAX(CheckOutDate) AS CheckOutDate
FROM ranked_data
GROUP BY CaseNo, DESTCODE, HotelName, group_id
ORDER BY CheckInDate;

逻辑解析 #

1. 分组标记：通过LAG()窗口函数获取当前记录的上一条记录的酒店名称和退房日期，判断是否满足"连续入住同一酒店"的条件（酒店相同且上一条退房日=当前入住日）。不满足时，分组ID加1，生成新的独立分组。
2. 聚合合并：按CaseNo, DESTCODE, HotelName, group_id分组，取每组的最早入住日期和最晚退房日期，实现连续记录的合并，同时保留间隔其他酒店的同酒店记录为独立条目。

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