自定义聚合函数求中位数

[liangCK]

vs.net->新建项目->C#->数据库项目->新建一个聚合函数

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Collections.Generic;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined,
                         MaxByteSize=8000,
                         IsNullIfEmpty=true,
                         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);
        }
        else
        {
            return SqlDouble.Null;
        }
    }

    public void Write(BinaryWriter writer)
    {
        int lCount = list.Count;

        writer.Write(lCount);

        if (lCount > 0)
        {
            foreach (double number in list)
            {
                writer.Write(number);
            }
        }
    }

    public void Read(BinaryReader reader)
    {
        int lCount = reader.ReadInt32();
        list = new List<double>();

        for (int i = 0; i < lCount; i++)
        {
            list.Add(reader.ReadDouble());
        }
    }

    // 这是占位符成员字段
    private List<double> list;

}

sql server management studio

CREATE ASSEMBLY ass_test
FROM 'G:\CLR_Test\SqlServerProject5\SqlServerProject5\bin\Debug\SqlServerProject5.dll';
GO

CREATE AGGREGATE MiddleNumber(@number float)
    RETURNS float
EXTERNAL NAME ass_test.MiddleNumber;
GO


declare @t table(id int,val float)
insert @t select 1,20.2
union all select 1,50
union all select 1,20

union all select 2,50
union all select 2,20

union all select 3,null

union 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 行受影响)
*/

[fuxiaoyang13]

xuexi

[昵称被占用了]

.

[liangCK]

海爷.指点一下.

[mybuhui]

ding

[lllsui]

向lz学习

[alisafan123]

[Quote=引用 41 楼 lllsui 的回复:]
向lz学习
[/Quote].

[ai_li7758521]

学习

[西瓜小刀]

哇！
学习学习……

[huntzw]

学习拉！

[zhouchuang301]

999999999999999999

[yuxinken]

学习

[xszzhong]

展示下

[lily_2006]

MARK一下以后学习

[playwarcraft]

[xuejie09242]

2000呢，可以利用扩展存储过程，是吗？呵呵
没有做过相关的内容，感觉既然是数据库不擅长的，就交给计算机语言去做，没必要在数据库中实现。

[SlaughtChen]

学习

[hhh871030]

好牛叉。。学习了

[mingyicz]

牛。。。。。。。。

[lfywy]

bucuode fangfa~~~~~~~~~~~~~~~~

[betaiii]

牛终于知道了。