PostgreSQLのbit_count()とは？バイナリ文字列の1を数える関数を徹底解説

2025-05-31

機能

bit_count() 関数は、引数として与えられたバイナリ文字列またはビット文字列を検査し、その中の「1」の数を数えて bigint 型（整数型）で返します。

使用例

ビット文字列の場合

SELECT bit_count(B'10111');
-- 結果: 4

この例では、B'10111'というビット文字列の中に「1」が4つあるため、結果は「4」になります。

バイナリ文字列の場合

bytea型（バイナリ文字列）に対しても bit_count() は使用できます。

SELECT bit_count('0xabcdef'::bytea);
-- 結果: 17

0xabcdef は16進数で表現されたバイナリ文字列です。これを2進数で考えると、 0xab = 10101011 (1が5個) 0xcd = 11001101 (1が6個) 0xef = 11101111 (1が7個) 合計で 5 + 6 + 7 = 18 となるはずですが、PostgreSQLのバージョンや内部実装によっては異なる結果が出る可能性があります。公式ドキュメントの例では0xabcdefのbit_countは17と記載されています。

データ型: bit、bit varying、bytea 型の引数を受け付けます。整数型など他のデータ型で使用したい場合は、明示的にキャスト（型変換）する必要があります。
PostgreSQL 14以降で利用可能: bit_count() 関数はPostgreSQL 14で導入されました。それ以前のバージョンでは使用できません。

バージョン互換性エラー

問題: bit_count関数が見つからない、または「function bit_count(bytea) does not exist」のようなエラーが発生する。

原因: bit_count()関数は、PostgreSQL 14で導入されました。それより古いバージョンのPostgreSQLを使用している場合、この関数は存在しません。

トラブルシューティング:

カスタム関数を作成する: アップグレードが難しい場合、PostgreSQL 13以前のバージョンでも、bit_countと同様の機能を持つカスタム関数をSQLまたはPL/pgSQLで作成することができます。例えば、byteaを16進数文字列に変換し、各バイトのビットを数えるロジックを実装するなどです。ただし、C言語で実装された組み込み関数と比較してパフォーマンスが劣る可能性があります。
PostgreSQLをアップグレードする: 可能であれば、PostgreSQLをバージョン14以降にアップグレードすることを検討してください。
PostgreSQLのバージョンを確認する: SELECT version(); を実行して、PostgreSQLのバージョンを確認します。

データ型不一致エラー

問題: bit_count()関数に間違ったデータ型の引数を渡すと、「function bit_count(integer) does not exist」のようなエラーが発生する。

原因: bit_count()関数は、bit型、bit varying型、またはbytea型の引数を受け付けます。他のデータ型（integer, textなど）を直接渡すことはできません。

トラブルシューティング:

入力値の検証: 不適切な形式の文字列をキャストしようとするとエラーになるため、入力値が有効なバイナリ/16進数表現であることを確認してください。
明示的な型キャストを行う: bit_count()関数に渡す前に、値を適切なデータ型にキャストします。
- 整数からビット文字列へ:
```
-- 整数をbit(N)型にキャスト
SELECT bit_count(10::bit(8)); -- 10はB'00001010'なので結果は2
-- 整数をbit varying型にキャスト（推奨）
SELECT bit_count(10::integer::bit varying);
```
  整数をbit(N)型にキャストする場合、指定したビット長（N）で右詰めされ、余りはゼロ埋めされます。bit varyingにキャストすると、必要なビット数に自動調整されます。
- テキストからビット文字列へ: テキスト形式のバイナリ表現を直接bit型にキャストできます。
```
SELECT bit_count('10110'::bit); -- 結果: 3
```
- テキストからバイナリ文字列へ: 16進数表現のテキストをbytea型にキャストできます。
```
SELECT bit_count('0x0f'::bytea); -- 0x0f は B'00001111' なので結果は4
```

NULL値の扱い

問題: bit_count(NULL)を実行した場合、何が返されるか。

原因: SQL関数は、通常、いずれかの入力がNULLであれば結果もNULLを返します（SQLのNULL伝播ルール）。

トラブルシューティング:

NULL値を「0」として扱いたい場合は、COALESCE関数などを使用します。
```
SELECT COALESCE(bit_count(column_name), 0) FROM your_table;
```
bit_count(NULL)はNULLを返します。

bit_count()はC言語で実装されており、効率的なポピュレーションカウントアルゴリズム（例: サイドウェイ和、ハッカーの喜びのアプローチ、CPUの組み込み命令など）を利用しているため、通常は非常に高速です。しかし、非常に大きなデータセットに対して頻繁に実行する場合や、複雑なクエリの一部として使用する場合には、パフォーマンスに影響を与える可能性があります。

トラブルシューティング:

適切なデータ型の選択: bit型やbytea型は、それぞれ異なる目的で設計されています。ビットフラグのように固定長の少ないビットを扱う場合はbit型、画像データやファイルコンテンツなどの可変長のバイナリデータを扱う場合はbytea型を使用するなど、用途に応じて適切なデータ型を選択することが重要です。
計算結果のキャッシュ/マテリアライズドビュー: bit_count()の結果が頻繁に必要とされ、元のデータがそれほど頻繁に更新されない場合は、計算結果を別のカラムに保存したり、マテリアライズドビューとしてキャッシュしたりすることを検討してください。これにより、bit_count()の呼び出し回数を減らすことができます。
インデックスの利用: bit_count()の結果を直接インデックス化することはできませんが、もしビット文字列やバイナリ文字列カラムに対して特定のビットパターンでフィルタリングを行う場合は、部分インデックスやGINインデックス（bytea型の場合）の使用を検討するかもしれません。ただし、これはbit_countそのもののパフォーマンス改善ではなく、関連するクエリ全体の最適化です。

以下に、bit_count()に関連するプログラミング例をいくつか示します。

基本的な使用例

ビット文字列 (bit / bit varying)

-- 固定長のビット文字列
SELECT bit_count(B'101101'::bit(6));
-- 結果: 4

-- 可変長のビット文字列
SELECT bit_count(B'11100101'::bit varying);
-- 結果: 5

B''プレフィックスはビット文字列リテラルを示します。::bit(N)や::bit varyingを使って明示的に型をキャストしています。

バイナリ文字列 (bytea)

bytea型は、生のバイナリデータを格納するために使用されます。bit_count()は、このバイナリデータ内のすべてのビットを考慮して「1」の数を数えます。

-- 16進数表記のバイナリ文字列
SELECT bit_count('0xFF'::bytea);
-- '0xFF' は B'11111111' なので、結果: 8

SELECT bit_count('0x0A'::bytea);
-- '0x0A' は B'00001010' なので、結果: 2

SELECT bit_count('0x1234567890'::bytea);
-- 0x12 = B'00010010' (2個の1)
-- 0x34 = B'00110100' (3個の1)
-- 0x56 = B'01010110' (4個の1)
-- 0x78 = B'01111000' (4個の1)
-- 0x90 = B'10010000' (2個の1)
-- 合計: 2+3+4+4+2 = 15
-- 結果: 15

テーブルでの利用例

テーブルにビットフラグやバイナリデータが格納されている場合、bit_count()を使って集計を行うことができます。

例: ユーザー権限の管理

ユーザーの権限をビットマスクで管理する一般的なシナリオです。

-- ユーザーテーブルの作成（権限カラムをbit varyingで持つ）
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    permissions BIT VARYING(32) DEFAULT B''
);

-- 権限ビットの説明（例）
-- 0番目: 読み取り (Read)
-- 1番目: 書き込み (Write)
-- 2番目: 削除 (Delete)
-- 3番目: 管理 (Admin)

-- データの挿入
INSERT INTO users (username, permissions) VALUES
('Alice', B'0001'), -- Admin
('Bob',   B'0011'), -- Write, Read
('Charlie', B'0101'), -- Delete, Read
('David',   B'0000'); -- No permissions

-- 各ユーザーが持つ権限の数をカウント
SELECT
    username,
    permissions,
    bit_count(permissions) AS num_permissions
FROM users;

-- 結果:
-- username | permissions | num_permissions
-- ----------+-------------+-----------------
-- Alice    | 0001        | 1
-- Bob      | 0011        | 2
-- Charlie  | 0101        | 2
-- David    | 0000        | 0

例: バイナリデータの特性分析

例えば、画像データのメタデータとしてバイナリハッシュを保存している場合など。

-- 画像メタデータテーブルの作成
CREATE TABLE image_metadata (
    image_id SERIAL PRIMARY KEY,
    image_name VARCHAR(100),
    image_hash BYTEA
);

-- データの挿入（簡略化されたハッシュ値）
INSERT INTO image_metadata (image_name, image_hash) VALUES
('photo1.jpg', '0x1234abcd'::bytea),
('document.pdf', '0x0000ffff'::bytea),
('logo.png', '0xaaaaaaaa'::bytea);

-- 各画像のハッシュ値に含まれるセットビットの数をカウント
SELECT
    image_name,
    image_hash,
    bit_count(image_hash) AS set_bit_count
FROM image_metadata;

-- 結果:
-- image_name   | image_hash   | set_bit_count
-- --------------+--------------+---------------
-- photo1.jpg   | \x1234abcd   | 15
-- document.pdf | \x0000ffff   | 16
-- logo.png     | \xaaaaaaaa   | 16

条件付き集計での利用

bit_count()の結果を使って、特定の条件でデータをフィルタリングしたり、集計したりすることができます。

-- 権限が2つ以上のユーザーを検索
SELECT
    username,
    permissions
FROM users
WHERE bit_count(permissions) >= 2;

-- 結果:
-- username | permissions
-- ----------+-------------
-- Bob      | 0011
-- Charlie  | 0101

-- 権限の数ごとのユーザー数を集計
SELECT
    bit_count(permissions) AS num_permissions,
    COUNT(*) AS user_count
FROM users
GROUP BY num_permissions
ORDER BY num_permissions;

-- 結果:
-- num_permissions | user_count
-- -----------------+------------
-- 0               | 1
-- 1               | 1
-- 2               | 2

bit_count()はinteger型を直接受け付けませんが、明示的にbitまたはbit varyingにキャストすることで、整数値のビット数を数えることができます。

-- 10 (decimal) は 00001010 (binary)
SELECT 10::integer::bit varying; -- B'1010'
SELECT bit_count(10::integer::bit varying); -- 結果: 2

-- 255 (decimal) は 11111111 (binary)
SELECT bit_count(255::integer::bit varying); -- 結果: 8

-- 大きな整数値の場合 (bigint)
SELECT bit_count(9223372036854775807::bigint::bit varying); -- bigintの最大値、全てのビットが1
-- 結果: 63 (符号ビットを考慮しない場合、あるいはbit varyingの挙動による)
-- 通常は64ビット整数なので、全て1であれば64となるが、PostgreSQLのbit varyingキャストは先頭のゼロを省略するため。

注意: 整数をbit(N)にキャストする場合、指定したビット長で右詰めされ、余りはゼロ埋めされます。負の数をbit(N)にキャストすると、2の補数表現になります。

SELECT bit_count(44::bit(10)); -- 44 = B'0000101100' -> 結果: 3
SELECT bit_count(-44::int::bit(12)); -- -44の2の補数表現（12ビット）
-- 結果: 9 (111111010100)

以下に、PostgreSQLでbit_count()の代替として利用できるプログラミング方法をいくつか説明します。

PL/pgSQLでカスタム関数を作成する (PostgreSQL 13以前のバージョン向け)

bit_count()が存在しない古いPostgreSQLバージョンで、同様の機能を実現する最も一般的な方法は、PL/pgSQLでカスタム関数を作成することです。

ビット文字列 (bit / bit varying) の場合

substring()関数とループを組み合わせて、各ビットを個別にチェックする方法です。

CREATE OR REPLACE FUNCTION custom_bit_count_bit(p_bit_string BIT VARYING)
RETURNS BIGINT AS $$
DECLARE
    bit_len INT := bit_length(p_bit_string);
    count_ones BIGINT := 0;
    i INT;
BEGIN
    IF p_bit_string IS NULL THEN
        RETURN NULL;
    END IF;

    FOR i IN 1..bit_len LOOP
        IF substring(p_bit_string FROM i FOR 1) = B'1' THEN
            count_ones := count_ones + 1;
        END IF;
    END LOOP;
    RETURN count_ones;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用例
SELECT custom_bit_count_bit(B'101101'); -- 結果: 4
SELECT custom_bit_count_bit(NULL::BIT VARYING); -- 結果: NULL

注意点:

substring()はビット位置を1から数えます。
この方法は、純粋なSQL関数よりもパフォーマンスが劣る可能性があります。特に長いビット文字列に対しては顕著です。

バイナリ文字列 (bytea) の場合

byteaの場合、バイト単位で処理し、各バイトをさらにビット単位でチェックする必要があります。これはより複雑になります。

CREATE OR REPLACE FUNCTION custom_bit_count_bytea(p_bytea BYTEA)
RETURNS BIGINT AS $$
DECLARE
    byte_len INT := octet_length(p_bytea); -- バイト長
    count_ones BIGINT := 0;
    i INT;
    current_byte INT;
    j INT;
BEGIN
    IF p_bytea IS NULL THEN
        RETURN NULL;
    END IF;

    FOR i IN 0..(byte_len - 1) LOOP -- バイトを0から数える
        current_byte := get_byte(p_bytea, i); -- i番目のバイトを取得

        -- 各バイトの8ビットをチェック
        FOR j IN 0..7 LOOP
            IF (current_byte & (1 << j)) <> 0 THEN -- j番目のビットが1かどうか
                count_ones := count_ones + 1;
            END IF;
        END LOOP;
    END LOOP;
    RETURN count_ones;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用例
SELECT custom_bit_count_bytea('0xFF'::bytea); -- 結果: 8
SELECT custom_bit_count_bytea('0x1234567890'::bytea); -- 結果: 15
SELECT custom_bit_count_bytea(NULL::BYTEA); -- 結果: NULL

注意点: get_byte()はビット位置を0から数えます。(1 << j)は2の累乗（2j）を生成し、ビットAND演算子&を使って特定のビットがセットされているかを確認します。

integer型に対するビット操作 (SQL関数)

PostgreSQLにはinteger型に対するビット単位演算子があります。これらを活用して、integerやbigintのビットカウントを行うカスタム関数を作成できます。

ビット単位演算子を利用したPopcountアルゴリズム

これは、整数を扱う場合に非常に効率的な方法です。シフト演算子 (>>) とビットAND演算子 (&) を利用します。一般的なPopcountアルゴリズムの一つである "Parallel Bit Count" をPL/pgSQLで実装する例です。

CREATE OR REPLACE FUNCTION popcount_int(n BIGINT)
RETURNS INTEGER AS $$
BEGIN
    IF n IS NULL THEN
        RETURN NULL;
    END IF;

    -- Hackers Delight 参照 (Modified for PostgreSQL bigint)
    -- 64-bit integer
    n := n - ((n >> 1) & 6148914691236517205::bigint); -- 0x5555555555555555
    n := (n & 3681400539650050867::bigint) + ((n >> 2) & 3681400539650050867::bigint); -- 0x3333333333333333
    n := (n + (n >> 4)) & 2305843009213693951::bigint; -- 0x0F0F0F0F0F0F0F0F
    RETURN CAST((n * 72340172838076673::bigint) >> 56 AS INTEGER); -- 0x0101010101010101
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用例
SELECT popcount_int(10::BIGINT); -- 10 (decimal) = B'1010' -> 結果: 2
SELECT popcount_int(255::BIGINT); -- 255 (decimal) = B'11111111' -> 結果: 8
SELECT popcount_int(9223372036854775807::BIGINT); -- 63 (bigintの最大値、全て1)
SELECT popcount_int(NULL::BIGINT); -- 結果: NULL

注意点:

このアルゴリズムはPL/pgSQLで記述できますが、C言語で記述された拡張関数の方がはるかに高速です。
これらの定数は64ビット整数用に計算されたものです。32ビット整数用には異なる定数が必要になります。

PostgreSQL拡張機能の利用

もし独自のPL/pgSQL関数ではパフォーマンスが不十分な場合、既存のPostgreSQL拡張機能を利用するか、C言語でカスタム拡張機能を作成することを検討できます。

C言語でカスタム関数を作成する: 最も高性能なソリューションが必要な場合、PostgreSQLのC言語インターフェース（pg_config --pgxsでビルド）を使用して、bit_count関数を直接実装できます。Popcountの実装には、CPUの組み込み命令（例: __builtin_popcountll for GCC/Clang, POPCNT instruction for x86-64）を利用することで、最高のパフォーマンスを得られます。
- これはPostgreSQLの内部構造に関する知識とC言語でのプログラミングスキルが必要となるため、より高度な方法です。

PostgreSQL 14以降の場合: 組み込みのbit_count()関数が最も推奨される方法です。最も効率的で、特別な設定も不要です。
PostgreSQL 13以前で一時的な解決策が必要な場合:
- 短いビット文字列/バイナリ文字列であれば、PL/pgSQLでループ処理をするカスタム関数が最も手軽です。
- integer/bigint型のビットカウントであれば、PL/pgSQLで実装したPopcountアルゴリズムも実用的です。
PostgreSQL 13以前で高性能な解決策が必要な場合:
- 既存のC言語で書かれたPostgreSQL拡張機能を探し、インストールするのが良い選択肢です。
- 最終手段として、C言語で独自の拡張機能を作成します。