如何通過數值分組來確定最小和最大日期，但要有特定的順序？ -有解無憂

我試圖在這里找到一些東西，但我沒有找到我的用例。我希望你能幫助我。首先，我的表格是可用的：

書籍日期A

STATION_NUMBER[/th	PART_NO
11111

2021-08-01 6:00:00

11111 11111 A 2021-08-01 6:05:00 2021-08-01 6:05:00

11111 11111 A 2021-08-01 6:07:00 2021-08-01 6:07:00

11111 11111 A 2021-08-01 6:08:00 2021-08-01 6:08:00

2021-08-01 7:10:00 2021-08-01 7:10:00

B 2021-08-01 7:13:00 2021-08-01 7:13:00

B 2021-08-01 7:15:00 2021-08-01 7:15:00

B 2021-08-01 7:25:00 2021-08-01 7:25:00

A 2021-08-01 8:10:00 2021-08-01 8:10:00

11111 11111 A 2021-08-01 8:12:00 2021-08-01 8:12:00

11111 11111 A 2021-08-01 8:16:00 2021-08-01 8:16:00

11111 11111 A 2021-08-01 8:19:00 2021-08-01 8:19:00

22222A 2021-08-01 6:00:00 2021-08-01 6:00:00

2021-08-01 6:05:00 2021-08-01 6:05:00

2021-08-01 6:07:00 2021-08-01 6:07:00

2021-08-01 6:08:00 2021-08-01 6:08:00

B 2021-08-01 7:10:00 2021-08-01 7:10:00

B 2021-08-01 7:13:00 2021-08-01 7:13:00

B 2021-08-01 7:15:00 2021-08-01 7:15:00

B 2021-08-01 7:25:00 2021-08-01 7:25:00

A 2021-08-01 8:10:00 2021-08-01 8:10:00

2021-08-01 8:12:00 2021-08-01 8:12:00

2021-08-01 8:16:00 2021-08-01 8:16:00

A 2021-08-01 8:19:00 2021-08-01 8:19:00

以下是我想得到的結果：

STATION_NUMBER[/th PART_NO起始書目日期結束書目日期END_BOOK_DATE 11111A 2021-08-01 6:00:00 2021-08-01 6:08:00 2021-08-01 6:08:00

11111B 2021-08-01 7:10:00 2021-08-01 7:10:00 2021-08-01 7:25:00 2021-08-01 7:25:00

11111A 2021-08-01 8:10:00 2021-08-01 8:10:00 2021-08-01 8:19:00 2021-08-01 8:19:00

A 2021-08-01 6:00:00 2021-08-01 6:00:00 2021-08-01 6:08:00 2021-08-01 6:08:00

22222B 2021-08-01 7:10:002021-08-01 7:25:00 2021-08-01 7:25:00

A 2021-08-01 8:10:002021-08-01 8:19:00 2021-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 SELECT PART_NO. 
            WHEN NOT LEAD（PART_NO, 1) OVER (ORDER BY BOOK_DATE) = PART_NO
            THEN ROWNUM
            ELSE0
        END IS_CHANGED
        FROM PROD_DATA
        WHERE STATION_NUMBER in ('11111','2222')
        AND BOOK_DATE BETWEEN TO_TIMESTAMP('01.08.2021 05:00:00', 'DD.MM.YYY HH24:MI:SS') and TO_TIMESTAMP('01.08.2021 12:00:00', 'DD.MM.YYYYY 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的變化是我計算新行的觸發器。

uj5u.com熱心網友回復：

你的問題屬于被稱為 "差距和島嶼問題 "的一類問題（如果你想進一步研究，可以在谷歌上搜索這個短語）。

在 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
;

車站號，零件號，開始日期，結束日期。     
-------------- ------- ------------------- -------------------
         11111 A 20210801 06: 00: 00 20210801 06: 08: 00
         11111 B 20210801 07：10: 00 20210801 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 20210801 06: 08:00: 00.
         22222 B 20210801 07：10: 00 20210801 07: 25: 00
         22222 A 2021-08-01 08：10: 00 20210801 08: 19:00: 00.

uj5u.com熱心網友回復：

從Oracle 12來看，這就是MATCH_RECOGNIZE的作用：

SELECT *
FROM prod_date
MATCH_RECOGNIZE(
  PARTITION BY station_number
  訂單 BY book_date
  衡量標準
    First(part_no) AS part_no,
    FIRST(book_date) as start_book_date,
    最后(book_date) as end_book_date
  ONE ROW PER MATCH
  PATTERN (same_part )
  DEFINE (same_part )
    same_part AS FIRST(part_no) = part_no
)

其中，對于示例資料：

CREATE TABLE prod_date (STATION_NUMBER, PART_NO, BOOK_DATE) AS
SELECT 11111, 'A'/span>,    DATE '2021-08-01'   INTERVAL '6。 00: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'A'/span>,    DATE '2021-08-01'   INTERVAL '6。 05: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'A'/span>,    DATE '2021-08-01'   INTERVAL '6。 07: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'A'/span>,    DATE '2021-08-01'   INTERVAL '6。 08: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'B'/span>,    DATE '2021-08-01'   INTERVAL '7。 10: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'B'/span>,    DATE '2021-08-01'   INTERVAL '7。 13: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'B'/span>,    DATE '2021-08-01'   INTERVAL '7。 15: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'B'/span>,    DATE '2021-08-01'   INTERVAL '7。 25: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'A'/span>,    DATE '2021-08-01'   INTERVAL '8。 10: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'A'/span>,    DATE '2021-08-01'   INTERVAL '8。 12: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'A'/span>,    DATE '2021-08-01'   INTERVAL '8。 16: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 11111, 'A'/span>,    DATE '2021-08-01'   INTERVAL '8。 19: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'A'/span>,    DATE '2021-08-01'   INTERVAL '6。 00: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'A'/span>,    DATE '2021-08-01'   INTERVAL '6。 05: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'A'/span>,    DATE '2021-08-01'   INTERVAL '6。 07: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'A'/span>,    DATE '2021-08-01'   INTERVAL '6。 08: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'B'/span>,    DATE '2021-08-01'   INTERVAL '7。 10: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'B'/span>,    DATE '2021-08-01'   INTERVAL '7。 13: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'B'/span>,    DATE '2021-08-01'   INTERVAL '7。 15: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'B'/span>,    DATE '2021-08-01'   INTERVAL '7。 25: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'A'/span>,    DATE '2021-08-01'   INTERVAL '8。 10: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'A'/span>,    DATE '2021-08-01'   INTERVAL '8。 12: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'A'/span>,    DATE '2021-08-01'   INTERVAL '8。 16: 00' HOUR TO SECOND FROM DUAL UNION ALL
SELECT 22222, 'A'/span>,    DATE '2021-08-01'   INTERVAL '8: 19:00' HOUR TO SECOND FROM DUAL;

輸出：

PART_NO起始書目日期END_BOOK_DATEA

車站號碼結束書目日期

11111
2021-08-01 06:00:00 2021-08-01 06:08:00 2021-08-01 06:08:00

11111B 2021-08-01 07:10:00 2021-08-01 07:10:00 2021-08-01 07:25:00 2021-08-01 07:25:00

11111A 2021-08-01 08:10:00 2021-08-01 08:10:00 2021-08-01 08:19:00 2021-08-01 08:19:00

A 2021-08-01 06:00:00 2021-08-01 06:00:00 2021-08-01 06:08:00 2021-08-01 06:08:00

22222B 2021-08-01 07:10:00 2021-08-01 07:10:00 2021-08-01 07:25:00 2021-08-01 07:25:00

A 2021-08-01 08:10:00 2021-08-01 08:10:00 2021-08-01 08:19:00 2021-08-01 08:19:00

車站號碼	結束書目日期
11111

db<>fiddle here

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/322152.html
標籤：

上一篇：Postgresql:如何用where條件連接同一個表
下一篇：選擇資料庫中沒有超過2個角色的賬戶