業務常需按數值區間統計分布 (如:金額 0-9、10-19、20-49、50-99、100+)。傳統 `CASE WHEN` 雖直觀但代碼冗長。本文介紹一種**位元組合編碼 + 條件彙總**的巧技,以極簡代碼實現任意區間分桶統計。
為每個區間邊界分配一個 Bit 位:
將數值依次與邊界比較,滿足則置位,最終 **Bit 總和** 直接對應區間編號:
| 數值範圍 | Bit 組合 | 總和 | 標籤 |
|---|---|---|---|
| 0-9 | 00001 | 1 | 0-9 |
| 10-19 | 00011 | 3 | 10-19 |
| 20-49 | 00111 | 7 | 20-49 |
| 50-99 | 01111 | 15 | 50-99 |
| 100+ | 11111 | 31 | 100+ |
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);
| RangeLabel | Cnt |
|---|---|
| 0-9 | 2 |
| 10-19 | 3 |
| 20-49 | 3 |
| 50-99 | 2 |
| 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 生成完整查詢
| 優點 | 缺點/限制 |
|---|---|
| 單次掃描,無需多次掃表或 UNION ALL | 邊界固定時最佳;動態邊界需動態 SQL |
| Bit 運算極快,CPU 開銷極低 | 區間數 > 30 時 Bit 位不足 (INT 32 位) |
| 代碼緊湊,易封裝為內聯函數/視圖 | 邏輯較隱晦,團隊需統一認知 |