Menu

PostgreSQL の DISTINCT ON: グループごとに 1 行を取得する

SELECT DISTINCT ON (key) ... ORDER BY key, sort は、顧客ごとの最新の注文のように、各グループの最初の行を残します。PostgreSQL だけの機能です。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

SELECT DISTINCT ON (expr) ... は、expr の異なる値ごとに 1 行を返します。返るのは、ORDER BY の順でそのグループの最初に来る行です。まずグループのキーで並べ、次に「最初」を決める列で並べます。顧客ごとの最新の注文なら ORDER BY customer, created_at DESC です。

3 行が返ります。asha は注文 2、ben は 4、chen は 5 で、それぞれの顧客の最新の注文です。DISTINCT ON は PostgreSQL の拡張機能です。ほかのデータベースでは、後で紹介するウィンドウ関数の形が必要です。

どの行を残すかは ORDER BY で決まる

PostgreSQL は行を並べ替え、DISTINCT ON の値が変わるたびに最初の行を残します。つまり次のようになります。

  • ORDER BY は DISTINCT ON の式で始める必要があります。そうしないと PostgreSQL は "SELECT DISTINCT ON expressions must match initial ORDER BY expressions" というエラーを出します。
  • それらの式のあとに ORDER BY の列がないと、どの行が残るかは予測できず、実行のたびに変わることもあります。

created_at DESC を total DESC に変えると、同じクエリが顧客ごとの最大の注文を返します。

今度は asha の行が注文 1 (40)、ben の行が注文 3 (60) になります。

同点を意図どおりに決める

並べ替えの列で 2 つの行が同点だと、どちらが残るかはわかりません。主キーのような一意の列を最後の並べ替えキーに加えると、毎回同じ結果になります。

行 2 と行 3 はどちらも 95 点です。最後のキーを id にすると、行 2 が残ります。

複数の列でグループ化する

式を複数並べると、組み合わせごとに 1 行になります。

センサーと種類の組ごとに最新の測定値が残ります: s1 humidity 40、s1 temp 22、s2 temp 19。

ROW_NUMBER を使う移植できる書き方

ROW_NUMBER() はパーティションごとに行に番号を振ります。番号 1 を残すと DISTINCT ON と同じ結果になり、MySQL 8、SQL Server、SQLite でも動きます。

SELECT customer, id, total, created_at
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

グループごとの上位 n 件 (rn <= 3) が必要なときや、クエリをほかのデータベースで動かす必要があるときは、この形を使ってください。PARTITION BY は ウィンドウ関数のページ で説明しています。

速くする

DISTINCT ON は入力を並べ替えるので、大きなテーブルでは ORDER BY に合ったインデックスを用意します。

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

こうすると PostgreSQL は並べ替えずに、行を順番どおりに読めます。グループが少なく、グループごとの行が多い場合は、同じインデックスから顧客ごとに LIMIT 1 を取る LATERAL の JOIN のほうが、読む行が少なくて済むことが多いです。LATERAL は JOIN のページ で紹介しています。自分のデータで EXPLAIN ANALYZE を使って両方を比べてください。

よくある質問

DISTINCT と DISTINCT ON の違いは何ですか?

DISTINCT は、選択したすべての列が同じ行を取り除きます。DISTINCT ON (a) は a の値ごとに 1 行を残し、ほかの列はそのグループで並べ替えの先頭に来た行の値になります。

DISTINCT ON は標準 SQL ですか?

いいえ。PostgreSQL の拡張機能なので、MySQL や SQL Server では動きません。移植できる書き方は、サブクエリで ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) を使い、行番号 1 に絞る方法です。

"SELECT DISTINCT ON expressions must match initial ORDER BY expressions" というエラーが出るのはなぜですか?

ORDER BY は、DISTINCT ON と同じ式で同じ順番に始まる必要があります。ORDER BY created_at DESC だけではなく、DISTINCT ON (customer) ... ORDER BY customer, created_at DESC と書いてください。

DISTINCT ON でグループごとの上位 3 行を取得できますか?

できません。DISTINCT ON が返すのはグループごとにちょうど 1 行です。上位 n 件なら、ROW_NUMBER() で行に番号を振って rn <= 3 を残すか、LIMIT 3 付きの LATERAL サブクエリを使ってください。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める