在oracle中创建指定时间范围的记录

最新推荐文章于 2024-03-15 11:36:30 发布

原创最新推荐文章于 2024-03-15 11:36:30 发布 · 1.4k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#oracle

统计SQL 专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用Oracle SQL的方法来统计指定日期范围内每天的日志数据，并按不同的时间间隔进行分组计数。同时，还提供了统计每日唯一登录用户的SQL语句。

用于日志统计,要求在指定的时间范围内每天都要统计

要求日期,5<=T<10,10<=T<20,20<=T<30,30<=T<40,T>=40,异常总数

with a as(

select to_date('2016-11-1','yyyy-mm-dd hh24:mi:ss') startdate,to_date('2017-01-22','yyyy-mm-dd hh24:mi:ss') enddate from dual
)select t.v,nvl(t1.cou,0),nvl(t2.cou,0),nvl(t3.cou,0),nvl(t4.cou,0),nvl(t5.cou,0),nvl(t6.cou,0) from (
select to_char(t,'yyyy-mm-dd') v
from (select distinct startdate + level - 1 t
from a
connect by level <= enddate - startdate + 1) order by t ) t ,
(select count(log.timespan) cou,to_char(log.logdatetime,'yyyy-mm-dd') su from sys_runlog log where log.logtype log.timespan >= 0 and log.timespan < 10 group by to_char(log.logdatetime,'yyyy-mm-dd')) t1 ,
(select count(log.timespan) cou,to_char(log.logdatetime,'yyyy-mm-dd') su from sys_runlog log where log.timespan >= 10 and log.timespan < 20 group by to_char(log.logdatetime,'yyyy-mm-dd')) t2,
(select count(log.timespan) cou,to_char(log.logdatetime,'yyyy-mm-dd') su from sys_runlog log where log.timespan >= 20 and log.timespan < 30 group by to_char(log.logdatetime,'yyyy-mm-dd')) t3,
(select count(log.timespan) cou,to_char(log.logdatetime,'yyyy-mm-dd') su from sys_runlog log where log.timespan >= 30 and log.timespan < 40 group by to_char(log.logdatetime,'yyyy-mm-dd')) t4,
(select count(log.timespan) cou,to_char(log.logdatetime,'yyyy-mm-dd') su from sys_runlog log where log.timespan >= 40 group by to_char(log.logdatetime,'yyyy-mm-dd')) t5,
(select count(log.timespan) cou,to_char(log.logdatetime,'yyyy-mm-dd') su from sys_runlog log where log.logtype=? group by to_char(log.logdatetime,'yyyy-mm-dd')) t6
where t.v = t1.su(+) and t.v = t2.su(+) and t.v = t3.su(+) and t.v = t4.su(+) and t.v = t5.su(+) and t.v = t6.su(+)

order by t.v

统计登录日志要求统计每天登录的总人数一个人登录一次或多次也只能算是一人

(select count(distinct t.user_account) cou,to_char(t.time,'yyyy-mm-dd') su from ops_log_login t group by to_char(t.time,'yyyy-mm-dd')) t1,

用oracle创建一个虚拟表内容为指定的时间范围

with a as(

select to_date('2016-11-1','yyyy-mm-dd hh24:mi:ss') startdate,to_date('2017-01-22','yyyy-mm-dd hh24:mi:ss') enddate from dual
)select t.v from (
select to_char(t,'yyyy-mm-dd') v
from (select distinct startdate + level - 1 t
from a
connect by level <= enddate - startdate + 1) order by t ) t