sql - 复杂的 SQL 查询 - 折叠日期范围

Question

我们有一个表格，列出了“服务时间”的开始和结束日期。我想要一个可以搜索所有行并根据日期间隔识别服务中断的查询。

Data:
Start         End
1/1/2000   2/1/2001
2/2/2001   4/1/2001
4/1/2004   6/2/2006
6/3/2006   9/1/2010
8/1/2011   9/1/2012

Desired result:
1/1/2001 - 4/1/2001     //The first two ranges collapsed because no break in service 
4/1/2004 - 9/1/2010     // The 3rd and 4th rows collapsed because no real break in service 
8/1/2011 - 9/1/2012  

这可能在应用程序逻辑或存储过程中更容易完成，只是想知道是否有任何 SQL 巫术可以让我接近。

Table definition:
CREATE TABLE CONG_MEMBER_TERM 
(
  CONG_MEMBER_TERM_ID NUMBER(10, 0) NOT NULL 
  , CONGRESS_ID NUMBER(10, 0) NOT NULL 
  , CHAMBER_CD VARCHAR2(30 BYTE) NOT NULL 
  , CONG_MEMBER_ID NUMBER(10, 0) NOT NULL 
  , STATE_CD CHAR(2 BYTE) NOT NULL 
  , CONG_MEMBER_TYPE_CD VARCHAR2(30 BYTE) NOT NULL 
  , DISTRICT NUMBER(10, 0) 
  , START_DT TIMESTAMP(6) WITH TIME ZONE 
  , END_DT TIMESTAMP(6) WITH TIME ZONE 
  , CREATE_DT TIMESTAMP(6) WITH TIME ZONE NOT NULL 
  , UPDATE_DT TIMESTAMP(6) WITH TIME ZONE NOT NULL
)

Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2945,104,'H',494,'OK','REP',2,to_timestamp_tz('04-JAN-95 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('04-OCT-96 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.47.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2946,105,'H',494,'OK','REP',2,to_timestamp_tz('07-JAN-97 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('19-DEC-98 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.47.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2947,106,'H',494,'OK','REP',2,to_timestamp_tz('06-JAN-99 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('15-DEC-00 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.47.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2948,109,'S',494,'OK','SEN',null,to_timestamp_tz('04-JAN-05 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('09-DEC-06 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2949,110,'S',494,'OK','SEN',null,to_timestamp_tz('04-JAN-07 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-JAN-09 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2951,111,'S',494,'OK','SEN',null,to_timestamp_tz('06-JAN-09 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('22-DEC-10 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));
Insert into CONG_MEMBER_TERM (CONG_MEMBER_TERM_ID,CONGRESS_ID,CHAMBER_CD,CONG_MEMBER_ID,STATE_CD,CONG_MEMBER_TYPE_CD,DISTRICT,START_DT,END_DT,CREATE_DT,UPDATE_DT) values (2950,112,'S',494,'OK','SEN',null,to_timestamp_tz('05-JAN-11 01.00.00.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),null,to_timestamp_tz('02-MAY-12 09.45.48.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'),to_timestamp_tz('02-MAY-12 09.45.49.000000000 AM -05:00','DD-MON-RR HH.MI.SS.FF AM TZR'));

如果上一次服务与下一次服务之间的间隔超过 24 个月，则被视为服务“间隔”。

_迈克

Answer 1

下面是使用分析函数在 SQL 中折叠时间范围的标准方法和示例。

您的 table :

SQL> create table mytable (startdate,enddate)
  2  as
  3  select date '2000-01-01', date '2001-02-01' from dual union all
  4  select date '2001-02-02', date '2001-04-01' from dual union all
  5  select date '2004-04-01', date '2006-06-02' from dual union all
  6  select date '2006-06-03', date '2010-09-01' from dual union all
  7  select date '2011-08-01', date '2012-09-01' from dual
  8  /

Table created.

查询:

SQL> select min(startdate) startdate
  2       , max(enddate)   enddate
  3    from ( select startdate
  4                , enddate
  5                , max(rn) over (order by startdate) maxrn
  6             from ( select startdate
  7                         , enddate
  8                         , case lag(enddate) over (order by startdate)
  9                           when startdate-1 then
 10                             null
 11                           else
 12                             rownum
 13                           end rn
 14                      from mytable
 15                  )
 16         )
 17   group by maxrn
 18   order by startdate
 19  /

STARTDATE           ENDDATE
------------------- -------------------
01-01-2000 00:00:00 01-04-2001 00:00:00
01-04-2004 00:00:00 01-09-2010 00:00:00
01-08-2011 00:00:00 01-09-2012 00:00:00

3 rows selected.

它分三个阶段进行:

1. 仅向组开头的记录分配唯一的 rownum
2. 为不是组开头的记录赋予与组开头相同的编号(使用带有滑动窗口的分析函数 MAX)
3. 按组编号聚合

这个查询的真正优点在于只需要一个 TABLE ACCESS FULL:

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
  2  /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
SQL_ID  8v1suw8j53tqz, child number 0
-------------------------------------
select min(startdate) startdate      , max(enddate)   enddate   from ( select startdate               , enddate
             , max(rn) over (order by startdate) maxrn            from ( select startdate
, enddate                        , case lag(enddate) over (order by startdate)                          when
startdate-1 then                            null                          else                            rownum
                         end rn                     from mytable                 )        )  group by maxrn
order by startdate

Plan hash value: 2933657513

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   1 |  SORT ORDER BY           |         |      1 |      5 |      3 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   2 |   HASH GROUP BY          |         |      1 |      5 |      3 |00:00:00.01 |       3 |       |       |          |
|   3 |    VIEW                  |         |      1 |      5 |      5 |00:00:00.01 |       3 |       |       |          |
|   4 |     WINDOW BUFFER        |         |      1 |      5 |      5 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |      VIEW                |         |      1 |      5 |      5 |00:00:00.01 |       3 |       |       |          |
|   6 |       WINDOW SORT        |         |      1 |      5 |      5 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   7 |        COUNT             |         |      1 |        |      5 |00:00:00.01 |       3 |       |       |          |
|   8 |         TABLE ACCESS FULL| MYTABLE |      1 |      5 |      5 |00:00:00.01 |       3 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------


24 rows selected.

问候，
罗布。