如何对特定时间段进行求和分组

Question

我有30分钟的数据，想要从当前日期的02:00 AM到下一日期的02:00 AM按天计算小时数和分组，我该怎么做？：

create table test (code varchar2(50), hour number, daytime date); 

insert into test (code, hour, daytime) values ('K11','0.5','23-Feb-17')
insert into test (code, hour, daytime) values ('K11','0.5','23-02-17 00:30')
insert into test (code, hour, daytime) values ('K11','0.5','23-02-17 01:00')
insert into test (code, hour, daytime) values ('K11','0.5','23-02-17 01:30')
insert into test (code, hour, daytime) values ('K11','0.5','23-02-17 02:00')
insert into test (code, hour, daytime) values ('K11','0.5','23-02-17 02:30')
insert into test (code, hour, daytime) values ('K11','0.5','24-Feb-17')
insert into test (code, hour, daytime) values ('K11','0.5','24-02-17 00:30')
insert into test (code, hour, daytime) values ('K11','0.5','24-02-17 01:00')
insert into test (code, hour, daytime) values ('K11','0.5','24-02-17 01:30')
insert into test (code, hour, daytime) values ('K11','0.5','24-02-17 02:00')
insert into test (code, hour, daytime) values ('K11','0.5','24-02-17 02:30')
insert into test (code, hour, daytime) values ('K12','0.5','23-Feb-17')
insert into test (code, hour, daytime) values ('K12','0.5','23-02-17 00:30')
insert into test (code, hour, daytime) values ('K12','0.5','23-02-17 01:00')
insert into test (code, hour, daytime) values ('K12','0.5','23-02-17 01:30')
insert into test (code, hour, daytime) values ('K12','0.5','23-02-17 02:00')
insert into test (code, hour, daytime) values ('K12','0.5','23-02-17 02:30')
insert into test (code, hour, daytime) values ('K12','0.5','24-Feb-17')
insert into test (code, hour, daytime) values ('K12','0.5','24-02-17 00:30')
insert into test (code, hour, daytime) values ('K12','0.5','24-02-17 01:00')
insert into test (code, hour, daytime) values ('K12','0.5','24-02-17 01:30')
insert into test (code, hour, daytime) values ('K12','0.5','24-02-17 02:00')
insert into test (code, hour, daytime) values ('K12','0.5','24-02-17 02:30')

最后，我想获得以下数据：

'K11',3,'23-02-17'
'K12',3,'23-02-17'

以K11为例，从('K11'，'0.5'，'23-02-17 02:00')到('K11'，'0.5'，'24-02-17 01:00')求和小时数

Answer 1

你可以通过减去两个小时来做到这一点。以下是一种方法：

select trunc(date - 2/24) as dte, sum(hours)
from t
group by trunc(date - 2/24)
order by dte;

Oracle现在支持interval语法，因此您还可以：

select trunc(date - interval 2 hour) as dte, sum(hours)
from t
group by trunc(date - interval 2 hour)
order by dte;