萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL CLR 實作環比計算:模擬 MDX PrevMember 獲取上一期間值

在 OLAP 中 [Dimension].CurrentMember.PrevMember 可輕鬆取得上一期間值以計算環比 (MoM/YoY)。關聯式 SQL 原生不支援此語意,通常需自聯結或視窗函數 (LAG),但在複雜分群、多維度交叉分析時寫法極繁瑣。本文示範用 **SQL CLR 純量函數** 配合靜態快取字典,在單一查詢掃描中即時取得「上一列同群組的值」,大幅簡化查詢邏輯。

CLR 核心代碼 (C#)

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

public partial class UserDefinedFunctions
{
    // 快取:Key=並發鍵, Value=上一列的值
    private static Dictionary _lastValue = new Dictionary();
    // 快取:Key=並發鍵, Value=當前群組值
    private static Dictionary _lastGroup = new Dictionary();

    [SqlFunction]
    public static SqlString GetPrevMemberValue(SqlString key, SqlString currentGroup, SqlString currentValue)
    {
        if (key.IsNull || currentGroup.IsNull) return SqlString.Null;

        try
        {
            SqlString prev = _lastValue.ContainsKey(key.Value) ? _lastValue[key.Value] : SqlString.Null;

            // 群組變更 → 重置上一值
            if (!_lastGroup.ContainsKey(key.Value) || _lastGroup[key.Value] != currentGroup.Value)
            {
                prev = SqlString.Null;
                _lastGroup[key.Value] = currentGroup.Value;
            }

            // 更新當前值供下一列使用
            _lastValue[key.Value] = currentValue;

            return prev;
        }
        catch { return SqlString.Null; }
    }

    [SqlFunction]
    public static SqlBoolean InitKey(SqlString key)
    {
        try { _lastValue[key.Value] = SqlString.Null; _lastGroup[key.Value] = string.Empty; return true; }
        catch { return false; }
    }

    [SqlFunction]
    public static SqlBoolean DisposeKey(SqlString key)
    {
        try { _lastValue.Remove(key.Value); _lastGroup.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, 區域, TradePrice) AS FLOAT) AS LastMonthPrice
    FROM @t
) t;

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

結果:

區域TradeMonthTradePriceLastMonthPrice環比
閔行2007-038796.67NULLNULL
閔行2007-049267.198796.675.35%
閔行2007-059335.269267.190.73%
浦東2007-018976.73NULLNULL
浦東2007-0212568.058976.7340.01%
浦東2007-038023.9812568.05-36.16%
浦東2007-0418337.238023.98128.53%
普陀2007-0111549.02NULLNULL
普陀2007-0213496.2411549.0216.86%
普陀2007-03783413496.24-41.95%

關鍵限制與改進方向

  • 依賴查詢順序:必須配合 ORDER BY 群組, 期間,否則「上一列」語意錯誤。
  • 平行查詢風險:並行計畫會導致多執行緒共用靜態字典產生競爭,實務上需加 OPTION (MAXDOP 1) 或改用 ConcurrentDictionary。
  • 連線池殘留:靜態字典隨 AppDomain 存活,連線池重用時殘留資料會污染下一次查詢,務必成對呼叫 InitKey/DisposeKey。
  • 進階可擴充:傳入排序欄位、支援多層級群組、整合 LAG() (SQL 2012+) 邏輯自動降級。
copyright © 萬盛學電腦網 all rights reserved