幫助中心
首頁 Hologres 操作指南 資料加工與計算 Dynamic Table Dynamic Table使用指南 增量重新整理函數支援記錄

增量重新整理函數支援記錄

更新時間：
Copy as MD
產品

本文記錄 Dynamic Table 增量重新整理支援的函數，包括函數支援一覽表及 row_number/rank、hg_id_encoding、min_by/max_by 等函數的使用說明與樣本。

函數支援一覽表

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;
上一篇：DROP DYNAMIC TABLE下一篇：Dynamic Table查詢改寫