按小时或按天对记录进行分组，并用零或空值填充空白 [英] Grouping records hour by hour or day by day and filling gaps with zero or null

问题描述

我写了一个查询，按小时计算记录:

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');

结果是:

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

我也有这些按天和月分组的查询！

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');

第二次查询(按天):

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');

第三次查询(按月):

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');

这篇关于按小时或按天对记录进行分组，并用零或空值填充空白的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！