自定义聚合函数求中位数

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

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

*/