oracle 按天数 均值,oracle 按天数统计数据

当使用group by crateTime时,但是某天没有数据时,就出现某天没有数据了.

所以使用以下sql,能把每天的都统计出来.

select

to_date('2015-06-01', 'yyyy-mm-dd')+level -1 as 时间

from dual connect by to_date('2015-06-01', 'yyyy-mm-dd') + level -1 <= to_date('2015-06-04', 'yyyy-mm-dd');

select

(to_date('2015-06-01','yyyy-mm-dd')+level - 1) as 时间

from dual connect by level <= to_date('2015-06-04','yyyy-mm-dd') - to_date('2015-06-01','yyyy-mm-dd') + 1;

查询结果如下:

时间

2015-6-1

2015-6-2

2015-6-3

2015-6-4

然后再使用left on 链接去查询统计数据:如

select c_time,nvl(totalUser,0) as total_user from (

select (to_date('2015-06-01','yyyy-mm-dd')+level - 1) as c_time from dual connect by level <= to_date('2015-06-11','yyyy-mm-dd') - to_date('2015-06-01','yyyy-mm-dd') + 1

) a left join (

select trunc(u.create_time) as create_time,count(*) as totalUser from t_user u group by trunc(u.create_time)

) b on a.c_time = b.create_time order by c_time