如何自定义SQL数据检索结果表？现有查询未达预期寻求修正

执行Select * from table1得到如下数据，表结构及插入语句如下：

CREATE TABLE Table1 (
    "StayDate" date         NOT NULL,
    "Type"     char(3)      NOT NULL,
    "Rate"     numeric(7,2) NOT NULL,
    "CityNo"   int          NOT NULL,
    "RoomNo"   int          NOT NULL,
    CONSTRAINT PK_Table1 PRIMARY KEY ( "StayDate", "CityNo", "RoomNo" )
);

INSERT INTO Table1 ( "StayDate", "Type", "Rate", "CityNo", "RoomNo" )
VALUES
( '2022-10-03', 'ALL',  10, 5001,        0 ),
( '2022-10-04', 'ALL',  10, 5001,        0 ),
( '2022-10-05', 'ALL', 101, 5001,        0 ),
( '2022-10-06', 'ALL', 101, 5001,        0 ),
( '2022-10-07', 'ALL', 101, 5001,        0 ),
( '2022-10-08', 'ALL', 101, 5001,        0 ),
( '2022-10-09', 'ALL', 101, 5001,        0 ),
( '2022-10-10', 'ALL', 101, 5001,        0 ),
( '2022-10-10', 'ALL', 101, 5001, 10000001 ),
( '2022-10-11', 'ALL',  12, 5001, 10000001 ),
( '2022-10-11', 'ALL',  10, 5001,        0 ),
( '2022-10-12', 'ALL',  12, 5001, 10000001 ),
( '2022-10-12', 'ALL',  10, 5001,        0 ),
( '2022-10-13', 'ALL',  10, 5001,        0 ),
( '2022-10-14', 'ALL',  10, 5001,        0 ),
( '2022-10-15', 'ALL',  10, 5001,        0 );

期望检索出的结果：将同一CityNo、RoomNo下连续相同Rate的日期合并为起始和结束日期，示例格式如下：

尝试的SQL查询：

SELECT
    MIN( "StayDate" ) AS "From Date",
    MAX( "StayDate" ) AS "To Date",
    "Type",
    "Rate",
    "CityNo",
    "RoomNo"
             
FROM
    (
        SELECT
            *,
            COUNT("chn") OVER( ORDER BY "StayDate" ) AS grp
        FROM
            (
                SELECT
                    *,
                    CASE WHEN "Rate" != LAG("Rate") OVER( ORDER BY "StayDate" ) THEN 1 END AS chn
                FROM
                    table1
            ) AS t
    ) AS t
--WHERE
--  "CityNo" = 5001
--  AND
--  "StayDate" BETWEEN '2022-10-03' AND '2022-10-15'

GROUP BY
    "grp",
    "Type",
    "Rate",
    "CityNo",
    "RoomNo"

ORDER BY
    "From Date";

但输出结果不符合预期，需解决该问题。

问题核心是窗口函数未按CityNo和RoomNo分区，导致不同房间的Rate变化被混在一起计算分组，最终合并结果出错。

正确的做法是在LAG和COUNT窗口函数中添加PARTITION BY "CityNo", "RoomNo"，确保每个房间的连续Rate分组独立计算。修改后的SQL如下：

SELECT
    MIN( "StayDate" ) AS "From Date",
    MAX( "StayDate" ) AS "To Date",
    "Type",
    "Rate",
    "CityNo",
    "RoomNo"
FROM
    (
        SELECT
            *,
            COUNT("chn") OVER( PARTITION BY "CityNo", "RoomNo" ORDER BY "StayDate" ) AS grp
        FROM
            (
                SELECT
                    *,
                    CASE WHEN "Rate" != LAG("Rate") OVER( PARTITION BY "CityNo", "RoomNo" ORDER BY "StayDate" ) THEN 1 END AS chn
                FROM
                    table1
            ) AS t
    ) AS t
GROUP BY
    "grp",
    "Type",
    "Rate",
    "CityNo",
    "RoomNo"
ORDER BY
    "CityNo",
    "RoomNo",
    "From Date";

关键说明： #

1. PARTITION BY "CityNo", "RoomNo"：限定窗口函数仅在同一城市、同一房间范围内计算，避免不同房间数据互相干扰。
2. LAG("Rate")：对比当前行与同一房间上一行的Rate，变化时标记为1。
3. COUNT("chn") OVER(...)：通过累计计数生成分组ID，相同连续Rate的行会被分到同一组。
4. 最后按分组ID及其他字段聚合，得到每个连续Rate时间段的起始和结束日期。

执行该SQL即可得到期望结果。

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