SQL2005CLR函数扩展-环比计算

      环比就是本月和上月的差值所占上月值的比例。在复杂的olap计算中我们经常会用到同比环比等概念，要求的上个维度的某个字段的实现语句非常简练，比如ssas的mdx语句类似[维度].CurrentMember.Prevmember就可以了。此类问题还可以延伸到类似进销存的批次计算中，这也要关注其他历史记录来决定当前某条记录的状态。

 

     sql语句无法简单实现mdx语句的类似功能，必须得用交叉表关联来对比。这里我们用CLR函数来实现mdx语句的类似语法。在select的时候把得到过的做个缓存就可以了。效率应该可以提高不少。

 

    clr的代码如下，编译为TestFun.dll，复制到sql服务器的文件目录下。

 

 

---

 

 

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

---

这样我们就可以使用了，测试脚本如下， xfn_GetPrevMemberValue就是获取上月价格的函数。

---

 

-- 建立测试环境

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)

 

 

-- 结果

/*

 

区域   TradeMonth TradePrice             LastMonthPrice         环比

---- ---------- ---------------------- ---------------------- -----------

闵行   2007-03    8796.67                NULL                   NULL

闵行   2007-04    9267.19                8796.67                5.35%

闵行   2007-05    9335.26                9267.19                 0.73%

浦东   2007-01    8976.73                NULL                   NULL

浦东   2007-02    12568.05               8976.73                40.01%

浦东   2007-03    8023.98                12568                  -36.16%

浦东   2007-04    18337.23                8023.98                128.53%

普陀   2007-01    11549.02               NULL                   NULL

普陀   2007-02    13496.24               11549                  16.86%

普陀   2007-03    7834                   13496.2                -41.95%

 

*/

 

---

 

这个函数写的还是比较粗糙，如果进一步改进还可以详细定义如何获取上一个维度的方法。这里只是根据查询顺序来做缓存。感兴趣的朋友可以完善一下。