この記事の目的

本日立て続けにXのタイムラインに流れてきた次の二つの記事へのalternativeを、DuckDBで書いてみようと思う

結論

最初に結論だが、手法としては二つ目の記事の choplin 氏の提案と概念は同じであり、使う関数が異なるだけである。実行環境がDuckDBという特殊性もあるが。

Prerequisite

brew install duckdb
duckdb

-- DuckDBに `SERIAL` は存在しない
CREATE SEQUENCE id START 1;
CREATE TABLE history (
    id INTEGER DEFAULT nextval('id') PRIMARY KEY,
    user_id INTEGER NOT NULL,
    data TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO history (user_id, data, created_at) VALUES
(1, 'First entry of user1',  '2024-01-01 10:00:00'),
(1, 'Second entry of user1', '2024-01-02 09:30:00'),
(2, 'First entry of user2',  '2024-01-01 11:00:00'),
(2, 'Second entry of user2', '2024-01-02 08:45:00'),
(2, 'Third entry of user2',  '2024-01-03 07:15:00'),
(3, 'First entry of user3',  '2024-01-01 12:15:00');

解説

DuckDBでは unnest関数の引数に recursive:=true を指定して以下で書くことで展開できる。

max_by での書き方

SELECT 
  unnest(
    max_by(history, created_at, 1),
    recursive:=true
  )
FROM history
GROUP BY user_id;

┌───────┬─────────┬───────────────────────┬─────────────────────┐
│  id   │ user_id │         data          │     created_at      │
│ int32 │  int32  │        varchar        │      timestamp      │
├───────┼─────────┼───────────────────────┼─────────────────────┤
│     2 │       1 │ Second entry of user1 │ 2024-01-02 09:30:00 │
│     5 │       2 │ Third entry of user2  │ 2024-01-03 07:15:00 │
│     6 │       3 │ First entry of user3  │ 2024-01-01 12:15:00 │
└───────┴─────────┴───────────────────────┴─────────────────────┘

any_value での書き方

SELECT
  unnest(
    any_value(history ORDER BY created_at DESC),
    recursive:=true
  )
FROM history
GROUP BY user_id;

┌───────┬─────────┬───────────────────────┬─────────────────────┐
│  id   │ user_id │         data          │     created_at      │
│ int32 │  int32  │        varchar        │      timestamp      │
├───────┼─────────┼───────────────────────┼─────────────────────┤
│     2 │       1 │ Second entry of user1 │ 2024-01-02 09:30:00 │
│     5 │       2 │ Third entry of user2  │ 2024-01-03 07:15:00 │
│     6 │       3 │ First entry of user3  │ 2024-01-01 12:15:00 │
└───────┴─────────┴───────────────────────┴─────────────────────┘

PostgreSQLの例では複合型を使いさらに列に展開する場合 .* を使っているが、この記法は（PostgreSQLと同じSyntaxを採用しているDuckDBだが Composite Types は存在しないので）DuckDBではエラーとなる。

-- PostgreSQL
SELECT 
  (any_value(history ORDER BY created_at DESC)).*
FROM history 
GROUP BY user_id;

max_by と any_value の違い

実行計画は一か所だけ異なる。なぜだかよくわからんが any_value の実行計画には UNNEST が存在していないため、微妙に早い。結論としては any_value を使うのが良さそう。一方で max_by のメリットは、第三引数を2以上の指定で最新のn件を取得することができるという違いがある。

EXPLAIN ANALYZE SELECT
    unnest(
      max_by(history, created_at, 1),
      recursive:=1
    )
  FROM history
  GROUP BY user_id;
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT    unnest(     max_by(history, created_at, 1),     recursive:=1   ) FROM history GROUP BY user_id;
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.0008s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│          user_id          │
│            data           │
│         created_at        │
│                           │
│           3 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           UNNEST          │
│    ────────────────────   │
│           3 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│   PERFECT_HASH_GROUP_BY   │
│    ────────────────────   │
│         Groups: #0        │
│                           │
│        Aggregates:        │
│     max_by(#1, #2, #3)    │
│                           │
│           3 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│          user_id          │
│  struct_pack(id, user_id, │
│      data, created_at)    │
│         created_at        │
│             1             │
│                           │
│           6 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│          history          │
│                           │
│        Projections:       │
│          user_id          │
│             id            │
│            data           │
│         created_at        │
│                           │
│           6 Rows          │
│          (0.00s)          │
└───────────────────────────┘

EXPLAIN ANALYZE SELECT
    unnest(
      any_value(history ORDER BY created_at DESC),
      recursive:=1
    )
  FROM history
  GROUP BY user_id;
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││    Query Profiling Information    ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT   unnest(     any_value(history ORDER BY created_at DESC),     recursive:=1   ) FROM history GROUP BY user_id;
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││              Total Time: 0.0006s             ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│           QUERY           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      EXPLAIN_ANALYZE      │
│    ────────────────────   │
│           0 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│          user_id          │
│            data           │
│         created_at        │
│                           │
│           3 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│   PERFECT_HASH_GROUP_BY   │
│    ────────────────────   │
│         Groups: #0        │
│                           │
│        Aggregates:        │
│      arg_min(#1, #2)      │
│                           │
│           3 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│          user_id          │
│  struct_pack(id, user_id, │
│      data, created_at)    │
│ create_sort_key(created_at│
│    , 'DESC NULLS LAST')   │
│                           │
│           6 Rows          │
│          (0.00s)          │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         TABLE_SCAN        │
│    ────────────────────   │
│          history          │
│                           │
│        Projections:       │
│          user_id          │
│             id            │
│            data           │
│         created_at        │
│                           │
│           6 Rows          │
│          (0.00s)          │
└───────────────────────────┘

経験を伴わない勝手な想像

PostgreSQLのテーブルにて、history系のテーブルでは業務ドメイン次第だがuuid型の user_id 列で cardinality が高いケースでは Hash index が、created_at など実時間の順番通り挿入される列は BRIN index などが効くのではないか、と勝手に想像してみた。

その他

PostgreSQLでは「DISTINCT ON（PostgreSQL拡張）」を使って書くことができるが、近年の処理系(Snowflake, BigQuery, DuckDB, etc.) では qualify句 でwindow関数の指定ができるのでよく目にするようになってきている。ただしこいつは標準SQLではないので移植性が低いことに注意。

SELECT *
FROM history
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) = 1;

以上