PostgreSQLで最新の値を取得する

はじめに

最近PostgreSQLで最新の値を取得するためにいろいろと試行錯誤したので備忘録として残す。
バージョンはpostgresql 16.0です。
(出力結果は一部省略しています)

やりたいこと

以下のようなテーブルから学生IDごとに最新のvalueを取得したい。

CREATE TABLE IF NOT EXISTS
    exam (
        id UUID DEFAULT gen_random_uuid (),
        student_id UUID DEFAULT gen_random_uuid (),
        scoring_date TIMESTAMP NOT NULL,
        VALUE INT NOT NULL,
        CONSTRAINT pk_id PRIMARY KEY (id)
    );

データ準備、student_idに対して複数のvalueを入れたいのでこんな感じ。

WITH
    student AS (
        SELECT
            gen_random_uuid () AS id
        FROM
            GENERATE_SERIES(1, 100)
    )
INSERT INTO
    exam (student_id, scoring_date, VALUE)
SELECT
    student.id,
    '2020-01-01'::date + (INTERVAL '1 day') * ROUND(RANDOM() * 365 * 2) AS scoring_date,
    (RANDOM() * 100)::INT
FROM
    student,
    GENERATE_SERIES(1, 100);

IN句を使って取得する

学生IDでgroup by してMAXのscoring_dateを取ってきてその行を出力する。

SELECT
    student_id,
    scoring_date,
    VALUE
FROM
    exam
WHERE
    (student_id, scoring_date) IN (
        SELECT
            student_id,
            MAX(scoring_date)
        FROM
            exam
        GROUP BY
            student_id
    );

              student_id              |    scoring_date     | value
--------------------------------------+---------------------+-------
 914a47da-1974-4e23-a012-bc9397d2e073 | 2021-12-25 00:00:00 |    72
 571dbc51-a3b5-4aed-a502-a0c983af5203 | 2021-12-28 00:00:00 |    25
 5ecd29d0-dfad-459e-9836-55cb638a0944 | 2021-12-22 00:00:00 |    27
 564456fc-efef-4173-8231-dd357af6bf95 | 2021-12-25 00:00:00 |    56
 d6e4649b-81ad-4bd9-a039-c96821076334 | 2021-12-12 00:00:00 |    76
 d6e4649b-81ad-4bd9-a039-c96821076334 | 2021-12-12 00:00:00 |    15
 5801b998-fc08-4530-8788-d0c838f145ba | 2021-12-31 00:00:00 |     6
 3e9f7406-7c22-4c7f-80d4-b1b88e54d789 | 2021-12-29 00:00:00 |    49
 (106 rows)

可能性としては低いですが、MAX(scoring_date)に同じ値がある場合、複数行出力されてしまう。
ちゃんと最新の値が取れてる確認する。

SELECT
    student_id,
    scoring_date,
    VALUE
FROM
    exam
WHERE
    student_id = '914a47da-1974-4e23-a012-bc9397d2e073'
ORDER BY
    scoring_date DESC
LIMIT
    1;

              student_id              |    scoring_date     | value
--------------------------------------+---------------------+-------
 914a47da-1974-4e23-a012-bc9397d2e073 | 2021-12-25 00:00:00 |    72
(1 row)

JOINに書き換えても同様の結果を得ることができる。

SELECT
    exam.student_id,
    exam.scoring_date,
    exam.value
FROM
    exam
    INNER JOIN (
        SELECT
            student_id,
            MAX(scoring_date) AS latest
        FROM
            exam
        GROUP BY
            student_id
    ) ex ON exam.student_id = ex.student_id
    AND exam.scoring_date = ex.latest;

ウィンドウ関数で取得する

上記のSQLだとscoring_dateが重複した場合、複数行出てしまうのでウィンドウ関数を使う。

SELECT
    ex.student_id,
    ex.scoring_date,
    ex.VALUE
FROM
    (
        SELECT
            student_id,
            scoring_date,
            VALUE,
            ROW_NUMBER() OVER (
                PARTITION BY
                    student_id
                ORDER BY
                    scoring_date DESC
            ) AS RANK
        FROM
            exam
    ) ex
WHERE
    RANK = 1;

              student_id              |    scoring_date     | value
--------------------------------------+---------------------+-------
 01ce6220-5a58-47d0-90b7-d75d5082cc99 | 2021-12-29 00:00:00 |    73
 073c8350-0d9a-4e6d-9d45-32f9818547a2 | 2021-12-27 00:00:00 |    79
 07b70065-7751-4e9d-8a4d-1273316cdba9 | 2021-12-30 00:00:00 |    94
 10a2e4e1-3341-428b-aa86-dc26eb9ba43a | 2021-12-27 00:00:00 |    86
 12867241-74f1-436e-9176-60407666af48 | 2021-12-31 00:00:00 |    39
 169da2e2-c2df-44a1-869d-2ec97856ad1a | 2021-12-28 00:00:00 |    64
 184ee610-11fd-490c-9014-ad2de1f4e4d0 | 2021-12-22 00:00:00 |    76
 18bc5089-6a8b-4ad2-8899-788cb4398fd0 | 2021-12-29 00:00:00 |    70
 (100 rows)

DISTINCT ON を使う

標準SQLにはないらしいがpostgresqlにはDISTINC ON句があり、それを使っても最新値を取ることができる。結果はウィンドウ関数を使用した場合と同じになるが、SQLがシンプルで読みやすくなる。

SELECT DISTINCT
    ON (student_id) student_id,
    scoring_date,
    VALUE
FROM
    exam
ORDER BY
    student_id,
    scoring_date DESC;

              student_id              |    scoring_date     | value
--------------------------------------+---------------------+-------
 01ce6220-5a58-47d0-90b7-d75d5082cc99 | 2021-12-29 00:00:00 |    73
 073c8350-0d9a-4e6d-9d45-32f9818547a2 | 2021-12-27 00:00:00 |    79
 07b70065-7751-4e9d-8a4d-1273316cdba9 | 2021-12-30 00:00:00 |    94
 10a2e4e1-3341-428b-aa86-dc26eb9ba43a | 2021-12-27 00:00:00 |    86
 12867241-74f1-436e-9176-60407666af48 | 2021-12-31 00:00:00 |    39
 169da2e2-c2df-44a1-869d-2ec97856ad1a | 2021-12-28 00:00:00 |    64
 184ee610-11fd-490c-9014-ad2de1f4e4d0 | 2021-12-22 00:00:00 |    76
 18bc5089-6a8b-4ad2-8899-788cb4398fd0 | 2021-12-29 00:00:00 |    70
 (100 rows)

一応簡単に確認しますがちゃんと取れてそうです。

SELECT
    student_id,
    scoring_date,
    VALUE
FROM
    exam
WHERE
    student_id = '01ce6220-5a58-47d0-90b7-d75d5082cc99'
ORDER BY
    scoring_date DESC
LIMIT
    1;

              student_id              |    scoring_date     | value
--------------------------------------+---------------------+-------
 01ce6220-5a58-47d0-90b7-d75d5082cc99 | 2021-12-29 00:00:00 |    73
(1 row)