SQL 2005 CLR関数拡張-奥行きループ比計算の詳細解


このような問題は、入荷したロットのような計算にも拡張され、他の履歴にも注目して、現在の記録の状態を決定します。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 ;
        }
    }
};
---------------------------------------------------カスタム関数の配置と生成は、同時に考慮され、私達はやはり一つの同時キーを必要として、当面のクエリーを表現します。----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------GetProvMember Valueとは、先月の価格を獲得する関数である。試験環境の確立

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
  -- テストステートメント  -- 結果/*エリア   TradeMonth TradePrice             LastMonth Price         サイクル-----------------------------------------閔行   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    1149.02               NULL                   NULL普陀   2007-02    13496.24               11519                  16.86%普陀   2007-03    7834                   13496.2                -41.95%*/--------------------------------------この関数はまだ比較的に粗いので、さらに改善すれば、前の次元を得る方法を詳しく定義することができます。ここではクエリーの順序だけでキャッシュを行います。興味のある友達は完璧になります。