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

尝试：

第一次查询(按小时)：

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