环比就是本月和上月的差值所占上月值的比例。在复杂的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 _listvalue = new system.collections.generic.dictionary ();
// 保存当前组
private static system.collections.generic.dictionary _listgroup = new system.collections.generic.dictionary ();
///
/// 获取当前组上条记录数值
///
/// 并发键
/// 当前组
/// 当前组当前值
///
[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;
}
}
///
/// 初始化并发键
///
///
///
[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 ;
}
}
///
/// 释放并发键
///
///
///
[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'
-- 测试语句
复制代码 代码如下: