Question

请问一个SQL语句怎么写

原始数据表的格式如下：
time value
00:00 1
00:02 2
... ....
这里是每两分钟记一次数据，一共记录了一天24小时的数据，也就是720条，我想取出每个小时的最大值，平均值等信息，读出的表的格式形式为：
time max avg
1 x x
2 y y
.... ... ....
一共24小时的数据表。
请问怎么做，谢谢~~~~~~~

Answer 1

sqlserver写法
select cast(substring(time,1,2) as int)+1,max(value),avg(value) from 表名 group by cast(substring(time,1,2) as int)+1

oracle写法
select substr(time,1,2)+1,max(value),avg(value) from 表名 group by substr(time,1,2)+1

Answer 2

select to_number(substr(time,1,2))+1 hour_time,
max(value) over (partition by substr(time,1,2)) max_value,
avg(value) over (partition by substr(time,1,2)) avg_value
from test
group by to_number(substr(time,1,2))+1

Answer 3

select trunc(time,'hh'),max_value,avg_value from
(select time,max(value) max_value,avg(value) avg_value from table1 group by time)

Answer 4

select hour(time), max(value) max_value, avg(value) avg_value

from table
group by hour(time)

Answer 5

求平均值的表达式
SELECT AVG( succ_rate ) FROM A WHERE instance IN ('cmnet', 'cmwap')
学sql的你应该看得懂这个表达式，怎么套用就不用我教了吧。

至于最大值对value排个序就好了，查询后在后面用个 order by 句型。

Answer 6

select substr(time,0,2),max(value),avg(value) from table group by substr(time,0,2);