oracle统计一段时间内每天的数据量，无数据是自动补零！

最新推荐文章于 2023-08-19 15:54:11 发布

头大如钟

最新推荐文章于 2023-08-19 15:54:11 发布

阅读量4.7k

点赞数 3

分类专栏： oracle 文章标签： oracle 统计

本文链接：https://blog.csdn.net/qq_39618582/article/details/85340334

版权

oracle 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

select a.time,nvl(b.num,0) as num from (
select to_char(to_date('2018/07/20', 'yyyy-mm-dd') + rownum - 1,
               'yyyy-mm-dd') as time
from dual
connect by rownum <=
           trunc(to_date('2018/08/20', 'yyyy/mm/dd') - to_date('2018/07/20', 'yyyy/mm/dd')) + 1)a
left join
                (select time,count(time) as num from (SELECT to_char(INPUT_DATE,'yyyy-mm-dd') as time FROM ORDER_REGISTRATION_  where INPUT_DATE between to_date('2018/07/20', 'yyyy/mm/dd')
  and to_date('2018/08/20', 'yyyy/mm/dd') + 1) group by time order by time)b on a.time = b.time order by a.time;

其中有三个select，，第二个select是将时间段生成日期，第二个select是将表中的不为0的数据量统计出来，然后用left join连接起来，在用第一个select将符合条件的查出来，无数据的自动补零

有需要添加限制条件的在第三个select中添加即可

折腾死我了！

具体例子：

查询一段时间内的锁号量和挂号量

锁号量为registeration表中每天的数据条数；

挂号量为order_表中每天status=3的数据条数；

代码实现：

select a.time,nvl(b.sum,0) as bnum,nvl(c.sum,0) as cnum from
      (select to_char(to_date('2018/10/20', 'yyyy-mm-dd') + rownum - 1,'yyyy-mm-dd') as time from dual
       connect by rownum <= trunc(to_date('2018/11/22', 'yyyy/mm/dd') - to_date('2018/10/20', 'yyyy/mm/dd')) + 1)a,
      (select time,count(time) as sum from
        (SELECT to_char(INPUT_DATE,'yyyy-mm-dd') as time FROM ORDER_REGISTRATION_
         where INPUT_DATE between to_date('2018/10/20', 'yyyy/mm/dd') and to_date('2018/11/22', 'yyyy/mm/dd') + 1) group by time)b,
      (select time,count(time) as sum from
        (SELECT to_char(INPUT_DATE,'yyyy-mm-dd') as time FROM ORDERS_  where INPUT_DATE between
          to_date('2018/10/20', 'yyyy/mm/dd') and to_date('2018/11/22', 'yyyy/mm/dd') + 1 and STATUS='3') group by time)c
       where a.time=b.time(+) and a.time=c.time(+) order by a.time;