Dynamic Table 增量刷新支持的函数-实时数仓 Hologres(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;