SQL 區間統計技巧：使用位元運算實現多區間單次掃描計數

需求：對數值欄位依多個區間（如 0-9、10-19、20-49、50-99、100+）統計筆數。傳統寫法需多次掃描或 UNION ALL，本文介紹**單次掃描 + 位元旗標 + GROUP BY** 的高效技巧。

核心邏輯

1. 對每個區間邊界計算 value / boundary，結果 ≥1 表示落在該區間或更高。
2. CONVERT(BIT, value / boundary) 產生 0/1 旗標（Bit 型別僅佔 1 位元）。
3. 所有旗標相加得到「區間索引」（1=第1區間, 2=第2區間...）。
4. 對索引 GROUP BY 計數，再用 CASE 映射回區間標籤。

完整範例腳本

-- 建立測試資料
DECLARE @tb TABLE (id INT IDENTITY(1,1), num INT);
INSERT INTO @tb (num) VALUES (1),(10),(20),(25),(12),(15),(13),(22),(5),(50),(80),(110);

-- 單次掃描多區間統計
SELECT 
    CASE (overall + over10 + over20 + over50 + over100)
        WHEN 1 THEN '0-9'
        WHEN 2 THEN '10-19'
        WHEN 3 THEN '20-49'
        WHEN 4 THEN '50-99'
        ELSE '100+'
    END AS [區間],
    COUNT(*) AS [筆數]
FROM (
    SELECT 
        1 AS overall,                    -- 基準旗標（所有行皆為1）
        CONVERT(BIT, num/10)  AS over10,   -- ≥10
        CONVERT(BIT, num/20)  AS over20,   -- ≥20
        CONVERT(BIT, num/50)  AS over50,   -- ≥50
        CONVERT(BIT, num/100) AS over100   -- ≥100
    FROM @tb
) AS flags
GROUP BY (overall + over10 + over20 + over50 + over100)
ORDER BY [區間];

輸出結果

區間   筆數
0-9    2
10-19  4
20-49  3
50-99  2
100+   1

原理解析

每個區間邊界對應一個 Bit 旗標，總和即為區間索引，**單次表掃描即可完成所有區間計數**。

擴展：動態區間邊界

若區間邊界儲存於設定表，可動態構建旗標欄位：

DECLARE @boundaries TABLE (seq INT, bound INT, label VARCHAR(20));
INSERT @boundaries VALUES (1,10,'0-9'),(2,20,'10-19'),(3,50,'20-49'),(4,100,'50-99'),(5,999999,'100+');

DECLARE @sql NVARCHAR(MAX) = N'SELECT ';
SELECT @sql += N'CONVERT(BIT, num/' + CAST(bound AS VARCHAR) + N') AS f' + CAST(seq AS VARCHAR) + N','
FROM @boundaries ORDER BY seq;
SET @sql = LEFT(@sql, LEN(@sql)-1) + N' FROM @tb'; -- 實際執行需動態 SQL
-- 後續 GROUP BY SUM(f1+f2+...) 並 JOIN @boundaries 取 label

效能優勢

• 單次全表掃描：避免多次 UNION ALL 或多次 COUNT(*) 子查詢的重複 I/O。
• 無排序/雜湊分組開銷：旗標總和僅 1~N 小整數，GROUP BY 極快（通常用 Hash Aggregate 或 Sort 但資料極小）。
• 可擴充性：新增區間只需加一個旗標運算，不改變查詢結構。

適用條件與限制

• 數值型別需支援除法運算（INT、DECIMAL、FLOAT）。
• 區間必須為**連續、不重疊、由小到大**的邊界（如 10,20,50,100）。
• Bit 旗標相加假設邊界嚴格遞增，若邊界不遞增邏輯失效。
• 極大資料集（億級）建議配合資料分區或彙總表預計算。

此技巧適用於報表儀表板、監控告警統計、數值分佈直方圖等**固定區間、高頻統計**場景，顯著降低查詢延遲。