PostgreSQL: `DISTINCT ON`、`GROUP BY`、`FIRST_VALUE`で各IDの最後の行をスマートに抽出

PostgreSQL で各 ID の最後の行を抽出する方法

このチュートリアルでは、PostgreSQL で 各 ID の最後の行 を効率的に抽出する方法について説明します。 複数の方法がありますが、それぞれ長所と短所があります。状況に応じて最適な方法を選択することが重要です。

方法 1: サブクエリを使用した ORDER BY

最も基本的な方法は、サブクエリ と ORDER BY 句を使用して、各 ID の最後の行を取得することです。 以下の例では、mytable テーブルから id 列と value 列の値を取得し、id 列でグループ化し、各グループの最後の行のみを表示する方法を示します。

SELECT id, value
FROM (
    SELECT id, value,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY id DESC) AS rn
    FROM mytable
) AS tmp
WHERE rn = 1;

説明:

1. 最初のサブクエリは、mytable テーブルから id と value 列の値を取得し、id 列でパーティション化された各グループ内の行を降順に並べ替えます。
2. ROW_NUMBER() ウィンドウ関数を使用して、各行に rn という名前の列を追加します。 この列には、各グループ内での行番号が格納されます。
3. 外部クエリは、rn が 1 である行のみを選択することで、各グループの最後の行のみを抽出します。

利点:

• シンプルで理解しやすい

欠点:

• 複数のサブクエリが必要になるため、非効率的な場合がある。
• 大規模なテーブルの場合、パフォーマンスが低下する可能性がある。

方法 2: CTE を使用した LAST_VALUE

PostgreSQL 10 以降では、LAST_VALUE ウィンドウ関数を使用して、各 ID の最後の行をより効率的に抽出することができます。 以下の例では、mytable テーブルから id 列と value 列の値を取得し、id 列でグループ化し、各グループの最後の行のみを表示する方法を示します。

WITH cte AS (
    SELECT id, value,
           LAST_VALUE(value) OVER (PARTITION BY id ORDER BY id DESC) AS last_value
    FROM mytable
)
SELECT *
FROM cte
WHERE value = last_value;

1. WITH 句を使用して、cte という名前の共用テーブル式 (CTE) を定義します。
2. LAST_VALUE ウィンドウ関数を使用して、各行に last_value という名前の列を追加します。 この列には、各グループ内の最後の行の value が格納されます。
3. 外部クエリは、value 列と last_value 列の値が一致する行のみを選択することで、各グループの最後の行のみを抽出します。

• 方法 1 よりも効率的
• サブクエリを使用しないため、可読性が高い

• PostgreSQL 10 以降でのみ使用可能

方法 3: 窓関数 row_number() を使った方法

SELECT id, value
FROM mytable
ORDER BY id DESC
WHERE row_number() OVER (PARTITION BY id ORDER BY id DESC) = 1;

1. mytable テーブルから id と value 列の値を取得し、id 列で降順に並べ替えます。

状況

このテーブルから、各 ID の最後の行 のみを取得したいと考えています。

SELECT id, value
FROM (
    SELECT id, value,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY id DESC) AS rn
    FROM mytable
) AS tmp
WHERE rn = 1;

WITH cte AS (
    SELECT id, value,
           LAST_VALUE(value) OVER (PARTITION BY id ORDER BY id DESC) AS last_value
    FROM mytable
)
SELECT *
FROM cte
WHERE value = last_value;

SELECT id, value
FROM mytable
ORDER BY id DESC
WHERE row_number() OVER (PARTITION BY id ORDER BY id DESC) = 1;

結果

上記のいずれの方法でも、以下の結果が得られます。

SELECT id, value
FROM mytable t1
WHERE value = (
    SELECT MAX(value)
    FROM mytable t2
    WHERE t2.id = t1.id
);

1. 外部クエリは、mytable テーブルから id と value 列の値を取得します。
2. 内側のサブクエリは、各 ID の最大 value を選択します。
3. 外部クエリは、value 列が内側のサブクエリで選択された最大 value に一致する行のみを抽出します。

• 方法 1 よりも非効率的な場合がある。

DISTINCT ON と ORDER BY

SELECT DISTINCT ON (id) id, value
FROM mytable
ORDER BY id DESC;

1. DISTINCT ON 句を使用して、id 列の値が異なる行のみを抽出します。
2. ORDER BY 句を使用して、id 列で降順に並べ替えます。

• DISTINCT ON 句は、PostgreSQL 8.4 以降でのみ使用可能

GROUP BY と MAX

SELECT id, MAX(value) AS last_value
FROM mytable
GROUP BY id;

1. GROUP BY 句を使用して、id 列でグループ化します。
2. MAX 集計関数を使用して、各グループの最大 value を選択します。

• value 列のすべての値を取得できない

窓関数 first_value() を使った方法

SELECT id, first_value(value) OVER (PARTITION BY id ORDER BY id ASC) AS first_value
FROM mytable;

1. 外部クエリは、すべての行を抽出します。

ARRAY_AGG と SUBSTRING

SELECT id, SUBSTRING(ARRAY_AGG(value ORDER BY value DESC) FOR 1) AS last_value
FROM mytable
GROUP BY id;

1. ARRAY_AGG 集計関数を使用して、value 列の値を降順に並べた配列を作成します。
2. SUBSTRING 関数を使用して、配列から最後の要素 (つまり、各グループの最後の行の value) を抽出します。

• 非効率的な場合がある。

• PostgreSQL ドキュ