W tym artykule omówimy klauzulę SQL PARTITION BY oraz, w szczególności różnicę z GROUP BY w instrukcji Select. Zbadamy również różne przypadki użycia SQL PARTITION BY.
Używamy SQL PARTITION BY, aby podzielić zestaw wyników na partycje i wykonać obliczenia na każdym podzbiorze danych podzielonych na partycje.
Przygotowanie próbki Dane
Utwórzmy tabelę Zamówienia w mojej przykładowej bazie danych SQLShackDemo i wstawmy rekordy do pisania dalszych zapytań.
Używam ApexSQL Generate, aby wstawić przykładowe dane do tego artykułu. Kliknij prawym przyciskiem myszy tabelę Zamówienia i Generuj dane testowe.
Uruchamia ApexSQL Generate. Wygenerowałem skrypt do wstawiania danych do tabeli Zamówienia. Wykonaj ten skrypt, aby wstawić 100 rekordów do tabeli Zamówienia.
Gdy wykonamy instrukcje wstawiania, zobaczymy dane w tabeli Zamówienia na poniższym obrazku.
Używamy klauzuli SQL GROUP BY do grupowania wyników według określonej kolumny i używamy funkcji agregujących, takich jak Avg (), Min (), Max () do obliczania wymaganych wartości.
Grupuj według składni funkcji
|
1
2
3
4
|
SELECT wyrażenie, funkcja agregująca ()
FROM tabel
Warunki WHERE
GROUP BY wyrażenie
|
Załóżmy, że chcemy znaleźć następujące wartości w tabeli Zamówienia
- Minimalna wartość zamówienia w mieście
- Maksymalna wartość zamówienia w mieście
- Średnia wartość zamówienia w mieście
Wykonaj następujące zapytanie z klauzulą GROUP BY, aby obliczyć te wartości.
|
1
2
3
4
5
6
|
SELECT Customercity,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM ( Orderamount) TotalOrderAmount
OD.
GROUP BY Customercity;
|
Na poniższym zrzucie ekranu widzimy średnią, minimalną i maksymalne wartości pogrupowane według CustomerCity.
Teraz chcemy dodać również kolumny CustomerName i OrderAmount. Dodajmy te kolumny do instrukcji select i wykonajmy następujący kod.
|
1
2
3
4
5
6
|
SELECT Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
OD.
GROUP BY Customercity;
|
Po wykonaniu tego zapytania otrzymujemy komunikat o błędzie . W klauzuli SQL GROUP BY możemy użyć kolumny w instrukcji select, jeśli jest ona również używana w klauzuli Group by. Nie zezwala na żadną kolumnę w klauzuli select, która nie jest częścią klauzuli GROUP BY.
Możemy użyć SQL PARTITION BY, aby rozwiązać ten problem. Przyjrzyjmy się temu dokładniej w następnej sekcji.
SQL PARTITION BY
Możemy użyć klauzuli SQL PARTITION BY z klauzulą OVER, aby określić kolumnę, na której musimy przeprowadzić agregację . W poprzednim przykładzie użyliśmy Group By z kolumną CustomerCity i obliczonymi wartościami średnią, minimalną i maksymalną.
Przeprowadźmy ponownie ten scenariusz z klauzulą SQL PARTITION BY, używając następującego zapytania.
|
1
2
3
4
5
|
SELECT Customercity,
AVG (kwota zamówienia) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (kwota zamówienia) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUMA (kwota zamówienia) OVER (PARTITION BY Customercity) TotalOrderAmount
OD.;
|
W wyniku otrzymujemy zagregowane wartości podobne do Klauzula GROUP By. Można zauważyć różnicę w wynikach danych wyjściowych klauzul SQL PARTITION BY i GROUP BY.
|
Grupuj według |
SQL PARTITION BY |
|
Korzystając z klauzuli Group By, uzyskujemy ograniczoną liczbę rekordów |
Otrzymujemy wszystkie rekordy w tabeli za pomocą klauzuli PARTITION BY. |
|
Daje to jeden wiersz na grupę w zestawie wyników. Na przykład otrzymujemy wynik dla każdej grupy CustomerCity w klauzuli GROUP BY. |
Daje zagregowane kolumny z każdym rekordem w określonej tabeli. Mamy 15 rekordów w tabeli Zamówienia. W wyniku zapytania SQL PARTITION BY otrzymujemy również 15 wierszy wraz z wartościami Min, Max i średnią. |
W poprzednim przykładzie otrzymujemy komunikat o błędzie, jeśli spróbujemy dodać kolumnę, która nie jest częścią klauzuli GROUP BY.
Możemy dodać wymagane kolumny w instrukcji select z klauzulą SQL PARTITION BY . Dodajmy kolumny CustomerName i OrderAmount i wykonaj następujące zapytanie.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AVGOrderAmount ,
MIN (kwota zamówienia) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (kwota zamówienia) OVER (PARTITION BY Customercity) TotalOrderAmount
OD.;
|
Otrzymujemy kolumnę CustomerName i OrderAmount wraz z danymi wyjściowymi zagregowana funkcja. Otrzymujemy również wszystkie wiersze dostępne w tabeli Zamówienia.
Na poniższym zrzucie ekranu możesz dla CustomerCity Chicago wykonać agregacje (Avg, Min i Max) i podaje wartości w odpowiednich kolumnach.
Podobnie możemy użyć innych funkcji agregujących, takich jak as count, aby znaleźć całkowitą liczbę zamówień w danym mieście z klauzulą SQL PARTITION BY.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
COUNT (ID zamówienia) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderam) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO N BY Customercity) AS MinOrderAmount,
SUMA (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
OD.;
|
Możemy zobaczyć liczbę zamówień dla konkretnego miasta. Na przykład mamy zatem dwa zamówienia z miasta Austin; pokazuje wartość 2 w kolumnie CountofOrders.
Klauzula PARTITION BY z ROW_NUMBER ()
Możemy użyj klauzuli SQL PARTITION BY z funkcją ROW_NUMBER (), aby uzyskać numer wiersza w każdym wierszu. Definiujemy następujące parametry, aby użyć ROW_NUMBER z klauzulą SQL PARTITION BY.
- kolumna PARTITION BY – w tym przykładzie chcemy podzielić dane na kolumnę CustomerCity.
- Order By : W kolumnie ORDER BY definiujemy kolumnę lub warunek definiujący numer wiersza. W tym przykładzie chcemy posortować dane w kolumnie OrderAmount
|
1
2
3
4
5
6
7
8
9
10
|
SELECT Customercity,
CustomerName,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Row Number”,
OrderAmount,
COUNT (ID zamówienia) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUMA (kwota zamówienia) OVER (PARTITION BY Customercity) TotalOrderAmount
OD.;
|
Na poniższym zrzucie ekranu widzimy CustomerCity Chicago , mamy wiersz numer 1 dla zamówienia o najwyższej kwocie 7577,90. zawiera numer wiersza z malejącą wartością OrderAmount.
Klauzula PARTITION BY z łączną wartością całkowitą
Załóżmy, że chcesz uzyskać łączną sumę zamówień w partycji.Łączna suma powinna znajdować się w bieżącym wierszu i kolejnym wierszu partycji.
Na przykład w mieście Chicago mamy cztery zamówienia.
|
CustomerCity |
CustomerName |
Ranga |
OrderAmount |
Łączna suma wierszy |
Skumulowana suma |
|
Chicago |
Marvin |
Ranga 1 + 2 |
|||
|
Chicago |
Lawrence |
Ranga 2 + 3 |
|||
|
Chicago |
Alex |
Ranga 3 + 4 |
|||
|
Chicago |
Jerome |
Pozycja 4 |
W poniższym zapytaniu określiliśmy klauzulę ROWS do se Wybierz bieżący wiersz (używając CURRENT ROW) i następny wiersz (używając 1 FOLLOWING). Następnie oblicza sumę w tych wierszach przy użyciu sumy (Kwota zamówienia) z podziałem na CustomerCity (przy użyciu OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Row Number”,
CONVERT (VARCHAR (20), SUM (Orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS MIĘDZY BIEŻĄCYM I 1 NASTĘPUJĄCYM WIERSZEM), 1) AS CumulativeTotal,
|
Podobnie możemy obliczyć skumulowaną średnią używając następujące zapytanie z klauzulą SQL PARTITION BY.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Numer wiersza”,
CONVERT (VARCHAR (20), AVG (kwota zamówienia) OVER (PARTITION BY Customercity)
ZAMÓWIENIE WEDŁUG ZAMÓWIENIA ZAMÓWIENIE WIERSZY POMIĘDZY BIEŻĄCYM I 1 NASTĘPUJĄCYM WIERSZEM) , 1) AS CumulativeAVG
|
ROWS UNBOUNDED PRECEDING z klauzulą PARTITION BY
Możemy użyć ROWS UNBOUNDED PRECEDING z klauzulą SQL PARTITION BY, aby wybrać wiersz w partycji przed bieżącym wierszem i najwyższą wartością r ow po bieżącym wierszu.
W poniższej tabeli widzimy wiersz 1; nie ma żadnego wiersza o dużej wartości w tej partycji. Dlatego skumulowana średnia wartość jest taka sama, jak w wierszu 1 OrderAmount.
W przypadku Row2 szuka bieżącej wartości wiersza (7199,61) i najwyższej wartości w wierszu 1 (7577,9). Oblicza średnią dla tych dwóch kwot.
W wierszu 3 szuka wartości bieżącej (6847,66) i wartości kwoty wyższej niż ta, która wynosi 7199,61 i 7577,90. Oblicza średnią z tych i zwraca.
|
CustomerCity |
CustomerName |
Rank |
OrderAmount |
Skumulowane średnie wiersze |
Średnia skumulowana |
|
Chicago |
Marvin |
Ranga 1 |
|||
|
Chicago |
Lawrence |
Ranga 1 + 2 |
|||
|
Chicago |
Alex |
Ranga 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Ranga 1 + 2 + 3 + 4 |
Wykonaj następujące zapytanie, aby uzyskać ten wynik z naszymi przykładowymi danymi.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS „Row Number”,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity)
ZAMÓWIENIE WEDŁUG KWOTY ZAMÓWIENIA WRZESZCZ. AS CumulativeAvg
OD.;
|
Podsumowanie
W tym artykule omówiliśmy klauzulę SQL PARTIION BY i jej porównanie z klauzulą GROUP BY. Dowiedzieliśmy się również o jego użyciu na kilku przykładach. Mam nadzieję, że ten artykuł okaże się przydatny i zachęcam do zadawania pytań w komentarzach poniżej.
- Autor
- Najnowsze posty
Jest twórcą jednej z największych bezpłatnych kolekcji artykułów online na jeden temat, obejmującej 50-częściową serię na temat grup dostępności SQL Server Always On. Dzięki swojemu wkładowi w społeczność SQL Server, został wyróżniony wieloma nagrodami, w tym prestiżowym tytułem „Najlepszy autor roku” w SQLShack w latach 2020 i 2021 nieprzerwanie.
Raj jest zawsze zainteresowany nowymi wyzwaniami, więc jeśli potrzebujesz konsultacji pomoc w każdym temacie poruszonym w jego pismach, można się z nim skontaktować pod adresem [email protected]
Wyświetl wszystkie posty Rajendry Gupty
- Limity czasu sesji w SQL Server Always On Availability Grupy – 8 lutego 2021
- Wykonywanie mniejszych i głównych aktualizacji wersji AWS RDS SQL Server – 29 stycznia 2021
- Wdrażanie wystąpień AWS RDS PostgreSQL – 27 stycznia 2021