基於 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;