Grouping records hour by hour or day by day and filling gaps with zero or null

Question

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.

Ghee

I use JPA native queries so I think there won't be any problem with pl/sql. – Ghee

Does anyone know how to handle this kind of query in Mysql? – Ghee

Answer 1

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

Carburet

Thanks A.B.Cade it works but I don't see hour in first column. i just see day-month-year ! I think it's because of trunc function. how can I use it to show hour too? may be it's because of sql developer that does not show hour? By the way how about the second and third query? – Ghee

Try to replace the trunc(d1, 'hh') with to_char(trunc(d1, 'hh'), 'yyyy-mm-dd hh24'). As for the other queries, they should be the same just trunc them according to "day" with dd or "month" with mm (just replace the hh) – Carburet

@Carburet I think this query is returning wrong results for dd and mm. I'm not familiar with connect by command but I think we should change the number 24 when using dd or mm. It works perfect for hh. even in your fiddle the result for mm and dd is wrong. execute your connect by part with dd and mm and you will see that there are more that one record for each day or month! – Ghee

this is right : select to_char(trunc(mnd + (level/24),'hh'),'yyyy-mm-dd hh24') ddd from (select min(copied_timestamp) mnd, max(copied_timestamp) mxd from req) v connect by mnd + (level/24) <= mxd; but this is wrong select to_char(trunc(mnd + (level/24),'dd'),'yyyy-mm-dd') ddd from (select min(copied_timestamp) mnd, max(copied_timestamp) mxd from req) v connect by mnd + (level/24) <= mxd; – Ghee

You are righ, of course, level is an integer representing the itteration, if you add it to a date type it adds days, so only for hours it makes sense to devide by 24. Fix is simple: for days use mnd+level and for months use add_months(mnd, level). I thought of another thing- level starts from 1 so basically you need to use level-1. I also thought that it should be better to truncate the min/max dates in the inner query. So, here is the new fiddle: sqlfiddle.com/#!4/6b5f5/21 .If it's working right then I'll update my answer (the fiddle won't stay forever...) – Carburet

@Carburet excellent. They work very well. Before you reply I fixed the query and when I wanted to reply, I lost the POWER and after turning on, lost all fixed code in sqlDeveloper ;) – Ghee

By the way this query is a little slow when choosing group by hour because connect by generated lots of records for long ranges. But no problem if this is an efficient way for this kind of query. I will mark this answer accepted ;) and please edit the answer. Thanks a lot A.B.Cade – Ghee

WOW, we were writing comments at the same time! – Ghee