Hologres:増分更新でサポートされる関数

構文

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]

引数：

• PARTITION BY partition_column：任意。順位付けの前に、行をパーティションにグループ化します。

• ORDER BY order_column [ASC|DESC]：必須。各パーティション内の順位付けの順序を定義します。

• rownum <= N：必須。各パーティションから上位 N 行のみを保持します。

注意事項

• Hologres V4.2 以降でサポートされています。

• サポートされるのは ROW_NUMBER() と RANK() のみです。DENSE_RANK、LAG、LEAD など、その他のウィンドウ関数はサポートされていません。

• auto_refresh_mode を auto に設定すると、Hologres は動的テーブルの増分モードを自動的に導出します。

例

次の例では、商品ごとに注文金額の上位 3 件を維持し、5 分ごとに増分更新される動的テーブルを作成します。

-- ソーステーブルを作成します
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;

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

-- 特定の商品に対する結果をクエリします
SELECT * FROM top3_orders WHERE product_id = 1;

構文

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

引数

動作

新しい UID から整数へのマッピングをユーザーマッピングテーブルに書き込みます。UID がすでにテーブルに存在する場合は既存のマッピングが保持され、重複レコードは挿入されません。

注意事項

• Hologres V4.1 以降でサポートされています。

• ユーザーマッピングテーブルには、プライマリキーと、(プライマリキー以外に) ちょうど 1 つの Serial フィールドが必要です。

• text 型の単一列プライマリキーのみをサポートします。

例

-- ソーステーブルを作成します
CREATE TABLE base_table(user_id text);
INSERT INTO base_table VALUES('a');

-- ユーザーマッピングテーブルを作成します
CREATE TABLE uid_mapping(user_id text PRIMARY KEY, id serial);

-- UID を整数にマッピングし、uid_mapping に書き込みます
SELECT user_id, hg_id_encoding_int4(user_id, 'uid_mapping') AS res
FROM base_table;

-- マッピングテーブルをクエリして確認します
-- user_id | id
-- --------+----
--   a     |  1

構文

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

引数

戻り値

expr2 が最小 (min_by) または最大 (max_by) となる行の expr1 の値です。

注意事項

Hologres V4.0 以降でサポートされています。

例

次の例では、イベントのタイムスタンプに基づいて、各ユーザーの最初と最後のイベントを追跡する動的テーブルを作成します。

-- ソーステーブルを作成します
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;

構文

string_agg([distinct] column_expr, const_expr)

引数

注意事項

• ORDER BY 句はサポートされていません。

• string_agg([distinct]) 構文を使用するには Hologres V3.1.10 以降が必要です。

• 基本の string_agg は Hologres V3.1 以降でサポートされています。

例

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;

構文

array_agg([distinct] expr)

引数

注意事項

• ORDER BY 句はサポートされていません。

• array_agg([distinct]) 構文を使用するには Hologres V3.1.10 以降が必要です。

• 基本の array_agg は Hologres V3.1 以降でサポートされています。

例

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;

構文

any_value(expr)

引数

注意事項

• Hologres V3.1.5 以降でサポートされています。

• expr には、int 型と bytea 型のみ指定できます。

• 戻り値は非決定的です。

例

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;