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 サブクエリを使ってください。