SQL Server: Unterschied zwischen PARTITION BY und GROUP BY
Ich habe GROUP BY im Laufe der Jahre für alle Arten von Aggregatabfragen verwendet. In letzter Zeit habe ich einen Code rückentwickelt, der PARTITION BY zum Ausführen von Aggregationen verwendet. Beim Durchlesen der gesamten Dokumentation, die ich über PARTITION BY finden kann, klingt es sehr nach GROUP BY, vielleicht mit ein paar zusätzlichen Funktionen. Sind es zwei Versionen derselben allgemeinen Funktionalität oder sind sie etwas völlig anderes?
Sie werden an verschiedenen Orten verwendet. group by ändert die gesamte Abfrage wie folgt:
select customerId, count(*) as orderCount
from Orders
group by customerId
Aber partition by funktioniert nur auf einer Fensterfunktion , wie row_number:
select row_number() over (partition by customerId order by orderId)
as OrderNumberForThisCustomer
from Orders
Ein group by reduziert normalerweise die Anzahl der zurückgegebenen Zeilen, indem sie aufgerollt und Durchschnittswerte oder Summen für jede Zeile berechnet werden. partition by hat keinen Einfluss auf die Anzahl der zurückgegebenen Zeilen, ändert jedoch die Berechnung der Ergebnisse einer Fensterfunktion.
Wir können ein einfaches Beispiel nehmen
wir haben eine Tabelle mit dem Namen TableA mit den folgenden Werten.
id firstname lastname Mark
-------------------------------------------------------------------
1 arun prasanth 40
2 ann antony 45
3 sruthy abc 41
6 new abc 47
1 arun prasanth 45
1 arun prasanth 49
2 ann antony 49
Gruppiere nach
Die SQL GROUP BY-Klausel kann in einer SELECT-Anweisung zum Sammeln von .__ verwendet werden. Daten über mehrere Datensätze hinweg und gruppieren die Ergebnisse nach einem oder mehreren Säulen.
In einfacheren Worten wird die GROUP BY-Anweisung in Verbindung mit .__ verwendet. die Aggregatfunktionen zum Gruppieren der Ergebnismenge nach einem oder mehreren Säulen.
Syntax :
SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;
Wir können GroupBy in unserer Tabelle anwenden
select SUM(Mark)marksum,firstname from TableA
group by id,firstName
Ergebnisse :
marksum firstname
----------------
94 ann
134 arun
47 new
41 sruthy
In unserer reellen Tabelle haben wir 7 Zeilen und wenn wir Gruppe nach ID anwenden, gruppiert der Server die Ergebnisse basierend auf ID
In einfachen Worten
diese Gruppe von reduziert normalerweise die Anzahl der Zeilen, die durch das Rollen von .__ zurückgegeben werden. sie auf und berechnen die Summe für jede Zeile.
partition von
bevor Sie durch partitionieren gehen
schauen wir uns die OVER-Klausel an
Laut MSDN-Definition
OVER-Klausel definiert ein Fenster oder einen benutzerdefinierten Satz von Zeilen in einer Abfrageergebnissatz Eine Fensterfunktion berechnet dann einen Wert für jede Zeile im Fenster. Sie können die OVER-Klausel mit Funktionen zum Berechnen von .__ verwenden. aggregierte Werte wie gleitende Durchschnitte, kumulierte Aggregate, laufende Summen oder ein Top N pro Gruppe.
partition by reduziert nicht die Anzahl der zurückgegebenen Zeilen
wir können partition in unserer Beispieltabelle anwenden
select SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname from TableA
ergebnis:
marksum firstname
-------------------
134 arun
134 arun
134 arun
94 ann
94 ann
41 sruthy
47 new
schauen Sie sich die Ergebnisse an, und es werden die Zeilen partitioniert, und alle Zeilen werden nicht gruppiert.
partition by rollt die Daten nicht wirklich auf. Sie können etwas für jede Gruppe zurücksetzen. Sie können beispielsweise eine Ordinalspalte in einer Gruppe abrufen, indem Sie das Gruppierungsfeld partitionieren und rownum() für die Zeilen in dieser Gruppe verwenden. Dadurch erhalten Sie etwas, das sich wie eine Identitätsspalte verhält, die zu Beginn jeder Gruppe zurückgesetzt wird.
PARTITION VON Unterteilt die Ergebnismenge in Partitionen. Die Fensterfunktion wird separat auf jede Partition angewendet, und die Berechnung wird für jede Partition neu gestartet.
Gefunden unter diesem Link: OVER-Klausel
Es bietet aufgerollte Daten ohne Aufrollen
nehmen wir an, ich möchte die relative Position der Verkaufsregion zurückgeben
Mit PARTITION BY kann ich den Umsatzbetrag für eine bestimmte Region zurückgeben und den MAX-Betrag für alle Verkaufsregionen in derselben Zeile.
Dies bedeutet, dass Sie wiederholte Daten haben werden. Dies kann jedoch für den Endverbraucher geeignet sein, da die Daten aggregiert wurden, aber keine Daten verloren gegangen sind - wie dies bei GROUP BY der Fall wäre.
PARTITION BY ist analytisch, während GROUP BY Aggregat ist. Um PARTITION BY zu verwenden, müssen Sie es mit einer OVER-Klausel enthalten.
Nach meinem Verständnis ist Partition By fast identisch mit Group By, jedoch mit den folgenden Unterschieden:
Diese Gruppe gruppiert tatsächlich die Ergebnismenge, die eine Zeile pro Gruppe zurückgibt. Dies führt dazu, dass SQL Server nur in der SELECT-Liste Aggregatfunktionen oder -spalten zulässt, die Teil der group by-Klausel sind (in diesem Fall kann SQL Server die Eindeutigkeit gewährleisten Ergebnisse für jede Gruppe).
Stellen Sie sich beispielsweise MySQL vor, das es erlaubt, Spalten in der SELECT-Liste zu haben, die nicht in der Group By-Klausel definiert sind. In diesem Fall wird immer noch eine Zeile pro Gruppe zurückgegeben. Wenn die Spalte jedoch keine eindeutigen Ergebnisse hat, gibt es keine Garantie was wird die Ausgabe sein!
Aber mit Partition By werden zwar die Ergebnisse der Funktion identisch zu den Ergebnissen einer Aggregatfunktion mit Group By, Sie erhalten jedoch immer noch die normale Ergebnismenge. Dies bedeutet, dass eine Zeile pro Zeile und nicht eine Zeile pro Zeile erhalten wird Gruppe, und aus diesem Grund kann es Spalten geben, die für jede Gruppe in der SELECT-Liste nicht eindeutig sind.
Zusammengefasst ist Group By also am besten, wenn eine Ausgabe von einer Zeile pro Gruppe benötigt wird, und Partition By ist am besten, wenn alle Zeilen benötigt werden, die Aggregatfunktion jedoch auf einer Gruppe basiert.
Natürlich kann es auch zu Leistungsproblemen kommen, siehe http://social.msdn.Microsoft.com/Forums/ms-MY/transactsql/thread/0b20c2b5-1607-40bc-b7a7-0c60a2a55fba .
Es gibt wirklich unterschiedliche Nutzungsszenarien. Wenn Sie GROUP BY verwenden, führen Sie einige der Datensätze für die gleichen Spalten zusammen und Sie haben eine Aggregation der Ergebnismenge.
Wenn Sie jedoch PARTITION BY verwenden, ist Ihre Ergebnismenge dieselbe, aber Sie haben nur eine Aggregation über die Fensterfunktionen und Sie führen die Datensätze nicht zusammen, haben Sie immer noch die gleiche Anzahl von Datensätzen.
Hier ist ein hilfreicher Artikel, der den Unterschied erklärt: http://alevryustemov.com/sql/sql-partition-by/
Angenommen, wir haben 14 Datensätze der Spalte name in der Tabelle
im group by
select name,count(*) as totalcount from person where name='Please fill out' group BY name;
es wird in einer Reihe gezählt, d. h. 14
aber in partition by
select row_number() over (partition by name) as total from person where name = 'Please fill out';
es werden 14 zeilen mehr gezählt
Kleine Beobachtung. Ein Automatisierungsmechanismus zum dynamischen Generieren von SQL mithilfe der 'Partition nach' ist in Bezug auf die 'Gruppierung nach' viel einfacher zu implementieren. Im Falle von 'group by' müssen wir uns um den Inhalt der Spalte 'select' kümmern.
Entschuldigung für mein Englisch.