SQL/Oracle按给定日期的某个类型按字段分组数据

Question

好吧，试图构建单个查询来为自己节省一大堆时间（而不是写大量的单独查询），但是我不'甚至不知道该如何开始。

我需要的是看看single day and type，并在上午8点至晚上8点之间按小时计算行动。因此，例如我有以下的假表

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 

所以，如果我想看看所有的记录与

• TYPE_ = 'A'
• 上日期2010-11-19
• 通过ACTION_每小时

分组我会看到这个结果

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 

或类似的东西，但至少应该提供一个我正在寻找的东西的想法。

任何人都可以帮忙吗？通常我会尝试提供一些我正在使用的示例代码，但我不知道如何通过条件来实现这一点。

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; 

现在，上面的select语句只返回小时其中至少有行动。为了显示所有小时的纪录 - {处理/未决}组合，下列修订提出，要查询：

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

顺便说一句，这里的DDL和DML我用：

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