程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> SqlServer2005 >> SQL2005CLR函數擴展-深入環比計算的詳解

SQL2005CLR函數擴展-深入環比計算的詳解

編輯:SqlServer2005

此類問題還可以延伸到類似進銷存的批次計算中,這也要關注其他歷史記錄來決定當前某條記錄的狀態。

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%
*/
--------------------------------------------------------------------------------
這個函數寫的還是比較粗糙,如果進一步改進還可以詳細定義如何獲取上一個維度的方法。這裡只是根據查詢順序來做緩存。感興趣的朋友可以完善一下。

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved