基于 Dynamic Table 与 RoaringBitmap 的任意长周期 UV 计算 - 实时数仓Hologres

对于大数据量且对 QPS、延迟有一定需求的业务，可通过 Hologres RoaringBitmap 结合 Dynamic Table 对数据增量计算，实现任意长周期的 UV 灵活计算。本文介绍 INT 与 TEXT 两种字段类型的实现方案。

方案介绍

本方案优点：Dynamic Table 使用增量刷新，每次只计算增量数据，计算性能好、资源占用低，可实现高 QPS 低延迟的 UV 计算；无需维护额外调度任务，由 Dynamic Table 自动刷新；查询灵活，支持任意周期范围。适用场景：大数据量（亿级）的任意长周期高 QPS 的 UV 计算。

根据业务场景与数据类型，RoaringBitmap 方案有两种实现方式：

方法 1：INT 字段类型的长周期 UV 计算 — 适用于 UID 为 INT 类型的精确去重，结合 Dynamic Table 可做任意标签/属性的交并差人群基数计算。
方法 2：TEXT 字段类型结合 Mapping 表 — 适用于 UID 为 TEXT 类型的场景，需结合 user mapping 表使用。

方法 1：INT 字段类型的 RB 长周期 UV 计算

适用场景：大数据量（亿级）的任意长周期高 QPS 的 UV 计算，且 UID 字段为 int 类型。

方案流程

创建用户明细表，存放业务所有维度的明细数据。
根据业务逻辑创建 Dynamic Table，对明细表增量加工，按基础维度 GROUP BY，将 uid 聚合为 RoaringBitmap 存入 Dynamic Table。
按查询维度查询 Dynamic Table，对 RoaringBitmap 字段做 RB_OR_AGG 去重后统计基数得 UV，计数得 PV，实现亚秒级查询。

准备基础数据

使用 RoaringBitmap 前需创建扩展：

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

准备用户明细表（建议按天分区），示例建表及属性设置如下：

DROP TABLE IF EXISTS ods_app_detail;
BEGIN;
CREATE TABLE IF NOT EXISTS ods_app_detail (
  uid int,
  country text,
  prov text,
  city text,
  ymd text NOT NULL
)
LOGICAL PARTITION BY LIST (ymd);
CALL set_table_property('ods_app_detail', 'orientation', 'column');
CALL set_table_property('ods_app_detail', 'bitmap_columns', 'country,prov,city,ymd');
CALL set_table_property('ods_app_detail', 'distribution_key', 'uid');
CALL set_table_property('ods_app_detail', 'clustering_key', 'ymd');
CALL set_table_property('ods_app_detail', 'event_time_column', 'ymd');
COMMIT;

创建 Dynamic Table 加工数据

创建 Dynamic Table 对明细表按维度聚合，使用 RB_BUILD_AGG 对 uid 计算，增量刷新，示例仅刷新最新分区，数据新鲜度 5 分钟：

CREATE DYNAMIC TABLE dt_dws_app_rb
LOGICAL PARTITION BY LIST(ymd)
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental',
  auto_refresh_partition_active_time = '1 days',
  partition_key_time_format = 'YYYYMMDD'
)
AS
SELECT
  RB_BUILD_AGG(uid) AS rb_uid,
  country,
  prov,
  city,
  ymd,
  COUNT(1) AS pv
FROM ods_app_detail
GROUP BY country, prov, city, ymd;

历史分区需手动 REFRESH，例如：

REFRESH DYNAMIC TABLE public.dt_dws_app_rb PARTITION(20251201) WITH (refresh_mode = 'full');

任意长周期 UV 查询

-- 查某天 UV、PV
SELECT
  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  sum(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;

-- 查一个月 UV、PV
SELECT
  RB_CARDINALITY(RB_OR_AGG(rb_uid)) AS uv,
  country,
  prov,
  city,
  sum(pv) AS pv
FROM dt_dws_app_rb
WHERE ymd >= '20251201' AND ymd <= '20251230'
GROUP BY country, prov, city;

方法 2：TEXT 字段类型结合 Mapping 表的 RB 长周期 UV 计算

适用场景：UID 为 text 类型，需结合 user mapping 表将 text 映射为 int 后使用 RoaringBitmap。可通过 hg_id_encoding 函数在 Dynamic Table 中自动写入与更新 mapping 表。

创建与更新用户 Mapping 表

BEGIN;
CREATE TABLE uid_mapping (
  uid text NOT NULL,
  uid_int32 serial,
  PRIMARY KEY (uid)
);
CALL set_table_property('uid_mapping', 'clustering_key', 'uid');
CALL set_table_property('uid_mapping', 'distribution_key', 'uid');
CALL set_table_property('uid_mapping', 'orientation', 'row');
COMMIT;

创建 Dynamic Table 加工数据

在 Dynamic Table 中通过 hg_id_encoding_int4(uid, 'uid_mapping') 将 text uid 映射为 int 并自动写入 mapping 表，再按维度聚合 RB：

CREATE DYNAMIC TABLE dt_dws_app_rb
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental',
  auto_refresh_partition_active_time = '1 days',
  partition_key_time_format = 'YYYYMMDD'
)
LOGICAL PARTITION BY LIST (ymd)
AS
SELECT
  country,
  prov,
  city,
  RB_BUILD_AGG(uid_int4) AS uid_rb,
  ymd
FROM (
  SELECT
    country,
    prov,
    city,
    hg_id_encoding_int4(uid, 'uid_mapping') AS uid_int4,
    ymd
  FROM ods_app_detail
) a
GROUP BY country, prov, city, ymd;

任意长周期 UV 查询

SELECT
  country,
  prov,
  city,
  RB_CARDINALITY(RB_OR_AGG(uid_rb)) AS uv
FROM dt_dws_app_rb
WHERE ymd = '20251223'
GROUP BY country, prov, city;