按n小时汇总的记录的SQL如何写

Question

Oracle下
数据库记录如下：
time1              value1
2010-10-1 0:00:00 0
2010-10-1 1:00:00 1
2010-10-1 2:00:00 2
2010-10-1 3:00:00 3
2010-10-1 4:00:00 4
2010-10-1 5:00:00 5
2010-10-1 6:00:00 6
2010-10-1 7:00:00 7
2010-10-1 8:00:00 8
2010-10-1 9:00:00 9
2010-10-1 10:00:00 10
2010-10-1 11:00:00 11
2010-10-1 12:00:00 12
2010-10-1 13:00:00 13
2010-10-1 14:00:00 14用户任意指定按n小时(1<=n<=24),对value1字段进行汇总.
请问如何写这个SQL

Answer 1

select sum(svalue) from (
select to_char(time1,'HH24') hour, sum(value1) svalue from temp where to_char(time1,'HH24')<='&n'  group by rollup(to_char(time1,'HH24'))
) where hour < &n

Answer 2

 
SQL> select * from t_timeslot;
 
TIME1                   VALUE1
------------------- ----------
2010-10-01 00:00:00          0
2010-10-01 01:00:00          1
2010-10-01 02:00:00          2
2010-10-01 03:00:00          3
2010-10-01 04:00:00          4
2010-10-01 05:00:00          5
2010-10-01 06:00:00          6
2010-10-01 07:00:00          7
2010-10-01 08:00:00          8
2010-10-01 09:00:00          9
2010-10-01 10:00:00         10
2010-10-01 11:00:00         11
2010-10-01 12:00:00         12
2010-10-01 13:00:00         13
2010-10-01 14:00:00         14
 
15 rows selected
 
SQL> SELECT trunc(t.time1) + floor(to_char(t.time1, 'hh24') / 7) * 7 / 24 starttime,
  2         trunc(t.time1) + floor(to_char(t.time1, 'hh24') / 7) * 7 / 24 +
  3         7 / 24 - 1 / 24 / 60 / 60 endtime,
  4         SUM(value1)
  5    FROM t_timeslot t
  6   GROUP BY trunc(t.time1), floor(to_char(t.time1, 'hh24') / 7);
 
STARTTIME           ENDTIME             SUM(VALUE1)
------------------- ------------------- -----------
2010-10-01 00:00:00 2010-10-01 06:59:59          21
2010-10-01 07:00:00 2010-10-01 13:59:59          70
2010-10-01 14:00:00 2010-10-01 20:59:59          14
 
SQL> 
把其中的数字7替换成用户的输入就可以了

Answer 3

with tb as(
select to_date('2010-10-1 0:00:00','yyyy-mm-dd hh24:mi:ss') time1,0 value1 from dual
union all
select to_date('2010-10-1 1:00:00','yyyy-mm-dd hh24:mi:ss'), 1 from dual
union all
select to_date('2010-10-1 2:00:00','yyyy-mm-dd hh24:mi:ss'), 2 from dual
union all
select to_date('2010-10-1 3:00:00','yyyy-mm-dd hh24:mi:ss'), 3 from dual
union all
select to_date('2010-10-1 4:00:00','yyyy-mm-dd hh24:mi:ss'), 4 from dual
union all
select to_date('2010-10-1 5:00:00','yyyy-mm-dd hh24:mi:ss'), 5 from dual
union all
select to_date('2010-10-1 6:00:00','yyyy-mm-dd hh24:mi:ss'), 6 from dual
union all
select to_date('2010-10-1 7:00:00','yyyy-mm-dd hh24:mi:ss'), 7 from dual
union all
select to_date('2010-10-1 8:00:00','yyyy-mm-dd hh24:mi:ss'), 8 from dual
union all
select to_date('2010-10-1 9:00:00','yyyy-mm-dd hh24:mi:ss'), 9 from dual
union all
select to_date('2010-10-1 10:00:00','yyyy-mm-dd hh24:mi:ss'), 10 from dual
union all
select to_date('2010-10-1 11:00:00','yyyy-mm-dd hh24:mi:ss'), 11 from dual
union all
select to_date('2010-10-1 12:00:00','yyyy-mm-dd hh24:mi:ss'), 12 from dual
union all
select to_date('2010-10-1 13:00:00','yyyy-mm-dd hh24:mi:ss'), 13 from dual
union all
select to_date('2010-10-1 14:00:00','yyyy-mm-dd hh24:mi:ss'), 14 from dual)
select max(time1),sum(value1)
from (select time1,value1,row_number() over(order by time1) rn
from tb)
group by trunc((rn-1)/3)--这里的3就是你说的N
order by 1MAX(TIME1)          SUM(VALUE1)
------------------- -----------
2010-10-01 02:00:00           3
2010-10-01 05:00:00          12
2010-10-01 08:00:00          21
2010-10-01 11:00:00          30
2010-10-01 14:00:00          39

Answer 4

还一种办法是先用connect by构造出小时区间表,然后与t_timeslot关联取值

Answer 5

SQL> SELECT trunc(b.time1) + a.starthour / 24,
  2         trunc(b.time1) + a.endhour / 24 + 1 / 24 - 1 / 24 / 60 / 60,
  3         SUM(value1)
  4    FROM (SELECT (rownum - 1) * 7 starthour,
  5                 CASE
  6                   WHEN rownum * 7 - 1 >= 24 THEN
  7                    23
  8                   ELSE
  9                    rownum * 7 - 1
 10                 END endhour
 11            FROM dual
 12          CONNECT BY (rownum - 1) * 7 < 24) a,
 13         t_timeslot b
 14   WHERE to_char(b.time1, 'hh24') BETWEEN a.starthour AND a.endhour
 15   GROUP BY trunc(b.time1), a.starthour, a.endhour
 16   ORDER BY trunc(b.time1) + a.starthour / 24
 17  ;
 
 
TO_CHAR(TRUNC(B.TIME1)+A.START TO_CHAR(TRUNC(B.TIME1)+A.ENDHO SUM(VALUE1)
------------------------------ ------------------------------ -----------
2010-10-01 00:00:00            2010-10-01 06:59:59                     21
2010-10-01 07:00:00            2010-10-01 13:59:59                     70
2010-10-01 14:00:00            2010-10-01 20:59:59                     14
 
SQL> 

Answer 6

SELECT TRUNC(time1),TO_CHAR(time1,'Hh'),SUM(VALUE1)  FROM TABLE
GROUP BY TRUNC(time1),TO_CHAR(time1,'Hh');

Answer 7

--还是aspen的比较帅，呵呵，我也这个思路
select max(time1),sum(value1) from 
(select time1,value1,row_number() over(order by time1) rn from tb)
group by trunc((rn-1)/N)--这里的N就是给定的任意小时
order by 1

Answer 8

这个好像不对吧SQL> with tb as(
  2  select to_date('2010-10-1 0:00:00','yyyy-mm-dd hh24:mi:ss') time1,0 value1 from dual
  3  union all
  4  select to_date('2010-10-1 9:00:00','yyyy-mm-dd hh24:mi:ss'), 9 from dual
  5  union all
  6  select to_date('2010-10-1 10:00:00','yyyy-mm-dd hh24:mi:ss'), 10 from dual
  7  union all
  8  select to_date('2010-10-1 11:00:00','yyyy-mm-dd hh24:mi:ss'), 11 from dual
  9  union all
 10  select to_date('2010-10-1 12:00:00','yyyy-mm-dd hh24:mi:ss'), 12 from dual
 11  union all
 12  select to_date('2010-10-1 13:00:00','yyyy-mm-dd hh24:mi:ss'), 13 from dual
 13  union all
 14  select to_date('2010-10-1 14:00:00','yyyy-mm-dd hh24:mi:ss'), 14 from dual)
 15  select max(time1),sum(value1)
 16  from (select time1,value1,row_number() over(order by time1) rn
 17  from tb)
 18  group by trunc((rn-1)/3)--这里的3就是你说的N
 19  order by 1
 20  ;
 
MAX(TIME1)                     SUM(VALUE1)
------------------------------ -----------
2010-10-01 10:00:00                     19
2010-10-01 13:00:00                     36
2010-10-01 14:00:00                     14
 
SQL> 

Answer 9

这样可能更好看出来问题
SQL> with tb as(
  2  select to_date('2010-10-1 0:00:00','yyyy-mm-dd hh24:mi:ss') time1,1 value1 from dual
  3  union all
  4  select to_date('2010-10-1 9:00:00','yyyy-mm-dd hh24:mi:ss'), 9 from dual
  5  union all
  6  select to_date('2010-10-1 10:00:00','yyyy-mm-dd hh24:mi:ss'), 10 from dual
  7  union all
  8  select to_date('2010-10-1 11:00:00','yyyy-mm-dd hh24:mi:ss'), 11 from dual
  9  union all
 10  select to_date('2010-10-1 12:00:00','yyyy-mm-dd hh24:mi:ss'), 12 from dual
 11  union all
 12  select to_date('2010-10-1 13:00:00','yyyy-mm-dd hh24:mi:ss'), 13 from dual
 13  union all
 14  select to_date('2010-10-1 14:00:00','yyyy-mm-dd hh24:mi:ss'), 14 from dual)
 15  select max(time1),sum(value1)
 16  from (select time1,value1,row_number() over(order by time1) rn
 17  from tb)
 18  group by trunc((rn-1)/3)--这里的3就是你说的N
 19  order by 1
 20  ;
 
MAX(TIME1)                     SUM(VALUE1)
------------------------------ -----------
2010-10-01 10:00:00                     20
2010-10-01 13:00:00                     36
2010-10-01 14:00:00                     14
 
SQL> 

Answer 10

SQL> ----把10改成你的 1<n<24就可以了
SQL> with tb as(
  2  select to_date('2010-10-1 0:00:00','yyyy-mm-dd hh24:mi:ss') time1,0 value1 from dual
  3  union all
  4  select to_date('2010-10-1 1:00:00','yyyy-mm-dd hh24:mi:ss'), 1 from dual
  5  union all
  6  select to_date('2010-10-1 2:00:00','yyyy-mm-dd hh24:mi:ss'), 2 from dual
  7  union all
  8  select to_date('2010-10-1 3:00:00','yyyy-mm-dd hh24:mi:ss'), 3 from dual
  9  union all
 10  select to_date('2010-10-1 4:00:00','yyyy-mm-dd hh24:mi:ss'), 4 from dual
 11  union all
 12  select to_date('2010-10-1 5:00:00','yyyy-mm-dd hh24:mi:ss'), 5 from dual
 13  union all
 14  select to_date('2010-10-1 6:00:00','yyyy-mm-dd hh24:mi:ss'), 6 from dual
 15  union all
 16  select to_date('2010-10-1 7:00:00','yyyy-mm-dd hh24:mi:ss'), 7 from dual
 17  union all
 18  select to_date('2010-10-1 8:00:00','yyyy-mm-dd hh24:mi:ss'), 8 from dual
 19  union all
 20  select to_date('2010-10-1 9:00:00','yyyy-mm-dd hh24:mi:ss'), 9 from dual
 21  union all
 22  select to_date('2010-10-1 10:00:00','yyyy-mm-dd hh24:mi:ss'), 10 from dual
 23  union all
 24  select to_date('2010-10-1 11:00:00','yyyy-mm-dd hh24:mi:ss'), 11 from dual
 25  union all
 26  select to_date('2010-10-1 12:00:00','yyyy-mm-dd hh24:mi:ss'), 12 from dual
 27  union all
 28  select to_date('2010-10-1 13:00:00','yyyy-mm-dd hh24:mi:ss'), 13 from dual
 29  union all
 30  select to_date('2010-10-1 14:00:00','yyyy-mm-dd hh24:mi:ss'), 14 from dual)
 31  select max(time1), sum(value1)
 32    from tb
 33    where sign(10 - to_char(time1, 'hh24')) >0
 34   group by trunc(time1),sign(10 - to_char(time1, 'hh24'))
 35  ;MAX(TIME1)  SUM(VALUE1)
----------- -----------
2010-10-1 ?         45SQL>

Answer 11

看了半天，还是觉得楼主的表达不是很清楚。大家哪来的根据，这么肯定的做出答案呢？疑问
1.  查询参数 小时数 N，这点知道了。 但结果呢？ 想要什么样的结果？
2.  查询参数 小时数 N ,  是否有特殊要求。 例如，是要求统计某一日的前N小时，还是从任意时间段开始的N小时？ 又或，其它要求？
至于其它，还没想到。 但，上面2点没清楚之前，很难理解上面的答案，是否就如楼主要求。

Answer 12

因为是动态生成SQL，所在采用了minitoy的方法。
Phoenix_99，wolianlin1987 给的结果测了一下好像不太正确，不知道是不是我问题。
感谢zhuomingwang，Oraclefans_的方案.同样coolkisses的疑问.

Answer 13

十分感谢coolkisses提出的疑问.