获取间隔时间范围内日期数据的开始时间结束时间

最新推荐文章于 2023-11-22 14:53:30 发布

Karl_zhujt

最新推荐文章于 2023-11-22 14:53:30 发布

阅读量466

点赞数

分类专栏： Oracle

本文链接：https://blog.csdn.net/mh942408056/article/details/108270289

版权

Oracle 专栏收录该内容

29 篇文章 1 订阅

订阅专栏

create table A_2
(
  pscode      VARCHAR2(12),
  outputcode  VARCHAR2(3),
  monitortime DATE,
  processname VARCHAR2(50)
);

insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:27:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:27:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:37:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:38:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:40:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:42:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:42:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:45:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:47:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:48:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:37:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:38:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:40:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:42:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:42:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:45:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:47:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:48:00', 'dd-mm-yyyy hh24:mi:ss'), 'SunloginClient.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:37:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:38:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:40:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:42:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:42:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:45:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:47:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 14:48:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:37:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:38:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:40:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:42:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:42:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:45:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:47:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
insert into A_2 (pscode, outputcode, monitortime, processname)
values ('410100001876', '1', to_date('27-08-2020 16:48:00', 'dd-mm-yyyy hh24:mi:ss'), 'WebChat.exe');
commit;

数据效果：

统计效果：

统计SQL


-- 第1种方案，有所欠缺
select m.pscode,m.outputcode,m.processname,m.startdate,max(monitortime) enddate
  from (select pscode,outputcode,monitortime,processname,
               (select min(monitortime) from a_2 e where ceil((s.monitortime - e.monitortime) * 24 * 60) <= 30) startdate
          from a_2 s
       ) m 
 group by m.pscode, m.outputcode, m.processname, m.startdate


-- 第2种方案，适应各种情况
select pscode,outputcode,processname,monitortime startdate,
       (select max(monitortime) from a_2 m 
         where m.pscode=n.pscode 
           and m.outputcode=n.outputcode 
           and m.processname=n.processname 
           and ((n.enddate is null and m.monitortime >= n.monitortime) or ( m.monitortime>=n.monitortime and m.monitortime<n.enddate) )
         
       ) enddate
  from (select pscode,outputcode,processname,monitortime,lead(s.monitortime,1,NULL) over(partition by pscode,outputcode,processname order by monitortime) enddate
          from (select pscode,outputcode,monitortime,processname,  
                       ceil((lag(monitortime,1,monitortime) over(partition by pscode,outputcode,processname order by monitortime) - monitortime) * 24 * 60) intervals,
                       row_number() over(partition by pscode,outputcode,processname order by monitortime) orderby
                  from a_2
               ) s
         where orderby=1 or intervals<-8 --时间间隔，8分钟内是否有数据
        ) n