sql server 2012 自定义聚合函数(MAX_O3_8HOUR_ND) 计算最大的臭氧8小时滑动平均值

System; using System.Collections; using System.Data; using Microsoft.SqlServer.Server; using System.Data.SqlTypes; using System.IO; using System.Text; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedAggregate( Format.UserDefined, //use clr serialization to serialize the intermediate result IsInvariantToNulls = true, //optimizer property IsInvariantToDuplicates = false, //optimizer property IsInvariantToOrder = false, //optimizer property MaxByteSize = 8000) //maximum size in bytes of persisted value ] public class MAX_O3_8HOUR_ND : IBinarySerialize { /// <summary> /// The variable that holds the intermediate result of the concatenation /// </summary> private StringBuilder intermediateResult; /// <summary> /// 系统初始化 /// </summary> public void Init() { this.intermediateResult = new StringBuilder(); } /// <summary> /// 积累文本内容，null除外，一般用标点符号隔开。 /// </summary> /// <param name="value"></param> public void Accumulate(SqlString value) { if (value.IsNull) { return; } this.intermediateResult.Append(value.Value).Append(‘,‘); } /// <summary> /// Merge the partially computed aggregate with this aggregate. /// </summary> /// <param name="Group"></param> public void Merge(MAX_O3_8HOUR_ND Group) { this.intermediateResult.Append(Group.intermediateResult); } /// <summary> ///在最后被调用，返回聚合函数结果 /// </summary> /// <returns></returns> public SqlString Terminate() { string output = string.Empty; ArrayList list = new ArrayList(); if (this.intermediateResult != null&& this.intermediateResult.Length > 0) { output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1); string [] result=output.Split(‘,‘); float max = 0; if (result.Length >= 8) { for (int i = 0; i <= result.Length - 8; i++) { float re = 0; for (int j = i; j < 8 + i; j++) { re = re + Convert.ToSingle(result[j]); } re=re/8; if (re > max) { max = re; } } output = Math.Ceiling(max).ToString(); } else { output=string.Empty; } } return new SqlString(output); } public void Read(BinaryReader r) { intermediateResult = new StringBuilder(r.ReadString()); } public void Write(BinaryWriter w) { w.Write(this.intermediateResult.ToString()); } }

dll添加到sql server，创建聚合函数。

CREATE ASSEMBLY [MAX_O3_8HOUR_ND] AUTHORIZATION [dbo]
FROM ‘c:\MAX_O3_8HOUR_ND.dll‘
WITH PERMISSION_SET = SAFE;

CREATE AGGREGATE [dbo].[MAX_O3_8HOUR_ND] (@FieldValue [nvarchar](4000))
RETURNS [nvarchar](4000)
EXTERNAL NAME [MAX_O3_8HOUR_ND].[MAX_O3_8HOUR_ND];

sql server 开启 CLR支持：

EXEC sp_configure ‘clr enabled‘, 1
RECONFIGURE WITH OVERRIDE
GO

 

示例：

SELECT FDATE,SITENAME, dbo.MAX_O3_8HOUR_ND(O3) FROM MONITOR_ND
GROUP BY FDATE,SITENAME
ORDER BY FDATE,SITENAME

sql server 2012 自定义聚合函数(MAX_O3_8HOUR_ND) 计算最大的臭氧8小时滑动平均值

标签：