sql - 如何按值分组以识别最小和最大日期，但按特定顺序排列？

Question

我试图在这里找到一些东西，但没有找到我的用例。我希望你能帮助我。首先我的 table 可用:

<表类="s-表"><头>STATION_NUMBERPART_NO预订日期<正文>11111一个2021-08-01 6:00:0011111一个2021-08-01 6:05:0011111一个2021-08-01 6:07:0011111一个2021-08-01 6:08:0011111B2021-08-01 7:10:0011111B2021-08-01 7:13:0011111B2021-08-01 7:15:0011111B2021-08-01 7:25:0011111一个2021-08-01 8:10:0011111一个2021-08-01 8:12:0011111一个2021-08-01 8:16:0011111一个2021-08-01 8:19:0022222一个2021-08-01 6:00:0022222一个2021-08-01 6:05:0022222一个2021-08-01 6:07:0022222一个2021-08-01 6:08:0022222B2021-08-01 7:10:0022222B2021-08-01 7:13:0022222B2021-08-01 7:15:0022222B2021-08-01 7:25:0022222一个2021-08-01 8:10:0022222一个2021-08-01 8:12:0022222一个2021-08-01 8:16:0022222一个2021-08-01 8:19:00

以下是我想要得到的结果:

<表类="s-表"><头>STATION_NUMBERPART_NOSTART_BOOK_DATEEND_BOOK_DATE<正文>11111一个2021-08-01 6:00:002021-08-01 6:08:0011111B2021-08-01 7:10:002021-08-01 7:25:0011111一个2021-08-01 8:10:002021-08-01 8:19:0022222一个2021-08-01 6:00:002021-08-01 6:08:0022222B2021-08-01 7:10:002021-08-01 7:25:0022222一个2021-08-01 8:10:002021-08-01 8:19:00

我试图用这个查询来解决它，但没有达到我的预期

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_NUMBER 分组，但我需要从时间顺序角度来看的第一个和最后一个 BOOK_DATE。 PART_NUMBER 和/或 STATION_NUMBER 的变化是我计算新线路的触发因素。

Answer 1

您的问题属于被称为“差距和孤岛问题”的问题类别(如果您想进一步研究，请谷歌搜索该短语)。

在 Oracle 11 及更早版本中，您可以使用解析函数来获得所需的结果。该方法被称为“tabibitosan 法”或“固定差异法”。

关键步骤首先出现(在下面 with 子句的子查询中):计算仅按站号分组(分区)的序列号，以及按站号和部件号分开计算的序号。在部件号相同的连续行序列中，差异是恒定的，然后当新的此类序列开始时，差异会跳到不同的值。然后在外部查询中使用它进行分组。

with 
  prep as (
    select pd.*,
           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 grp
    from   prod_date pd
  )
select station_number, part_no, min(book_date) as start_book_date,
       max(book_date) as end_book_date
from   prep
group  by station_number, part_no, grp
order  by station_number, start_book_date
;

STATION_NUMBER PART_NO START_BOOK_DATE     END_BOOK_DATE      
-------------- ------- ------------------- -------------------
         11111 A       2021-08-01 06:00:00 2021-08-01 06:08:00
         11111 B       2021-08-01 07:10:00 2021-08-01 07:25:00
         11111 A       2021-08-01 08:10:00 2021-08-01 08:19:00
         22222 A       2021-08-01 06:00:00 2021-08-01 06:08:00
         22222 B       2021-08-01 07:10:00 2021-08-01 07:25:00
         22222 A       2021-08-01 08:10:00 2021-08-01 08:19:00