Detaillierte Erläuterung der Verwendung von OVER (PARTITION BY ..) in Oracle-Abfragen

Dieser Artikel stellt hauptsächlich die Verwendung von OVER (PARTITION BY ..) in Oracle-Abfragen vor. Ich hoffe, dass er allen helfen kann. Um das Lernen und Testen für alle zu erleichtern, werden alle Beispiele unter Oracles eigenem Benutzer Scott erstellt.

Hinweis: Die rote Reihenfolge nach im Titel weist darauf hin, dass bei Verwendung dieser Methode „Reihenfolge nach“ angegeben werden muss.

1. rank()/dense_rank() over(partition by ...order by ...)

Jetzt hat der Kunde eine solche Nachfrage, fragen Sie den Mitarbeiter mit dem höchsten Gehalt in Ich glaube, dass Studenten mit bestimmten Oracle-Anwendungskenntnissen die folgenden SQL-Anweisungen schreiben können:

select e.ename, e.job, e.sal, e.deptno 
 from scott.emp e, 
    (select e.deptno, max(e.sal) sal from scott.emp e group by e.deptno) me 
 where e.deptno = me.deptno 
  and e.sal = me.sal;

Bei der Erfüllung der Kundenbedürfnisse sollte sich jeder daran gewöhnen Überlegen Sie genau, ob es auch andere Möglichkeiten gibt. Dies ist sicher, verwenden Sie einfach die Syntax „rank() over(partition by...)“ oder „dense_rank() over(partition by…)“ im Titel dieses Abschnitts. Die SQL lautet wie folgt:

select e.ename, e.job, e.sal, e.deptno 
 from (select e.ename, 
        e.job, 
        e.sal, 
        e.deptno, 
        rank() over(partition by e.deptno order by e.sal desc) rank 
     from scott.emp e) e 
 where e.rank = 1;

select e.ename, e.job, e.sal, e.deptno 
 from (select e.ename, 
        e.job, 
        e.sal, 
        e.deptno, 
        dense_rank() over(partition by e.deptno order by e.sal desc) rank 
     from scott.emp e) e 
 where e.rank = 1;

Warum kommt es zum gleichen Ergebnis wie in der obigen Aussage? Hier ist eine ergänzende Erklärung der syntax rank()/dense_rank() over(partition by e.deptno order by e.sal desc).

über: Unter welchen Bedingungen.

Partition nach e.deptno: Teilen (Partition) durch Abteilungsnummer.

Reihenfolge nach e.sal desc: Sortieren nach Gehalt von hoch nach niedrig (bei Verwendung von rank()/dense_rank() muss „Reihenfolge nach“ enthalten sein, andernfalls ist dies illegal)

rank()/dense_rank(): Grading

Die Bedeutung der gesamten Aussage ist: Auf der Grundlage der Aufteilung nach Abteilungen werden die Mitarbeiter je nach Gehalt von hoch nach niedrig eingestuft und die „Level“ wird durch eine große Zahl dargestellt (der Mindestwert muss 1 sein).

Was ist also der Unterschied zwischen rank() und dense_rank()?

rank(): Sprungsortierung, wenn es zwei erste Ebenen gibt, ist die nächste die dritte Ebene.

dense_rank(): Kontinuierliche Sortierung, wenn es zwei erste Ebenen gibt, ist die nächste immer noch die zweite Ebene.

Kleine Hausaufgabe: Fragen Sie die Mitarbeiterinformationen zum Mindestlohn der Abteilung ab.

2. min()/max() over(partition by...)

Nachdem wir das höchste/minimale Gehalt der Abteilung erhalten haben, kommt die Nachfrage des Kunden erneut Mitarbeiterinformationen Berechnen Sie gleichzeitig die Differenz zwischen dem Gehalt des Mitarbeiters und dem Höchst-/Mindestgehalt der Abteilung. Dies ist relativ einfach. Basierend auf der Groupby-Anweisung im ersten Abschnitt ändern Sie sie wie folgt:

select e.ename, 
     e.job, 
     e.sal, 
     e.deptno, 
     e.sal - me.min_sal diff_min_sal, 
     me.max_sal - e.sal diff_max_sal 
  from scott.emp e, 
     (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal 
      from scott.emp e 
      group by e.deptno) me 
  where e.deptno = me.deptno 
  order by e.deptno, e.sal;

Oben haben wir min() und max( ), ersterer ermittelt den Minimalwert und letzterer ermittelt den Maximalwert. Welchen Effekt hat es, wenn diese beiden Methoden zusammen mit over(partition by...) verwendet werden? Schauen Sie sich die folgende SQL-Anweisung an:

select e.ename, 
    e.job, 
    e.sal, 
    e.deptno, 
    nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal, 
    nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal 
 from scott.emp e;

Sie können sehen, dass min() und max( ) ermittelt tatsächlich die minimalen und maximalen Werte, basiert jedoch auf Partition für Partition.

Kleine Hausaufgabe: Wenn Sie in diesem Beispiel „Ordnung nach“ hinzufügen, was wird das Ergebnis sein?

3. Lead()/lag() over(partition by ... order by ...)

Chinesen lieben es, zu vergleichen, ihr Gesicht zu wahren und sind weltberühmt. Dem Kunden gefiel dies noch besser, nachdem er es mit dem Höchst-/Mindestgehalt verglichen hatte. Diesmal stellte er eine eher ungewöhnliche Forderung, nämlich die Differenz zwischen seinem persönlichen Gehalt und dem Gehalt von zu berechnen eine Person höher oder niedriger als er. Diese Anforderung ist mir wirklich sehr peinlich. Ich weiß nicht, wie ich sie in der Groupby-Anweisung umsetzen soll. Aber. . . . Jetzt, wo wir über(partitionieren nach...) haben, sieht alles so einfach aus. Wie folgt:

select e.ename, 
    e.job, 
    e.sal, 
    e.deptno, 
    lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal, 
    lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal, 
    nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal, 
      0) diff_lead_sal, 
    nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal 
 from scott.emp e;

Glauben Sie, dass es sich nach dem Lesen des obigen Satzes um einen falschen Alarm handelte? Lassen Sie uns die beiden oben verwendeten neuen Methoden erklären.

lead(column name,n,m): Der Wert von , der in der n-ten Zeile nach dem aktuellen Datensatz aufgezeichnet wird, wenn nicht, ist der Standardwert m; Parameter n,m, dann finden Sie den Wert des Datensatzes in der ersten Zeile nach dem aktuellen Datensatz. Wenn nicht, ist der Standardwert null.

lag(Spaltenname,n,m): Der Wert von wird in der n-ten Zeile vor dem aktuellen Datensatz aufgezeichnet. Wenn nicht, ist der Standardwert m; Parameter n,m, dann finden Sie den Wert des Datensatzes in der ersten Zeile vor dem aktuellen Datensatz. Wenn nicht, ist der Standardwert null.

Im Folgenden sind die Anwendungen einiger häufig verwendeter Methoden in dieser Grammatik aufgeführt (Hinweis: Methoden mit einer order by-Klausel geben an, dass order by verwendet werden muss, wenn diese Methode verwendet wird):

select e.ename, 
    e.job, 
    e.sal, 
    e.deptno, 
    first_value(e.sal) over(partition by e.deptno) first_sal, 
    last_value(e.sal) over(partition by e.deptno) last_sal, 
    sum(e.sal) over(partition by e.deptno) sum_sal, 
    avg(e.sal) over(partition by e.deptno) avg_sal, 
    count(e.sal) over(partition by e.deptno) count_num, 
    row_number() over(partition by e.deptno order by e.sal) row_num 
 from scott.emp e;

Nach dem Lesen dieses Artikels haben Sie möglicherweise einige Missverständnisse, das heißt, OVER (PARTITION BY ..) ist besser als GROUP BY. Ersteres kann das nicht ersetzen Letzteres und die Ausführungseffizienz des ersteren ist nicht so hoch wie die des letzteren, aber ersteres bietet mehr Funktionen, daher hoffe ich, dass Sie bei der Verwendung entsprechend Ihren Anforderungen wählen können.

Verwandte Empfehlungen:

Tipps zur Oracle-Programmentwicklung

Vergleich der Fälle von in Oracle verwendeten Triggern und von in MySQL verwendeten Triggern

99 häufig verwendete Abfrageanweisungen in Oracle-Datenbanken

Das obige ist der detaillierte Inhalt vonDetaillierte Erläuterung der Verwendung von OVER (PARTITION BY ..) in Oracle-Abfragen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!