SQL / Oracle Grouping field data by hour for a specific type on a specific day

Question

Well, trying to build one query to save myself a whole bunch of time (instead of writing a ton of individual queries), but I don’t even know how to start from that.

I need to look at single day and type and break down the number of actions into actions, by the hour, from 8:00 to 20:00. 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 see all the records using

• TYPE_ = 'A'
• on the 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 like this, but it should at least give an idea of ​​what I'm looking for.

Can anyone help? I usually try to provide some sample code that I work with, but I have no idea how I will work with the group on the articles necessary for this.

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 select statement above returns a watch that has at least an action. To show a record for all hours - {processed / pending} combinations, the following changes should be made to the request:

 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_; 

By the way, DDL and DML are used here:

 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