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.

• Only ROW_NUMBER() and RANK() are supported. Other window functions—such as DENSE_RANK, LAG, and LEAD—are not supported.

• 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

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;