【SQL】DISTINCT ONの使い方まとめ【PostgreSQL】

はじめに

PostgreSQLのDISTINCT ONについてまとめます。
標準SQLには無い句なので、他のDBMS（MySQL, Oralceなど）では使えない点に注意してください。

非標準句
DISTINCT ON ( ... )は標準SQLの拡張です。

引用元：PostgreSQL 16.0文書 / SQLコマンド / SELECT

DISTINCT ONとは？

特徴

1. 指定したカラムの値が重複するレコード群を、その中のどれか1レコードに集約する
2. DISTINCT ONの処理順は、ORDER BYの後
3. 集約に使用していないカラムも出力できる

説明

1. 指定したカラムの値が重複するレコード群を、その中のどれか1レコードに集約する

例えば通販システムで、顧客ごとの最新の注文情報だけを抽出したいとします。
その場合は以下のようになります。

例1

SELECT DISTINCT ON ("顧客ID")
    "注文ID"
    , "注文日"
    , "顧客ID"
    , "顧客名"
    , "製品名"
FROM 
    "注文情報テーブル"
ORDER BY
    "顧客ID"
    , "注文日" DESC
;

このSQL文は、顧客ID昇順で並べ替えた後、さらに注文日降順で並べ替えたレコード群に対して集約を行っています。

まずDISTINCT ONの後の()の中に、集約条件に指定するカラムを記述します。
今回は顧客ごとに集約するため、"顧客ID"を集約条件に選んでいます。

ORDER BYでは集約条件に指定した"顧客ID"を最初に記述し、次にその中で最新を取るため"注文日"を記述しています。

2. DISTINCT ONの処理順は、ORDER BYの後

標準SQLのDISTINCTでは、集約した後ORDER BYでソートを実行します。
対してPostgreSQLのDISTINCT ONでは、ORDER BYでソートした結果に対して集約を行います。

SELECT DISTINCT ON (expression [, expression ...]) select_list ...

ここでexpressionは、すべての行で評価される任意の評価式です。 すべての式が等しくなる行の集合は、重複しているとみなされ、集合の最初の行だけが出力内に保持されます。 DISTINCTフィルタに掛けられる行の順序の一意性を保証できるよう十分な数の列で問い合わせを並べ替えない限り、出力される集合の「最初の行」は予想不可能であることに注意してください。
（DISTINCT ON処理は、ORDER BYによる並べ替えの後に行われます。）

引用元：PostgreSQL 16.0文書 / 第7章 問い合わせ / 7.3. 選択リスト

3. 集約に使用していないカラムも出力できる

標準SQLのDISTINCTは、集約条件に指定するカラム以外をSELECT句に書くことができません。
これは"顧客ID"ごとに"注文日"が最も新しいレコードに集約したとしても、他の取得したい注文情報である"顧客名"や"製品名"は抽出できないということです。
（サブクエリを使って複雑なSQL文を書けば可能ですが）

一方DISTINCT ONは、集約条件に指定しなかったカラムを含めて全カラムをSELECT句に書けます。
構文がシンプルになり、他人が見て理解しやすいSQL文で、取得したい注文情報を全て抽出できます。

検証

検証方法

DISTINCT ONなしとありのSELECT文を実行し結果を比較します。
抽出条件は「顧客ごとの最新の注文情報を取得する」というものです。

データは以下の記事でDB（sandbox）の各テーブルにINSERTしたものを使用します。
SQLを簡略化するため、3つのテーブル（orders, customers, products）を結合したVIEW（orderinfo_view）をFROM句に取ります。
orderinfo_viewの総レコード数は50です。
【SQL】SQL初心者が次のステップを目指すハンズオン【RDBMS】

環境

• Windows 11 Home 23H2
• PostgreSQL 16.2
• pgAdmin 8.2

DISTINCT ONなし

【SELECT文】

SELECT
    order_id            "注文ID"
    , order_date        "注文日"
    , customer_id       "顧客ID"
    , customer_name     "顧客名"
    , product_name      "製品名" 
FROM
    orderinfo_view      -- 注文情報ビュー
;

【結果】

・集約していないため、50レコード全て出力されています。
・ORDER BYを指定していないため順番は不定のはずですが、今回は注文ID（PK）でソートされた状態、つまり注文日が古い順で出力されています。

DISTINCT ONあり

【SELECT文】

SELECT DISTINCT ON (customer_id)
    order_id            "注文ID"
    , order_date        "注文日"
    , customer_id       "顧客ID"
    , customer_name     "顧客名"
    , product_name      "製品名" 
FROM
    orderinfo_view      -- 販売テーブル
ORDER BY
    customer_id
    , order_date DESC
;

【結果】

・集約した結果、customer_id（顧客ID）の件数である19レコード出力されています。
・ORDER BYの指定に従って、customer_id（顧客ID）昇順で並び替えたあとorder_date（注文日）降順、つまり新しい順で出力されています。
・その後DISTINCT ONに指定したcustomer_idごとに、先頭の1レコードに集約されて出力されています。

比較

customer_id 1, 2, 3に絞って比較してみます。
【SELECT文】

-- DISTINCT ONなし
SELECT
    order_id            "注文ID"
    , order_date        "注文日"
    , customer_id       "顧客ID"
    , customer_name     "顧客名"
    , product_name      "製品名" 
FROM
    orderinfo_view      -- 販売テーブル
WHERE
    customer_id IN (1, 2, 3)
;

-- DISTINCT ONあり
SELECT DISTINCT ON (customer_id)
    order_id            "注文ID"
    , order_date        "注文日"
    , customer_id       "顧客ID"
    , customer_name     "顧客名"
    , product_name      "製品名" 
FROM
    orderinfo_view      -- 販売テーブル
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id
    , order_date DESC
;

【結果】

・DISTINCT ONなしは、ORDER BYを指定していないため順序が不定です。
・DISTINCT ONありは、顧客ID1, 2, 3ともそれぞれのIDの中で最も注文日が新しい1レコードに集約されて出力されています。
・最新のレコードが取れていることから、ちゃんとORDER BYで注文日降順に並び替えた後、DISTINCT ONで集約されていることが分かります。

おわりに

以上、DISTINCT ONについてまとめました。
PostgreSQLを使用する際には便利な道具になります。
ぜひ活用してみてください。

参考記事

PostgreSQL 16.0文書 / SQLコマンド / SELECT

PostgreSQL 16.0文書 / 第7章 問い合わせ / 7.3. 選択リスト