PostgreSQL の DISTINCT ON をさまざまな ORDER BY 句で正しく使用するにはどうすればよいですか?

PostgreSQL の DISTINCT ON および ORDER BY インタラクションを理解する

PostgreSQL の DISTINCT ON 句は、指定された式で同じ値を持つ行の各グループから最初の行を選択するように設計されています。 重要な点は、「最初の」行の選択が完全に ORDER BY 句に依存しているということです。 それらは整列する必要があります。

よくある間違いは、DISTINCT ON 式を含まない ORDER BY 句と一緒に DISTINCT ON 句を使用することです。これにより、データベースによる「最初の」行の選択が恣意的になるため、予測できない結果が生じます。

DISTINCT ON での順序の問題の修正

DISTINCT ON のフィールドが ORDER BY の先頭のフィールドと一致しない場合、エラーが発生します。これを修正するには、ORDER BY 句が DISTINCT ON と同じ式で始まるようにします。これにより、各グループ内の最初の行の一貫性のある予測可能な選択が保証されます。

「グループごとの最大 N」問題に対する代替アプローチ

各 address_id の最新の購入を購入日順に検索することが目的の場合、これは古典的な「グループごとの最大 N」クエリです。 ここでは 2 つの効率的なソリューションを紹介します:

一般的な SQL ソリューション:

このアプローチでは、サブクエリを使用して各 purchased_at の最大 address_id を見つけ、それを元のテーブルに結合して完全な行を取得します。

SELECT t1.* 
FROM purchases t1
JOIN (
    SELECT address_id, max(purchased_at) max_purchased_at
    FROM purchases
    WHERE product_id = 1
    GROUP BY address_id
) t2
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC

PostgreSQL 固有の最適化:

PostgreSQL は、ネストされた DISTINCT ON クエリを使用して、より簡潔で潜在的に高速なソリューションを提供します。

SELECT * FROM (
  SELECT DISTINCT ON (address_id) *
  FROM purchases 
  WHERE product_id = 1
  ORDER BY address_id, purchased_at DESC
) t
ORDER BY purchased_at DESC

これらの代替案は、「グループごとの最大 N」シナリオを扱うときに DISTINCT ON のみに依存する場合と比較して、よりクリーンで効率的なソリューションを提供します。 これらにより、不必要な並べ替えが回避され、クエリのパフォーマンスが向上します。

以上がPostgreSQL の DISTINCT ON をさまざまな ORDER BY 句で正しく使用するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。