17,086
社区成员
发帖
与我相关
我的任务
分享
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