タイトルの通り、GROUP BY
でグループ化された各行セットの最初の行を選択したいと思います。
具体的には、次のようなpurchases
テーブルがあったとします。
SELECT * FROM purchases;
私の出力:
続いて ID|顧客|合計 ---+----------+------ 1|ジョー|5 2|サリー|3 3|ジョー|2 4|サリー|1
それぞれの顧客
が購入した最大の購入額(合計
)のid
を照会したいと思います。以下のような感じです。
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
期待される出力:。 となります。 FIRST(id) | お客様 | FIRST(total) ----------+----------+------------- 1|ジョー|5 2|サリー|3
PostgreSQLでは、この方法は一般的により簡単で速い**です(以下にパフォーマンスの最適化について説明します)。
SELECT DISTINCT ON (customer)
ID、顧客、合計
購買から
ORDER BY customer, total DESC, id;
。
あるいは、出力列の序列番号を使って、(明確ではないかもしれませんが)もっと短くすることもできます。
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
もし total
が NULL であれば (どちらでも構いませんが、既存のインデックスと一致させたいでしょう)。
...
ORDER BY customer, total DESC NULLS LAST, id;
###主要なポイント
- **`DISTINCT ON`**][1]は、標準(`SELECT`リスト全体に対する`DISTINCT`のみが定義されている)をPostgreSQLが拡張したものです。
- DISTINCT ON`句で任意の数の式を列挙し、結合された行の値は重複を定義します。[マニュアル:][2] 。
> 明らかに、2つの行が少なくとも1つの列の値が異なる場合、2つの行は異なるものとみなされます。
> 1つの列値が異なる場合、2つの行は異なるとみなされます。**この比較ではNull値は等しいとみなされます**。
太字で強調しています。
- DISTINCT ON "は "ORDER BY "と組み合わせることができます。先頭の表現は、先頭の `DISTINCT ON` の表現と同じ順序でなければなりません。また、`ORDER BY`に*付加的な*表現を加えることで、各グループのピアから特定の行を選ぶことができます。私は最後の項目として`id`を追加して、タイを崩しました。
*"最大の `total` を共有する各グループから、最小の `id` を持つ行を選ぶ "*。
グループごとに最初に決定されるソート順とは異なる方法で結果を並べるには、上記のクエリを別の `ORDER BY` を持つ外部クエリにネストすることができます。こんな感じです。
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- もし`total`がNULLであれば、おそらく*最大の非NULL値を持つ行が必要になります。デモのように **NULLS LAST`** を追加してください。詳細は以下の通りです。
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- SELECT`リスト**は、`DISTINCT ON`や`ORDER BY`の表現によって制約を受けることはありません。(上記の単純なケースでは必要ありません)。
- DISTINCT ON` や `ORDER BY` に含まれる式を含める必要はありません。
- SELECT`リストには、他のどんな式でも含めることができます。これは、サブクエリやアグリゲート/ウィンドウ関数を使用して、より複雑なクエリを置き換えるのに役立ちます。
- 私は Postgres バージョン 8.3 ~ 12 でテストしました。しかし、この機能は少なくともバージョン7.1以降に搭載されていますので、基本的には常に使用できます。
##インデックス
上記のクエリに対する*完璧な*インデックスは、3つの列すべてを一致した順序とソート順で含む[複数列インデックス][3]です。
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
特殊すぎるかもしれません。しかし、特定のクエリの読み取りパフォーマンスが重要な場合に使用してください。クエリで DESC NULLS LAST
を使用している場合は、インデックスでも同じものを使用して、ソート順を一致させ、インデックスが適用できるようにします。
クエリごとにカスタマイズされたインデックスを作成する前に、コストとベネフィットを比較検討してください。上記のインデックスの可能性は、データ配布に大きく依存します。 このインデックスは、事前にソートされたデータを配信するために使用されます。Postgres 9.2以降では、インデックスが基礎となるテーブルよりも小さい場合、クエリはインデックスのみのスキャンからも利益を得ることができます。ただし、インデックスは全体をスキャンしなければなりません。
customer
列のカーディナリティが高い)場合、これは非常に効率的です。ソートされた出力が必要であれば、なおさらです。顧客ごとの行数が増えれば増えるほど、メリットは小さくなります。work_mem
*]5があればよいでしょう。しかし、一般的には、work_mem
を 高すぎる ように設定すると、悪影響を及ぼす可能性があります。例外的に大きなクエリの場合には SET LOCAL
を検討してください。また、EXPLAIN ANALYZE
で必要な量を確認してください。ソートステップで「Disk:*」とあるのは、もっと必要だということです。customer
列のカーディナリティが低い)場合、loose index scan (別名 "skip scan")の方が(はるかに)効率的ですが、Postgres 12までは実装されていません。(Postgres 13ではインデックスオンリースキャンの実装が開発されています。ここ]8とここを参照してください)。私はここで簡単なベンチマークをしていましたが、それはもう古くなっています。私はそれを詳細なベンチマークはこの別の回答でに置き換えました。 [1]: https://www.postgresql.org/docs/current/sql-select.html#SQL-DISTINCT
WITH summary AS (
SELECT p.id,
p.customer,
p.total,
ROW_NUMBER() OVER(PARTITION BY p.customer
ORDER BY p.total DESC) AS rk
FROM PURCHASES p)
SELECT s.*
FROM summary s
WHERE s.rk = 1
しかし、タイを破るためのロジックを追加する必要があります。
SELECT MIN(x.id), -- change to MAX if you want the highest
x.customer,
x.total
FROM PURCHASES x
JOIN (SELECT p.customer,
MAX(total) AS max_total
FROM PURCHASES p
GROUP BY p.customer) y ON y.customer = x.customer
AND y.max_total = x.total
GROUP BY x.customer, x.total
このソリューションは、Erwin氏が指摘したように、SubQsの存在により、あまり効率的ではありません。
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;