oracle获取每隔5分钟的数据趋势

 

往前一小时每隔 5 分钟
SELECT to_char(trunc(sysdate,'hh24')+(to_char(sysdate,'mi')-mod(to_char(sysdate,'mi'),5))/60/24 - 1/24+(rownum*5)/(24*60),'hh24:mi') AS occur_hour
                        FROM DUAL
                        CONNECT BY 12>=ROWNUM


select a.occur_hour as "occurHour",
                       nvl(b.num1, 0) as "num1",
                       nvl(b.num2, 0) as "num2",
                       nvl(b.num3, 0) as "num3",
                       nvl(b.num4, 0) as "num4",
                       nvl(b.num5, 0) as "num5"
                  from ( SELECT to_char(trunc(sysdate,'hh24')+(to_char(sysdate,'mi')-mod(to_char(sysdate,'mi'),5))/60/24 - 1/24+(rownum*5)/(24*60),'hh24:mi') AS occur_hour
                        FROM DUAL
                        CONNECT BY 12>=ROWNUM ) a
                  left join (
                             select occur_hour,
                                     sum(num1) as num1,
                                     sum(num2) as num2,
                                     sum(num3) as num3,
                                     sum(num4) as num4,
                                     sum(num5) as num5
                               from (select to_char(trunc(t.WARN_STARTDATETIME,'hh24')+(to_char(t.WARN_STARTDATETIME,'mi')-mod(to_char(t.WARN_STARTDATETIME,'mi'),5))/60/24, 'hh24:mi') occur_hour,
                                             decode(trim(t.ALARMLEVEL), '1', 1, 0) as num1,
                                             decode(trim(t.ALARMLEVEL), '2', 1, 0) as num2,
                                             decode(trim(t.ALARMLEVEL), '3', 1, 0) as num3,
                                             decode(trim(t.ALARMLEVEL), '4', 1, 0) as num4,
                                             decode(trim(t.ALARMLEVEL), '5', 1, 0) as num5
                                        from (select * from alarm_rule_details where WARN_STARTDATETIME >= trunc(sysdate,'hh24')-1/24) t
                                        where 1=1
                                        )
                              group by occur_hour) b
                    on b.occur_hour = a.occur_hour
                 order by a.occur_hour

当天时间每隔1小时的sql
select to_char(to_date(to_char(sysdate,'yyyy-mm-dd'), 'yyyy-mm-dd') + (rownum - 1) / 24,'hh24:mi') as occur_hour
                        from dual
                      connect by 24 >= level

select a.occur_hour as "occurHour",
                       nvl(b.num1, 0) as "num1",
                       nvl(b.num2, 0) as "num2",
                       nvl(b.num3, 0) as "num3",
                       nvl(b.num4, 0) as "num4",
                       nvl(b.num5, 0) as "num5"
                  from (select to_char(to_date(to_char(sysdate,'yyyy-mm-dd'), 'yyyy-mm-dd') + (rownum - 1) / 24,'hh24:mi') as occur_hour
                        from dual
                      connect by 24 >= level ) a
                  left join (
                             select occur_hour,
                                     sum(num1) as num1,
                                     sum(num2) as num2,
                                     sum(num3) as num3,
                                     sum(num4) as num4,
                                     sum(num5) as num5
                               from (select to_char(to_date(to_char(sysdate,'yyyy-mm-dd'), 'yyyy-mm-dd') + (rownum - 1) / 24,'hh24:mi') occur_hour,
                                             decode(trim(t.ALARMLEVEL), '1', 1, 0) as num1,
                                             decode(trim(t.ALARMLEVEL), '2', 1, 0) as num2,
                                             decode(trim(t.ALARMLEVEL), '3', 1, 0) as num3,
                                             decode(trim(t.ALARMLEVEL), '4', 1, 0) as num4,
                                             decode(trim(t.ALARMLEVEL), '5', 1, 0) as num5
                                        from (select * from alarm_rule_details where WARN_STARTDATETIME >= trunc(sysdate,'hh24')-1/24) t
                                        where 1=1
                                        )
                              group by occur_hour) b
                    on b.occur_hour = a.occur_hour
                 order by a.occur_hour