SQL 進階技巧：CROSS APPLY 實現分組內 Top N 與百分比取樣

業務場景

從全量客戶表中，按機構、公司、地區、渠道四維分組，針對特定渠道（1027、1028）各自取銷量前 10% 客戶進行電話調查。

核心查詢

SELECT DISTINCT
    b.OrgID, b.CompanyID, b.AreaID, b.CustChannelID, b.CustID, b.SaleTotalQty,
    GETDATE() AS ProcessTime
FROM KDS400TelSurveyCustAll_Temp tmp1
CROSS APPLY (
    SELECT TOP 10 PERCENT *
    FROM KDS400TelSurveyCustAll_Temp tmp2
    WHERE tmp1.OrgID = tmp2.OrgID
      AND tmp1.CompanyID = tmp2.CompanyID
      AND tmp1.AreaID = tmp2.AreaID
      AND tmp1.CustChannelID = tmp2.CustChannelID
      AND tmp2.CustChannelID IN (1027, 1028)
    ORDER BY tmp2.OrgID, tmp2.CompanyID, tmp2.AreaID, tmp2.AreaType, tmp2.CustChannelID
) b;

關鍵技術解析

CROSS APPLY vs INNER JOIN

執行邏輯圖解

外層 tmp1 掃描每一行
    │
    ▼
內層 tmp2 依外層鍵值過濾
    │
    ▼
按銷量降序排序
    │
    ▼
取前 10% 記錄返回
    │
    ▼
外層 SELECT DISTINCT 去重

效能優化策略

1. 索引設計：

CREATE INDEX IX_Survey_Grouping 
ON KDS400TelSurveyCustAll_Temp (OrgID, CompanyID, AreaID, CustChannelID, SaleTotalQty DESC)
INCLUDE (CustID, AreaType);

2. 統計資訊維護：大表分組查詢極依賴直方圖，排程 UPDATE STATISTICS ... WITH FULLSCAN。
3. 避免 SELECT *：內層僅選擇必要欄位，減少 Key Lookup。
4. 分區表支援：若表已分區，確保分區欄位包含在 JOIN 條件中以利分區剪枝。

替代方案對比

方案二：ROW_NUMBER() 窗口函數（SQL 2005+）

WITH Ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY OrgID, CompanyID, AreaID, CustChannelID
            ORDER BY SaleTotalQty DESC
        ) AS rn,
        COUNT(*) OVER (
            PARTITION BY OrgID, CompanyID, AreaID, CustChannelID
        ) AS cnt
    FROM KDS400TelSurveyCustAll_Temp
    WHERE CustChannelID IN (1027, 1028)
)
SELECT OrgID, CompanyID, AreaID, CustChannelID, CustID, SaleTotalQty, GETDATE()
FROM Ranked
WHERE rn <= CEILING(cnt * 0.1);

優點：單次掃描、並行度高。
缺點：記憶體壓力大、需計算精確行數。

方案三：APPROX_PERCENTILE（SQL 2022+）

SELECT DISTINCT
    b.*
FROM KDS400TelSurveyCustAll_Temp tmp1
CROSS APPLY (
    SELECT *
    FROM KDS400TelSurveyCustAll_Temp tmp2
    WHERE tmp1.OrgID = tmp2.OrgID
      AND tmp1.CompanyID = tmp2.CompanyID
      AND tmp1.AreaID = tmp2.AreaID
      AND tmp1.CustChannelID = tmp2.CustChannelID
      AND tmp2.CustChannelID IN (1027, 1028)
      AND tmp2.SaleTotalQty >= (
          SELECT APPROX_PERCENTILE(0.9) WITHIN GROUP (ORDER BY SaleTotalQty)
          FROM KDS400TelSurveyCustAll_Temp
          WHERE OrgID = tmp1.OrgID
            AND CompanyID = tmp1.CompanyID
            AND AreaID = tmp1.AreaID
            AND CustChannelID = tmp1.CustChannelID
      )
) b;

適用：超大數據集近似計算，效能極佳但有誤差。

最佳實踐建議

• 資料量 < 100 萬：CROSS APPLY + TOP PERCENT 可讀性最佳。
• 資料量 100 萬 - 1 億：ROW_NUMBER() 配合並行查詢（MAXDOP）。
• 資料量 > 1 億：考慮預先聚合物化視圖或近似算法。
• 永遠測試真實數據量下的執行計畫，關注 Actual Rows vs Estimated Rows 偏差。