一张表table_name,字段:tname(名称),ttime(日期时间),tspeed(速度),tindex(指数),例如四个字段的数据如下(测试数据):
tname tindex ttime tspeed
丰台区 0.4 2011-10-10 11:55:00 PM 53.1
海淀区 0.5 2011-10-10 11:55:00 PM 45.2
朝阳区 0.7 2011-10-10 11:55:00 PM 45.1
丰台区 0.3 2011-10-10 11:50:00 PM 43.2
海淀区 0.6 2011-10-10 11:50:00 PM 53.9
朝阳区 0.2 2011-10-10 11:50:00 PM 61.9
.......
丰台区 0.3 2011-10-09 11:50:00 PM 43.2
海淀区 0.6 2011-10-09 11:50:00 PM 53.9
朝阳区 0.2 2011-10-09 11:50:00 PM 61.9这些测试数据的意思是每五分钟就会有数据更新,也就是说时间间隔是5分钟,比如说丰台区在10号下午的11:55时,车辆的平均速度为53.1,指数为0.4。
现在想查询的是:
每个时间段的所有区域的平均速度和平均指数,也就是说丰台区和海淀区和朝阳区在每个时间段时的平均速度和指数如何计算?
我写了一个先只计算所有区域的平均速度未求平均指数,不过写的不对:SELECT t.ttime,AVG(t.tspeed) FROM table_name t
GROUP BY t.ttime望指教……
tname tindex ttime tspeed
丰台区 0.4 2011-10-10 11:55:00 PM 53.1
海淀区 0.5 2011-10-10 11:55:00 PM 45.2
朝阳区 0.7 2011-10-10 11:55:00 PM 45.1
丰台区 0.3 2011-10-10 11:50:00 PM 43.2
海淀区 0.6 2011-10-10 11:50:00 PM 53.9
朝阳区 0.2 2011-10-10 11:50:00 PM 61.9
.......
丰台区 0.3 2011-10-09 11:50:00 PM 43.2
海淀区 0.6 2011-10-09 11:50:00 PM 53.9
朝阳区 0.2 2011-10-09 11:50:00 PM 61.9这些测试数据的意思是每五分钟就会有数据更新,也就是说时间间隔是5分钟,比如说丰台区在10号下午的11:55时,车辆的平均速度为53.1,指数为0.4。
现在想查询的是:
每个时间段的所有区域的平均速度和平均指数,也就是说丰台区和海淀区和朝阳区在每个时间段时的平均速度和指数如何计算?
我写了一个先只计算所有区域的平均速度未求平均指数,不过写的不对:SELECT t.ttime,AVG(t.tspeed) FROM table_name t
GROUP BY t.ttime望指教……
GROUP BY t.ttime,t.tname
with tbl as
(
select '丰台区' as tname, 0.4 as tindex, '2011-10-10 11:55:00 PM' as ttime, 53.1 as tspeed from dual
union all
select '海淀区' as tname, 0.5 as tindex, '2011-10-10 11:55:00 PM' as ttime, 45.2 as tspeed from dual
union all
select '朝阳区' as tname, 0.7 as tindex, '2011-10-10 11:55:00 PM' as ttime, 45.1 as tspeed from dual
union all
select '丰台区' as tname, 0.3 as tindex, '2011-10-10 11:50:00 PM' as ttime, 43.2 as tspeed from dual
union all
select '海淀区' as tname, 0.6 as tindex, '2011-10-10 11:50:00 PM' as ttime, 53.9 as tspeed from dual
union all
select '朝阳区' as tname, 0.2 as tindex, '2011-10-10 11:50:00 PM' as ttime, 61.9 as tspeed from dual
union all
select '丰台区' as tname, 0.3 as tindex, '2011-10-09 11:50:00 PM' as ttime, 43.2 as tspeed from dual
union all
select '海淀区' as tname, 0.6 as tindex, '2011-10-09 11:50:00 PM' as ttime, 53.9 as tspeed from dual
union all
select '朝阳区' as tname, 0.2 as tindex, '2011-10-09 11:50:00 PM' as ttime, 61.9 as tspeed from dual
)select ttime, tname, avg(tspeed) as tspeed, avg(tindex) as tindex from tbl
group by ttime, tname
order by ttime, tname;
TTIME TNAME TSPEED TINDEX
---------------------- --------- ---------- ----------
2011-10-09 11:50:00 PM 丰台区 43.2 0.3
2011-10-09 11:50:00 PM 朝阳区 61.9 0.2
2011-10-09 11:50:00 PM 海淀区 53.9 0.6
2011-10-10 11:50:00 PM 丰台区 43.2 0.3
2011-10-10 11:50:00 PM 朝阳区 61.9 0.2
2011-10-10 11:50:00 PM 海淀区 53.9 0.6
2011-10-10 11:55:00 PM 丰台区 53.1 0.4
2011-10-10 11:55:00 PM 朝阳区 45.1 0.7
2011-10-10 11:55:00 PM 海淀区 45.2 0.5
GROUP BY t.ttimeSQL本身没什么问题,估计是表结构的问题,看下你的表的tspeed和tindex两列是数值型的吗?如果是数值型的,这个SQL就不应当出错。否则就转换下:
select t.ttime,avg(to_number(t.tspeed)) from table_name t group by t.ttime