PostgreSQL でグループごとに上位 N 行を取得するにはどうすればよいですか?

PostgreSQL: 各グループの上位 N 行の取得

PostgreSQL では、多くの場合、データセット内の各グループから特定の数の行を取得する必要があります。これは、ランク付けされたデータを扱う場合、またはカテゴリごとに結果を制限する必要がある場合に特に便利です。 たとえば、各部門の上位 3 つの製品を売上順に表示することができます。

例:

次の構造とサンプル データを持つ products という名前のテーブルについて考えます。

product_id	department_id	product_name	sales
1	1	Product A	100
2	1	Product B	150
3	1	Product C	200
4	1	Product D	250
5	2	Product E	50
6	2	Product F	100
7	3	Product G	120
8	2	Product H	180

目的は、各 department_id から (売上に基づく) 上位 2 つの製品を取得することです。 期待される結果は次のようになります:

product_id	department_id	product_name	sales
4	1	Product D	250
3	1	Product C	200
8	2	Product H	180
6	2	Product F	100
7	3	Product G	120

ROW_NUMBER() を使用した解決策 (PostgreSQL 8.4 以降):

PostgreSQL 8.4 以降では ROW_NUMBER() ウィンドウ関数が提供され、効率的なソリューションが提供されます。 次のクエリはこのタスクを実行します:

<code class="language-sql">SELECT product_id, department_id, product_name, sales
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY sales DESC) AS rn,
    product_id, department_id, product_name, sales
  FROM
    products
) ranked_products
WHERE rn <= 2;</code>

このクエリは、売上に基づいて部門内の各製品にランク (rn) を割り当て、ランクが 2 以下の製品のみを含むようにフィルタリングします。

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