複数のテーブルスキャンを行わずにパーティションから最大行を効率的に選択する方法は?

パーティション内の最大の行を効率的に取得し、冗長なテーブル アクセスを回避します

データクエリの分野では、特に大きなテーブルを扱う場合、効率が非常に重要です。最適化に関する一般的な課題は、テーブルの各パーティションの特定の列で最大値を持つ行を見つけることです。

最新のラウンド (ROUND) で各 ID によって取得されたスコアを SCORES テーブルから取得する必要があるとします。

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

初期メソッド:

1 つの方法は、すべての行を取得し、ID ごとの最大 ROUND を表さない行をフィルターで除外することです。

SELECT * FROM 
(SELECT id, round,
CASE WHEN (MAX(round) OVER (PARTITION BY id)) = round THEN score ELSE NULL END score
 FROM
 SCORES
 where id in (1,2,3)
) scorevals
WHERE
scorevals.round is not null;

この方法は機能しますが、テーブル スキャンが冗長であるため非効率的です。

最適化計画:

もう 1 つのより効率的な方法は、ウィンドウ関数と 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;

このメソッドでは、ウィンドウ関数 max(round) OVER (PARTITION BY id) が各 ID の最大 ROUND を計算します。次に、ウィンドウ関数の後に DISTINCT 句を適用して、各 ID の最上位の ROUND 行のみが返されるようにします。最後に、first_value(score) OVER (PARTITION BY id ORDER BY round DESC) ウィンドウ関数は、各 ID の最大 ROUND に関連付けられた最初の SCORE を取得します。

この最適化されたスキームにより、複数のテーブル スキャンを必要とせずに望ましい結果が得られ、パフォーマンスが大幅に向上します。

以上が複数のテーブルスキャンを行わずにパーティションから最大行を効率的に選択する方法は?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。