Wie wählt man effizient die maximale Zeile aus Partitionen ohne mehrere Tabellenscans aus?

Erhalten Sie effizient die größte Zeile in der Partition und vermeiden Sie redundanten Tabellenzugriff

Im Bereich der Datenabfrage ist Effizienz entscheidend, insbesondere beim Umgang mit großen Tabellen. Eine häufige Optimierungsherausforderung besteht darin, in jeder Partition einer Tabelle die Zeile mit dem größten Wert in einer bestimmten Spalte zu finden.

Angenommen, wir müssen die von jeder ID in der letzten Runde (ROUND) erzielte Punktzahl aus der SCORES-Tabelle abrufen:

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

Anfängliche Methode:

Eine Möglichkeit besteht darin, alle Zeilen abzurufen und dann diejenigen herauszufiltern, die nicht die maximale ROUND pro ID darstellen:

<code class="language-sql">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;</code>

Obwohl diese Methode funktioniert, ist sie aufgrund redundanter Tabellenscans ineffizient.

Optimierungsplan:

Eine weitere effizientere Möglichkeit ist die Verwendung von Fensterfunktionen und der DISTINCT-Klausel:

<code class="language-sql">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;</code>

Bei dieser Methode berechnet die Fensterfunktion max(round) OVER (PARTITION BY id) den maximalen ROUND für jede ID. Wenden Sie dann nach der Fensterfunktion eine DISTINCT-Klausel an, um sicherzustellen, dass nur die höchste ROUND-Zeile für jede ID zurückgegeben wird. Schließlich ruft die Fensterfunktion first_value(score) OVER (PARTITION BY id ORDER BY round DESC) den ersten SCORE ab, der der größten ROUND für jede ID zugeordnet ist.

Dieses optimierte Schema erzielt die gewünschten Ergebnisse, ohne dass mehrere Tabellenscans erforderlich sind, was die Leistung erheblich verbessert.

Das obige ist der detaillierte Inhalt vonWie wählt man effizient die maximale Zeile aus Partitionen ohne mehrere Tabellenscans aus?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!