PL/SQL Split，根据黑色日期将一个日期分割成新的日期！

Question

我有一个“旅行日期”和禁止日期。 我会根据限制日期将旅行日期分成几部分。

注意：旅行日期可以在 0 - 9999 99 99

示例：

Travel Date:
Travel | START DATE | END DATE
T      | 2011 01 04 | 2011 12 11

Black Out Dates:
BO   | START DATE | END DATE
A    | 2010 11 01 | 2011 02 11
B    | 2011 01 20 | 2011 02 15
C    | 2011 03 13 | 2011 04 10
D    | 2011 03 20 | 2011 06 29

例外结果：

New Travel  | START DATE | END DATE
X1          | 2011 02 16 | 2011 03 12
X2          | 2011 06 30 | 2011 12 11

视觉上：

NAME        : date range
Travel Date : -----[--------------------------]--

A           : --[------]-------------------------
B           : ------[---]------------------------
C           : --------------[---]----------------
D           : ----------------[------]-----------

结果：

X1           : -----------[--]--------------------
X2           : -----------------------[--------]--

---

示例 2：

Travel Date  : -[--------------------------------]--

BO Date A    : ----[------]-------------------------
BO Date B    : ----------------------[------]-------
BO Date C    : --------------------[---]------------
BO Date D    : ------------------[------]-----------

Result X1    : -[--]--------------------------------
Result X2    : -----------[------]------------------
Result X3    : -----------------------------[----]--

示例 3：

Travel Date  : ]-----------------------------------[

BO Date A    : ----[------]-------------------------
BO Date B    : -------------------------[---]-------
BO Date C    : ----------------[---]----------------
BO Date D    : ------------------[------]-----------

Result X1    : ---]---------------------------------
Result X2    : -----------[--]----------------------
Result X3    : -----------------------------[-------

如何使用 PL SQL 执行此操作？

谢谢。

以下是表格和测试用例：

DROP TABLE TRACES.TRAVEL CASCADE CONSTRAINTS;

CREATE TABLE TRACES.TRAVEL
(
  START_DATE  DATE,
  END_DATE    DATE
);

DROP TABLE TRACES.BLACK_OUT_DATES CASCADE CONSTRAINTS;

CREATE TABLE TRACES.BLACK_OUT_DATES
(
   BO           CHAR( 1 BYTE ),
   START_DATE   DATE,
   END_DATE     DATE
);

---

/*
TEST CASE 1
-------------------------------------------------------------------
Expected Results:
01/01/0001    09/02/2011
16/02/2011    01/04/2011
21/04/2011    10/05/2011
16/06/2011    19/11/2011
30/11/2011    31/12/9999

Visually:
Travel:
----------------------------------------------------

BO:
--[--]----------------------------------------------
---------------[------]-----------------------------
-----------------------------[---------------]------

Result:
[-]---[--------]-------[-----]----------------[-----]

*/

TRUNCATE TABLE TRACES.TRAVEL;

TRUNCATE TABLE TRACES.BLACK_OUT_DATES;

INSERT INTO TRACES.TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '0001-01-01', DATE '9999-12-31' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-02-10', DATE '2011-02-15' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-04-02', DATE '2011-04-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-05-11', DATE '2011-06-15'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-11-20', DATE '2011-11-29'  );
--INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E',  DATE '2011-05-09', DATE '2011-05-12'  );

COMMIT;

SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;

---

/* 
TEST CASE 2
-------------------------------------------------------------------
Expected Results:
01/01/2011    01/02/2011
07/05/2011    06/07/2011
21/07/2011    31/12/2011

Visually:
Travel:
[------------------------------------------------------]

BO:
--[----------------------]------------------------------
---------------[--]-------------------------------------
---------------------------------[--------]-------------
----------------------------------------[--------]------

Result:
[--]---------------------[---------]--------------[----]

*/
TRUNCATE TABLE TRACES.TRAVEL;

TRUNCATE TABLE TRACES.BLACK_OUT_DATES;

INSERT INTO TRACES.TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '2011-01-01', DATE '2011-12-31' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-02-02', DATE '2011-05-06' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-03-03', DATE '2011-03-05'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-07-07', DATE '2011-07-09'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-07-08', DATE '2011-07-20'  );

COMMIT;

SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;

---

/* 
TEST CASE 3
-------------------------------------------------------------------
Expected Results:
04/05/2011    03/06/2011
21/06/2011    07/08/2011

Visually:
Travel:
[------------------------------------------------------]

BO:
--[----------------------]------------------------------
---------------[--]-------------------------------------
---------------------------------[--------]-------------
----------------------------------------[--------]------

Result:
[--]---------------------[---------]--------------[----]
*/

TRUNCATE TABLE TRACES.TRAVEL;

TRUNCATE TABLE TRACES.BLACK_OUT_DATES;

INSERT INTO TRACES.TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '2011-04-02', DATE '2011-10-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-01-01', DATE '2011-05-03'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-06-04', DATE '2011-06-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-06-06', DATE '2011-06-08'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-08-08', DATE '2011-12-30'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E',  DATE '2011-08-08', DATE '2011-12-30'  );

COMMIT;

SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;

---

/*
TEST CASE 4
-------------------------------------------------------------------
Expected Results:
21/02/2011    09/04/2011
26/04/2011    09/05/2011

Visually:
Travel:
----[-------------------------]-------------------------

BO:
--[----]------------------------------------------------
----[----]----------------------------------------------
-------------[--------]---------------------------------
------------------[--]----------------------------------
--------------------------[--------]--------------------

Result:
----------[--]--------[--]-----------------------------
*/

TRUNCATE TABLE TRACES.TRAVEL;

TRUNCATE TABLE TRACES.BLACK_OUT_DATES;

INSERT INTO TRACES.TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '2011-02-10', DATE '2011-05-15'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-02-02', DATE '2011-02-15'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-02-10', DATE '2011-02-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-04-10', DATE '2011-04-25'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-04-15', DATE '2011-04-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E',  DATE '2011-05-10', DATE '2011-05-20'  );

COMMIT;

SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;

---

/*
TEST CASE 5
-------------------------------------------------------------------
Expected Results:
21/02/2011    04/05/2011

Visually:
Travel:
------[-------------------------]-----------------------

BO:
-[-----]------------------------------------------------
--[--]--------------------------------------------------
----------------------------[--------]------------------
------------------------------[-----]-------------------
-------------------------------[--]---------------------

Result:
--------[-------------------]---------------------------
*/

TRUNCATE TABLE TRACES.TRAVEL;

TRUNCATE TABLE TRACES.BLACK_OUT_DATES;

INSERT INTO TRACES.TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '2011-02-10', DATE '2011-05-17'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-02-05', DATE '2011-02-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-02-07', DATE '2011-02-09'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-05-05', DATE '2011-05-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-05-07', DATE '2011-05-15'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E',  DATE '2011-05-09', DATE '2011-05-12'  );

COMMIT;

SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;

---

/* 
TEST CASE 6
-------------------------------------------------------------------
Expected Results:
No Result

Visually:
Travel:
------[----------------------------]--------------------

BO:
--[---------------------------------------]-------------

Result:
No Result

*/

TRUNCATE TABLE TRACES.TRAVEL;

TRUNCATE TABLE TRACES.BLACK_OUT_DATES;

INSERT INTO TRACES.TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '2011-02-10', DATE '2011-09-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-01-05', DATE '2011-10-10' );

COMMIT;

SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;

Answer 1

这将返回旅行日期范围内但不在限制日期列表中的所有日期的离散列表，然后使用 Oracle - 将值从行转换为范围：

WITH traveldate AS
  (SELECT TO_DATE('2011 01 04','YYYY MM DD') AS start_date
         ,TO_DATE('2011 12 11','YYYY MM DD') AS end_date FROM DUAL)
    ,blackout AS
  (SELECT TO_DATE('2010 11 01','YYYY MM DD') AS start_date
         ,TO_DATE('2011 02 11','YYYY MM DD') AS end_date FROM DUAL
   UNION ALL
   SELECT TO_DATE('2011 01 20','YYYY MM DD') AS start_date
         ,TO_DATE('2011 02 15','YYYY MM DD') AS end_date FROM DUAL
   UNION ALL
   SELECT TO_DATE('2011 03 13','YYYY MM DD') AS start_date
         ,TO_DATE('2011 04 10','YYYY MM DD') AS end_date FROM DUAL
   UNION ALL
   SELECT TO_DATE('2011 03 20','YYYY MM DD') AS start_date
         ,TO_DATE('2011 06 29','YYYY MM DD') AS end_date FROM DUAL)
    ,days AS
  (SELECT TO_DATE('2010 01 01','YYYY MM DD') + ROWNUM d
   FROM DUAL CONNECT BY LEVEL <= 1000)
    ,base AS
  (SELECT d AS n
   FROM   days, traveldate
   WHERE  d >= traveldate.start_date AND d <= traveldate.end_date
   MINUS
   SELECT d AS n
   FROM   days, blackout
   WHERE  d >= blackout.start_date AND d <= blackout.end_date
  )
,lagged AS
(
    SELECT n, LAG(n) OVER (ORDER BY n) lag_n FROM base
)
, groups AS
(
    SELECT n, row_number() OVER (ORDER BY n) groupnum
      FROM lagged
      WHERE lag_n IS NULL OR lag_n < n-1
)
, grouped AS
(
    SELECT n, (SELECT MAX(groupnum) FROM groups
                 WHERE groups.n <= base.n
              ) groupnum
      FROM base
)
SELECT groupnum, MIN(n), MAX(n)
  FROM grouped
  GROUP BY groupnum
  ORDER BY groupnum;

结果：

GROUPNUM    MIN(N)          MAX(N)

1           16/02/2011  12/03/2011
2           30/06/2011  11/12/2011

Answer 2

您的表：

SQL> create table travel (start_date,end_date)
  2  as
  3  select date '2011-01-04', date '2011-12-11' from dual
  4  /

Table created.

SQL> create table black_out_dates (bo,start_date,end_date)
  2  as
  3  select 'A', date '2010-11-01', date '2011-02-11' from dual union all
  4  select 'B', date '2011-01-20', date '2011-02-15' from dual union all
  5  select 'C', date '2011-03-13', date '2011-04-10' from dual union all
  6  select 'D', date '2011-03-20', date '2011-06-29' from dual
  7  /

Table created.

以及查询，该查询考虑了完全重叠的停电期：

SQL> select 'X' || to_char(row_number() over (order by new_start_date)) new_travel
  2       , new_start_date
  3       , new_end_date
  4    from ( select end_date + 1 new_start_date
  5                , lead(start_date - 1, 1, t_end_date) over (order by start_date) new_end_date
  6             from ( select start_date
  7                         , end_date
  8                         , t_end_date
  9                         , row_number() over (order by start_date) rn_start_date
 10                         , row_number() over (order by end_date) rn_end_date
 11                      from ( select bo.start_date
 12                                  , bo.end_date
 13                                  , t.end_date t_end_date
 14                               from black_out_dates bo
 15                                  , travel t
 16                              where t.start_date <= bo.end_date
 17                                and t.end_date >= bo.start_date
 18                              union all
 19                             select start_date - 1
 20                                  , start_date - 1
 21                                  , null
 22                               from travel
 23                           )
 24                  )
 25            where rn_start_date <= rn_end_date
 26         )
 27   where new_start_date <= new_end_date
 28   order by new_start_date
 29  /

NEW_TRAVEL NEW_START_DATE      NEW_END_DATE
---------- ------------------- -------------------
X1         16-02-2011 00:00:00 12-03-2011 00:00:00
X2         30-06-2011 00:00:00 11-12-2011 00:00:00

2 rows selected.

如果您的 black_out_dates 表包含 N 行，则最多有 N+1 个间隙。该查询在 [2011-01-03,2011-01-03] 组成一个虚拟的中断日期期间，然后使用分析函数 LEAD 来确定下一个中断日期的开始位置。完全重叠的周期会被 ROW_NUMBER 分析函数删除，因为它们会弄乱间隙周期。

---

编辑 2011 年 3 月 14 日

对于这些表：

SQL> create table travel (start_date,end_date)
  2  as
  3  select date '2001-01-04', date '2013-12-11' from dual
  4  /

Table created.

SQL> create table black_out_dates (bo,start_date,end_date)
  2  as
  3  select 'A', date '2010-11-01', date '2011-02-11' from dual union all
  4  select 'B', date '2011-01-20', date '2011-02-15' from dual union all
  5  select 'C', date '2011-03-13', date '2011-04-10' from dual union all
  6  select 'D', date '2011-03-20', date '2011-06-29' from dual
  7  /

Table created.

我之前的查询仍然无法正确处理重叠周期。所以这里是一个修订版本，方便地存储在视图 v:

SQL> create view v
  2  as
  3  with t1 as
  4  ( select bo.start_date
  5         , bo.end_date
  6         , t.end_date t_end_date
  7      from black_out_dates bo
  8         , travel t
  9     where bo.start_date <= t.end_date
 10       and bo.end_date >= t.start_date
 11     union all
 12    select start_date - 1
 13         , start_date - 1
 14         , end_date
 15      from travel
 16  )
 17  , t2 as
 18  ( select t1.*
 19         , nvl
 20           ( max(end_date)
 21             over
 22             ( order by start_date,end_date desc
 23               rows between unbounded preceding and 1 preceding
 24             )
 25           , to_date('1','j')
 26           ) max_date
 27      from t1
 28  )
 29  , t3 as
 30  ( select start_date
 31         , end_date
 32         , t_end_date
 33         , sum( case when start_date > max_date then 1 else 0 end )
 34           over
 35           ( order by start_date, end_date desc ) grp
 36      from t2
 37  )
 38  , t4 as
 39  ( select max(end_date) + 1 new_start_date
 40         , lead(min(start_date) - 1, 1, t_end_date) over (order by min(start_date)) new_end_date
 41      from t3
 42     group by t_end_date
 43         , grp
 44  )
 45  select new_start_date
 46       , new_end_date
 47    from t4
 48   where new_start_date <= new_end_date
 49  /

View created.

和测试结果中：

SQL> set feedback off
SQL> remark  Test 1
SQL> select * from v
  2  /

NEW_START_DATE      NEW_END_DATE
------------------- -------------------
04-01-2001 00:00:00 31-10-2010 00:00:00
16-02-2011 00:00:00 12-03-2011 00:00:00
30-06-2011 00:00:00 11-12-2013 00:00:00
SQL> remark  Test 2
SQL> delete travel
  2  /
SQL> delete black_out_dates
  2  /
SQL> INSERT INTO TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '2011-01-01', DATE '2011-12-31' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-02-02', DATE '2011-05-06' );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-03-03', DATE '2011-03-05'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-07-07', DATE '2011-07-09'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-07-08', DATE '2011-07-20'  );
SQL> select * from v
  2  /

NEW_START_DATE      NEW_END_DATE
------------------- -------------------
01-01-2011 00:00:00 01-02-2011 00:00:00
07-05-2011 00:00:00 06-07-2011 00:00:00
21-07-2011 00:00:00 31-12-2011 00:00:00
SQL> remark  Test 3
SQL> delete travel
  2  /
SQL> delete black_out_dates
  2  /
SQL> INSERT INTO TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '2011-04-02', DATE '2011-10-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-01-01', DATE '2011-05-03'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-06-04', DATE '2011-06-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-06-06', DATE '2011-06-08'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-08-08', DATE '2011-12-30'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E',  DATE '2011-08-08', DATE '2011-12-30'  );
SQL> select * from v
  2  /

NEW_START_DATE      NEW_END_DATE
------------------- -------------------
04-05-2011 00:00:00 03-06-2011 00:00:00
21-06-2011 00:00:00 07-08-2011 00:00:00
SQL> remark  Test 4
SQL> delete travel
  2  /
SQL> delete black_out_dates
  2  /
SQL> INSERT INTO TRAVEL(  START_DATE, END_DATE )                 VALUES (   DATE '2011-02-10', DATE '2011-05-15'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-02-02', DATE '2011-02-15'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-02-10', DATE '2011-02-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-04-10', DATE '2011-04-25'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-04-15', DATE '2011-04-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E',  DATE '2011-05-10', DATE '2011-05-20'  );
SQL> select * from v
  2  /

NEW_START_DATE      NEW_END_DATE
------------------- -------------------
21-02-2011 00:00:00 09-04-2011 00:00:00
26-04-2011 00:00:00 09-05-2011 00:00:00
SQL> remark  Test 5
SQL> delete travel
  2  /
SQL> delete black_out_dates
  2  /
SQL> INSERT INTO TRAVEL VALUES (   DATE '2011-02-10', DATE '2011-05-17'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'A',  DATE '2011-02-05', DATE '2011-02-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'B',  DATE '2011-02-07', DATE '2011-02-09'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'C',  DATE '2011-05-05', DATE '2011-05-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'D',  DATE '2011-05-07', DATE '2011-05-15'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO, START_DATE, END_DATE ) VALUES ( 'E',  DATE '2011-05-09', DATE '2011-05-12'  );
SQL> select * from v
  2  /

NEW_START_DATE      NEW_END_DATE
------------------- -------------------
21-02-2011 00:00:00 04-05-2011 00:00:00
SQL> remark  Test 6
SQL> delete travel
  2  /
SQL> delete black_out_dates
  2  /
SQL> INSERT INTO TRAVEL VALUES (DATE '2011-02-10', DATE '2011-09-20' );
SQL> INSERT INTO BLACK_OUT_DATES VALUES ('A', DATE '2011-01-05', DATE '2011-10-10' );
SQL> select * from v
  2  /

问候，
抢。