达梦 oracle-按年、月、周、日、时、分 分组查询统计数据,无数据补零(connect by)

目的:统计一段时间内每年、每月、每周、每日、每时、每分数据,无数据时自动补零

思路:1. 生成给定时间段对应日期

           2. 将原表中该时间段内的不为0的数据量统计出来

           3. 用left join连接起来,无数据的自动补零

难点主要在于步骤一中生成该时间段对应的日期,话不多说,直接贴代码:

- - 获取某时间段内的每年

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 4), 'yyyy'), (ROWNUM - 1) * 12), 'yyyy')  AS DATES  FROM DUAL
CONNECT BY ROWNUM <= CEIL(MONTHS_BETWEEN(TO_DATE(SUBSTR('2020-01-12 08:01:00', 1, 4), 'yyyy'), TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 4),
'yyyy')) / 12 +1)


- - 获取某时间段内的每月

SELECT TO_CHAR(ADD_MONTHS(TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 7), 'YYYY-MM'), ROWNUM - 1), 'YYYY-MM') AS DATES FROM DUAL
CONNECT BY ROWNUM <= CEIL(MONTHS_BETWEEN(TO_DATE(SUBSTR('2020-01-12 08:01:00', 1, 7), 'YYYY-MM'), TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 7),
'YYYY-MM'))+1)


- - 获取某时间段内的每周

SELECT TO_CHAR(TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 10), 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'IW') AS DATE FROM DUAL
CONNECT BY ROWNUM <=TRUNC(TO_DATE(SUBSTR('2020-01-12 08:01:00', 1, 10), 'YYYY-MM-DD') - TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 10),
 'YYYY-MM-DD')) / 7 + 1


- - 获取某时间段内的每日

SELECT TO_CHAR(TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 10), 'yyyy-mm-dd') + ROWNUM - 1, 'yyyy-mm-dd') AS DATAS FROM DUAL
CONNECT BY ROWNUM <= TRUNC(TO_DATE(SUBSTR('2020-01-12 08:01:00', 1, 10), 'yyyy-mm-dd') - TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 10), 'yyyy-mm-dd')) + 1


- - 获取某时间段内的每时

SELECT to_char(TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 13), 'YYYY-MM-DD HH24') + (ROWNUM - 1) / 24,'yyyy-mm-dd HH24') AS DATES FROM DUAL
CONNECT BY ROWNUM <= floor(to_number(TO_DATE(SUBSTR('2020-01-12 08:01:00', 1, 13), 'yyyy-mm-dd HH24')-to_date(SUBSTR('2019-12-12 08:01:00', 1, 13),
'yyyy-mm-dd hh24'))*24 +1)


- - 获取某时间段内的每分

SELECT to_char(TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 16), 'YYYY-MM-DD HH24:MI') + (ROWNUM-1 ) / (24 * 60) ,'yyyy-mm-dd HH24:MI') AS DATES FROM DUAL
CONNECT BY ROWNUM <= floor(to_number(TO_DATE(SUBSTR('2020-01-12 08:01:00', 1, 16), 'yyyy-mm-dd HH24:MI')-to_date(SUBSTR('2019-12-12 08:01:00', 1, 16),
'yyyy-mm-dd hh24:MI'))*(24 * 60) +1)


完整版(以周为例)

SELECT NVL(D.DATA1, 0) POWER,NVL(D.UNIT, 'Week') UNIT, C.WEEK TIME1
       FROM(SELECT TO_CHAR(TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 10), 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'IW') AS WEEK
                   FROM DUAL CONNECT BY ROWNUM <=                 TRUNC(TO_DATE(SUBSTR('2020-01-12 08:01:00', 1, 10), 'YYYY-MM-DD') - TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 10), 'YYYY-MM-DD')) / 7 + 1)C
       LEFT JOIN (SELECT SUM(DATA1) DATA1,TIME1,'Week' Unit                    FROM (SELECT SUM(TO_NUMBER(DATA1)) DATA1,to_char(INSERT_TIME,'iw') Time1 FROM TABLE1
                                where to_char(INSERT_TIME,'yyyy-mm-dd')>=SUBSTR('2019-12-12 08:01:00', 1, 10) and                                       to_char(INSERT_TIME,'yyyy-mm-dd')<=SUBSTR('2020-01-12 08:01:00', 1, 10)
                                      GROUP BY to_char(INSERT_TIME,'iw'))
                               GROUP BY TIME1)D
                   ON  C.WEEK = D.TIME1
       ORDER  BY WEEK



Oracle connect by 基本语法:

select * from table [start with condition1]
    connect by [prior] id=parentid

  • 2
    点赞
  • 8
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值