Hologres:Supported functions for incremental refresh

Syntaxe

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 : facultatif. Regroupe les lignes en partitions avant le classement.

• ORDER BY order_column [ASC|DESC] : obligatoire. Définit l'ordre de classement au sein de chaque partition.

• rownum <= N : obligatoire. Conserve uniquement les N premières lignes de chaque partition.

Notes d'utilisation

• Prise en charge disponible à partir de Hologres V4.2.

• Définissez refresh_mode sur auto pour permettre à Hologres de déduire automatiquement le mode incrémentiel pour la table dynamique.

Exemple

L'exemple suivant crée une table dynamique qui conserve les trois commandes les plus élevées par montant pour chaque produit, avec une actualisation incrémentielle toutes les 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;

Syntaxe

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;

Paramètres

• expr : la colonne à lire. Cette colonne doit également figurer dans la liste SELECT. Sinon, l'actualisation échoue. Pour plus d'informations, consultez la section Notes d'utilisation.

• PARTITION BY : obligatoire. Colonnes utilisées pour le partitionnement. Plusieurs colonnes sont prises en charge.

• ORDER BY : obligatoire. Colonnes utilisées pour le tri. Plusieurs colonnes, ainsi que les options ASC, DESC, NULLS FIRST et NULLS LAST, sont prises en charge.

Notes d'utilisation

• Prise en charge disponible à partir de Hologres V5.0.

• Les clauses PARTITION BY et ORDER BY sont obligatoires.

• Seules les formes à un seul argument lead(expr) et lag(expr) sont prises en charge, ce qui signifie que l'offset est toujours égal à 1. Les formes lead(expr, offset) et lead(expr, offset, default) ne sont pas prises en charge.

• La colonne d'entrée de lead ou lag doit apparaître dans la liste SELECT. Par exemple, SELECT id, k, lead(price) OVER (...) échoue lors de l'actualisation et doit être réécrite sous la forme SELECT id, k, price, lead(price) OVER (...).

• Les colonnes PARTITION BY et ORDER BY ne peuvent pas utiliser de types à virgule flottante (float, float4, float8, real ou double precision). Pour trier des valeurs décimales, utilisez numeric ou decimal. Les types de clés pris en charge sont int, bigint, smallint, numeric ou decimal, text ou varchar, date, timestamp, timestamptz et bool.

• Assurez-vous que les colonnes ORDER BY sont uniques au sein de chaque partition. Si plusieurs lignes d'une même partition partagent la même clé de tri, y compris plusieurs valeurs NULL, l'actualisation incrémentielle départage les ex æquo à l'aide d'un numéro de ligne interne. Ce mécanisme diffère de celui utilisé lors d'une actualisation complète ou de requêtes OLAP classiques, ce qui peut entraîner des résultats divergents. Les deux résultats restent valides selon la sémantique SQL. Pour obtenir des résultats déterministes, ajoutez une colonne unique à ORDER BY comme critère de départage.

• Les options IGNORE NULLS et RESPECT NULLS ne sont pas prises en charge.

• La première actualisation construit l'état complet et peut prendre beaucoup de temps.

• La durée d'une actualisation incrémentielle dépend principalement de la proportion de partitions touchées par la modification, et non du nombre de lignes modifiées. L'actualisation incrémentielle offre les meilleures performances lorsque les modifications sont concentrées dans quelques partitions. Si chaque modification concerne toutes les partitions, privilégiez l'actualisation complète. Notez également que l'actualisation incrémentielle maintient une table d'état supplémentaire, dont l'empreinte de stockage est nettement supérieure à celle de l'actualisation complète. Intégrez cet aspect dans votre planification de capacité.

Exemple

L'exemple suivant calcule la variation de montant entre deux événements consécutifs pour chaque utilisateur.

-- 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;

Le résultat est le suivant.

 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)

Syntaxe

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

Arguments

Comportement

Écrit les nouveaux mappages UID-vers-entier dans la table de mappage utilisateur. Si un UID existe déjà dans la table, le mappage existant est conservé et aucun doublon n'est inséré.

Notes d'utilisation

• Prise en charge disponible à partir de Hologres V4.1.

• La table de mappage utilisateur doit posséder une clé primaire et exactement un champ Serial (en plus de la clé primaire).

• Seule une clé primaire à colonne unique de type texte est prise en charge.

Exemple

-- 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

Syntaxe

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

Arguments

Valeur de retour

Valeur de expr1 issue de la ligne où expr2 est minimal (min_by) ou maximal (max_by).

Notes d'utilisation

Prise en charge disponible à partir de Hologres V4.0.

Exemple

L'exemple suivant crée une table dynamique qui suit le premier et le dernier événement de chaque utilisateur, en se basant sur les horodatages des événements.

-- 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;

Syntaxe

string_agg([distinct] column_expr, const_expr)

Arguments

Notes d'utilisation

• La clause ORDER BY n'est pas prise en charge.

• La syntaxe string_agg([distinct]) nécessite Hologres V3.1.10 ou une version ultérieure.

• La fonction de base string_agg est prise en charge à partir de Hologres V3.1.

Exemple

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;

Syntaxe

array_agg([distinct] expr)

Arguments

Notes d'utilisation

• La clause ORDER BY n'est pas prise en charge.

• La syntaxe array_agg([distinct]) nécessite Hologres V3.1.10 ou une version ultérieure.

• La fonction de base array_agg est prise en charge à partir de Hologres V3.1.

Exemple

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;

Syntaxe

any_value(expr)

Arguments

Notes d'utilisation

• Prise en charge disponible à partir de Hologres V3.1.5.

• expr prend uniquement en charge les types int et binary.

• La valeur de retour est non déterministe.

Exemple

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;