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

jinjazz 2009-04-28 11:54:51
加精
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%

*/


这个函数写的还是比较粗糙,如果进一步改进还可以详细定义如何获取上一个维度的方法。这里只是根据查询顺序来做缓存。感兴趣的朋友可以完善一下。
...全文
618 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
lubingll 2010-04-11
  • 打赏
  • 举报
回复
看不懂,先收藏,后学习
coolfsa896 2009-05-02
  • 打赏
  • 举报
回复
学习一下~~
tjwater20 2009-04-30
  • 打赏
  • 举报
回复
haoahoahoahoahao
a285232083 2009-04-29
  • 打赏
  • 举报
回复
Arial2010 2009-04-29
  • 打赏
  • 举报
回复
现在还看不懂啊郁闷
jinjazz 2009-04-29
  • 打赏
  • 举报
回复
[Quote=引用 20 楼 octverve 的回复:]
按楼主这样弄,环比,同比就没有太多价值了~
[/Quote]

价值不是实现方式决定的,是需求决定的。
SlaughtChen 2009-04-29
  • 打赏
  • 举报
回复
mark
octverve 2009-04-29
  • 打赏
  • 举报
回复
按楼主这样弄,环比,同比就没有太多价值了~
dinzjiaoyu 2009-04-28
  • 打赏
  • 举报
回复
长沙鼎卓人力资源管理公司教育部为社会下岗失业人员、转业军人、应、往届大学毕业生,为想学一技之长的青年免费提供电工、焊工培训,报名地址:五一广场平和堂对面锦绣中环28楼、联系电话:0731-4440515,联系人:赵老师
xiaomeixiang 2009-04-28
  • 打赏
  • 举报
回复
[Quote=引用 7 楼 liangCK 的回复:]
学习.
[/Quote]
liangCK 2009-04-28
  • 打赏
  • 举报
回复
学习.
fcuandy 2009-04-28
  • 打赏
  • 举报
回复
..
  • 打赏
  • 举报
回复
学学习
qqhmitzk 2009-04-28
  • 打赏
  • 举报
回复
学习
水族杰纶 2009-04-28
  • 打赏
  • 举报
回复
頂下~~
jinjazz 2009-04-28
  • 打赏
  • 举报
回复
自己mark一下,吃饭去
-狙击手- 2009-04-28
  • 打赏
  • 举报
回复
..
angelalien 2009-04-28
  • 打赏
  • 举报
回复
还没有用过.不知道怎样
lsd123 2009-04-28
  • 打赏
  • 举报
回复
.
laozihuen520 2009-04-28
  • 打赏
  • 举报
回复
dfgfd gfd gdf gd gddfgdg
加载更多回复(5)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧