Orcale按时间单位统计

一.Oracle统计时间范围内的日,周,月: --获取日期列表：

SELECT TO_CHAR(TO_DATE('20200601', 'yyyyMMdd') + ROWNUM - 1, 'yyyyMMdd') as daylist FROM DUAL CONNECT BY ROWNUM <= trunc(to_date('20200712', 'yyyyMMdd') - to_date('20200601', 'yyyyMMdd')) + 1

                    --获取月份列表：

SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201912', 'yyyyMM'), ROWNUM - 1),'yyyyMM') as monthlist   FROM DUAL   CONNECT BY ROWNUM <= months_between(to_date('202007', 'yyyyMM'), to_date('201912', 'yyyyMM')) + 1

--获取周列表: 获取自然周   以周一开始  -周日结束

SELECT trunc(to_DATE('20200622', 'YYYYMMDD') + (ROWNUM - 1) * 7, 'iw') AS mon,trunc(to_DATE('20200622', 'YYYYMMDD') + (ROWNUM - 1) * 7, 'iw') + 6 AS sun FROM DUAL CONNECT BY ROWNUM <= (trunc(to_DATE('20200712', 'YYYYMMDD'), 'iw') + 6 - trunc(to_DATE('20200622', 'YYYYMMDD'), 'iw')) / 7 + 1

二.Oracle统计时间范围内,按日,周,月统计记录数

--时间单位是每日

SELECT t.countNum From (     SELECT to_char(signdate,'yyyymmdd'),count(1) countNum     FROM HMAOP_MEMBERSIGN_RECORD      WHERE 1 = 1       and telnum = '13677173658'       and signdate >=TO_DATE('20190101', 'YYYYMMDD')        and signdate <=TO_DATE('20200101', 'YYYYMMDD')      group by to_char(signdate,'yyyymmdd')  )t

--时间单位是每周,周一开始,周日结束

SELECT t.countNum From (  SELECT TRUNC(signdate,'iw'),count(1) countNum     FROM  HMAOP_MEMBERSIGN_RECORD      WHERE 1 = 1       and telnum = '13677173658'       and signdate >=TO_DATE('20190101', 'YYYYMMDD')        and signdate <=TO_DATE('20200101', 'YYYYMMDD')      group by TRUNC(signdate,'iw')   )t

--时间单位是每月

SELECT t.countNum From (     SELECT to_char(signdate,'yyyymm'),count(1) countNum     FROM HMAOP_MEMBERSIGN_RECORD      WHERE 1= 1       and telnum = '13677173658'       and signdate >=TO_DATE('20190101', 'YYYYMMDD')        and signdate <=TO_DATE('20200101', 'YYYYMMDD')      group by to_char(signdate,'yyyymm')  )t