萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL 區間統計巧技:用位運算與條件彙總實現靈活分桶統計

業務常需按數值區間統計分布 (如:金額 0-9、10-19、20-49、50-99、100+)。傳統 `CASE WHEN` 雖直觀但代碼冗長。本文介紹一種**位元組合編碼 + 條件彙總**的巧技,以極簡代碼實現任意區間分桶統計。

核心思路:位元標記法

為每個區間邊界分配一個 Bit 位:

  • Bit 0 (值 1):基準位,永遠 = 1
  • Bit 1 (值 2):≥ 10
  • Bit 2 (值 4):≥ 20
  • Bit 3 (值 8):≥ 50
  • Bit 4 (值 16):≥ 100

將數值依次與邊界比較,滿足則置位,最終 **Bit 總和** 直接對應區間編號:

數值範圍Bit 組合總和標籤
0-90000110-9
10-1900011310-19
20-4900111720-49
50-99011111550-99
100+1111131100+

實戰代碼

DECLARE @Data TABLE (Val INT);
INSERT @Data VALUES (1),(10),(20),(25),(12),(15),(13),(22),(5),(50),(80),(110);

-- 一次查詢完成分桶統計
SELECT 
    CASE (Base + B10 + B20 + B50 + B100)
        WHEN 1  THEN '0-9'
        WHEN 3  THEN '10-19'
        WHEN 7  THEN '20-49'
        WHEN 15 THEN '50-99'
        ELSE '100+'
    END AS RangeLabel,
    COUNT(*) AS Cnt
FROM (
    SELECT 
        1 AS Base,
        CAST(CASE WHEN Val >= 10 THEN 1 ELSE 0 END AS BIT) * 2 AS B10,
        CAST(CASE WHEN Val >= 20 THEN 1 ELSE 0 END AS BIT) * 4 AS B20,
        CAST(CASE WHEN Val >= 50 THEN 1 ELSE 0 END AS BIT) * 8 AS B50,
        CAST(CASE WHEN Val >= 100 THEN 1 ELSE 0 END AS BIT) * 16 AS B100
    FROM @Data
) x
GROUP BY Base + B10 + B20 + B50 + B100
ORDER BY MIN(Val);

輸出結果

RangeLabelCnt
0-92
10-193
20-493
50-992
100+2

泛化模板:任意區間邊界

-- 定義邊界陣列 (需遞增)
DECLARE @Bounds TABLE (BoundValue INT, BitWeight INT, Label NVARCHAR(50));
INSERT @Bounds VALUES 
    (10, 2, '10-19'), (20, 4, '20-49'), (50, 8, '50-99'), (100, 16, '100+');

-- 動態生成位元運算 SELECT 列表
DECLARE @select NVARCHAR(MAX) = N'1 AS Base';
SELECT @select += N', CAST(CASE WHEN Val >= ' + CAST(BoundValue AS NVARCHAR) 
                 + N' THEN 1 ELSE 0 END AS BIT) * ' + CAST(BitWeight AS NVARCHAR) + N' AS B' + CAST(BoundValue AS NVARCHAR)
FROM @Bounds;

DECLARE @sql NVARCHAR(MAX) = N'
SELECT 
    CASE (Base ' + REPLACE(@select, '1 AS Base,', '') + N')
        WHEN 1 THEN ''0-9''
        -- 動態生成 WHEN 分支略
        ELSE ''Other''
    END AS RangeLabel, COUNT(*) AS Cnt
FROM (SELECT ' + @select + N' FROM @Data) x
GROUP BY Base ' + REPLACE(@select, '1 AS Base,', '') + N';';

-- 實際專案建議改用 TVP 傳遞邊界表,配合動態 SQL 生成完整查詢

優缺點評析

替代方案對比

  • CASE WHEN:可讀性最好,維護成本最低,效能略低 (多次評估)
  • NTILE/窗口函數:等頻分桶適用,等寬分桶不適用
  • 數值表/遞歸 CTE 生成區間表 + LEFT JOIN:最標準、最易維護,適合報表系統

位元編碼分桶是「寫給 CPU 看的優雅代碼」,適合高頻、固定邊界、極致效能場景;常規業務報表仍推薦 `CASE WHEN` 或區間表 JOIN 以利長期維護。

copyright © 萬盛學電腦網 all rights reserved
優點缺點/限制
單次掃描,無需多次掃表或 UNION ALL邊界固定時最佳;動態邊界需動態 SQL
Bit 運算極快,CPU 開銷極低區間數 > 30 時 Bit 位不足 (INT 32 位)
代碼緊湊,易封裝為內聯函數/視圖邏輯較隱晦,團隊需統一認知