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

环比就是本月和上月的差值所占上月值的比例在复杂的olap计算中我们经常会用到同比环比等概念要求的上个维度的某个字段的实现语句非常简练比如ssas的mdx语句类似[维度]CurrentMemberPrevmember就可以了

此类问题还可以延伸到类似进销存的批次计算中这也要关注其他历史记录来决定当前某条记录的状态

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

clr的代码如下编译为TestFundll复制到sql服务器的文件目录下

复制代码 代码如下:
using System;
using SystemData;
using SystemDataSqlClient;
using SystemDataSqlTypes;
using MicrosoftSqlServerServer;

public partial class UserDefinedFunctions
{

// 保存当前组当前值
private static SystemCollectionsGenericDictionary <string   SqlString > _listValue = new SystemCollectionsGenericDictionary  <string SqlString >();
// 保存当前组
private static  SystemCollectionsGenericDictionary <string string >  _listGroup  = new SystemCollectionsGenericDictionary <string   string >();

/// <summary>
/// 获取当前组上条记录数值
/// </summary>
/// <param name="key"> 并发键 </param>
/// <param name="currentGroup"> 当前组 </param>
/// <param name="currentValue"> 当前组当前值 </param>
/// <returns></returns>
[MicrosoftSqlServerServerSqlFunction ]
public static SqlString GetPrevMemberValue(SqlString keySqlString currentGroupSqlString currentValue)
{
if (keyIsNull || currentGroupIsNull) return SqlString Null;


try
{
SqlString prevMemberValue = _listValue[keyValue];

// 组变更
if (_listGroup[keyValue] != currentGroupValue)
{
prevMemberValue = SqlString Null;
_listGroup[keyValue] = currentGroupValue;
}
// 值变更
_listValue[keyValue] = currentValue;

return prevMemberValue;
}
catch
{
return SqlString Null;
}
}
/// <summary>
/// 初始化并发键
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
[MicrosoftSqlServerServerSqlFunction ]
public static SqlBoolean InitKey(SqlString key)
{
try
{
_listValueAdd(keyValue SqlString Null);
_listGroupAdd(keyValue string Empty);
return true ;
}
catch
{
return false ;
}
}
/// <summary>
/// 释放并发键
/// </summary>
/// <param name="key"></param>
/// <returns></returns>
[MicrosoftSqlServerServerSqlFunction ]
public static SqlBoolean DisposeKey(SqlString key)
{
try
{
_listValueRemove(keyValue);
_listGroupRemove(keyValue);
return true ;
}
catch
{
return false ;
}
}
};

部署和生成自定义函数其中考虑到并发我们还是需要一个并发键来表达当前查询

复制代码 代码如下:
CREATE ASSEMBLY TestForSQLCLR FROM E:/sqlclrdata/TestFundll WITH PERMISSION_SET = UnSAFE;

go
CREATE FUNCTION dbo xfn_GetPrevMemberValue  
(   
@key nvarchar ( )
@initByDim nvarchar ( )
@currentValue nvarchar ( )
)     
RETURNS nvarchar ( )
AS EXTERNAL NAME TestForSQLCLR [UserDefinedFunctions] GetPrevMemberValue
go
CREATE FUNCTION dbo xfn_initKey
(   
@key nvarchar ( )
)     
RETURNS bit
AS EXTERNAL NAME TestForSQLCLR [UserDefinedFunctions] InitKey
go
CREATE FUNCTION dbo xfn_disposeKey  
(   
@key nvarchar ( )
)     
RETURNS bit
AS EXTERNAL NAME TestForSQLCLR [UserDefinedFunctions] DisposeKey

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

建立测试环境

复制代码 代码如下:
declare @t table (
[ 区域 ] [varchar]( ) COLLATE Chinese_PRC_CI_AS NULL
[TradeMonth] [varchar]( ) COLLATE Chinese_PRC_CI_AS NULL
[TradeMoney] [float] NULL
[TradeArea] [float] NULL
[TradePrice] [float] NULL
)
insert into @t
select 闵行 union
select 闵行 union
select 闵行 union
select 浦东 union
select 浦东 union
select 浦东 union
select 浦东 union
select 普陀 union
select 普陀 union
select 普陀

测试语句

复制代码 代码如下:
declare @key varchar ( )
declare @b bit

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

select 区域 TradeMonth TradePrice LastMonthPrice
cast ( round (( Tradeprice LastMonthPrice)* / LastMonthPrice ) as varchar ( ))+ % 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         环比

闵行                       NULL                   NULL
闵行                                       %
闵行                                        %
浦东                       NULL                   NULL
浦东                                      %
浦东                                         %
浦东                                       %
普陀                      NULL                   NULL
普陀                                        %
普陀                                          %
*/

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