当前位置：首页 > 资讯 > 技术文档

SQL2005CLR函数扩展-深入环比计算的详解

时间：2020-11-15 13:30 编辑：来源：阅读：
扫一扫，手机访问

摘要：SQL2005CLR函数扩展-深入环比计算的详解

此类问题还可以延伸到类似进销存的批次计算中，这也要关注其他历史记录来决定当前某条记录的状态。 sql语句无法简单实现mdx语句的类似功能，必须得用交叉表关联来对比。这里我们用CLR函数来实现mdx语句的类似语法。在select的时候把得到过的做个缓存就可以了。效率应该可以提高不少。 clr的代码如下，编译为TestFun.dll，复制到sql服务器的文件目录下。 --------------------------------------------------------------------------------

[u]复制代码[/u] 代码如下:

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 ;
        }
    }
};

-------------------------------------------------------------------------------- 部署和生成自定义函数，其中考虑到并发，我们还是需要一个并发键来表达当前查询 --------------------------------------------------------------------------------

[u]复制代码[/u] 代码如下:

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就是获取上月价格的函数。 -------------------------------------------------------------------------------- -- 建立测试环境

[u]复制代码[/u] 代码如下:

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'

-- 测试语句

[u]复制代码[/u] 代码如下:

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% */ -------------------------------------------------------------------------------- 这个函数写的还是比较粗糙，如果进一步改进还可以详细定义如何获取上一个维度的方法。这里只是根据查询顺序来做缓存。感兴趣的朋友可以完善一下。

全部评论(0)

上一篇：解决无法配置SQL2005问题
下一篇：SqlServer 2005 T-SQL Query 学习笔记（4）

资讯排行榜
更多>>