SQL如何按值分组识别最小最大日期且符合时间顺序拆分要求

我尝试查找相关方案但未找到匹配我的使用场景，希望获得协助。

现有表结构 #

期望查询结果 #

已尝试的查询 #

SELECT PART_NO,
      STATION_NUMBER,
      GROUP_NUMBER,
      MIN(BOOK_DATE) START_BOOK_DATE,
      MAX(BOOK_DATE) END_BOOK_DATE
FROM(
    SELECT PART_NO,
           STATION_NUMBER,
           BOOK_DATE,
           IS_CHANGED,
           RANK() OVER (ORDER BY PART_NO,IS_CHANGED) GROUP_NUMBER
    FROM(
        SELECT PART_NO,
        STATION_NUMBER,
        BOOK_DATE,
        CASE 
            WHEN NOT LEAD(PART_NO, 1) OVER (ORDER BY BOOK_DATE) = PART_NO
            THEN ROWNUM
            ELSE 0
        END IS_CHANGED
        FROM PROD_DATA
        WHERE STATION_NUMBER in ('11111','22222')
        AND BOOK_DATE BETWEEN TO_TIMESTAMP('01.08.2021 05:00:00', 'DD.MM.YYYY HH24:MI:SS') and TO_TIMESTAMP('01.08.2021 12:00:00', 'DD.MM.YYYY HH24:MI:SS')
        ORDER BY BOOK_DATE
    )ORDER BY BOOK_DATE
) GROUP BY STATION_NUMBER, PART_NO, GROUP_NUMBER

需求说明 #

需要按STATION_NUMBER和PART_NO分组，但要从时间先后维度获取每个连续相同组合的首个和最后一个BOOK_DATE，只要PART_NUMBER或STATION_NUMBER发生变化，就生成新的统计行。

这是典型的连续序列孤岛检测场景，核心逻辑是给每个连续的STATION_NUMBER+PART_NO组合分配独立的分组标识，再按分组聚合取首尾时间即可。

正确查询语句（适配Oracle语法） #

SELECT 
    STATION_NUMBER,
    PART_NO,
    MIN(BOOK_DATE) AS START_BOOK_DATE,
    MAX(BOOK_DATE) AS END_BOOK_DATE
FROM (
    SELECT 
        STATION_NUMBER,
        PART_NO,
        BOOK_DATE,
        -- 生成连续相同组合的分组标识
        ROW_NUMBER() OVER (PARTITION BY STATION_NUMBER ORDER BY BOOK_DATE) 
        - ROW_NUMBER() OVER (PARTITION BY STATION_NUMBER, PART_NO ORDER BY BOOK_DATE) AS GROUP_ID
    FROM PROD_DATA
    WHERE 
        STATION_NUMBER IN ('11111','22222')
        AND BOOK_DATE BETWEEN TO_TIMESTAMP('01.08.2021 05:00:00', 'DD.MM.YYYY HH24:MI:SS') 
                          AND TO_TIMESTAMP('01.08.2021 12:00:00', 'DD.MM.YYYY HH24:MI:SS')
) t
GROUP BY STATION_NUMBER, PART_NO, GROUP_ID
ORDER BY STATION_NUMBER, START_BOOK_DATE;

原查询问题说明 #

原有查询的两个核心错误导致结果不符合预期：

1. 仅通过LEAD判断PART_NO变化，未校验STATION_NUMBER的变化，跨站点的同PART_NO数据会被误判为同一组
2. 分组排序逻辑没有按站点分区，两个站点的同类型序列会被合并为同一个GROUP_NUMBER，导致聚合结果错误。

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