自定义聚合函数求中位数
using System; System.Data; System.Data.SqlClient; System.Data.SqlTypes; Microsoft.SqlServer.Server; System.IO; System.Collections.Generic;
[Serializable] [SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000,"sans-serif"; font-size: 9pt; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 0pt; mso-no-proof: yes;" lang="EN-US"> IsNullIfEmpty = true,"sans-serif"; font-size: 9pt; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 0pt; mso-no-proof: yes;" lang="EN-US"> Name = "MiddleNumber")] public class MiddleNumber : IBinarySerialize { public void Init() { list = new List<double>(); } public void Accumulate(SqlDouble Value) if (Value.IsNull == false) list.Add(Value.Value); public void Merge(MiddleNumber Group) list.AddRange(Group.list); public SqlDouble Terminate() double re = 0; int lCount = 0; list.Sort(); lCount = list.Count; if (lCount > 0) if (lCount % 2 == 0) re = (double)((Convert.ToDouble(list[lCount / 2 - 1]) + Convert.ToDouble(list[lCount / 2])) / 2.0); else re = (double)(Convert.ToDouble(list[Convert.ToInt32(Math.Floor(lCount / 2.0))])); return new SqlDouble(re); return SqlDouble.Null; public void Write(BinaryWriter writer) int lCount = list.Count; writer.Write(lCount); foreach (double number in list) writer.Write(number); public void Read(BinaryReader reader) int lCount = reader.ReadInt32(); for (int i = 0; i < lCount; i++) list.Add(reader.ReadDouble()); // 这是占位符成员字段 private List<double> list; } --SQL SERVER CREATE ASSEMBLY ass_test FROM 'G:/CLR_Test/SqlServerProject5/SqlServerProject5/bin/Debug/SqlServerProject5.dll'; GO
AGGREGATE MiddleNumber(@number float) RETURNS float EXTERNAL NAME ass_test.MiddleNumber; declare @t table(id int,val float) insert @t select 1,20.2 union all select 1,50 all select 2,"sans-serif"; font-size: 9pt; mso-bidi-font-family: 'Courier New'; mso-font-kerning: 0pt; mso-no-proof: yes;" lang="EN-US"> all select 3,null
all select 4,0 --查询 select id,dbo.MiddleNumber(val) as 中位数 from @t group by id /* id 中位数 ----------- ---------------------- 1 20.2 2 35 3 NULL 4 0
(4 行受影响) */ (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |