Oracle中无需循环统计置位比特位的最优方法

场景背景 #

为节省Oracle SQL存储空间，采用位图法记录连续时间段的布尔状态：二进制数的每个比特位对应当日事件是否发生，例如32位数字可存储32天的连续状态。统计置位（值为1）的比特位数量，即可直接得到事件发生天数，无需存储每日单独行。

每日更新位图示例（移除最旧比特位并设置最新比特位） #

SELECT testbitmap original_bitmap,
       BITAND((testbitmap * POWER(2,/*上次更新后间隔天数*/1)) + /*今日事件发生标记*/1,POWER(2,32)-1) new_bitmap
  FROM (SELECT BIN_TO_NUM(1,1,0,0,1,1,0,0,0,1,1,0,1,0,1,1,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0) testbitmap
          FROM dual)

核心问题 #

需要统计位图中置位的比特位数量，但Oracle没有BIN_TO_NUM的逆函数，是否存在无需逐位循环检测的统计方法？例如数字9（二进制1001）应返回2，数字7（二进制0111）应返回3。

解决方案 #

1. 内置函数BIT_COUNT（推荐，Oracle 12cR2+） #

Oracle 12cR2及以上版本提供了直接统计置位数量的内置函数BIT_COUNT，用法简单高效：

-- 示例：统计数字9的置位数量
SELECT BIT_COUNT(9) AS set_bit_count FROM dual; -- 返回2

-- 统计数字7的置位数量
SELECT BIT_COUNT(7) AS set_bit_count FROM dual; -- 返回3

-- 直接应用到位图字段
SELECT BIT_COUNT(your_bitmap_column) AS event_days FROM your_table;

2. 分治位运算（低版本Oracle兼容） #

针对不支持BIT_COUNT的Oracle版本，可使用分治法位运算实现无循环统计，原理是逐步合并相邻位的计数：

SELECT 
  bitmap,
  -- 32位数字的置位统计逻辑
  (BITAND(step4, 0x0000FFFF) + BITAND(step4 >> 16, 0x0000FFFF)) AS set_bit_count
FROM (
  SELECT 
    bitmap,
    (BITAND(step3, 0x00FF00FF) + BITAND(step3 >> 8, 0x00FF00FF)) AS step4
  FROM (
    SELECT 
      bitmap,
      (BITAND(step2, 0x0F0F0F0F) + BITAND(step2 >> 4, 0x0F0F0F0F)) AS step3
    FROM (
      SELECT 
        bitmap,
        (BITAND(step1, 0x33333333) + BITAND(step1 >> 2, 0x33333333)) AS step2
      FROM (
        SELECT 
          bitmap,
          (BITAND(bitmap, 0x55555555) + BITAND(bitmap >> 1, 0x55555555)) AS step1
        FROM (SELECT 9 AS bitmap FROM dual) -- 替换为你的位图字段/值
      )
    )
  )
);

3. 二进制字符串转换法 #

将数字转换为二进制字符串后，统计其中1的数量，无需循环：

-- 32位二进制格式转换，统计1的数量
SELECT 
  bitmap,
  LENGTH(REPLACE(TO_CHAR(bitmap, 'FM32B'), '0', '')) AS set_bit_count
FROM (SELECT 9 AS bitmap FROM dual); -- 返回2

-- 通用写法（自动适配数字长度）
SELECT 
  bitmap,
  LENGTH(REPLACE(TO_CHAR(bitmap, 'FM' || LENGTH(TO_CHAR(bitmap, 'B')) || 'B'), '0', '')) AS set_bit_count
FROM (SELECT 7 AS bitmap FROM dual); -- 返回3

内容的提问来源于stack exchange，提问作者Paul W