でグループ化された各行セットの最初の行を選択したいと思いますGROUP BY。
purchases具体的には、次のようなテーブルがあるとします。
SELECT * FROM purchases;
私の出力:
| id | お客様 | 合計 |
|---|---|---|
| 1 | ジョー | 5 |
| 2 | サリー | 3 |
| 3 | ジョー | 2 |
| 4 | サリー | 1 |
各 によるid最大の購入 ( )の を照会したいと思います。次のようになります。totalcustomer
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
期待される出力:
| 最初(id) | お客様 | 最初(合計) |
|---|---|---|
| 1 | ジョー | 5 |
| 2 | サリー | 3 |
ベストアンサー1
DISTINCT ONPostgreSQLでは、これが最もシンプルで高速です。
(特定のワークロードのパフォーマンス最適化については、以下を参照してください。)
SELECT DISTINCT ON (customer)
id, customer, total
FROM purchases
ORDER BY customer, total DESC, id;
または、出力列の序数を使用して短く（明確でない場合は）：
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
可能であれば、以下を追加しtotalます。nullNULLS LAST
...
ORDER BY customer, total DESC NULLS LAST, id;
どちらの方法でも機能しますが、既存のインデックスと一致
db<>フィドルここ
主なポイント
DISTINCT ONPostgreSQL 標準の拡張であり、リストDISTINCT全体SELECTが定義されています。
句内に任意の数の式をリストしDISTINCT ON、結合された行の値によって重複が定義されます。マニュアル:
明らかに、2 つの行は、少なくとも 1 つの列の値が異なる場合は別個の行とみなされます。この比較では、NULL 値は等しいとみなされます。
太字は私による強調です。
DISTINCT ONは と組み合わせることができますORDER BY。 内の先頭の式はORDER BY内の式の集合に含まれている必要がありますDISTINCT ONが、それらの順序は自由に変更できます。例。
追加の式を追加して、各ピアのグループから特定の行を選択することができますORDER BY。または、マニュアルにはこう書いてある:
式
DISTINCT ONは、最も左のORDER BY式と一致する必要があります。ORDER BY句には通常、各グループ内の行の望ましい優先順位を決定する追加の式が含まれますDISTINCT ON。
id同点の場合の最後の項目として、 「各グループから最も高い値を共有する最小の行を選択する」を追加しました。
idtotal
グループごとに最初の結果を決定する並べ替え順序と異なる方法で結果を並べ替えるには、別の外部クエリを使用して上記のクエリをネストしますORDER BY。例。
totalが可能な場合はnull、おそらく最大の非 NULL 値を持つ行が必要になります。NULLS LAST示されているように追加します。参照:
リストSELECTはDISTINCT ONまたはの式によっていかなる形でも制約されませんORDER BY。
またはの式を含める必要はありません。
DISTINCT ONORDER BYリストには他の任意の式を含めることができます
SELECT。これは、複雑なサブクエリや集計/ウィンドウ関数を置き換えるのに役立ちます。
私は Postgres バージョン 8.3 ～ 16 でテストしました。ただし、この機能は少なくともバージョン 7.1 以降で存在していたため、基本的に常に存在していました。
索引
上記のクエリに最適なインデックスは複数列インデックス3 つの列すべてを一致する順序で、一致する並べ替え順序で展開します。
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
特化しすぎている可能性があります。ただし、特定のクエリの読み取りパフォーマンスが重要な場合は、これを使用してください。クエリにこれがある場合はDESC NULLS LAST、インデックスでも同じものを使用して、並べ替え順序が一致し、インデックスが完全に適用できるようにします。
効果 / パフォーマンスの最適化
各クエリに合わせてカスタマイズされたインデックスを作成する前に、コストと利点を比較検討してください。上記のインデックスの可能性は、データの分布に大きく依存します。
インデックスは、事前にソートされたデータを提供するために使用されます。Postgres 9.2以降では、クエリはインデックスのみのスキャンインデックスが基になるテーブルよりも小さい場合。ただし、インデックス全体をスキャンする必要があります。例。
顧客あたりの行数が少ない場合（列のカーディナリティが高い場合customer）、これは非常に効率的です。ソートされた出力が必要な場合はなおさらです。顧客あたりの行数が増えると、メリットは小さくなります。
理想的には、十分な行数が必要です。work_mem関連するソート手順を RAM で処理し、ディスクに書き込まないようにします。ただし、一般的に設定値が高work_mem すぎると悪影響が出る可能性があります。SET LOCAL非常に大きなクエリについて検討してください。 で必要な量を調べてください。ソート手順でEXPLAIN ANALYZE「ディスク:」と記載されている場合は、さらに容量が必要であることを示しています。
顧客あたりの行数が多い場合（列のカーディナリティが低い場合customer）、インデックススキップスキャンまたは「ルーズインデックススキャン」（はるかに）効率的です。しかし、Postgres 16 では実装されていません。何らかの方法で実装するための真剣な作業が何年も続いていますが、これまでのところ成功していません。ここそしてここ
現時点では、これに代わるより高速なクエリ手法があります。特に、固有の顧客を保持する別のテーブルがある場合、これが典型的な使用例です。ただし、そうでない場合も、次のようになります。
- PostgreSQL のテーブルで SELECT DISTINCT が予想よりも遅い
- GROUP BYクエリを最適化して、ユーザーごとに最新の行を取得します。
- グループワイズ最大クエリを最適化する
- 行ごとに最後のN行をクエリする