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
一般用来查找存在父子关系的数据，也就是树形结构的数据；其返还的数据也能够明确的区分出每一层的数据。
start with condition1 是用来限制第一层的数据，或者叫根节点数据；以这部分数据为基础来查找第二层数据，然后以第二层数据查找第三层数据以此类推。
connect by [prior] id=parentid 这部分是用来指明oracle在查找数据时以怎样的一种关系去查找；比如说查找第二层的数据时用第一层数据的id去跟表里面记录的parentid字段进行匹配，如果这个条件成立那么查找出来的数据就是第二层数据，同理查找第三层第四层…等等都是按这样去匹配。
