在 OLAP 中 [Dimension].CurrentMember.PrevMember 可輕鬆取得上一期間值以計算環比 (MoM/YoY)。關聯式 SQL 原生不支援此語意,通常需自聯結或視窗函數 (LAG),但在複雜分群、多維度交叉分析時寫法極繁瑣。本文示範用 **SQL CLR 純量函數** 配合靜態快取字典,在單一查詢掃描中即時取得「上一列同群組的值」,大幅簡化查詢邏輯。
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; }
}
}
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);
結果:
| 區域 | 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.05 | -36.16% |
| 浦東 | 2007-04 | 18337.23 | 8023.98 | 128.53% |
| 普陀 | 2007-01 | 11549.02 | NULL | NULL |
| 普陀 | 2007-02 | 13496.24 | 11549.02 | 16.86% |
| 普陀 | 2007-03 | 7834 | 13496.24 | -41.95% |
ORDER BY 群組, 期間,否則「上一列」語意錯誤。OPTION (MAXDOP 1) 或改用 ConcurrentDictionary。LAG() (SQL 2012+) 邏輯自動降級。