求一个SQL，三个连续时间点求平均

h99 2017-11-28 11:05:37
描述：
一个关于空气质量的采集的表TEST_01 ，一个 DEV_CODE（CSITE_ID ），一个时间点（RECV_TIME ）采集一次数据，包含PM2.5，噪音NOISE，风向WIND_DIRECT ，风速WIND_SPEED 等等。
问题一：求每个半点（00分，30分）前后一共三分钟的数据的平均
00分（59分,00分,01分数据指标的平均），
30分（29分,30分,31分数据指标的平均），
风向不能平均，取条数多的那个（3条记录如果两条都是东风一个南风，就取东风）
问题二：
优先取整个半点数据的（00分,30分），如果没有取前一分钟的（29分，59分），如果还没有就去后一分钟的（01分,31分）
风向取条数多的那个（3条记录如果两条都是东风一个南风，就取东风）

谢谢了~~测试代码如下：

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

--  文件已创建 - 星期二-十一月-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');
...全文
358 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
看了半天没看明白风向的含义！
jdsnhan 2017-11-29
  • 打赏
  • 举报
 回复
风向是0的算那边风啊，没风？还是旋风。 三条数据，一条东南风，一条西南风，又如何判断？ 其实，两道题的思路差不多，都是把时间字段处理成方便访问的，第一道题是处理成整点或整半点，第二道题是处理成序号。 如： 
select dev_code,
       wind_direct,
       pm2_5,
       to_char(recv_time, 'yyyymmddhh24') || case
         when to_char(recv_time, 'mi') in ('29', '31') then
          '30'
         when to_char(recv_time, 'mi') in ('59', '01') then
          '00'
         else
          to_char(recv_time, 'mi')
       end,
       case
         when to_char(recv_time, 'mi') in ('30', '00') then
          '1'
         when to_char(recv_time, 'mi') in ('29', '59') then
          '2'
         else
          '3'
       end,
       to_char(recv_time, 'yyyymmddhh24mi')
  from TEST_01
剩下的就是处理风向了
自由自在_Yu 2017-11-28
  • 打赏
  • 举报
 回复
问题一：求每个半点（00分，30分）前后一共三分钟的数据的平均(风向暂时没查询) 
SELECT CASE WHEN SUBSTR(RECV_TIME,14,2)='01' THEN RECV_TIME else RECV_TIME end FROM TEST_01;
SELECT TEST.RECV_TIME_,count(*),sum(DUST)/count(*),sum(AIR_PRESSURE)/count(*),sum(PM10)/count(*),sum(TEMP)/count(*),sum(PM2_5)/count(*),sum(WIND_SPEED)/count(*),sum(HUMID)/count(*),sum(NOISE)/count(*) from (
SELECT TEST_01.*, 
(CASE WHEN SUBSTR(RECV_TIME,14,2)='01' THEN REPLACE(to_char(RECV_TIME,'YYYY-MM-DD hh24:mi:ss'),':01:',':00:') 
      WHEN SUBSTR(RECV_TIME,14,2)='59' THEN REPLACE(to_char(RECV_TIME,'YYYY-MM-DD hh24:mi:ss'),':59:',':00:')
      WHEN SUBSTR(RECV_TIME,14,2)='29' THEN REPLACE(to_char(RECV_TIME,'YYYY-MM-DD hh24:mi:ss'),':29:',':30:')
      WHEN SUBSTR(RECV_TIME,14,2)='31' THEN REPLACE(to_char(RECV_TIME,'YYYY-MM-DD hh24:mi:ss'),':31:',':30:')
ELSE to_char(RECV_TIME,'YYYY-MM-DD hh24:mi:ss') END)  as RECV_TIME_
FROM TEST_01) TEST 
group by TEST.RECV_TIME_;
SQL Server 2019数据库基础入门视频教程
本课程使用sql server 2019版本的数据库进行讲解。主要讲解SQL Server的基本操作和SQL主要语法，注重基础性和实用性。软件公司里面各个岗位包括实施、运维、客服、测试、程序员等都涉及数据库的使用，学会本课程足可以胜任日常数据库工作。 数据库操作是软件行业人员必备的技能，这门课程主要是针对从事软件技术的新人的，适合在校大学生和软件公司新入职员工。我讲的这门课程主要涉及SQL Server数据的基础操作和SQL语法，因为99.9%的软件技术人员掌握这些就可以了，数据库的大部分功能，我们平时是用不到的，所以不用花太多时间去研究，在需要的时候百度就可以了。每节课程都结合工作实际，并且经过认真准备。把这门课程里面的内容学会，足以胜任日常性SQL Server数据库相关的工作。
大厂SQL面试题，连续时间问题
今天分享一个非常常考，同时也不太容易的sql题目类型，求连续时间问题。 这类题的核心是：分组排序，用时间减去排序，如果连续的话他们的差会是相同值 记住下面的表，按照提供的三步思路理清楚里面的数据逻辑，连续时间问题再不会难到你 思路： 求解连续天数是面试时非常常考的问题，这类问题有非常巧妙的统一解法，分为3步： 1、为日期排序 row number() over (partition by use_id order by date) as rank 2、求日期和排序的差值（diff） 因为日期存在周期
一文读懂SQL中的连续日期、连续登陆问题
一文学会sql中的连续问题
每日sql--统计员工近三个月的总薪水（不包括最新一个月）
每日sql–统计员工近三个月的总薪水（不包括最新一个月） DDL Create table If Not Exists Employee (Id int, Month int, Salary int);insert into Employee (Id, Month, Salary) values (1, 1, 20);insert into Employee (Id, Month, Salary) values (2, 1, 20);insert into Employee (Id, Month, Sala
hive求近三个月数据的平均环比值
计算公式：(L1汇总金额 / L2汇总金额 + L2汇总金额 / L3汇总金额+ L3汇总金额 / L4汇总金额) / N； N为近3个月实际有交易月份数，若某月为空会跳过该月和下月计算， 例如L2月份没有数据，则计算公式就为：(L1汇总金额 / L3汇总金额+ L3汇总金额 / L4汇总金额) / 2。以此类推，若没有数据默认为-999999,若只有一个月数据则为999999
Oracle

17,086

社区成员

55,238

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章