oracle-计算一段时间内按年、月、周、日、时、分分组

oracle-计算一段时间内按年、月、周、日、时、分分组

目的：统计一段时间内每年、每月、每周、每日、每时、每分数据
思路： 1. 生成给定时间段对应日期
2. 将原表中该时间段内的不为0的数据量统计出来
3. 用left join连接起来，无数据的自动补零

1.获取某时间段内的每年

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)

2.获取某时间段内的每月

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)

3.获取某时间段内的每周

SELECT TO_CHAR(TO_DATE(SUBSTR('2019-12-12 08:01:00', 1, 10), 'YYYY-MM-DD') + (ROWNUM - 1) * 7, 'IW') AS DATE1 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

4.获取某时间段内的每日

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

5.获取某时间段内的每时

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)

6.获取某时间段内的每分

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)

转载：https://www.cnblogs.com/snow-leopard/p/12176921.html