単一の SQL テーブル内のグループごとの最大値を効率的に取得するにはどうすればよいですか?

グループごとの最大値取得のための SQL クエリの最適化

大規模な SQL テーブルを操作する場合、効率的なデータ取得が最も重要です。 頻繁に行われるタスクには、特定の行グループ内の最大値を特定することが含まれます。 この記事では、複数のテーブル スキャンを回避し、これを効率的に達成するための最適化された SQL クエリを示します。

SCORES、ID、および ROUND 列を持つ SCORE テーブルを考えてみましょう。目的は、各 ROUND とそれに対応する ID の最大 SCORE を取得することです。 反復処理を伴う非効率的な方法は、大規模なデータセットには適していません。

次のクエリは、最適なパフォーマンスを得るためにウィンドウ関数と DISTINCT を使用します。

SELECT DISTINCT
       id
      ,max(round) OVER (PARTITION BY id) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

このアプローチでは、ウィンドウ関数を使用して、最大の ROUND と各 SCORE グループ内の対応する ID を計算します。次に、DISTINCT 句は重複する行を削除し、ROUND ごとに最も高い ID のみが返されるようにします。これにより、複数のテーブル スキャンに比べて処理時間が大幅に短縮されます。

同様に効果的な別の方法では、FIRST_VALUE を 2 回使用します。

SELECT DISTINCT
       id
      ,first_value(round) OVER (PARTITION BY id ORDER BY round DESC) AS round
      ,first_value(score) OVER (PARTITION BY id ORDER BY round DESC) AS score
FROM   SCORES
WHERE  id IN (1,2,3)
ORDER  BY id;

どちらのクエリでも同じ結果が得られます:

ID	ROUND	SCORE
1	3	2
2	2	12
3	1	6

これらの最適化されたクエリは、1 回のテーブル スキャンでグループごとの最大値を取得することにより、効率の悪い方法に比べてパフォーマンスが大幅に向上します。 これは、大規模なデータセットを扱う際の応答性を維持するために非常に重要です。

以上が単一の SQL テーブル内のグループごとの最大値を効率的に取得するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。