SQL Server HAVING 子句進階：多層聚合、條件聚合與 UDF 分解技巧

前言

HAVING 用於篩選**分組後的聚合結果**，常與 GROUP BY 搭配。但當業務需要「聚合的聚合」(如：每筆訂單總額的平均值) 時，單層查詢無法直接巢狀聚合函數。本文以 AdventureWorks 範例資料庫，示範錯誤場景、UDF 分解法與現代 CTE/子查詢最佳實踐。

情境：客戶訂單總額分析

需求：找出**所有訂單總額超過 25,000** 的客戶清單。需連接 Customer、SalesOrderHeader、SalesOrderDetail 三表。

正確寫法：單層聚合 + HAVING

SELECT 
    C.CustomerID,
    SOH.SalesOrderID,
    SUM(SOD.LineTotal) AS SubTotal
FROM Sales.Customer C
JOIN Sales.SalesOrderHeader SOH ON SOH.CustomerID = C.CustomerID
JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SOH.SalesOrderID
GROUP BY C.CustomerID, SOH.SalesOrderID
HAVING SUM(SOD.LineTotal) > 25000.00
ORDER BY C.CustomerID, SOH.SalesOrderID;

• LineTotal 為計算欄位 (UnitPrice * (1-Discount) * OrderQty)，聚合函數同樣適用。
• HAVING 篩選分組後的 SUM(LineTotal)。

陷阱：雙層聚合無法直接寫在同一查詢

-- 錯誤嘗試：想求所有訂單的平均總額
SELECT 
    SOH.SalesOrderID,
    AVG(SUM(SOD.LineTotal)) AS Average  -- 錯誤！
FROM Sales.SalesOrderHeader SOH
JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SOH.SalesOrderID
GROUP BY SOH.SalesOrderID;

錯誤訊息：Msg 130, Level 15, State 1: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SQL 標準禁止直接巢狀聚合函數 (聚合的聚合)。

解決方案一：表值函數 (TVF) 分解 (舊版相容)

步驟 1：建立第一層聚合 TVF

CREATE FUNCTION dbo.SalesTotals_TVF ()
RETURNS TABLE
AS RETURN
(
    SELECT 
        SOD.SalesOrderID,
        SUM(SOD.LineTotal) AS TotalSale
    FROM Sales.SalesOrderDetail SOD
    GROUP BY SOD.SalesOrderID
);
GO

步驟 2：建立第二層純量函數計算平均

CREATE FUNCTION dbo.AverageSale_Scalar ()
RETURNS MONEY
AS
BEGIN
    DECLARE @Result MONEY;
    SELECT @Result = AVG(TotalSale) FROM dbo.SalesTotals_TVF();
    RETURN @Result;
END
GO

步驟 3：組合查詢

DECLARE @Avg MONEY = dbo.AverageSale_Scalar();

SELECT 
    ST.*,
    @Avg AS OverallAverage,
    ST.TotalSale / @Avg AS Ratio,
    CASE 
        WHEN ST.TotalSale / @Avg > 1 THEN N'高於平均'
        WHEN ST.TotalSale / @Avg < 1 THEN N'低於平均'
        ELSE N'持平'
    END AS Level
FROM dbo.SalesTotals_TVF() ST;

解決方案二：CTE / 子查詢分解 (現代最佳實踐，SQL 2005+)

WITH OrderTotals AS (
    SELECT 
        SOD.SalesOrderID,
        SUM(SOD.LineTotal) AS TotalSale
    FROM Sales.SalesOrderDetail SOD
    GROUP BY SOD.SalesOrderID
),
OverallStats AS (
    SELECT AVG(TotalSale) AS AvgSale FROM OrderTotals
)
SELECT 
    OT.*,
    OS.AvgSale,
    OT.TotalSale / OS.AvgSale AS Ratio,
    CASE 
        WHEN OT.TotalSale / OS.AvgSale > 1 THEN N'高於平均'
        WHEN OT.TotalSale / OS.AvgSale < 1 THEN N'低於平均'
        ELSE N'持平'
    END AS Level
FROM OrderTotals OT
CROSS JOIN OverallStats OS;

• 無需建立永久物件，邏輯內聚、易維護、優化器可整體最佳化。
• CROSS JOIN 單列統計表等同標量子查詢，效能極佳。

解決方案三：窗口函數 (SQL 2012+)

SELECT 
    SOD.SalesOrderID,
    SUM(SOD.LineTotal) OVER (PARTITION BY SOD.SalesOrderID) AS TotalSale,
    AVG(SUM(SOD.LineTotal) OVER (PARTITION BY SOD.SalesOrderID)) OVER () AS AvgSale
FROM Sales.SalesOrderDetail SOD
GROUP BY SOD.SalesOrderID;  -- 注意：窗口函數需配合 GROUP BY 或 DISTINCT

窗口函數 AVG(...) OVER () 可直接計算「聚合結果的平均」，但語法較繁瑣，CTE 更清晰。

方案比較總結

HAVING 進階用法補充

1. 多條件 HAVING：HAVING SUM(Amt) > 1000 AND COUNT(*) >= 5
2. HAVING 子查詢：HAVING SUM(Amt) > (SELECT AVG(SUM(Amt)) FROM ... GROUP BY ...) (需子查詢先算好)
3. 無 GROUP BY 的 HAVING：SELECT SUM(Amt) FROM Orders HAVING SUM(Amt) > 10000 —— 視為單一群組，等同 WHERE 但語意不同。

最佳實踐建議

1. 優先使用 CTE 分解多層聚合，邏輯清晰、效能穩定。
2. 避免永久函數封裝單一查詢邏輯，增加部署複雜度與統計資料維護負擔。
3. 大量資料聚合：考慮 Columnstore Index、批次模式、預先彙總表。
4. 命名規範：CTE 名稱用 PascalCase，欄位別名用 PascalCase。