sql server 2012カスタム重合関数(MAX_O 3_8 HOUR_ND)計算最大オゾン8時間滑り平均値
9909 ワード
c#を使用してdllを開発し、sql serverに追加します.
具体的なコードは、visual studioのウィザードでテンプレートを生成できます.
sql serverはCLRサポートを開く:
例:
SELECT FDATE,SITENAME, dbo.MAX_O3_8HOUR_ND(O3) FROM (select top 100 percent * from MONITOR_ND order by ftime)aGROUP BY SITENAME,FDATE
具体的なコードは、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', 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)aGROUP BY SITENAME,FDATE