PostgreSQL で各グループの最も古いエントリを取得するにはどうすればよいですか?

PostgreSQL での各グループの最初の行の効率的な選択

このガイドでは、PostgreSQL で各グループの最も古いエントリを効率的に取得する方法を説明します。これは、グループ化されたデータを操作する場合の一般的なタスクです。 最も効果的な方法は、DISTINCT ON 句を利用することです。

DISTINCT ON 句の利用

PostgreSQL の DISTINCT ON 句は、指定された列で定義された各グループから最初の行を選択するための簡潔かつ効率的な方法を提供します。 その構文は単純です:

SELECT DISTINCT ON (column_list)
FROM table_name
ORDER BY column_list, ...;

例: 各顧客の最初の購入の識別

列 purchases、id、および customer を含む total テーブルについて考えます。各顧客の最も合計が高い最も早い購入 (id 列に基づく) を検索するには、次のクエリを使用します。

SELECT DISTINCT ON (customer)
       id, customer, total
FROM   purchases
ORDER  BY customer, total DESC, id;

重要な考慮事項:

• NULL 処理: total 列で NULL 値が許可される場合は、NULLS LAST 句に ORDER BY を含めて、NULL 以外の値が優先されるようにします。
• SELECT リストの柔軟性: SELECT リストは、DISTINCT ON または ORDER BY の列に限定されません。
• PostgreSQL の仕様: DISTINCT ON は PostgreSQL 固有の拡張機能であり、標準 SQL の一部ではありません。
• 代替順序: 最終結果をグループ化順序とは異なる順序にするには、クエリをネストし、2 番目の ORDER BY 句を適用します。

インデックス作成によるパフォーマンスの最適化:

(customer, total DESC, id) に複数列インデックスを作成すると、特に顧客あたりの行数が比較的少ない場合、クエリのパフォーマンスが大幅に向上します。

大規模なデータセットに対する代替アプローチ:

顧客ごとに多数の行を含むデータセットの場合は、別の戦略の方が効率的である可能性があります。

• サブクエリ: サブクエリまたは相関サブクエリを使用して各顧客の最大 total を見つけ、それらの顧客の最小 id を持つ行を選択します。
• 一時テーブル/CTE: 一意の顧客を含む一時テーブルまたは共通テーブル式 (CTE) を作成し、元のテーブルと結合して目的の行を取得します。 このアプローチは、複雑なシナリオに有益です。

以上がPostgreSQL で各グループの最も古いエントリを取得するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。