|
此类问题还可以延伸到类似进销存的批次计算中，这也要关注其他历史记录来决定当前某条记录的状态。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 ' 浦东 ','5985405.76','745.94','8023.98' union
select ' 浦东 ','21030788.61','1146.89','18337.23' union
select ' 普陀 ','1863896','161.39','11549.02' union
select ' 普陀 ','1614015','119.59','13496.24' union
select ' 普陀 ','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%
*/
--------------------------------------------------------------------------------
这个函数写的还是比较粗糙，如果进一步改进还可以详细定义如何获取上一个维度的方法。这里只是根据查询顺序来做缓存。感兴趣的朋友可以完善一下。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
