oracle 按天数均值,求一个连续时间点平均值的SQL

最新推荐文章于 2021-12-30 10:35:49 发布

主播仁杰

最新推荐文章于 2021-12-30 10:35:49 发布

阅读量287

点赞数

文章标签： oracle 按天数均值

--------------------------------------------------------

-- 文件已创建 - 星期二-十一月-28-2017

--------------------------------------------------------

-- DDL for Table TEST_01

--------------------------------------------------------

CREATE TABLE TEST_01

( DEV_CODE VARCHAR2(20 BYTE),

DUST NUMBER(8,3),

AIR_PRESSURE NUMBER(8,3),

PM10 NUMBER(8,3),

TEMP NUMBER(8,3),

PM2_5 NUMBER(8,3),

WIND_SPEED NUMBER(8,3),

HUMID NUMBER(8,3),

WIND_DIRECT VARCHAR2(30 BYTE),

NOISE NUMBER(8,3),

RECV_TIME TIMESTAMP (6),

CSITE_ID VARCHAR2(5 BYTE)

);

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.208,1001.815,0.142,17.4,0.098,0.24,72,'东南 145.7',67.8,to_timestamp('2017-11-28 10:29:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.7,0.165,16.2,0,0,75.6,'0',0,to_timestamp('2017-11-28 10:30:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.21,1004.816,0.14,17.3,0.096,0.24,72.7,'南 190.0',64.4,to_timestamp('2017-11-28 10:30:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.7,0.175,16.1,0,0,73.8,'0',0,to_timestamp('2017-11-28 10:00:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.216,1000.821,0.142,16.9,0.097,0.24,73.1,'西南 232.3',63.9,to_timestamp('2017-11-28 10:00:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.219,1004.318,0.143,16.9,0.098,0.24,73.1,'东 96.8',65.7,to_timestamp('2017-11-28 10:01:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.6,0.129,14.9,0,0,79.1,'0',0,to_timestamp('2017-11-28 09:30:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.205,1007.509,0.137,15.9,0.099,0.24,76.3,'东南 134.9',67.6,to_timestamp('2017-11-28 09:30:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.5,0.134,11.9,0,0,92.2,'0',0,to_timestamp('2017-11-28 07:30:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.189,1009.683,0.129,12.6,0.091,0.23,90.8,'西南 207.7',68.2,to_timestamp('2017-11-28 07:30:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.19,1004.409,0.129,12.6,0.086,0.23,90.8,'东南 142.3',66.6,to_timestamp('2017-11-28 07:31:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.6,0.109,12.5,0,0,90.7,'0',0,to_timestamp('2017-11-28 08:00:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.184,1009.449,0.122,14,0.083,0.23,84.7,'东 98.5',68.8,to_timestamp('2017-11-28 08:29:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.6,0.138,13.2,0,0,87.2,'0',0,to_timestamp('2017-11-28 08:30:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.4,0.119,11.5,0,0,91.3,'0',0,to_timestamp('2017-11-28 06:30:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.194,1007.132,0.131,12.2,0.092,0.23,91,'西南 224.6',62.9,to_timestamp('2017-11-28 06:30:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.19,1005.687,0.128,12.2,0.094,0.23,91.2,'西南 220.3',58.5,to_timestamp('2017-11-28 06:31:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.6,0.167,14,0,0,84,'0',0,to_timestamp('2017-11-28 09:00:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.189,1000.264,0.127,12.3,0.092,0.23,90.9,'西南 213.4',60.3,to_timestamp('2017-11-28 06:29:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.188,1008.847,0.129,12.2,0.089,0.23,92.5,'西 283.7',68.3,to_timestamp('2017-11-28 06:59:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('56030082',0,101.5,0.126,11.6,0,0,92.6,'0',0,to_timestamp('2017-11-28 07:00:00','YYYY-MM-DD HH24:MI:SS'),'404');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.198,1001.177,0.13,12.3,0.087,0.23,92.5,'西南 242.8',68.8,to_timestamp('2017-11-28 07:00:00','YYYY-MM-DD HH24:MI:SS'),'610');

Insert into TEST_01 (DEV_CODE,DUST,AIR_PRESSURE,PM10,TEMP,PM2_5,WIND_SPEED,HUMID,WIND_DIRECT,NOISE,RECV_TIME,CSITE_ID) values ('16030204',0.194,1009.487,0.127,12.3,0.089,0.23,92.5,'南 200.0',69.4,to_timestamp('2017-11-28 07:01:00','YYYY-MM-DD HH24:MI:SS'),'610');