sql分钟数据求平均得到小时数据

最新推荐文章于 2025-04-07 03:00:00 发布

u010082526

最新推荐文章于 2025-04-07 03:00:00 发布

阅读量2.2k

点赞数

CC 4.0 BY-SA版权

分类专栏： sqlserver

本文链接：https://blog.youkuaiyun.com/u010082526/article/details/52689717

sqlserver 专栏收录该内容

58 篇文章

订阅专栏

该博客讲述了如何使用SQL将分钟级别的空气质量数据转换并计算为每小时的平均值，涉及datetime转换、数据截取、聚合函数以及自定义函数的使用，以便进行数据分析和入库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.datetime转成字符串

convert(varchar,MONIDATE,120)

截取到小时

substring(convert(varchar,MONIDATE,120),0,14)

再转成“2016-09-21 17:00:00”的格式，方便入库

substring(convert(varchar,MONIDATE,120),0,14)+':00:00'

2."PM25;"去掉最后的分号

PRIMARYPOLLUTANT的长度

LEN(PRIMARYPOLLUTANT

截取

SUBSTRING(PRIMARYPOLLUTANT,0,LEN(PRIMARYPOLLUTANT))

3.获取T_MID_AIRQUALITYHOUR最新的时间，并加1小时

加1小时，DATEADD（天/时，个数，时间值）

DATEADD(HOUR,1,max(MONIDATE))

select max(monidate) from T_MID_AIRQUALITYHOUR

2016-09-21 17:00:00.000

select DATEADD(HOUR,1,max(MONIDATE)) from T_MID_AIRQUALITYHOUR

2016-09-21 18:00:00.000

insert into [AirDB_YiZhuang].[dbo].[T_MID_AIRQUALITYHOUR]
(STATIONCODE,MONIDATE,SO2,SO2IAQI,NO2,NO2IAQI,
CO,COIAQI,O3,O3IAQI,PM25,PM25IAQI,PM10,PM10IAQI,
AQI, PRIMARYPOLLUTANT,AIRLEVEL,
TEMPERATURE,AIRPRESSURE,RELATIVEHUMIDITY,WINDSPEED,WINDIRECTION)
select STATIONCODE,cast(MONIDATE as datetime) MONIDATE,SO2,SO2IAQI,NO2,NO2IAQI,
CO,COIAQI,O3,O3IAQI,PM25,PM25IAQI,PM10,PM10IAQI,
AQI,
SUBSTRING(PRIMARYPOLLUTANT,0,LEN(PRIMARYPOLLUTANT)) PRIMARYPOLLUTANT,
AIRLEVEL,
TEMPERATURE,AIRPRESSURE,RELATIVEHUMIDITY,WINDSPEED,WINDIRECTION
from
(select STATIONCODE,
substring(convert(varchar,MONIDATE,120),0,14)+':00:00' MONIDATE,
AVG(SO2) SO2,[dbo].[so22iaqi](AVG(SO2)) SO2IAQI,
AVG(NO2) NO2,[dbo].[no22iaqi](AVG(NO2)) NO2IAQI,
AVG(CO) CO,[dbo].[co2iaqi](AVG(CO)) COIAQI,
AVG(O3) O3,[dbo].[o32iaqi](AVG(O3)) O3IAQI,
AVG(PM25) PM25,[dbo].[pm252iaqi](AVG(PM25)) PM25IAQI,
AVG(PM10) PM10,[dbo].[pm102iaqi](AVG(PM10)) PM10IAQI,
[dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) AQI,

[dbo].[fun_AQI_FristPollutant]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10))) PRIMARYPOLLUTANT,

[dbo].[fun_AirAQIdegree]([dbo].[fun_AirQualityIndex]([dbo].[co2iaqi](AVG(CO)),[dbo].[so22iaqi](AVG(SO2)),
[dbo].[no22iaqi](AVG(NO2)),[dbo].[o32iaqi](AVG(O3)),[dbo].[o32iaqi](AVG(O3)),
[dbo].[pm252iaqi](AVG(PM25)),[dbo].[pm102iaqi](AVG(PM10)))) AIRLEVEL,
AVG(TEMPERATURE) TEMPERATURE,AVG(AIRPRESSURE) AIRPRESSURE,AVG(RELATIVEHUMIDITY) RELATIVEHUMIDITY,
AVG(WINDSPEED) WINDSPEED,AVG(WINDIRECTION) WINDIRECTION
from [dbo].[T_MID_AIRQUALITYMin]
where MONIDATE >'2016-09-21 16:00' and MONIDATE <'2016-09-21 18:00'
group by STATIONCODE,substring(convert(varchar,MONIDATE,120),0,14)) as a