oracle 按小时或按天对记录进行分组，并用零或空值填充空白
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10798905/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grouping records hour by hour or day by day and filling gaps with zero or null
提问by Heidarzadeh
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.
我也需要将它们的间隙填充为零或空值。任何帮助都非常感谢。
回答by A.B.Cade
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');
