BIT_OR and BIT_AND in PostgreSQL: Bitwise Aggregates over Flag Masks

Когда права, фичи и признаки упакованы в одно число, привычный SUM тут бессилен: складывать биты по столбцу бессмысленно, их нужно сводить логикой. Этим и заняты BIT_OR с BIT_AND — они проходят по целочисленному столбцу группы и схлопывают все значения побитовым ИЛИ или И за один проход. Одним запросом вы узнаёте, какие биты встретились хоть где-то, а какие выставлены сразу у всех — без разворота флагов в отдельные строки и без цикла на стороне приложения.

Что считают BIT_OR, BIT_AND и BIT_XOR

Возьмём привычный пример: права пользователя упакованы в число, где бит 1 — чтение, бит 2 — запись, бит 4 — удаление. Три агрегата отвечают на три разных вопроса по группе строк.

SELECT
    user_id,
    BIT_OR(flags)  AS any_bit_set,
    BIT_AND(flags) AS bits_in_every_row,
    BIT_XOR(flags) AS parity
FROM permissions
GROUP BY user_id;

• BIT_OR — это объединение: бит загорается, если он был хотя бы в одной строке. Так собирают итоговую маску всех прав.
• BIT_AND — пересечение: бит уцелеет, только если стоит во всех строках группы. Это «общий знаменатель» группы.
• BIT_XOR — чётность: бит равен 1, если в столбце он встретился нечётное число раз. Пригодится для контрольных сумм и поиска непарных значений.

На выходе — обычное целое, которое дальше читают оператором &.

Объединяем маски прав через BIT_OR

Самый ходовой сценарий — собрать в одну маску все права пользователя, разбросанные по множеству строк: по ролям, командам, ресурсам.

SELECT
    user_id,
    BIT_OR(flags) AS effective_mask
FROM permissions
GROUP BY user_id;

Маска на руках — теперь проверяем нужный бит тем же побитовым И. Например, кто умеет удалять (бит 4):

SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) = 4;

Грабли: пишите (mask & 4) = 4, а не mask & 4 = 4. В PostgreSQL приоритет = выше, чем у &, поэтому без скобок выражение разберётся как mask & (4 = 4) — и либо упадёт по типам, либо выдаст ерунду. Скобки вокруг битовой операции тут не косметика, а обязательное условие.

Тот же приём без изменений масштабируется на страны или отделы — скажем, какие возможности активны хоть у одного пользователя в стране:

SELECT
    u.country,
    BIT_OR(p.flags) AS country_features
FROM users u
JOIN permissions p ON p.user_id = u.id
GROUP BY u.country;

Находим общие биты через BIT_AND

Там, где BIT_OR спрашивает «у кого-нибудь?», BIT_AND спрашивает «у всех?». Допустим, нужно понять, какие права гарантированно есть у каждого сотрудника отдела — тот самый минимальный доступ, на который можно опереться:

SELECT
    e.dept,
    BIT_AND(p.flags) AS common_to_all
FROM employees e
JOIN permissions p ON p.user_id = e.id
GROUP BY e.dept;

Ноль в результате означает, что общего бита нет ни одного. Это удобно для проверки инвариантов: например, бит «активен» (значение 1) обязан стоять у всех записей пользователя, и нарушителей легко выловить.

SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_AND(flags) & 1) = 0;  -- somebody is missing the active bit

Моделирование флагов: практичные приёмы

Чтобы биты не превратились в магические числа, которые через полгода никто не расшифрует, держите их именованными и считайте маску явно:

-- bit layout: 1 = read, 2 = write, 4 = delete, 8 = export
SELECT
    user_id,
    BIT_OR(flags) AS mask,
    (BIT_OR(flags) & 1) <> 0 AS can_read,
    (BIT_OR(flags) & 2) <> 0 AS can_write,
    (BIT_OR(flags) & 4) <> 0 AS can_delete
FROM permissions
GROUP BY user_id;

Несколько ориентиров на каждый день:

• Один бит — один независимый признак. Взаимоисключающие состояния битами не кодируют: для них есть отдельный столбец-статус.
• 32-битного integer хватает на 31 безопасный флаг (старший бит уходит под знак); нужно больше — берите bigint.
• NULL агрегаты игнорируют, ровно как SUM и COUNT. Пустая группа даёт NULL.
• Если флаги лежат в типе bit(n), те же BIT_OR и BIT_AND работают и над ним, возвращая битовую строку.

Грабли: побитовые агрегаты не отменяют нормализацию. Как только понадобится выборка «найти всех с правом X», индекс по упакованному числу окажется бесполезен — планировщик не умеет искать по отдельному биту и уйдёт в полный скан. Под такие запросы либо заводите индекс по выражению ((flags & 4)), либо выносите права в отдельную таблицу.

MySQL и ClickHouse

BIT_OR, BIT_AND и BIT_XOR живут и в MySQL — синтаксис тот же, но проверку бита там чище писать через сравнение с нулём:

-- MySQL: users that can delete (bit 4)
SELECT user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) <> 0;

Отличия, о которых стоит помнить:

• В старых версиях MySQL BIT_AND по пустой группе возвращал «все единицы» (например, 18446744073709551615), а не NULL. Сверяйтесь с поведением своей версии.
• В ClickHouse функции зовут groupBitOr, groupBitAnd и groupBitXor — имена другие, суть та же.
• Везде результат остаётся целым: отдельные биты читают через & со скобками, не забывая про приоритет операторов.

Когда состояние упаковано в биты, эти три агрегата отвечают на вопросы «у кого-нибудь / у всех / нечётно» одним проходом по группе — точно, дёшево и без логики на стороне приложения. Освойте их один раз, и громоздкие развороты флагов в строки вам попросту перестанут быть нужны.