Dynamic Table 增量刷新支持的函数-实时数仓 Hologres(Hologres)-阿里云帮助中心

函数支持一览表

Dynamic Table 增量刷新支持基本的聚合函数：COUNT、SUM、MIN/MAX、COUNT DISTINCT，更多复杂函数的支持记录如下表所示。

函数名	函数说明	dynamic table使用示例	支持的版本
row_number / rank	窗口函数，用于实现TopN数据加工。支持在增量模式下使用row_number()或rank()函数对数据进行分组排序并筛选前N条记录。	参见Dynamic Table 使用示例	仅 Hologres V4.2 及以上版本支持
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类型

row_number / rank

从 Hologres V4.2 版本开始支持 row_number() 和 rank() 窗口函数，能够在 Dynamic Table 增量模式下实现 TopN 数据加工。支持通过 PARTITION BY 进行分组，ORDER BY 进行排序，并在外层筛选前 N 条记录。

语法

SELECT [column_list]
FROM (
   SELECT [column_list],
     ROW_NUMBER() OVER ([PARTITION BY col1[, col2...]]
       ORDER BY col1 [asc|desc][, col2 [asc|desc]...]) AS rownum
   FROM table_name)
WHERE rownum <= N [AND conditions]

参数说明：

PARTITION BY col1[, col2...]：可选，指定分组列。
ORDER BY col1 [asc|desc][, col2 [asc|desc]...]：必选，指定排序列及排序方向。
rownum <= N：必选，筛选前 N 条记录。

使用限制

仅 Hologres V4.2 及以上版本支持。
支持 row_number() 和 rank() 两个窗口函数。
不支持其他窗口函数（如 dense_rank、lag、lead 等）。
若刷新模式指定为 auto，会自动推导为 incremental 模式。

Dynamic Table 使用示例

CREATE TABLE orders (
  order_id bigint,
  product_id bigint,
  amount bigint
);

INSERT INTO orders
SELECT i, i % 100, (random() * 1000000)::bigint
FROM generate_series(1, 10000)i;

CREATE DYNAMIC TABLE top3_orders
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental'
) AS
SELECT order_id, product_id, amount
FROM (
   SELECT order_id, product_id, amount,
     ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount desc) AS rownum
   FROM orders)
WHERE rownum <= 3;

SELECT * FROM top3_orders WHERE product_id = 1;

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;