复杂SQL查询 - 折叠日期范围(Complex SQL query - collapsing date ranges)
我们有一个表格，列出“服务时间”的开始和结束日期。 我想要一个查询，可以搜索所有行，并根据日期中的间隙识别服务中的任何中断。
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这可能更容易在app逻辑或存储过程中完成，只是想知道是否有任何SQL voodoo可以让我接近。
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个月，那么它将被视为服务中的“差距”。
_麦克风
We have a table that lists start and end dates for "service time". I would like a query that can search through all of the rows and identify any breaks in service based on gaps in the dates.
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/2012This probably more easily done in app logic or stored proc, just wondering if there is any SQL voodoo that could get me close.
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'));If the gap between the previous service and the next service is greater then 24 months, it's considered a "gap" in service.
_mike
最满意答案
下面是使用分析函数和示例在SQL中折叠时间范围的标准方法。
你的桌子：
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.它分三个阶段运作：
仅为那些作为组开头的记录分配唯一的rownum 给出不是组起点的记录与组的起点相同（使用具有滑动窗口的分析函数MAX） 按组编号汇总而这个查询的真正之处在于只需要一个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.问候， 抢。
Here is a standard way to do collapsing time ranges in SQL using analytic functions with an example.
Your 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.The query:
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.It works in three phases:
assign a unique rownum to only those records that are the start of a group give the records that are not the start of a group the same number as the start of the group (using analytic function MAX with a sliding window) aggregate by group numberAnd the real beauty of this query is that only one TABLE ACCESS FULL is needed:
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.Regards, Rob.
更多推荐
发布评论