我现在有一个需求,就是页面有个折线图,展示一段时间内的平均值,图表上有10个点,也就是说我现在要查询10遍SQL才能求出每一段时间内的平均值,可是如果将来图表变成50个点,那我就要查询50遍,效率太低了,有没有什么好办法可以一次把所有结果都统计出来的。
说明一下:time是毫秒值,系统会吧日期转成毫秒值然后存入数据库,datetime是我为了方便查看临时加的字段,实际表中并没有该字段。
表结构create table TEST
(
time NUMBER,
metricid NUMBER,
valueindex NUMBER,
datetime DATE,
value VARCHAR2(222)
);insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463367350194, 1, 1, to_date('16-05-2016 10:55:00', 'dd-mm-yyyy hh24:mi:ss'), '10');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463367757872, 1, 1, to_date('16-05-2016 11:02:37', 'dd-mm-yyyy hh24:mi:ss'), '20');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463367777872, 1, 1, to_date('16-05-2016 11:02:57', 'dd-mm-yyyy hh24:mi:ss'), '30');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463367977872, 1, 1, to_date('16-05-2016 11:06:57', 'dd-mm-yyyy hh24:mi:ss'), '40');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463371977872, 1, 1, to_date('16-05-2016 12:12:57', 'dd-mm-yyyy hh24:mi:ss'), '50');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463373977872, 1, 1, to_date('16-05-2016 12:46:57', 'dd-mm-yyyy hh24:mi:ss'), '60');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463379977872, 1, 1, to_date('16-05-2016 14:46:57', 'dd-mm-yyyy hh24:mi:ss'), '70');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463379877872, 2, 1, to_date('16-05-2016 14:24:57', 'dd-mm-yyyy hh24:mi:ss'), '80');
说明一下:time是毫秒值,系统会吧日期转成毫秒值然后存入数据库,datetime是我为了方便查看临时加的字段,实际表中并没有该字段。
表结构create table TEST
(
time NUMBER,
metricid NUMBER,
valueindex NUMBER,
datetime DATE,
value VARCHAR2(222)
);insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463367350194, 1, 1, to_date('16-05-2016 10:55:00', 'dd-mm-yyyy hh24:mi:ss'), '10');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463367757872, 1, 1, to_date('16-05-2016 11:02:37', 'dd-mm-yyyy hh24:mi:ss'), '20');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463367777872, 1, 1, to_date('16-05-2016 11:02:57', 'dd-mm-yyyy hh24:mi:ss'), '30');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463367977872, 1, 1, to_date('16-05-2016 11:06:57', 'dd-mm-yyyy hh24:mi:ss'), '40');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463371977872, 1, 1, to_date('16-05-2016 12:12:57', 'dd-mm-yyyy hh24:mi:ss'), '50');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463373977872, 1, 1, to_date('16-05-2016 12:46:57', 'dd-mm-yyyy hh24:mi:ss'), '60');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463379977872, 1, 1, to_date('16-05-2016 14:46:57', 'dd-mm-yyyy hh24:mi:ss'), '70');insert into TEST (TIME, METRICID, VALUEINDEX, DATETIME, VALUE)
values (1463379877872, 2, 1, to_date('16-05-2016 14:24:57', 'dd-mm-yyyy hh24:mi:ss'), '80');
SQL> select t.* , avg(value) over(order by time) s from test t ;
TIME METRICID VALUEINDEX DATETIME VALUE S
---------- ---------- ---------- ----------- ---------- ----------
1463367350 1 1 2016-05-16 10 10
1463367757 1 1 2016-05-16 20 15
1463367777 1 1 2016-05-16 30 20
1463367977 1 1 2016-05-16 40 25
1463371977 1 1 2016-05-16 50 30
1463373977 1 1 2016-05-16 60 35
1463379877 2 1 2016-05-16 80 41.4285714
1463379977 1 1 2016-05-16 70 45
8 rows selected
SQL> drop table test purge ;
Table dropped