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
普陀 %
普陀 %
*/

這個函數寫的還是比較粗糙如果進一步改進還可以詳細定義如何獲取上一個維度的方法這裡只是根據查詢順序來做緩存感興趣的朋友可以完善一下

From:http://tw.wingwit.com/Article/program/MySQL/201311/29559.html