SQL2005CLR函数扩展-深入环比计算的详解

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{

    // 保存当前组当前值
    private static System.Collections.Generic.Dictionary <string , SqlString > _listValue = new System.Collections.Generic.Dictionary <string , SqlString >();
    // 保存当前组
    private static System.Collections.Generic.Dictionary <string , string > _listGroup  = new System.Collections.Generic.Dictionary <string , string >();

    /// <summary>
    /// 获取当前组上条记录数值
    /// </summary>
    /// <param name="key"> 并发键 </param>
    /// <param name="currentGroup"> 当前组 </param>
    /// <param name="currentValue"> 当前组当前值 </param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction ]
    public static SqlString GetPrevMemberValue(SqlString key,SqlString currentGroup,SqlString currentValue)
    {
        if (key.IsNull || currentGroup.IsNull) return SqlString .Null;

      
        try
        {
            SqlString prevMemberValue = _listValue[key.Value];

            // 组变更
            if (_listGroup[key.Value] != currentGroup.Value)
            {
                prevMemberValue = SqlString .Null;
                _listGroup[key.Value] = currentGroup.Value;
             }
            // 值变更
            _listValue[key.Value] = currentValue;

            return prevMemberValue;
        }
        catch
        {
            return SqlString .Null;
        }
    }
    /// <summary>
    /// 初始化并发键
    /// </summary>
    /// <param name="key"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction ]
    public static SqlBoolean InitKey(SqlString key)
    {
        try
        {
            _listValue.Add(key.Value, SqlString .Null);
            _listGroup.Add(key.Value, string .Empty);
            return true ;
        }
        catch
        {
            return false ;
        }
    }
    /// <summary>
    /// 释放并发键
    /// </summary>
    /// <param name="key"></param>
    /// <returns></returns>
    [Microsoft.SqlServer.Server.SqlFunction ]
    public static SqlBoolean DisposeKey(SqlString key)
    {
        try
        {
            _listValue.Remove(key.Value);
            _listGroup.Remove(key.Value);
            return true ;
        }
        catch
        {
            return false ;
        }
    }
};

CREATE ASSEMBLY TestForSQLCLR FROM E:/sqlclrdata/TestFun.dll WITH PERMISSION_SET = UnSAFE;
--
go
CREATE FUNCTION dbo. xfn_GetPrevMemberValue 
(  
    @key nvarchar ( 255),
    @initByDim nvarchar ( 255),
    @currentValue nvarchar ( 255)
)    
RETURNS nvarchar ( 255)
AS EXTERNAL NAME TestForSQLCLR. [UserDefinedFunctions]. GetPrevMemberValue
go
CREATE FUNCTION dbo. xfn_initKey
(  
    @key nvarchar ( 255)
)    
RETURNS bit
AS EXTERNAL NAME TestForSQLCLR. [UserDefinedFunctions]. InitKey
go
CREATE FUNCTION dbo. xfn_disposeKey 
(  
    @key nvarchar ( 255)
)    
RETURNS bit
AS EXTERNAL NAME TestForSQLCLR. [UserDefinedFunctions]. DisposeKey

declare @t table (
    [ 区域 ] [varchar]( 4) COLLATE Chinese_PRC_CI_AS NULL,
    [TradeMonth] [varchar]( 7) COLLATE Chinese_PRC_CI_AS NULL,
    [TradeMoney] [float] NULL,
    [TradeArea] [float] NULL,
    [TradePrice] [float] NULL
)
insert into @t
select 闵行 , 2007-03 , 2125714.91 , 241.65 , 8796.67 union
select 闵行 , 2007-04 , 8408307.64 , 907.32 , 9267.19 union
select 闵行 , 2007-05 , 10230321.95 , 1095.88 , 9335.26 union
select 浦东 , 2007-01 , 12738432.25 , 1419.05 , 8976.73 union
select 浦东 , 2007-02 , 4970536.74 , 395.49 , 12568.05 union
select 浦东 , 2007-03 , 5985405.76 , 745.94 , 8023.98 union
select 浦东 , 2007-04 , 21030788.61 , 1146.89 , 18337.23 union
select 普陀 , 2007-01 , 1863896 , 161.39 , 11549.02 union
select 普陀 , 2007-02 , 1614015 , 119.59 , 13496.24 union
select 普陀 , 2007-03 , 1059235.19 , 135.21 , 7834

declare @key varchar ( 40)
declare @b bit

set @key= newid ()
select @b= dbo. xfn_initKey( @key)

select 区域 , TradeMonth, TradePrice, LastMonthPrice,
cast ( round (( Tradeprice- LastMonthPrice)* 100/ LastMonthPrice, 2) as varchar ( 10))+ % as 环比 from (
select *, cast ( dbo. xfn_GetPrevMemberValue( @key, 区域 , Tradeprice) as float ) as LastMonthPrice from @t
) t
select @b= dbo. xfn_disposeKey( @key)