SQL使用LEAD/LAG计算酒店价格变动区间起止日期的实现问题

实现逻辑 #

你需要的是连续相同价格/备注的时间区间统计，属于SQL经典的「孤岛问题」，可以通过标记累加的方式实现，天然支持价格回落的场景，不需要额外做奇偶行过滤，逻辑更简洁性能更优：

1. 先按业务维度（酒店、竞争对手、渠道、价格类型）分区，判断当前行与上一行的价格、备注是否一致，不一致的行打变更标记
2. 对变更标记做累加，得到分组ID，同一个分组ID下的所有行都是连续的相同价格/备注记录
3. 按分组ID聚合，取每组的最小日期为区间起始，最大日期为区间结束即可

完整实现代码 #

WITH RateChangeMark AS (
    -- 第一步：打价格/备注变更标记
    SELECT  
        HotelId, CompetitorId, RateShopDate, ChannelId, RequestedRateType, Rate, RateRemark,
        -- 与上一行对比，价格或备注不同则标记为1，否则为0
        CASE WHEN 
            COALESCE(Rate, 0) <> COALESCE(LAG(Rate) OVER (
                PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType 
                ORDER BY RateShopDate
            ), 0)
            OR COALESCE(RateRemark, '') <> COALESCE(LAG(RateRemark) OVER (
                PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType 
                ORDER BY RateShopDate
            ), '')
        THEN 1 ELSE 0 END AS IsChange
    FROM #TempRateShop
),
RateGroup AS (
    -- 第二步：累加标记生成分组ID
    SELECT 
        *,
        SUM(IsChange) OVER (
            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType 
            ORDER BY RateShopDate
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM RateChangeMark
)
-- 第三步：分组聚合得到价格区间
SELECT 
    HotelId, CompetitorId, ChannelId, RequestedRateType,
    Rate, RateRemark,
    MIN(RateShopDate) AS ShopStartDate,
    MAX(RateShopDate) AS ShopEndDate
FROM RateGroup
GROUP BY HotelId, CompetitorId, ChannelId, RequestedRateType, Rate, RateRemark, GroupId
ORDER BY ShopStartDate

方案优势 #

• 天然支持价格回落场景：哪怕价格后期回到之前的数值，只要中间有过变更，分组ID就会不同，不会把非连续的同价格记录合并
• 逻辑简洁易维护：不需要多层子查询过滤首尾变动行，也不需要额外的奇偶行判断
• 性能更优：仅需要两次窗口函数扫描，数据量大的场景下优势更明显

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