触发器(八)Trigger_Insert_PollHour-优快云博客

本文介绍了一个SQL触发器的实现细节，该触发器用于将每分钟的环境监测数据汇总为每小时的平均值。具体操作包括从分钟级表中选取最新数据，并计算NOx、NH3等指标的平均值，然后将这些平均值插入到小时级的数据表中。

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

USE [AirDB_YiZhuang]
GO

/****** Object: Trigger [dbo].[Trigger_Insert_PollHour] Script Date: 2017/12/25 17:08:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: pyy
-- Create date: 2017-2-15
-- Description: min数据求平均得到hour
---- mondify Description: 2017-4-5站点“宾士国际”的数据只更新到了3-28
--DATEADD(hour,1,cast(MONIDATE as datetime))小时求平均,加1小时
--where MONIDATE >=(select DATEADD(HOUR,1,max(MONIDATE)) from [AirDB_YiZhuang].[dbo].[T_Data_PollHour])不用加1了
--where MONIDATE >=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_PollHour])
-- =============================================
CREATE TRIGGER [dbo].[Trigger_Insert_PollHour]
ON [dbo].[T_Data_PollMin]
AFTER INSERT--,DELETE,UPDATE
AS
BEGIN
declare @t1 datetime
declare @t2 datetime
declare @Mint datetime
--站点1的最新时间
set @t1=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_PollMin] where STATIONCODE= '500000001')
--编号写错了5000001
--站点2的最新时间
set @t2=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_PollMin] where STATIONCODE= '500000002')
--5000002
--最新时间里的最小值
set @Mint=@t1
--(select case when @t1<@t2 then @t1 else @t2 end)
insert into [AirDB_YiZhuang].[dbo].[T_Data_PollHour]
(STATIONCODE,MONIDATE,NOx,NH3,TNX,H2S)
select STATIONCODE,DATEADD(hour,1,cast(MONIDATE as datetime)) MONIDATE,NOx,NH3,TNX,H2S
from
(select STATIONCODE,
substring(convert(varchar,MONIDATE,120),0,14)+':00:00' MONIDATE,
AVG(NOx) NOx,AVG(NH3) NH3,
AVG(TNX) TNX,AVG(H2S) H2S
from
[AirDB_YiZhuang].[dbo].[T_Data_PollMin]
where MONIDATE >=(select max(MONIDATE) from [AirDB_YiZhuang].[dbo].[T_Data_PollHour])
--T_MID_AIRQUALITYHOUR最新的时间
and
--MONIDATE <(select substring(convert(varchar,max(monidate),120),0,14)+':00:00' from T_MID_AIRQUALITYMin)
--T_MID_AIRQUALITYMin最新的时间的小时值
--当两个站点的分钟数据不同步的时候,数据慢的那个站点的小时平均数据就可能不对(不足60条分钟数据)
MONIDATE <(substring(convert(varchar,@Mint,120),0,14)+':00:00')
group by STATIONCODE,substring(convert(varchar,MONIDATE,120),0,14)) as a

END

GO