SQL Server CLR 函數實作環比計算：模擬 MDX PrevMember 語義

在複雜 OLAP 分析中，常需計算「環比」（本期與上期差值佔上期比例）。MDX 只需 `[維度].CurrentMember.PrevMember` 即可取得上一維度成員值，但 T-SQL 需自行 `JOIN` 歷史記錄，效能與撰寫皆較繁瑣。本文示範以 **CLR 純量函數** 實作緩存機制，模擬 MDX `PrevMember` 行為，大幅簡化查詢並提升效能。

CLR 核心邏輯

• 使用靜態 `Dictionary` 緩存「當前群組的上一筆值」。
• 以「並發鍵」區分多佈建/多執行緒查詢上下文。
• 提供三個函數：`InitKey`（初始化）、`GetPrevMemberValue`（取上一值）、`DisposeKey`（釋放）。

C# 程式碼

using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;

public partial class UserDefinedFunctions
{
    private static Dictionary _listValue = new Dictionary();
    private static Dictionary _listGroup = new Dictionary();

    [SqlFunction]
    public static SqlString GetPrevMemberValue(SqlString key, SqlString currentGroup, SqlString currentValue)
    {
        if (key.IsNull || currentGroup.IsNull) return SqlString.Null;
        try
        {
            SqlString prev = _listValue[key.Value];
            if (_listGroup[key.Value] != currentGroup.Value) // 群組變更
            {
                prev = SqlString.Null;
                _listGroup[key.Value] = currentGroup.Value;
            }
            _listValue[key.Value] = currentValue; // 更新當前值供下一列使用
            return prev;
        }
        catch { return SqlString.Null; }
    }

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

    [SqlFunction]
    public static SqlBoolean DisposeKey(SqlString key)
    {
        try { _listValue.Remove(key.Value); _listGroup.Remove(key.Value); return true; }
        catch { return false; }
    }
}

部署 T-SQL

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

使用範例：各區域月交易價格環比

-- 測試資料
DECLARE @t TABLE (區域 VARCHAR(4), TradeMonth VARCHAR(7), TradePrice FLOAT);
INSERT INTO @t VALUES
('閔行','2007-03',8796.67),('閔行','2007-04',9267.19),('閔行','2007-05',9335.26),
('浦東','2007-01',8976.73),('浦東','2007-02',12568.05),('浦東','2007-03',8023.98),('浦東','2007-04',18337.23),
('普陀','2007-01',11549.02),('普陀','2007-02',13496.24),('普陀','2007-03',7834);

-- 執行查詢
DECLARE @key VARCHAR(40) = NEWID(), @b BIT;
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, 區域, CAST(TradePrice AS NVARCHAR(255))) AS FLOAT) AS LastMonthPrice
    FROM @t
) t;

SELECT @b = dbo.xfn_disposeKey(@key);

結果

擴展建議

• 目前依查詢順序緩存，若需嚴格依時間排序，查詢前應 `ORDER BY TradeMonth`。
• 可進一步封裝「維度鍵、排序欄位、分區欄位」為參數，實作通用 `Lag/Lead` 函數。
• 注意 `UNSAFE` 權限組件需 `TRUSTWORTHY ON` 或簽署非對稱金鑰登入。