SQL Server 2012 custom aggregate function (MAX_O3_8HOUR_ND) calculates the maximum 8-hour sliding average of ozone

Develop DLLs in C # and add them to SQL Server.

Specific code, you can use the Wizard of Visual Studio to generate the template.

usingSystem;usingSystem.Collections;usingSystem.Data;usingMicrosoft.SqlServer.Server;usingSystem.Data.SqlTypes;usingSystem.IO;usingSystem.Text; [Serializable] [Microsoft.SqlServer.Server.SqlUserDefinedAggregate (format.userdefined,//Use of CLR serialization to serialize the intermediate resultIsinvarianttonulls =true,//Optimizer PropertyIsinvarianttoduplicates =false,//Optimizer PropertyIsinvarianttoorder =false,//Optimizer PropertyMaxByteSize =8000)//maximum size in bytes of persisted value] Public classmax_o3_8hour_nd:ibinaryserialize{/// <summary>    ///The variable that holds the intermediate result of the concatenation/// </summary>    PrivateStringBuilder Intermediateresult; /// <summary>    ///initialization of the system/// </summary>     Public voidInit () { This. Intermediateresult =NewStringBuilder (); }    /// <summary>    ///accumulate text content, except for null, usually separated by punctuation marks. /// </summary>    /// <param name= "value" ></param>     Public voidaccumulate (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 voidMerge (max_o3_8hour_nd Group) { This. Intermediateresult.append (Group.intermediateresult); }    /// <summary>    ///at the end of the call, the result of the aggregate function is returned/// </summary>    /// <returns></returns>     PublicSqlString Terminate () {stringOutput =string.        Empty; ArrayList List=NewArrayList (); if( This. intermediateresult! =NULL&& This. intermediateresult.length >0) {Output= This. intermediateresult.tostring (0, This. Intermediateresult.length-1); string[] result=output. Split (','); floatMax =0; if(result.) Length >=8)            {                 for(inti =0; I <= result. Length-8; i++)                {                    floatRe =0;  for(intj = 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 NewSqlString (output); }     Public voidRead (BinaryReader r) {Intermediateresult=NewStringBuilder (r.readstring ()); }     Public voidWrite (BinaryWriter w) {W.write ( This. intermediateresult.tostring ()); }}

DLL to SQL Server to create an aggregate function.

' C:\MAX_O3_8HOUR_ND.dll '  = 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 turns on CLR support:

' CLR enabled ' 1 RECONFIGURE with Overridego

Example:

SELECT fdate,sitename, dbo. Max_o3_8hour_nd (O3) from Monitor_nd
GROUP by Fdate,sitename
ORDER by Fdate,sitename

SQL Server 2012 custom aggregate function (MAX_O3_8HOUR_ND) calculates the maximum 8-hour sliding average of ozone