问题描述
我写了一个按小时计数的查询:
select TO_CHAR(copied_timestamp,'YYYY-MM-DD HH24'),从请求组计数(*)到
TO_CHAR(copied_timestamp,'YYYY-MM-DD HH24');
结果是:
2012-02-22 13 2280
2012-02-22 15 1250
2012-02-22 16 1245
2012-02-22 19 1258
但是我需要这样的结果:
2012-02-22 13 2280
2012-02-22 14 0
2012-02-22 15 1250
2012-02-22 16 1245
2012-02-22 17 0
2012-02-22 18 0
2012-02-22 19 1258
另外我也有这些查询按日期和月份分组!
select TO_CHAR( TO_CHAR(copied_timestamp,'YYYY-MM-DD');从req
组计数(*)到TO_CHAR(copied_timestamp,'YYYY-MM-DD');
通过TO_CHAR(copied_timestamp,'YYYY-MM')选择TO_CHAR(copied_timestamp,'YYYY-MM'),从req
组中计数(*);
我需要填充零或零。
提供任何帮助。
试试:
第一个查询(按小时) :
with t as(
select mnd +((level-1)/ 24)ddd
从
(从req选择trunc(min(copied_timestamp),'hh')mnd,trunc(max(copied_timestamp),'hh')mxd)v
通过mnd +((level-1) / 24))
从
中选择to_char(trunc(d1,'hh'),'yyyy-mm-dd hh24'),count(d2)(select nvl (copied_timestamp,ddd)d1,copied_timestamp d2 from req右外连接(
从dd选择ddd)ddd = trunc(copied_timestamp,'hh'))
group by trunc(d1,'hh' );
第二个查询(按天):
<$从
中选择mnd + level-1 ddd
(select trunc(min(copied_timestamp),'dd')mnd,trunc (max(copied_timestamp),'dd')mxd from req)v
connect by mnd + level-1 )
select to_char(trunc(d1,'dd') ,'yyyy-mm-dd'),从
开始计数(d2)(从req右外连接中选择nvl(copied_timestamp,ddd)d1,copies_timestamp d2(
从t中选择ddd)ddd = trunc(copied_timestamp,'dd'))
by trunc(d1,'dd');
第三个查询(按月):
<$从
中选择add_months(mnd,level-1)ddd
(select trunc(min(copied_timestamp),'mm') (mnd,level-1))
选择to_char(trunc(从
开始计数(d2)(从复制右外部联接中选择nvl(copied_timestamp,ddd)d1,copies_timestamp d2(
从t中选择ddd)d1,'mm'),'yyyy- ad on ddd = trunc(copied_timestamp,'mm'))
by trunc(d1,'mm');
I have written a query that counts records hour by hour:
select TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24'),count(*) from req group by
TO_CHAR(copied_timestamp, 'YYYY-MM-DD HH24');
the result is:
2012-02-22 13 2280
2012-02-22 15 1250
2012-02-22 16 1245
2012-02-22 19 1258
But I need a result like this:
2012-02-22 13 2280
2012-02-22 14 0
2012-02-22 15 1250
2012-02-22 16 1245
2012-02-22 17 0
2012-02-22 18 0
2012-02-22 19 1258
Also I have these queries that group by day and month too!
select TO_CHAR(copied_timestamp, 'YYYY-MM-DD'),count(*) from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM-DD');
select TO_CHAR(copied_timestamp, 'YYYY-MM'),count(*) from req
group by TO_CHAR(copied_timestamp, 'YYYY-MM');
I need their gaps to be filled with zero or null too.Any help is really appreciated.
try:
first query (by hour):
with t as (
select mnd + ((level-1)/24) ddd
from
(select trunc(min(copied_timestamp),'hh') mnd, trunc(max(copied_timestamp),'hh') mxd from req) v
connect by mnd + ((level-1)/24) <= mxd
)
select to_char(trunc(d1, 'hh'), 'yyyy-mm-dd hh24'), count(d2) from
(select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
select ddd from t) ad on ddd = trunc(copied_timestamp, 'hh'))
group by trunc(d1, 'hh');
second query (by day):
with t as (
select mnd + level-1 ddd
from
(select trunc(min(copied_timestamp),'dd') mnd, trunc(max(copied_timestamp),'dd') mxd from req) v
connect by mnd + level-1 <= mxd
)
select to_char(trunc(d1, 'dd'), 'yyyy-mm-dd'), count(d2) from
(select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
select ddd from t) ad on ddd = trunc(copied_timestamp, 'dd'))
group by trunc(d1, 'dd');
third query (by month):
with t as (
select add_months(mnd, level-1) ddd
from
(select trunc(min(copied_timestamp),'mm') mnd, trunc(max(copied_timestamp),'mm') mxd from req) v
connect by add_months(mnd, level-1) <= mxd
)
select to_char(trunc(d1, 'mm'), 'yyyy-mm'), count(d2) from
(select nvl(copied_timestamp, ddd) d1, copied_timestamp d2 from req right outer join (
select ddd from t) ad on ddd = trunc(copied_timestamp, 'mm'))
group by trunc(d1, 'mm');
这篇关于按小时或按天分组记录,并用零或空填充空位的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!