【交流】SQL2005CLR函数扩展-环比计算

blog原文，希望大家支持我的blog
http://blog.csdn.net/jinjazz/archive/2009/04/28/4132162.aspx

---------

环比就是本月和上月的差值所占上月值的比例。在复杂的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%

*/

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