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%*/--------------------------------------この関数はまだ比較的に粗いので、さらに改善すれば、前の次元を得る方法を詳しく定義することができます。ここではクエリーの順序だけでキャッシュを行います。興味のある友達は完璧になります。