五餅二魚工作室

這幾天剛好有教到使用 SQL CLR C# 的方式，來撰寫使用者自定義函數和預存程序，擴充 T-SQL 的功能。而在課後收到一個學生的訊息，對於課堂上對於有關於連乘的處理，感覺有點空洞，因此整理一下範例，希望對後面學習的人有幫助。

在 SQL 的 Aggregate Function 中，SUM() , MIN() , MAX() 這一類的函數，大部分使用者在使用上都沒有問題。但有些時候像是我們在計算直通率的時候，需要把相關的值給連乘起來，那麼這個時候就會有一點困擾了，因此在 Aggregate 函數中，只能用 SUM() 做加法而沒有函數可以做乘法，因此遇到這樣的問題就有點麻煩了。

基本上如果要純用 T-SQL 來處理的話，是可以利用數學的 EXP 與 LOG 的處理 , 可以將 A * B * C 轉換為 EXP( LOG(A) + LOG(B) + LOG(C) , 這樣就可以利用先把欄位值取 LOG 之後做加總 , 最後再將加總後的值做 EXP 的計算就可以。這個方法雖然可以很方便處理，但如果遇到欄位值是 0 , 或者是負數的狀況下，則會發生溢位錯誤。

因此這裡我們可以用 SQL CLR C# 來撰寫 Aggregate 函數，自己來寫這樣的一個計算函數，雖然會多花一點功，但卻會比較好來做處理。

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct Product
{
    private double product ;
    private int count;
    public void Init()
    {
        // 將程式碼放在此處
        product = 1;
        count = 0;
    }

    public void Accumulate(SqlDouble Multiplier)
    {
        // 將程式碼放在此處
        product *= (Multiplier.IsNull) ? 1 : Multiplier.Value ;
        count += (Multiplier.IsNull) ? 0 : 1;
    }

    public void Merge (Product Group)
    {
        // 將程式碼放在此處
        Accumulate( new SqlDouble(Group.product) );
    }

    public SqlDouble Terminate ()
    {
        // 將程式碼放在此處
        return count == 0 ? SqlDouble.Null : new SqlDouble( product );
    }

上面的程式可以利用資料庫專案，發行到 SQL Server 上面之後，下面我們就用這兩種方式，來進行測試

WITH A AS
(
	SELECT * 
	FROM ( VALUES ( 'STEP1' , 1 ),( 'STEP2' , 0.9 ),( 'STEP3' , 0.8 ),( 'STEP4' , 1) ) AS A(Name,Yield)
)
SELECT 
	EXP(SUM(LOG(Yield))) AS FPY1,
	[dbo].[Product](Yield) AS FPY2 
FROM A
	

看起來都可以符合我們所要的，而在我們自己寫的 Product 函數，也可以不用擔心遇到 0 的狀況，比複雜去用 CTE 做遞迴的處理簡單的多了。