您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 CLR 函數實作：環比計算與跨列資料存取

編輯：SQL Server 2005

在報表與分析查詢中，常需取得「上一筆記錄」的值進行環比計算（如本月營收與上月比較）。T-SQL 雖可用自我連結或視窗函數達成，但在複雜分群、大量資料下效能不彰。本文示範透過 CLR 純量函數搭配靜態快取，在單一查詢執行期間暫存「前一筆分群的值」，實現類 MDX 的 PrevMember 效果，大幅提升效能。

核心 CLR 程式碼 (C#)

使用兩個靜態 Dictionary 分別快取「目前分群」與「前一筆值」，並提供初始化、取值、釋放三個函數。

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

public partial class UserDefinedFunctions
{
    // 快取：Key=並發鍵, Value=前一筆值
    private static Dictionary _prevValue = new Dictionary();
    // 快取：Key=並發鍵, Value=目前分群
    private static Dictionary _currentGroup = new Dictionary();

    /// 取得前一筆分群記錄的值
    [SqlFunction]
    public static SqlString GetPrevMemberValue(SqlString key, SqlString currentGroup, SqlString currentValue)
    {
        if (key.IsNull || currentGroup.IsNull) return SqlString.Null;
        try
        {
            SqlString prev = _prevValue[key.Value];

            // 分群變更：重置前一筆值
            if (_currentGroup[key.Value] != currentGroup.Value)
            {
                prev = SqlString.Null;
                _currentGroup[key.Value] = currentGroup.Value;
            }

            // 更新快取為目前值，供下一筆使用
            _prevValue[key.Value] = currentValue;
            return prev;
        }
        catch { return SqlString.Null; }
    }

    /// 初始化並發鍵
    [SqlFunction]
    public static SqlBoolean InitKey(SqlString key)
    {
        try
        {
            _prevValue.Add(key.Value, SqlString.Null);
            _currentGroup.Add(key.Value, string.Empty);
            return true;
        }
        catch { return false; }
    }

    /// 釋放並發鍵
    [SqlFunction]
    public static SqlBoolean DisposeKey(SqlString key)
    {
        try
        {
            _prevValue.Remove(key.Value);
            _currentGroup.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),
    @currentGroup 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();
DECLARE @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(50))) 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%
...
*/