OracleSQL-如何回傳公共日期期間并在期間之間存在間隔時“劃分”-有解無憂

我正在嘗試從以下資料中回傳常見日期期間（每個 id），但是當日期期間在常見期間之間存在差距時，我找不到處理這種情況的方法。任何人都可以幫忙嗎？

|id|code_id|code|date_from|date_to|
|--|--|--|--|--|
|10|100|    1000    |02/02/2022             |03/02/2022 23:57:00|
|10|100|    1000    |07/02/2022 01:00:00    |08/02/2022         |
|10|100|    2000    |02/02/2022             |02/02/2022 23:00:00|
|10|100|    2000    |07/02/2022 03:00:00    |08/02/2022         |
|10|200|    2000    |02/02/2022 02:14:00    |04/02/2022 21:37:00|
|20|100|    1000    |01/02/2022 05:00:00    |03/02/2022         |
|30|100|    2000    |02/02/2022             |02/02/2022 23:00:00|
|30|200|    2000    |02/02/2022 02:14:00    |04/02/2022         |
|40|100|    2000    |07/02/2022 03:00:00    |08/02/2022 23:10:00|
|50|200|    2000    |04/02/2022             |04/02/2022 21:37:00|
|50|200|    3000    |04/02/2022 02:12:00    |05/02/2022 23:31:00|

下面的簡單查詢可以正常作業，但僅適用于具有一個共同周期（沒有間隙）的 id。

我希望id = 10回傳兩行（因為日期之間存在差距），期間為：

I) 2022 年 2 月 2日 00:00:00 <-> 2022 年 4 月 2 日21:37:00

II) 07/02/2022 01:00:00 <-> 08/02/2022 00:00:00

 SELECT id
       ,MIN(date_from) date_from
       ,MAX(date_to)   date_to
  FROM my_gtt 
  GROUP BY id
  ORDER BY id

當前結果（但 id = 10 不正確）：

|id|date_from|date_to|
|--|--|--|
|10|    02/02/2022          |08/02/2022         |
|20|    01/02/2022 05:00:00 |03/02/2022         |
|30|    02/02/2022          |04/02/2022         |
|40|    07/02/2022 03:00:00 |08/02/2022 23:10:00|
|50|    04/02/2022          |05/02/2022 23:31:00|

資料和表創建：

CREATE GLOBAL TEMPORARY TABLE my_gtt
(
  id         NUMBER(10),
  code_id    NUMBER(10),
  code       NUMBER(10),
  date_from  DATE,
  date_to    DATE
)
ON COMMIT PRESERVE ROWS;

INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('03-02-2022 23:57:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('07-02-2022 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (20, 100, 1000, TO_DATE('01-02-2022 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('03-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (30, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (30, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (40, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 2000, TO_DATE('04-02-2022', 'dd-mm-yyyy'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 3000, TO_DATE('04-02-2022 02:12:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('05-02-2022 23:31:00', 'dd-mm-yyyy hh24:mi:ss'));

uj5u.com熱心網友回復：

從 Oracle 12 開始，MATCH_RECOGNIZE是最簡單的解決方案：

SELECT *
FROM   my_gtt
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY date_from, date_to
  MEASURES
    MIN(date_from) AS start_date,
    MAX(date_to)   AS end_date
  PATTERN (overlap* last_row)
  DEFINE
    overlap AS MAX(date_to) >= NEXT(date_from)
);

但是，如果您使用的是早期版本，則可以使用以下命令找到輸出：

SELECT id,
       MIN(dt) AS date_from,
       MAX(dt) AS date_to
FROM   (
  SELECT id,
         dt,
         SUM(value) OVER (PARTITION BY id ORDER BY dt, ROWNUM) AS match_no
  FROM   (
    SELECT id,
           dt,
           type * SUM(type) OVER (PARTITION BY id ORDER BY dt, ROWNUM) AS value
    FROM   my_gtt
    UNPIVOT (dt FOR type IN (date_from AS 1, date_to AS -1))
  )
  WHERE  value IN (1,0)
)
GROUP BY id, match_no

其中，對于樣本資料：

INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('03-02-2022 23:57:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 1000, TO_DATE('07-02-2022 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (10, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (10, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (20, 100, 1000, TO_DATE('01-02-2022 05:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('03-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (30, 100, 2000, TO_DATE('02-02-2022', 'dd-mm-yyyy'), TO_DATE('02-02-2022 23:00:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (30, 200, 2000, TO_DATE('02-02-2022 02:14:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('04-02-2022', 'dd-mm-yyyy'));
INSERT INTO my_gtt VALUES (40, 100, 2000, TO_DATE('07-02-2022 03:00:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('08-02-2022 23:10:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 2000, TO_DATE('04-02-2022', 'dd-mm-yyyy'), TO_DATE('04-02-2022 21:37:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (50, 200, 3000, TO_DATE('04-02-2022 02:12:00', 'dd-mm-yyyy hh24:mi:ss'), TO_DATE('05-02-2022 23:31:00', 'dd-mm-yyyy hh24:mi:ss'));
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-01', DATE '2022-01-10');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-02', DATE '2022-01-04');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-06', DATE '2022-01-11');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-13', DATE '2022-01-16');
INSERT INTO my_gtt VALUES (60, 200, 3000, DATE '2022-01-14', DATE '2022-01-15');

兩個輸出：

ID 開始日期結束日期

10 2022-02-02 00:00:00 2022-02-04 21:37:00

10 2022-02-07 01:00:00 2022-02-08 00:00:00

20 2022-02-01 05:00:00 2022-02-03 00:00:00

30 2022-02-02 00:00:00 2022-02-04 00:00:00

40 2022-02-07 03:00:00 2022-02-08 23:10:00

50 2022-02-04 00:00:00 2022-02-05 23:31:00

60 2022-01-01 00:00:00 2022-01-11 00:00:00

60 2022-01-13 00:00:00 2022-01-16 00:00:00

ID	開始日期	結束日期
10	2022-02-02 00:00:00	2022-02-04 21:37:00
10	2022-02-07 01:00:00	2022-02-08 00:00:00
20	2022-02-01 05:00:00	2022-02-03 00:00:00
30	2022-02-02 00:00:00	2022-02-04 00:00:00
40	2022-02-07 03:00:00	2022-02-08 23:10:00
50	2022-02-04 00:00:00	2022-02-05 23:31:00
60	2022-01-01 00:00:00	2022-01-11 00:00:00
60	2022-01-13 00:00:00	2022-01-16 00:00:00

db<>在這里擺弄

uj5u.com熱心網友回復：

SQL 模式匹配可以幫助：

select * from my_gtt match_recognize (
  partition by id
  order by date_from, date_to
  measures 
    first ( date_from ) start_date,
    last ( date_to ) end_date
  pattern ( init overlap* )
  define 
    overlap as date_from <= prev ( date_to )
);

        ID START_DATE           END_DATE            
---------- -------------------- --------------------
        10 02-FEB-2022 00:00:00 04-FEB-2022 21:37:00
        10 07-FEB-2022 01:00:00 08-FEB-2022 00:00:00
        20 01-FEB-2022 05:00:00 03-FEB-2022 00:00:00
        30 02-FEB-2022 00:00:00 04-FEB-2022 00:00:00
        40 07-FEB-2022 03:00:00 08-FEB-2022 23:10:00
        50 04-FEB-2022 00:00:00 05-FEB-2022 23:31:00

我在模式匹配用例中更詳細地討論了它是如何作業的

轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432918.html

標籤：sql 甲骨文差距和岛屿

上一篇：為什么我不能格式化sql日期？

下一篇：每個專案的天數以及每個專案的開始日期和結束日期