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

文法

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 及以上版本支援。

• 若重新整理模式指定為 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;

文法

SELECT [column_list],
  {lead | lag}(<expr>) OVER (
    PARTITION BY col1[, col2...]
    ORDER BY col1 [asc|desc] [nulls first|nulls last][, col2 ...]) AS <alias>
FROM table_name;

參數

• expr：取值列。該列必須同時出現在 SELECT 列表中，否則重新整理時報錯，詳情請參見下方使用限制。

• PARTITION BY：必選，用於分組的列，支援多列。

• ORDER BY：必選，用於排序的列，支援多列排序、asc/desc、NULLS FIRST/NULLS LAST。

使用限制

• 僅 Hologres V5.0 及以上版本支援。

• PARTITION BY 和 ORDER BY 均為必選。

• 僅支援單參數形式 lead(expr) 和 lag(expr)，即位移量固定為 1，不支援 lead(expr, offset) 和 lead(expr, offset, default)。

• lead 和 lag 的入參列必須出現在 SELECT 列表中。例如 SELECT id, k, lead(price) OVER (...) 會在重新整理時失敗，需改寫為 SELECT id, k, price, lead(price) OVER (...)。

• PARTITION BY 與 ORDER BY 的列不支援浮點類型（float、float4、float8、real、double precision）。如需按小數排序，請使用 numeric 或 decimal 類型。可用作分組或排序鍵的類型包括：int、bigint、smallint、numeric/decimal、text/varchar、date、timestamp、timestamptz、bool。

• 建議 ORDER BY 列在每個分組內取值唯一。當同一分組記憶體在多行排序鍵相同（含多個 NULL）時，增量重新整理使用內部行序號打破並列，與全量重新整理、普通 OLAP 查詢的並列處理依據不同，結果可能不一致。兩者在 SQL 語義上均為合法解，如需結果確定，請在 ORDER BY 中追加唯一列作為並列裁決列。

• 不支援 IGNORE NULLS 和 RESPECT NULLS 文法。

• 首次重新整理需要構建全量 state，耗時可能較長。

• 增量重新整理的耗時主要由本次變更觸碰到的分組佔比決定，而非變更行數。變更集中在少量分組時增量優勢明顯；如果每次變更鋪滿全部分組，建議使用全量重新整理。此外，增量重新整理會額外維護 state 表，儲存開銷顯著高於全量重新整理，請納入容量評估。

Dynamic Table 使用樣本

以下樣本計算每個使用者相鄰兩次行為的金額變化。

-- 建立源表：使用者行為明細
CREATE TABLE user_events (
  event_id bigint,
  user_id  text,
  event_ts timestamptz,
  amount   numeric(10,2)
);

INSERT INTO user_events VALUES
  (1, 'u1', '2026-08-20 10:00:00+08', 100.00),
  (2, 'u1', '2026-08-20 11:00:00+08', 150.00),
  (3, 'u1', '2026-08-20 12:00:00+08', 120.00),
  (4, 'u2', '2026-08-20 09:00:00+08', 80.00),
  (5, 'u2', '2026-08-20 10:30:00+08', 95.00);

-- 建立Dynamic Table，注意入參列amount必須出現在SELECT列表中
CREATE DYNAMIC TABLE user_amount_diff
WITH (
  freshness = '5 minutes',
  auto_refresh_mode = 'incremental'
) AS
SELECT
  event_id,
  user_id,
  event_ts,
  amount,
  lag(amount) OVER (PARTITION BY user_id ORDER BY event_ts) AS prev_amount,
  amount - COALESCE(lag(amount) OVER (PARTITION BY user_id ORDER BY event_ts), 0) AS diff
FROM user_events;

SELECT * FROM user_amount_diff ORDER BY user_id, event_ts;

查詢結果如下。

 event_id | user_id |        event_ts        | amount | prev_amount |  diff
----------+---------+------------------------+--------+-------------+--------
        1 | u1      | 2026-08-20 10:00:00+08 | 100.00 |             | 100.00
        2 | u1      | 2026-08-20 11:00:00+08 | 150.00 |      100.00 |  50.00
        3 | u1      | 2026-08-20 12:00:00+08 | 120.00 |      150.00 | -30.00
        4 | u2      | 2026-08-20 09:00:00+08 |  80.00 |             |  80.00
        5 | u2      | 2026-08-20 10:30:00+08 |  95.00 |       80.00 |  15.00
(5 rows)

文法

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

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;