SQL-LEAD和LAG查詢

從這個邏輯來看：

SELECT  HotelId, CompetitorId,
        RateShopDate AS ShopDate,
        Rate, RateRemark,
        RowNumber
    FROM
    (
        SELECT  HotelId, CompetitorId, RateShopDate, ChannelId, RequestedRateType, Rate, RateRemark,
                PreviousRate, PreviousRateRemark, NextRate, NextRateRemark,
                ROW_NUMBER() OVER ( ORDER BY HotelId, CompetitorId, ChannelId, RequestedRateType, RateShopDate ) AS RowNumber
            FROM
            (
                SELECT  HotelId, CompetitorId, RateShopDate, ChannelId, RequestedRateType, Rate, RateRemark, RowNumber,
                        LAG( Rate ) OVER
                        (
                            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType
                            ORDER BY RowNumber
                        ) AS PreviousRate,
                        LAG( RateRemark ) OVER
                        (
                            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType
                            ORDER BY RowNumber
                        ) AS PreviousRateRemark,
                        LEAD( Rate ) OVER
                        (
                            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType
                            ORDER BY RowNumber
                        ) AS NextRate,
                        LEAD( RateRemark ) OVER
                        (
                            PARTITION BY HotelId, CompetitorId, ChannelId, RequestedRateType
                            ORDER BY RowNumber
                        ) AS NextRateRemark
                    FROM
                    (
                        SELECT  HotelId, CompetitorId, RateShopDate, ChannelId, RequestedRateType, Rate, RateRemark,
                                ROW_NUMBER() OVER ( ORDER BY HotelId, CompetitorId, ChannelId, RequestedRateType, RateShopDate ) AS RowNumber
                            FROM #TempRateShop
                    ) Rates
            ) PrevNextRates
                WHERE COALESCE(Rate, 0) <> COALESCE(PreviousRate, 0)
                OR COALESCE(RateRemark, '') <> COALESCE(PreviousRateRemark, '')
                OR COALESCE(Rate, 0) <> COALESCE(NextRate, 0)
                OR COALESCE(RateRemark, '') <> COALESCE(NextRateRemark, '')
    ) FilteredRates
        ORDER BY RateShopDate

我正在嘗試獲取每次費率變化的開始和結束 ShopDate。但是由于費率有可能從一個變為另一個并回到原來的狀態，我遇到了一些困難；例如，費率在一段時間內從 90 美元漲到 95 美元，然后又回落到 90 美元。

我想要得到的是：

我想出的唯一解決方案是使用 LEAD/LAG 并按 RowNumber 列對其進行排序，然后按奇數/偶數對其進行過濾……但我并不認為這是最好的解決方案。

    LEAD ( RateShopDate ) OVER
    (
        ORDER BY RowNumber
    ) AS ShopEndDate

uj5u.com熱心網友回復：

這是一種間隙和島嶼問題。

有很多解決方案，這里是一個：

• 使用LAG在每個組的開始，以確定行
• 運行條件COUNT為我們提供了每個組的 ID
• 然后簡單地按該 ID 分組

WITH PrevValues AS (
    SELECT *,
      IsStart = CASE WHEN LAG(rs.Rate, 1, -1) OVER (PARTITION BY rs.HotelId, rs.CompetitorId
                    ORDER BY rs.RateShopDate) <> rs.Rate THEN 1 END
    FROM #RateShop rs
),
Groupings AS (
    SELECT *,
      GroupId = COUNT(IsStart) OVER (PARTITION BY rs.HotelId, rs.CompetitorId
                    ORDER BY rs.RateShopDate ROWS UNBOUNDED PRECEDING)
    FROM PrevValues rs
)
SELECT
  rs.HotelId,
  rs.CompetitorId,
  StartDate  = MIN(rs.RateShopDate),
  EndDate    = MAX(rs.RateShopDate),
  Rate       = MIN(rs.Rate),
  RateRemark = STRING_AGG(rs.RateRemark, '; ')
FROM Groupings rs
GROUP BY
  rs.HotelId,
  rs.CompetitorId,
  rs.GroupId;

資料庫<>小提琴

標籤：sql sql-server 查询语句 sql-server-2019

上一篇：自底向上遞回求和（最低級別只有值）

下一篇：如何從列中選擇拆分字串