【交流】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].GetPrevMemberValuego--初始化并发键CREATE FUNCTION dbo.xfn_initKey( @key nvarchar(255)) RETURNS bitAS EXTERNAL NAME TestForSQLCLR.[UserDefinedFunctions].InitKeygo--清理并发键CREATE FUNCTION dbo.xfn_disposeKey ( @key nvarchar(255)) RETURNS bitAS 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 @tselect '闵行','2007-03','2125714.91','241.65','8796.67' unionselect '闵行','2007-04','8408307.64','907.32','9267.19' unionselect '闵行','2007-05','10230321.95','1095.88','9335.26' unionselect '浦东','2007-01','12738432.25','1419.05','8976.73' unionselect '浦东','2007-02','4970536.74','395.49','12568.05' unionselect '浦东','2007-03','5985405.76','745.94','8023.98' unionselect '浦东','2007-04','21030788.61','1146.89','18337.23' unionselect '普陀','2007-01','1863896','161.39','11549.02' unionselect '普陀','2007-02','1614015','119.59','13496.24' unionselect '普陀','2007-03','1059235.19','135.21','7834' --测试语句declare @key varchar(40)declare @b bitset @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)tselect @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%*/