oracle：sql 判断时间段重叠

需求：统计的时间和有重叠的部分要去除

SQL语句：

SELECT MIN(start_date) 
      ,MAX(end_date) 
  FROM (SELECT start_date
              ,end_date
              ,SUM(broken) OVER (ORDER BY start_date,end_date) flag
         FROM (SELECT t.*
                     ,(CASE WHEN start_date <= MAX(end_date) OVER (ORDER BY start_date,end_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
                           THEN 0
                           ELSE 1
                       END) AS broken
                 FROM QM.T_PRD_LOSTIEM_EQUIMENT T
                 WHERE T.EQUIMENT_PID = '5634B26F4BB96D96E053B703A8C0E819'
               )
       )  
GROUP BY flag;

表结构：

create table T_PRD_LOSTIEM_EQUIMENT 
(
   PID                  CHAR(32)             not null,
   LOSTIME_TYPE_PID     CHAR(32),
   EQUIMENT_PID         CHAR(32),
   START_DATE           DATE,
   END_DATE             DATE,
   REMARK               NVARCHAR2(200),
   CREATED_BY           CHAR(32),
   CREATE_DATE          TIMESTAMP,
   UPDATED_BY           CHAR(32),
   UPDATE_DATE          TIMESTAMP,
   IS_DELETED           CHAR(1),
   DELETED_BY           CHAR(32),
   DELETE_DATE          TIMESTAMP,
   constraint PK_T_PRD_LOSTIEM_EQUIMENT primary key (PID)
);

表数据：

PID	LOSTIME_TYPE_PID	EQUIMENT_PID	START_DATE	END_DATE	REMARK
599A9173D2C57B20E053B703A8C0FF43	5988C343988F770CE053B703A8C0ECE0	5634B26F4BB96D96E053B703A8C0E819	2017/1/1 00:01:00	2017/1/1 1:01:00	测试
599A9173D2C67B20E053B703A8C0FF43	5988C343988F770CE053B703A8C0ECE0	5634B26F4BB96D96E053B703A8C0E819	2017/1/1 1:01:00	2017/1/1 6:01:00	测试
599A9173D2C77B20E053B703A8C0FF43	5988C343988F770CE053B703A8C0ECE0	5634B26F4BB96D96E053B703A8C0E819	2017/1/1 5:01:00	2017/1/1 6:00:00	测试
599A9173D2C87B20E053B703A8C0FF43	5988C343988F770CE053B703A8C0ECE0	5634B26F4BB96D96E053B703A8C0E819	2017/1/1 5:20:00	2017/1/1 8:00:00	测试
599A9173D2C97B20E053B703A8C0FF43	5988C343988F770CE053B703A8C0ECE0	5634B26F4BB96D96E053B703A8C0E819	2017/1/1 9:20:00	2017/1/1 10:00:00	测试
599A9173D2CA7B20E053B703A8C0FF43	5988C343988F770CE053B703A8C0ECE0	5634B26F4BB96D96E053B703A8C0E819	2017/1/1 10:20:00	2017/1/2 12:00:00	测试

查询结果：

MIN(START_DATE)	MAX(END_DATE)
2017/1/1	2017/1/1 8:00
2017/1/1 9:20	2017/1/1 10:00
2017/1/1 10:20	2017/1/2 12:00

解读：

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 

第一行到前一行

CASE WHEN start_date <= MAX(end_date) OVER (ORDER BY start_date,end_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 
                           THEN 0
                           ELSE 1
                       END

如果当前行的start_date小于等于第一行到前一行的最大值end_date，标记0，否则标记1

SUM(broken) OVER (ORDER BY start_date,end_date) flag

start_date,end_date排序后broken的 和

参考：http://www.itpub.net/thread-1923366-1-1.html