oracle – 按小时或按日分组记录并用零或空填充缺口

我写了一个按小时统计记录的查询：

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

此外,我也有按日和月分组的查询！

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'),'YYYY-MM');

我需要将它们的间隙填充为零或零.
任何帮助都非常感谢.

解决方法

尝试：
第一次查询(按小时)：

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,'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,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,'mm') mxd from req) v
      connect by add_months(mnd,level-1) <= mxd
      )
    select to_char(trunc(d1,'mm'),'yyyy-mm'),'mm'))
    group by trunc(d1,'mm');

oracle – 按小时或按日分组记录并用零或空填充缺口

解决方法

猜你在找的Oracle相关文章