oracle – PL / SQL Split,根据黑暗日期将日期分为新日期！

发布时间：2020-12-12 13:16:16 所属栏目：百科来源：网络整理

我可以说一个“旅行日期”和黑色日期.
我会根据停电日期将旅行日期分成几部分.

注意：旅行日期可以在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,END_DATE ) VALUES ( 'B',DATE '2011-04-02',DATE '2011-04-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,END_DATE ) VALUES ( 'C',DATE '2011-05-11',DATE '2011-06-15'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,END_DATE ) VALUES ( 'D',DATE '2011-11-20',DATE '2011-11-29'  );
--INSERT INTO TRACES.BLACK_OUT_DATES( BO,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,DATE '2011-02-02',DATE '2011-05-06' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-03-03',DATE '2011-03-05'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-07-07',DATE '2011-07-09'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,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,DATE '2011-01-01',DATE '2011-05-03'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-06-04',DATE '2011-06-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-06-06',DATE '2011-06-08'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-08-08',DATE '2011-12-30'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,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,DATE '2011-02-15'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-02-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-04-10',DATE '2011-04-25'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-04-15',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,DATE '2011-05-17'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-02-05',DATE '2011-02-07',DATE '2011-02-09'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-05-05',DATE '2011-05-20'  );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-05-07',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,DATE '2011-09-20' );
INSERT INTO TRACES.BLACK_OUT_DATES( BO,DATE '2011-01-05',DATE '2011-10-10' );

COMMIT;

SELECT * FROM BLACK_OUT_DATES;
SELECT * FROM TRAVEL;

解决方法

你的桌子：

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分析函数删除了完全重叠的句点,因为它们搞乱了间隙期.

编辑14-3-2011

使用这些表格：

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,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,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,DATE '2011-12-31' );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-05-06' );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-03-05'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-07-09'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,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,DATE '2011-10-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-05-03'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-06-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-06-08'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-12-30'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,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,DATE '2011-05-15'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-02-15'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-02-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-04-25'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-04-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,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,DATE '2011-02-09'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,DATE '2011-05-20'  );
SQL> INSERT INTO BLACK_OUT_DATES( BO,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-10-10' );
SQL> select * from v
  2  /

问候,抢.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!