create table SENREALTIMDATTAB
(
MONITORPOIDATID INTEGER not null,
SENSORID INTEGER,
CHECKTIME DATE,
CHECKVALUE NUMBER(30,6)
)comment on table SENREALTIMDATTAB
is '传感器实时数据表';comment on column SENREALTIMDATTAB.MONITORPOIDATID
is '监测点数据ID';
comment on column SENREALTIMDATTAB.SENSORID
is '传感器ID';
comment on column SENREALTIMDATTAB.CHECKTIME
is '检测时间';
comment on column SENREALTIMDATTAB.CHECKVALUE
is '检测值';通过给定sensorid和checktime的时间范围,
每隔15分钟进行一次求平均值。
现求sql?假设sensorid=66,时间范围不限,
请给出可执行sql语句?
(
MONITORPOIDATID INTEGER not null,
SENSORID INTEGER,
CHECKTIME DATE,
CHECKVALUE NUMBER(30,6)
)comment on table SENREALTIMDATTAB
is '传感器实时数据表';comment on column SENREALTIMDATTAB.MONITORPOIDATID
is '监测点数据ID';
comment on column SENREALTIMDATTAB.SENSORID
is '传感器ID';
comment on column SENREALTIMDATTAB.CHECKTIME
is '检测时间';
comment on column SENREALTIMDATTAB.CHECKVALUE
is '检测值';通过给定sensorid和checktime的时间范围,
每隔15分钟进行一次求平均值。
现求sql?假设sensorid=66,时间范围不限,
请给出可执行sql语句?
建议,再建一个表,把每次执行的记过,插入到另外一个表,插入:date sensorid avg(CHECKVALUE) 三个值
那不是我每个时间都要写个JOB吧.
MONITORPOIDATID SENSORID CHECKTIME CHECKVALUE
--------------------------------------- --------------------------------------- ----------- --------------------------------
1 66 2010-8-18 1 1.000000
1 66 2010-8-18 1 2.000000
1 66 2010-8-18 1 3.000000
1 66 2010-8-18 1 4.000000
1 66 2010-8-18 1 5.000000
1 66 2010-8-18 1 6.000000
1 66 2010-8-18 1 7.000000
1 66 2010-8-18 1 8.000000
1 66 2010-8-18 1 9.000000
1 66 2010-8-18 1 10.000000
1 66 2010-8-18 1 11.000000
1 66 2010-8-18 1 12.000000
1 66 2010-8-18 1 13.000000
1 66 2010-8-18 1 14.000000
1 66 2010-8-18 1 15.000000
1 66 2010-8-18 1 16.000000
1 66 2010-8-18 1 17.000000
1 66 2010-8-18 1 18.000000
1 66 2010-8-18 1 19.000000
1 66 2010-8-18 1 20.000000
20 rows selected
SQL>
SQL> WITH t1 AS (SELECT MIN(checktime) mintime ,MAX(checktime) maxtime FROM SENREALTIMDATTAB t WHERE t.sensorid=66) ,
2 t2 AS(SELECT mintime+(ROWNUM-1)*1/24/60*15 timebegin,mintime+(ROWNUM)*1/24/60*15 timeend FROM dual ,t1 CONNECT BY (ROWNUM-1)*1/24/60*15+mintime<maxtime)
3 SELECT t2.timebegin,AVG(a.checkvalue) FROM SENREALTIMDATTAB a,t2
4 WHERE a.sensorid=66 AND a.checktime BETWEEN t2.timebegin AND t2.timeend
5 GROUP BY t2.timebegin;
TIMEBEGIN AVG(A.CHECKVALUE)
----------- -----------------
2010-8-18 14:59:00 12.5
2010-8-18 15:14:00 3
SQL>
MONITORPOIDATID SENSORID CHECKTIME CHECKVALUE
--------------------------- ------------------ ------------------------------ ----------------------
1 66 2010-8-18 15:18:00 1.000000
1 66 2010-8-18 15:17:00 2.000000
1 66 2010-8-18 15:16:00 3.000000
1 66 2010-8-18 15:15:00 4.000000
1 66 2010-8-18 15:14:00 5.000000
1 66 2010-8-18 15:13:00 6.000000
1 66 2010-8-18 15:12:00 7.000000
1 66 2010-8-18 15:11:00 8.000000
1 66 2010-8-18 15:10:00 9.000000
1 66 2010-8-18 15:09:00 10.000000
1 66 2010-8-18 15:08:00 11.000000
1 66 2010-8-18 15:07:00 12.000000
1 66 2010-8-18 15:06:00 13.000000
1 66 2010-8-18 15:05:00 14.000000
1 66 2010-8-18 15:04:00 15.000000
1 66 2010-8-18 15:03:00 16.000000
1 66 2010-8-18 15:02:00 17.000000
1 66 2010-8-18 15:01:00 18.000000
1 66 2010-8-18 15:00:00 19.000000
1 66 2010-8-18 14:59:00 20.000000
20 rows selected
SQL>
SQL> WITH t1 AS (SELECT MIN(checktime) mintime ,MAX(checktime) maxtime FROM SENREALTIMDATTAB t WHERE t.sensorid=66) ,
2 t2 AS(SELECT mintime+(ROWNUM-1)*1/24/60*15 timebegin,mintime+(ROWNUM)*1/24/60*15 timeend FROM dual ,t1 CONNECT BY (ROWNUM-1)*1/24/60*15+mintime<maxtime)
3 SELECT t2.timebegin,AVG(a.checkvalue) FROM SENREALTIMDATTAB a,t2
4 WHERE a.sensorid=66 AND a.checktime BETWEEN t2.timebegin AND t2.timeend
5 GROUP BY t2.timebegin;
TIMEBEGIN AVG(A.CHECKVALUE)
----------- -----------------
2010-8-18 14:59:00 12.5
2010-8-18 15:14:00 3
SQL>
SQL> select * from SENREALTIMDATTAB;
MONITORPOIDATID SENSORID CHECKTIME CHECKVALUE
--------------------------- ------------------ ------------------------------ ----------------------
1 66 2010-8-18 15:18:00 1.000000
1 66 2010-8-18 15:17:00 2.000000
1 66 2010-8-18 15:16:00 3.000000
1 66 2010-8-18 15:15:00 4.000000
1 66 2010-8-18 15:14:00 5.000000
1 66 2010-8-18 15:13:00 6.000000
1 66 2010-8-18 15:12:00 7.000000
1 66 2010-8-18 15:11:00 8.000000
1 66 2010-8-18 15:10:00 9.000000
1 66 2010-8-18 15:09:00 10.000000
1 66 2010-8-18 15:08:00 11.000000
1 66 2010-8-18 15:07:00 12.000000
1 66 2010-8-18 15:06:00 13.000000
1 66 2010-8-18 15:05:00 14.000000
1 66 2010-8-18 15:04:00 15.000000
1 66 2010-8-18 15:03:00 16.000000
1 66 2010-8-18 15:02:00 17.000000
1 66 2010-8-18 15:01:00 18.000000
1 66 2010-8-18 15:00:00 19.000000
1 66 2010-8-18 14:59:00 20.000000
20 rows selected
SQL>
SQL> WITH t1 AS (SELECT MIN(checktime) mintime ,MAX(checktime) maxtime FROM SENREALTIMDATTAB t WHERE t.sensorid=66) ,
2 t2 AS(SELECT mintime+(ROWNUM-1)*1/24/60*15 timebegin,mintime+(ROWNUM)*1/24/60*15 timeend FROM dual ,t1 CONNECT BY (ROWNUM-1)*1/24/60*15+mintime<maxtime)
3 SELECT t2.timebegin,AVG(a.checkvalue) FROM SENREALTIMDATTAB a,t2
4 WHERE a.sensorid=66 AND a.checktime BETWEEN t2.timebegin AND t2.timeend
5 GROUP BY t2.timebegin;
TIMEBEGIN AVG(A.CHECKVALUE)
----------- -----------------
2010-8-18 14:59:00 12.5
2010-8-18 15:14:00 3
SQL>
1 1 7.12 2010-8-16 11:29:43
2 1 7.22 2010-8-16 11:30:24
3 1 7.42 2010-8-16 11:30:36
4 1 7.82 2010-8-16 11:30:48
5 1 7.42 2010-8-16 11:31:31
6 1 7.42 2010-8-16 11:31:36
7 1 7.42 2010-8-16 11:31:37
8 1 7.42 2010-8-16 11:31:37
9 1 7.42 2010-8-16 11:31:37
10 1 7.42 2010-8-16 11:31:38
11 1 7.42 2010-8-16 11:31:38
12 1 7.42 2010-8-16 11:31:38
13 1 7.42 2010-8-16 11:31:39
14 1 7.42 2010-8-16 11:31:39
15 1 7.42 2010-8-16 11:31:39
16 1 7.42 2010-8-16 11:31:40
17 1 7.42 2010-8-16 11:31:40
18 1 7.42 2010-8-16 11:31:48
19 1 7.42 2010-8-16 11:31:50
20 1 7.42 2010-8-16 11:31:52
21 1 7.42 2010-8-16 11:31:54
22 4 7.42 2010-8-16 11:32:20
23 4 7.42 2010-8-16 11:32:22
24 4 7.42 2010-8-16 11:32:28
25 4 7.42 2010-8-16 11:32:30
26 4 7.42 2010-8-16 11:32:32
27 4 7.12 2010-8-16 11:32:41
28 4 7.12 2010-8-16 11:32:44
29 4 7.12 2010-8-16 11:32:46
30 4 7.12 2010-8-16 11:32:48
31 4 7.12 2010-8-16 11:32:50
32 4 7.12 2010-8-16 11:32:52
33 4 7.12 2010-8-16 11:32:54
34 4 7.12 2010-8-16 11:32:56
35 4 7.12 2010-8-16 11:32:58
36 4 7.12 2010-8-16 11:33:00
37 6 7.22 2010-8-16 11:33:39
38 6 7.22 2010-8-16 11:33:42
39 6 7.22 2010-8-16 11:33:44
40 6 7.22 2010-8-16 11:33:47
41 6 7.92 2010-8-16 11:33:55
42 6 7.92 2010-8-16 11:34:03
43 6 7.10 2010-8-16 11:34:15
44 6 7.10 2010-8-16 11:34:17
45 6 7.10 2010-8-16 11:34:19
46 6 7.10 2010-8-16 11:34:21
47 6 7.10 2010-8-16 11:34:23
48 6 7.10 2010-8-16 11:34:25
49 6 7.10 2010-8-16 11:34:27
50 7 7.10 2010-8-16 11:34:36
51 7 7.10 2010-8-16 11:34:38
52 7 7.80 2010-8-16 11:34:43
53 7 7.80 2010-8-16 11:34:47
54 7 7.80 2010-8-16 11:34:48
55 7 7.80 2010-8-16 11:34:50
56 7 7.80 2010-8-16 11:34:52
57 7 7.80 2010-8-16 11:34:54
58 7 7.80 2010-8-16 11:34:56
59 7 7.80 2010-8-16 11:34:58
----- -------- ------------------ -----------
1 1 7.12 2010-8-16 1
2 1 7.22 2010-8-16 1
3 1 7.42 2010-8-16 1
4 1 7.82 2010-8-16 1
5 1 7.42 2010-8-16 1
6 1 7.42 2010-8-16 1
7 1 7.42 2010-8-16 1
8 1 7.42 2010-8-16 1
9 1 7.42 2010-8-16 1
10 1 7.42 2010-8-16 1
11 1 7.42 2010-8-16 1
12 1 7.42 2010-8-16 1
13 1 7.42 2010-8-16 1
14 1 7.42 2010-8-16 1
15 1 7.42 2010-8-16 1
16 1 7.42 2010-8-16 1
17 1 7.42 2010-8-16 1
18 1 7.42 2010-8-16 1
19 1 7.42 2010-8-16 1
20 1 7.42 2010-8-16 1
ID SENSORID CHECKVALUE CHECKTIME
----- -------- ------------------ -----------
1 1 7.12 2010-8-16 1
2 1 7.22 2010-8-16 1
3 1 7.42 2010-8-16 1
4 1 7.82 2010-8-16 1
5 1 7.42 2010-8-16 1
6 1 7.42 2010-8-16 1
7 1 7.42 2010-8-16 1
8 1 7.42 2010-8-16 1
9 1 7.42 2010-8-16 1
10 1 7.42 2010-8-16 1
11 1 7.42 2010-8-16 1
12 1 7.42 2010-8-16 1
13 1 7.42 2010-8-16 1
14 1 7.42 2010-8-16 1
15 1 7.42 2010-8-16 1
16 1 7.42 2010-8-16 1
17 1 7.42 2010-8-16 1
18 1 7.42 2010-8-16 1
19 1 7.42 2010-8-16 1
20 1 7.42 2010-8-16 1
ID PH_INSTRUMENTATION_ID PH_VALUE COLLECTION_TIME
--------------------- --------------------- ------------ ---------------
1 1 7.120 2010-8-16 11:29
2 1 7.220 2010-8-16 11:30
3 1 7.420 2010-8-16 11:30
4 1 7.820 2010-8-16 11:30
SQL>
SQL> WITH t1 AS (SELECT MIN(COLLECTION_TIME) mintime ,MAX(COLLECTION_TIME) maxtime FROM tttt t WHERE t.PH_INSTRUMENTATION_ID=1 AND COLLECTION_TIME BETWEEN to_date('20100816','yyyymmdd') AND to_date('20100817','yyyymmdd')-1/24/60/60 ) ,
2 t2 AS(SELECT mintime+(ROWNUM-1)*1/24/60*15 timebegin,mintime+(ROWNUM)*1/24/60*15 timeend FROM dual ,t1 CONNECT BY (ROWNUM-1)*1/24/60*15+mintime<maxtime)
3 SELECT t2.timebegin,AVG(a.PH_VALUE) FROM tttt a,t2
4 WHERE a.PH_INSTRUMENTATION_ID=1 AND a.COLLECTION_TIME BETWEEN t2.timebegin AND t2.timeend
5 GROUP BY t2.timebegin;
TIMEBEGIN AVG(A.PH_VALUE)
----------- ---------------
2010-8-16 1 7.395
SQL>
TRUNC(((COLLECTION_TIME-to_date('2010-08-16 00:00:50','yyyy-mm-dd hh24:mi:ss'))*24*60)/15 ,0)*15
from t_ph t where COLLECTION_TIME > to_date('2010-08-16 00:00:50','yyyy-mm-dd hh24:mi:ss') and
COLLECTION_TIME <= to_date('2010-08-16 23:00:00','yyyy-mm-dd hh24:mi:ss')and t.PH_INSTRUMENTATION_ID =1 group by
TRUNC(((COLLECTION_TIME-to_date('2010-08-16 00:00:50','yyyy-mm-dd hh24:mi:ss'))*24*60)/15 ,0)*15,PH_INSTRUMENTATION_ID
order by PH_INSTRUMENTATION_ID这个也可以实现
我看了下这个好像比较易懂哦