SQL でカテゴリごとに最新のアイテムを効率的に選択するにはどうすればよいですか?

SQL クエリを最適化して各カテゴリの最新アイテムを効率的に選択します

データベース内の各カテゴリの最新プロジェクトを表示するという課題には、効率的な SQL クエリが必要です。カテゴリごとに繰り返して最新のアイテムをクエリするのは簡単なアプローチですが、データベース呼び出しを減らしてクエリを最適化することをお勧めします。

グループあたり最大 N 個の質問

カテゴリ グループ内の最新の n レコードを識別するタスクは、max-n-per-group 問題として知られており、一般的な SQL クエリの問題です。

外部接続ソリューション

外部結合を使用すると、望ましい結果を得ることができます:

<code class="language-sql">SELECT i1.*
FROM item i1
LEFT OUTER JOIN item i2
  ON (i1.category_id = i2.category_id AND i1.item_id < i2.item_id)
GROUP BY i1.item_id
HAVING COUNT(*) < 4;</code>

説明

このクエリは、同じカテゴリ内のアイテムのみを考慮して、各アイテム (i1) とそれより新しいアイテム (i2) の間で外部結合を実行します。 COUNT(*) 句は、各 i1 の新しい項目の数を決定します。新しいプロジェクトが 4 つ未満のプロジェクトが、選択の適格候補となります。

メリット

このソリューションは柔軟で適応性があります:

• 任意の数のカテゴリを簡単に処理
• カテゴリの変更の影響を受けません
• アイテム数が異なるカテゴリを処理できます

代替案

ユーザー変数を使用した別の MySQL 固有のソリューション:

<code class="language-sql">SELECT *
FROM (
    SELECT i.*, @r := IF(@g = category_id, @r+1, 1) AS rownum, @g := category_id
    FROM (SELECT @g:=null, @r:=0) AS _init
    CROSS JOIN item i
    ORDER BY i.category_id, i.date_listed
) AS t
WHERE t.rownum <= 4;</code>

SQL ウィンドウ関数

MySQL 8.0.3 ではウィンドウ関数が導入され、別の効率的なソリューションが可能になりました:

<code class="language-sql">WITH numbered_item AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY item_id) AS rownum
  FROM item
)
SELECT * FROM numbered_item WHERE rownum <= 4;</code>

結論

特定の MySQL バージョンとデータベース構造に基づいて適切なクエリ方法を選択することで、各カテゴリの最新アイテムの選択を効果的に最適化できます。

以上がSQL でカテゴリごとに最新のアイテムを効率的に選択するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。