SQL/Oracle Grouping Data by field by hours for a certain type on a given day

Question

Okay trying to construct a single query to save myself a whole bunch of time (rather than writing a ton of seperate queries), but I don't even know how to start on this.

What I need to is look at single day and type and break out counts on actions, by hour, between 8:00am - 8:00pm. So for example I have the following fake table

TYPE_   ACTION_     TIMESTAMP_
------------------------------
A       processed   2010-11-19 10:00:00.000
A       processed   2010-11-19 10:46:45.000
A       processed   2010-11-19 11:46:45.000
A       processed   2010-11-19 12:46:45.000
A       processed   2010-11-19 12:48:45.000
A       pending     2010-11-19 11:46:45.000
A       pending     2010-11-19 11:50:45.000
A       pending     2010-11-19 12:46:45.000
A       pending     2010-11-19 12:48:45.000
B       pending     2010-11-19 19:48:45.000
B       pending     2010-11-19 21:46:45.000
.etc

So if I wanted to look at all records with

• TYPE_ = 'A'
• on date 2010-11-19
• grouped by ACTION_ per hour

I would see this result

ACTION_ NUMOCCURENCES   RANGE
---------------------------------------------
processed  2                10:00:00 - 11:00:00
pending    0                10:00:00 - 11:00:00
processed  1                11:00:00 - 12:00:00
pending    2                11:00:00 - 12:00:00
processed  2                12:00:00 - 13:00:00
pending    2                12:00:00 - 13:00:00

Or something similar to that, but that should at least give an idea of what I am looking for.

Can anyone help? Normally I would try to provide some sample code I'm working with, but I have no idea how I would work with the group by clauses needed to make this happen.

Answer 1

select
   action_,
   count(*) as numoccurences,
   to_char(timestamp_       , 'hh24') || ':00:00-' || 
   to_char(timestamp_ + 1/24, 'hh24') || ':00:00' as range
 from 
   tq84_action 
 where  
   timestamp_ between timestamp '2010-11-19 08:00:00' and 
                      timestamp '2010-11-19 20:00:00' and
   type_ = 'A'
 group by
   action_,
   to_char(timestamp_       , 'hh24') || ':00:00-' || 
   to_char(timestamp_ + 1/24, 'hh24') || ':00:00' 
 order by 
   range;

Now, the above select statement only returns hours in which there is at least on action. In order to show a record for all hour - {processed/pending} combinations, the following amendments should be made to the query:

select
   action_,
   count(type_) as numoccurences,
   to_char(timestamp_       , 'hh24') || ':00:00-' || 
   to_char(timestamp_ + 1/24, 'hh24') || ':00:00' as range_
 from (
   select * from tq84_action 
    where  
      timestamp_ between timestamp '2010-11-19 08:00:00' and 
                         timestamp '2010-11-19 20:00:00' and
      type_ = 'A'
    union all (
      select 
        null as type_,
        action.name_ as action_,
        date '2010-11-19' + 8/24 + hour.counter_ / 24 as timestamp_1
      from (
        select  
          level-1 counter_
        from dual
          connect by level <= 12
      ) hour,
      ( 
        select 'processed' as name_ from dual union all
        select 'pending'   as name_ from dual
      ) action
    )
 )
 group by
   action_,
   to_char(timestamp_       , 'hh24') || ':00:00-' || 
   to_char(timestamp_ + 1/24, 'hh24') || ':00:00' 
 order by 
   range_;

BTW, here's the DDL and DML I used:

drop   table tq84_action;
create table tq84_action (
  type_      varchar2( 1),
  action_    varchar2(10),
  timestamp_ timestamp
);


insert into tq84_action values('A', 'processed' , timestamp '2010-11-19 10:00:00.000');
insert into tq84_action values('A', 'processed' , timestamp '2010-11-19 10:46:45.000');
insert into tq84_action values('A', 'processed' , timestamp '2010-11-19 11:46:45.000');
insert into tq84_action values('A', 'processed' , timestamp '2010-11-19 12:46:45.000');
insert into tq84_action values('A', 'processed' , timestamp '2010-11-19 12:48:45.000');
insert into tq84_action values('A', 'pending'   , timestamp '2010-11-19 11:46:45.000');
insert into tq84_action values('A', 'pending'   , timestamp '2010-11-19 11:50:45.000');
insert into tq84_action values('A', 'pending'   , timestamp '2010-11-19 12:46:45.000');
insert into tq84_action values('A', 'pending'   , timestamp '2010-11-19 12:48:45.000');
insert into tq84_action values('B', 'pending'   , timestamp '2010-11-19 19:48:45.000');
insert into tq84_action values('B', 'pending'   , timestamp '2010-11-19 21:46:45.000');

Answer 2

select
   ACTION_
   count(*) NUMOCCURENCES,
   to_char(TIMESTAMP_, 'hh24') || ':00:00 - ' || to_char(TIMESTAMP_ + 1/24, 'hh24') || ':00:00' RANGE
from tbl
where TIMESTAMP_ between DATE '2010-11-19' and DATE '2010-11-20'
  and TYPE_ = 'A'
  and 1 * to_char(TIMESTAMP_, 'hh24') between 8 and 19
group by ACTION_, to_char(TIMESTAMP_, 'hh24'), to_char(TIMESTAMP_ + 1/24, 'hh24')
order by RANGE, ACTION_ desc