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

ameimeng

　　采用c#开发dll，并添加到sql server 中。
　　具体代码，可以用visual studio的向导生成模板。



using 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 (select top 100 percent * from MONITOR_ND order by ftime)a
GROUP BY SITENAME,FDATE