采用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{    /// 
    /// The variable that holds the intermediate result of the concatenation    /// 
    private StringBuilder intermediateResult;    /// 
    /// 系统初始化    /// 
    public void Init()    {        this.intermediateResult = new StringBuilder();    }    /// 
    /// 积累文本内容，null除外，一般用标点符号隔开。    /// 
    ///     public void Accumulate(SqlString value)    {        if (value.IsNull)        {            return;        }        this.intermediateResult.Append(value.Value).Append(',');    }    /// 
    /// Merge the partially computed aggregate with this aggregate.    /// 
    ///     public void Merge(MAX_O3_8HOUR_ND Group)    {        this.intermediateResult.Append(Group.intermediateResult);    }    /// 
    ///在最后被调用，返回聚合函数结果    /// 
    /// 
         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', 1RECONFIGURE WITH OVERRIDEGO

 

示例：

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