统计某指定时间段内每周每月数据，含分组数据为0

1. 按月：

(1) 简单分组查询

-- 分组后查询数据: 仅1月份6条，2月份因为不存在记录，没有显示
SELECT to_char(r.sys_created_on, 'yyyy-mm') AS create_time, count(*) AS quantity
  FROM table_r r,table_g g
 WHERE r.assignment_group = g.sys_id 
       AND g.name = 'Provisioning Ops'
   AND r.sys_created_on BETWEEN 
                TO_DATE('2019-01-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                TO_DATE('2019-02-18 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
 GROUP BY to_char(r.sys_created_on, 'yyyy-mm')
 ORDER BY to_char(r.sys_created_on, 'yyyy-mm');

 

 

 

（2）构建虚拟月份表

-- 虚拟月份
SELECT TO_CHAR(ADD_MONTHS(TO_DATE(substr('2019-01-19 23:59:59', 1, 7), 'yyyy-MM'), ROWNUM - 1),
               'yyyy-MM') AS create_time
  FROM DUAL
CONNECT BY ROWNUM <=
           months_between(TO_DATE(substr('2019-02-18 23:59:59', 1, 7), 'YYYY-MM'),
                          TO_DATE(substr('2019-01-19 23:59:59', 1, 7), 'yyyy-MM')) + 1;

 

 

 

（3）连接后分组查询：

-- 左连接
SELECT a.create_time as create_time, nvl(b.quantity, 0) as quantity
  FROM (SELECT TO_CHAR(ADD_MONTHS(TO_DATE(substr('2019-01-19 23:59:59', 1, 7),
                                          'yyyy-MM'),
                                  ROWNUM - 1),
                       'yyyy-MM') AS create_time
          FROM DUAL
        CONNECT BY ROWNUM <=
                   months_between(TO_DATE(substr('2019-02-18 23:59:59', 1, 7),
                                          'YYYY-MM'),
                                  TO_DATE(substr('2019-01-19 23:59:59', 1, 7),
                                          'yyyy-MM')) + 1) a
  LEFT JOIN (SELECT to_char(r.sys_created_on, 'yyyy-mm') AS create_time,
                    count(*) AS quantity
               FROM table_r r, table_g g
              WHERE r.assignment_group = g.sys_id
                AND g.name = 'Provisioning Ops'
                AND r.sys_created_on BETWEEN
                    TO_DATE('2019-01-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                    TO_DATE('2019-02-18 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
              GROUP BY to_char(r.sys_created_on, 'yyyy-mm')
              ORDER BY to_char(r.sys_created_on, 'yyyy-mm')) b
    ON a.create_time = b.create_time
 ORDER BY a.create_time
 

 

 

 

 

 

2. 按周：

(1) 简单分组查询：

-- 分组查询：仅有2周存在记录，2019-01-14和2019-01-21分别为这两周的周一
SELECT to_char(next_day(r.sys_created_on + 15 / 24 - 7, 2), 'YYYY-MM-DD') create_time,
       count(*) quantity
  FROM table_r r, table_g g
 WHERE r.assignment_group = g.sys_id
   AND g.name = 'Provisioning Ops'
   AND r.sys_created_on BETWEEN
       TO_DATE('2019-01-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
       TO_DATE('2019-02-18 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
 GROUP BY to_char(next_day(r.sys_created_on + 15 / 24 - 7, 2), 'YYYY-MM-DD')
 ORDER BY to_char(next_day(r.sys_created_on + 15 / 24 - 7, 2), 'YYYY-MM-DD')

 

 

 

 

(2) 构建虚拟周数表：

-- 虚拟周数
SELECT to_char(trunc(TO_DATE('2019-01-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS') +
                     (ROWNUM - 1) * 7,
                     'd') + 1,
               'yyyy-MM-dd') AS create_time
  FROM DUAL
CONNECT BY ROWNUM <=
           (TRUNC(TO_DATE('2019-02-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),
                  'IW') -
           TRUNC(TO_DATE('2019-01-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS'),
                  'IW')) / 7 + 1

 

 

 

(3) 连接后分组查询：

-- 左连接
SELECT a.create_time as create_time, nvl(b.quantity, 0) as quantity
  FROM (SELECT to_char(trunc(TO_DATE('2019-01-19 00:00:00',
                                     'YYYY-MM-DD HH24:MI:SS') +
                             (ROWNUM - 1) * 7,
                             'd') + 1,
                       'yyyy-MM-dd') AS create_time
          FROM DUAL
        CONNECT BY ROWNUM <=
                   (TRUNC(TO_DATE('2019-02-18 00:00:00',
                                  'YYYY-MM-DD HH24:MI:SS'),
                          'IW') - TRUNC(TO_DATE('2019-01-19 23:59:59',
                                                 'YYYY-MM-DD HH24:MI:SS'),
                                         'IW')) / 7 + 1) a
  LEFT JOIN (SELECT to_char(next_day(r.sys_created_on + 15 / 24 - 7, 2),
                            'YYYY-MM-DD') create_time,
                    count(*) quantity
               FROM table_r r, table_g g
              WHERE r.assignment_group = g.sys_id
                AND g.name = 'Provisioning Ops'
                AND r.sys_created_on BETWEEN
                    TO_DATE('2019-01-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND
                    TO_DATE('2019-02-18 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
              GROUP BY to_char(next_day(r.sys_created_on + 15 / 24 - 7, 2),
                               'YYYY-MM-DD')
              ORDER BY to_char(next_day(r.sys_created_on + 15 / 24 - 7, 2),
                               'YYYY-MM-DD')) b
    ON a.create_time = b.create_time
 ORDER BY a.create_time
 

 

 

 

参考：

周数转日期： https://www.cnblogs.com/Innogen/p/8269092.html

构建虚拟表：https://www.cnblogs.com/LLSutdy/p/7998322.html

整体参考：https://blog.csdn.net/weixin_36150793/article/details/81607574

 

 

纠正：

按周查询那条sql测试有问题，本地测时发现如下问题：

select to_char(next_day(TO_DATE('2019-02-17 08:59:00', 'YYYY-MM-DD HH24:MI:SS') + 15 / 24 - 7, 2), 'YYYY-MM-DD'),
       to_char(next_day(TO_DATE('2019-02-17 09:00:00', 'YYYY-MM-DD HH24:MI:SS') + 15 / 24 - 7, 2), 'YYYY-MM-DD'),
       dbtimezone
  from dual;

 本人猜测应该算法还是这个算法，只是和时区关联上了，因为本公司DB时区统一采用UTC时间（=GMT时间，其实他们还是有区别的），所以找了一个周的交叉点用笨方法测试了一下（就是一个个改数字，好笨啊，毕竟算法没看懂），sql如下：

select to_char(next_day(TO_DATE('2019-02-16 23:59:59', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a1,
       to_char(next_day(TO_DATE('2019-02-17 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a2,
       to_char(next_day(TO_DATE('2019-02-17 02:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a3,
       to_char(next_day(TO_DATE('2019-02-17 04:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a4,
       to_char(next_day(TO_DATE('2019-02-17 06:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a5,
       to_char(next_day(TO_DATE('2019-02-17 08:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a6,
       to_char(next_day(TO_DATE('2019-02-17 10:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a7,
       to_char(next_day(TO_DATE('2019-02-17 12:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a8,
       to_char(next_day(TO_DATE('2019-02-17 14:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a9,
       to_char(next_day(TO_DATE('2019-02-17 16:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a10,
       to_char(next_day(TO_DATE('2019-02-17 18:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a11,
       to_char(next_day(TO_DATE('2019-02-17 20:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a12,
       to_char(next_day(TO_DATE('2019-02-17 22:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a13,
       to_char(next_day(TO_DATE('2019-02-17 23:59:59', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a14,
       to_char(next_day(TO_DATE('2019-02-18 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a15,
       to_char(next_day(TO_DATE('2019-02-18 23:59:59', 'YYYY-MM-DD HH24:MI:SS') + 0 / 24 - 7, 2), 'YYYY-MM-DD') as a16,
       dbtimezone
  from dual;

可以看到明显有个时间的跳变，正好和日期对上了！