Dynamic Table 增量重新整理支援的函數 - Hologres

函數支援一覽表

Dynamic Table 增量重新整理支援基本的彙總函式：COUNT、SUM、MIN/MAX、COUNT DISTINCT，更多複雜函數的支援記錄如下表所示。

函數名	函數說明	dynamic table使用樣本	支援的版本
hg_id_encoding_int32 / hg_id_encoding_int64	將txet類型的UID欄位對應成int/bigint類型，每次調用函數時，會自動對應資料寫入與更新user mapping表，通常使用在計算長UV情境中，使用者UID為text欄位，映射成int類型，方便進行rb計算。	參見Hologres Dynamic Table任意長周期UV計算方案	僅 Hologres V4.1 及以上版本支援該函數
min_by / max_by	min/max_by用於比較expr2列的最大/最小值，給出對應的expr1列的值 `min/max_by(expr1, expr2)` 參數說明：expr2用於比較大小的列，expr1展示的對應列傳回值說明：expr2最大/最小行對應的expr1的值	參見Dynamic Table 使用樣本	僅 Hologres V4.0 及以上版本支援
RB_BUILD_AGG	`RB_BUILD_AGG(<column>)` 說明：column的參數類型支援int32和int64，詳細使用見文檔RoaringBitmap函數	參見Hologres Dynamic Table任意長周期UV計算方案	Hologres V3.1 及以上版本
string_agg	`string_agg([distinct] column_expr, const_expr)` 說明：參數類型：column_expr需為text/char/varchar類型，const_expr需為text類型的常量不支援使用order by文法從 Hologres V3.1.10 版本開始支援string_agg([distinct]	`CREATE DYNAMIC TABLE string_agg_test_dt WITH ( freshness = '3 minutes', refresh_mode = 'incremental') as SELECT day, string_agg(gameversion, ',') AS gameversion_list FROM base_table group by day;`	Hologres V3.1 及以上版本從 Hologres V3.1.10 版本開始支援string_agg([distinct]
array_agg	`array_agg([distinct] expr)` 說明： expr參數類型：支援bool類型、所有數字類型、text類型、bytea類型不支援使用order by文法從 Hologres V3.1.10 版本開始支援string_agg([distinct]	`CREATE DYNAMIC TABLE array_agg_test_dt WITH ( freshness = '3 minutes', refresh_mode = 'incremental') as SELECT day, array_agg(gameversion) AS gameversion_list FROM base_table group by day;`	Hologres V3.1 及以上版本從 Hologres V3.1.10 版本開始支援string_agg([distinct]
any_value	在包含group by的彙總查詢中，從每個彙總分組中隨機播放某行的結果返回，結果不確定	`CREATE DYNAMIC TABLE dt_t0 WITH ( -- dynamic table的屬性 freshness = '1 minutes', auto_refresh_mode = 'auto' ) AS select a,any_value(c),sum(b) from t0 group by a;`	Hologres V3.1.5 及以上版本支援 any_value的輸入參數僅支援int和binary類型

hg_id_encoding_int32 / hg_id_encoding_int64

從 Hologres V4.1 版本開始支援 hg_id_encoding_int32 / hg_id_encoding_int64，可將 text 類型的 uid 欄位對應成 int32/int64，自動將資料寫入 user_mapping 表，與 Dynamic Table 增量重新整理及 RoaringBitmap 結合可實現長周期 UV 計算。詳情可參考使用者行為分析最佳實務文檔。

文法

hg_id_encoding_int4(<user_id>, '<mapping_tablename>')
hg_id_encoding_int8(<user_id>, '<mapping_tablename>')

參數說明：

第一個參數：text 類型的 uid 列。
第二個參數：user_mapping 的表名，需提前建立 user mapping 表，將 text 類型的 uid 映射成 int 類型。

使用限制

user_mapping 必須有主鍵，且主鍵之外只有一個 Serial 欄位；目前僅支援主鍵為 text 類型且為單列主鍵。
第一個參數僅支援 text 類型的 uid 欄位，不支援 NULL 值，否則函數執行報錯。
調用函數時會自動將 mapping 資料寫入 user_mapping 表；若 uid 已存在則忽略，新資料則新增。
僅 Hologres V4.1 及以上版本支援。

使用樣本

CREATE TABLE base_table(user_id text);
INSERT INTO base_table VALUES('a');

-- 建立 user_mapping 表
CREATE TABLE uid_mapping(user_id text PRIMARY KEY, id serial);

-- 將 base 表的 uid 經 hg_id_encoding_int4 映射後自動寫入 mapping 表
SELECT user_id, hg_id_encoding_int4(user_id, 'uid_mapping') AS res FROM base_table;

-- 查詢 mapping 表
-- user_id | id
-- --------+----
--   a     |  1

min_by / max_by

min_by / max_by 用於按 expr2 列取最小/最大值，返回對應的 expr1 列的值。

min_by(expr1, expr2)
max_by(expr1, expr2)

參數說明：expr2 為用於比較大小的列，expr1 為要展示的對應列。傳回值：expr2 最小/最大行對應的 expr1 的值。使用限制：僅 Hologres V4.0 及以上版本支援。

Dynamic Table 使用樣本

DROP TABLE IF EXISTS detail;
CREATE TABLE detail (
  userid       text,
  event_id     text,
  create_time  timestamptz
);

INSERT INTO detail(userid, event_id, create_time) VALUES
  ('user_1', 'e1', '2024-12-20 10:00:00+08'),
  ('user_1', 'e2', '2024-12-20 11:30:00+08'),
  ('user_1', 'e3', '2024-12-21 09:15:00+08'),
  ('user_2', 'e4', '2024-12-20 08:05:00+08'),
  ('user_2', 'e5', '2024-12-22 14:20:00+08'),
  ('user_3', 'e6', '2024-12-21 16:45:00+08');

DROP TABLE IF EXISTS detail_user_first_last_event;

CREATE DYNAMIC TABLE detail_user_first_last_event
WITH (
  auto_refresh_mode = 'incremental',
  computing_resource = 'local',
  freshness = '3 minutes'
)
AS
SELECT
  userid,
  min_by(event_id, create_time) AS first_event_id,
  max_by(event_id, create_time) AS last_event_id,
  date_trunc('day', max(create_time))::date AS dt
FROM detail
GROUP BY userid;