All Products
Search

This Product

    Hologres:bit_construct and bit_match

    Document Center

    Hologres:bit_construct and bit_match

    Last Updated:Mar 26, 2026

    bit_construct and bit_match are audience segmentation functions that identify users meeting a specific combination of conditions from a detail table — without multiple JOIN operations.

    Background

    In audience segmentation, a single user typically has multiple records, each satisfying a different condition. Finding users who satisfy a specific *combination* of conditions — for example, users who both added a product to their shopping cart and saved it to a favorites page — traditionally requires multiple rounds of conditional filtering and JOIN statements, resulting in complex SQL and high resource consumption.

    bit_construct and bit_match, available in Hologres V0.10 and later, replace that multi-JOIN pattern with a single aggregation pass.

    The following detail table illustrates the problem. User A satisfies both the click shopping cart and view favorites page conditions; user B does not.

    user action page
    A click shopping cart
    B click home page
    A view favorites page
    B click shopping cart
    A click favorites page

    Prerequisites

    Before you begin, ensure that you have:

    Enable the extension

    The flow_analysis extension is database-scoped. Run the following statement once per database. If you create a new database, run it again.

    CREATE EXTENSION flow_analysis;

    To uninstall the extension:

    DROP EXTENSION flow_analysis;
    A superuser must run these statements.

    bit_construct

    *Available in Hologres V0.10 and later.*

    Evaluates up to 32 boolean filter conditions per row and encodes the results as a 32-bit integer bitmap.

    Syntax

    bit_construct(
  a  := <bool_expr>,
  b  := <bool_expr>,
  ...,
  a6 := <bool_expr>
)

    Parameters

    Parameter Type Description
    a, b, ... z, a1–a6 bool Filter conditions. Up to 32 conditions are supported. Valid names are a through z and a1 through a6.

    Return value

    int — a bitmap representing the satisfied conditions.

    bit_match

    *Available in Hologres V0.10 and later.*

    Evaluates a logical expression against a bitmap produced by bit_construct to determine whether a user satisfies the required combination of conditions.

    Syntax

    bit_match('expression', bitmask)

    Parameters

    Parameter Type Description Example
    expression text A logical expression over the condition labels defined in bit_construct. Supports & (AND), | (OR), ! (NOT), and ^ (XOR). 'a&b'
    bitmask int The bitmap returned by bit_construct or aggregated by bit_or. —

    Usage example

    The following end-to-end example finds users who both added a product to their shopping cart and saved it to their favorites page.

    Step 1: Enable the extension

    CREATE EXTENSION flow_analysis;

    Step 2: Create a table and insert sample data

    create table ods_app_dwd(
event_time timestamptz,
uid bigint,
action text,
page text,
product_code text,
from_days int
);

insert into ods_app_dwd values('2021-04-03 10:01:30', 274649163, 'click', 'shopping cart', 'MDS', 1);
insert into ods_app_dwd values('2021-04-03 10:04:30', 274649163, 'view', 'favorites page', 'MDS', 4);
insert into ods_app_dwd values('2021-04-03 10:06:30', 274649165, 'click', 'shopping cart', 'MMS', 8);
insert into ods_app_dwd values('2021-04-03 10:09:30', 274649165, 'view', 'shopping cart', 'MDS', 10);

    Step 3: Query the target audience

    Two query patterns are available. Both use bit_construct to label each row's conditions, bit_or to aggregate per user, and bit_match to filter users who satisfy both conditions.

    bit_or performs a logical OR across all records for a given user: if any record satisfies condition a, the user is considered to meet condition a.

    Using a WHERE clause

    Filter rows with a WHERE clause before aggregation. The fewer rows the WHERE clause matches, the better the query performance.

    WITH tbl as (
SELECT uid, bit_or(bit_construct(
  a := (action='click' and page='shopping cart'),
  b := (action='view' and page='favorites page'))) as uid_mask
  FROM ods_app_dwd
WHERE event_time > '2021-04-03 10:00:00' AND event_time < '2021-04-04 10:00:00'
GROUP BY uid )
SELECT uid from tbl where bit_match('a&b', uid_mask);

    • bit_construct — condition a captures users who clicked the shopping cart; condition b captures users who viewed the favorites page.

    • bit_or — aggregates all records per user; a user satisfies a condition if at least one of their records matches it.

    • bit_match('a&b', ...) — returns only users who satisfy both condition a and condition b.

    Using a HAVING clause

    Use a HAVING clause to push the condition filter into the aggregation step directly.

    SELECT uid FROM (
    SELECT uid, bit_or(bit_construct(
      a := (action='click' AND page='shopping cart'),
      b := (action='view' AND page='favorites page'))) as uid_mask
    FROM ods_app_dwd
    WHERE event_time > '2021-04-03 10:00:00' AND event_time < '2021-04-04 10:00:00'
    GROUP BY uid
    HAVING bit_match('a&b', bit_or(bit_construct(
      a := (action='click' and page='shopping cart'),
      b := (action='view' and page='favorites page'))))
) t