Hologres:Fungsi yang Didukung untuk Incremental Refresh

Sintaks

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]

Argumen:

• PARTITION BY partition_column: Opsional. Mengelompokkan baris ke dalam partisi sebelum diberi peringkat.

• ORDER BY order_column [ASC|DESC]: Wajib. Menentukan urutan peringkat dalam setiap partisi.

• rownum <= N: Wajib. Mempertahankan hanya N baris teratas dari setiap partisi.

Catatan penggunaan

• Didukung di Hologres V4.2 dan versi lebih baru.

• Atur refresh_mode ke auto agar Hologres secara otomatis menentukan mode incremental untuk Tabel Dinamis.

Contoh

Contoh berikut membuat Tabel Dinamis yang mempertahankan 3 pesanan teratas berdasarkan jumlah untuk setiap produk, dengan refresh incremental setiap 5 menit.

-- Buat tabel sumber
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;

-- Buat Tabel Dinamis untuk 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;

-- Kueri hasil untuk produk tertentu
SELECT * FROM top3_orders WHERE product_id = 1;

Sintaks

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;

Parameter

• expr: kolom yang akan dibaca. Kolom ini juga harus muncul dalam daftar SELECT. Jika tidak, refresh gagal. Untuk informasi lebih lanjut, lihat Catatan Penggunaan.

• PARTITION BY: wajib. Kolom untuk partisi. Mendukung beberapa kolom.

• ORDER BY: wajib. Kolom untuk pengurutan. Mendukung beberapa kolom, ASC dan DESC, serta NULLS FIRST dan NULLS LAST.

Catatan penggunaan

• Didukung di Hologres V5.0 dan versi lebih baru.

• PARTITION BY dan ORDER BY wajib digunakan.

• Hanya bentuk satu argumen lead(expr) dan lag(expr) yang didukung, artinya offset selalu 1. lead(expr, offset) dan lead(expr, offset, default) tidak didukung.

• Kolom input lead atau lag harus muncul dalam daftar SELECT. Misalnya, SELECT id, k, lead(price) OVER (...) gagal saat refresh dan harus ditulis ulang sebagai SELECT id, k, price, lead(price) OVER (...).

• Kolom PARTITION BY dan ORDER BY tidak boleh menggunakan tipe floating-point (float, float4, float8, real, atau double precision). Untuk mengurutkan berdasarkan nilai desimal, gunakan numeric atau decimal. Tipe kunci yang didukung adalah int, bigint, smallint, numeric atau decimal, text atau varchar, date, timestamp, timestamptz, dan bool.

• Buat kolom ORDER BY unik dalam setiap partisi. Jika beberapa baris dalam satu partisi memiliki kunci pengurutan yang sama, termasuk beberapa nilai NULL, incremental refresh memecahkan seri berdasarkan nomor baris internal, yang berbeda dari cara full refresh dan kueri OLAP biasa memecahkan seri, sehingga hasilnya mungkin tidak cocok. Kedua hasil tersebut valid menurut semantik SQL. Untuk hasil deterministik, tambahkan kolom unik ke ORDER BY sebagai pemecah seri.

• IGNORE NULLS dan RESPECT NULLS tidak didukung.

• Refresh pertama membangun status penuh dan mungkin memerlukan waktu lama.

• Durasi incremental refresh terutama bergantung pada proporsi partisi yang terkena perubahan, bukan pada jumlah baris yang berubah. Incremental refresh bekerja paling baik ketika perubahan terkonsentrasi di beberapa partisi. Jika setiap perubahan mencakup semua partisi, gunakan full refresh sebagai gantinya. Incremental refresh juga memelihara tabel status tambahan, yang overhead penyimpanannya jauh lebih tinggi daripada full refresh. Sertakan hal ini dalam perencanaan kapasitas Anda.

Contoh

Contoh berikut menghitung perubahan jumlah antara dua event berturut-turut untuk setiap pengguna.

-- Buat tabel sumber untuk detail event pengguna.
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);

-- Buat Tabel Dinamis. Kolom input amount harus muncul dalam daftar 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;

Hasilnya sebagai berikut.

 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)

Sintaks

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

Argumen

Perilaku

Menulis pemetaan UID-ke-bilangan bulat baru ke tabel pemetaan pengguna. Jika UID sudah ada di tabel, pemetaan yang ada dipertahankan dan tidak disisipkan duplikat.

Catatan penggunaan

• Didukung di Hologres V4.1 dan versi lebih baru.

• Tabel pemetaan pengguna harus memiliki primary key dan tepat satu bidang Serial (selain primary key).

• Hanya primary key satu kolom bertipe teks yang didukung.

Contoh

-- Buat tabel sumber
CREATE TABLE base_table(user_id text);
INSERT INTO base_table VALUES('a');

-- Buat tabel pemetaan pengguna
CREATE TABLE uid_mapping(user_id text PRIMARY KEY, id serial);

-- Petakan UID ke bilangan bulat dan tulis pemetaan ke uid_mapping
SELECT user_id, hg_id_encoding_int4(user_id, 'uid_mapping') AS res
FROM base_table;

-- Kueri tabel pemetaan untuk verifikasi
-- user_id | id
-- --------+----
--   a     |  1

Sintaks

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

Argumen

Pengembalian

Nilai expr1 dari baris tempat expr2 berada pada nilai minimum (min_by) atau maksimum (max_by).

Catatan penggunaan

Didukung di Hologres V4.0 dan versi lebih baru.

Contoh

Contoh berikut membuat Tabel Dinamis yang melacak event pertama dan terakhir setiap pengguna berdasarkan timestamp event.

-- Buat tabel sumber
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');

-- Buat Tabel Dinamis
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;

Sintaks

string_agg([distinct] column_expr, const_expr)

Argumen

Catatan penggunaan

• Klausa ORDER BY tidak didukung.

• Sintaks string_agg([distinct]) memerlukan Hologres V3.1.10 atau versi lebih baru.

• string_agg dasar didukung di Hologres V3.1 dan versi lebih baru.

Contoh

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;

Sintaks

array_agg([distinct] expr)

Argumen

Catatan penggunaan

• Klausa ORDER BY tidak didukung.

• Sintaks array_agg([distinct]) memerlukan Hologres V3.1.10 atau versi lebih baru.

• array_agg dasar didukung di Hologres V3.1 dan versi lebih baru.

Contoh

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;

Sintaks

any_value(expr)

Argumen

Catatan penggunaan

• Didukung di Hologres V3.1.5 dan versi lebih baru.

• expr hanya mendukung tipe int dan binary.

• Nilai kembali bersifat non-deterministik.

Contoh

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;