oracle:sql 判断时间段重叠

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/hchyboy/article/details/78041707

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

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

展开阅读全文

没有更多推荐了,返回首页