Supported functions for incremental refresh

Syntax

SELECT [column_list]
FROM (
   SELECT [column_list],
     ROW_NUMBER() OVER ([PARTITION BY partition_column[, ...]]
       ORDER BY order_column [ASC|DESC][, ...]) AS rownum
   FROM table_name)
WHERE rownum <= N [AND conditions]

Arguments:

• PARTITION BY partition_column: Optional. Groups rows into partitions before ranking.

• ORDER BY order_column [ASC|DESC]: Required. Defines the ranking order within each partition.

• rownum <= N: Required. Retains only the top N rows from each partition.

Usage notes

• Supported in Hologres V4.2 and later.

• Set refresh_mode to auto to let Hologres automatically derive incremental mode for the Dynamic Table.

Example

The following example creates a Dynamic Table that maintains the top 3 orders by amount for each product, refreshed incrementally every 5 minutes.

-- Create the source 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 the Dynamic Table for TopN
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;

-- Query results for a specific product
SELECT * FROM top3_orders WHERE product_id = 1;

Syntax

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;

Parameters

• expr: the column to read. This column must also appear in the SELECT list. Otherwise, the refresh fails. For more information, see Usage notes.

• PARTITION BY: required. The columns to partition by. Multiple columns are supported.

• ORDER BY: required. The columns to sort by. Multiple columns, ASC and DESC, and NULLS FIRST and NULLS LAST are supported.

Usage notes

• Supported in Hologres V5.0 and later.

• Both PARTITION BY and ORDER BY are required.

• Only the single-argument forms lead(expr) and lag(expr) are supported, which means the offset is always 1. lead(expr, offset) and lead(expr, offset, default) are not supported.

• The input column of lead or lag must appear in the SELECT list. For example, SELECT id, k, lead(price) OVER (...) fails at refresh time and must be rewritten as SELECT id, k, price, lead(price) OVER (...).

• PARTITION BY and ORDER BY columns cannot use floating-point types (float, float4, float8, real, or double precision). To sort by decimal values, use numeric or decimal. Supported key types are int, bigint, smallint, numeric or decimal, text or varchar, date, timestamp, timestamptz, and bool.

• Make the ORDER BY columns unique within each partition. If several rows in one partition share the same sort key, including multiple NULL values, incremental refresh breaks the tie by an internal row number, which differs from how full refresh and regular OLAP queries break ties, so the results may not match. Both results are valid under SQL semantics. For deterministic results, add a unique column to ORDER BY as a tiebreaker.

• IGNORE NULLS and RESPECT NULLS are not supported.

• The first refresh builds the full state and may take a long time.

• The duration of an incremental refresh depends mainly on the proportion of partitions touched by the change, not on the number of changed rows. Incremental refresh performs best when changes are concentrated in a few partitions. If every change spans all partitions, use full refresh instead. Incremental refresh also maintains an extra state table, whose storage overhead is significantly higher than that of full refresh. Include it in your capacity planning.

Example

The following example computes the amount change between two consecutive events for each user.

-- Create the source table for user event details.
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);

-- Create the Dynamic Table. The input column amount must appear in the SELECT list.
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;

The result is as follows.

 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)

Syntax

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

Arguments

Behavior

Writes new UID-to-integer mappings to the user mapping table. If a UID already exists in the table, the existing mapping is preserved and no duplicate is inserted.

Usage notes

• Supported in Hologres V4.1 and later.

• The user mapping table must have a primary key and exactly one Serial field (besides the primary key).

• Only a single-column primary key of the text type is supported.

Example

-- Create the source table
CREATE TABLE base_table(user_id text);
INSERT INTO base_table VALUES('a');

-- Create the user mapping table
CREATE TABLE uid_mapping(user_id text PRIMARY KEY, id serial);

-- Map UIDs to integers and write mappings to uid_mapping
SELECT user_id, hg_id_encoding_int4(user_id, 'uid_mapping') AS res
FROM base_table;

-- Query the mapping table to verify
-- user_id | id
-- --------+----
--   a     |  1

Syntax

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

Arguments

Returns

The value of expr1 from the row where expr2 is at its minimum (min_by) or maximum (max_by).

Usage notes

Supported in Hologres V4.0 and later.

Example

The following example creates a Dynamic Table that tracks each user's first and last events, based on event timestamps.

-- Create the source 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');

-- Create the Dynamic Table
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;

Syntax

string_agg([distinct] column_expr, const_expr)

Arguments

Usage notes

• The ORDER BY clause is not supported.

• string_agg([distinct]) syntax requires Hologres V3.1.10 or later.

• Basic string_agg is supported in Hologres V3.1 and later.

Example

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;

Syntax

array_agg([distinct] expr)

Arguments

Usage notes

• The ORDER BY clause is not supported.

• array_agg([distinct]) syntax requires Hologres V3.1.10 or later.

• Basic array_agg is supported in Hologres V3.1 and later.

Example

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;

Syntax

any_value(expr)

Arguments

Usage notes

• Supported in Hologres V3.1.5 and later.

• expr supports only the int and binary types.

• The return value is non-deterministic.

Example

CREATE DYNAMIC TABLE dt_t0
WITH (
  freshness = '1 minutes',
  auto_refresh_mode = 'auto'
)
AS
SELECT a, any_value(c), sum(b) FROM t0 GROUP BY a;