w dzisiejszej lekcji dowiesz się o grupowaniu wyników zwracanych z zapytań za pomocą klauzuli SQL GROUP BY.
celem dzisiejszej lekcji jest:
- dowiedz się, jak grupować wyniki za pomocą GROUP BY
- Użyj funkcji agregujących do wykonywania obliczeń
- dowiedz się, jak filtrować grupy za pomocą klauzuli HAVING
ważne! Proszę postępować zgodnie z poniższymi przykładami w swojej bazie danych. Jeśli jeszcze tego nie zrobiłeś, zapisz się do mojego przewodnika jak zacząć korzystać z SQL Server., Otrzymasz instrukcje, jak zainstalować darmowe narzędzia i przykładową bazę danych.
Klauzula SQL GROUP BY
Klauzula SQL GROUP BY jest używana do wypisania wiersza pomiędzy podanymi wartościami kolumn. Jest zwykle używany w połączeniu z funkcji agregacji, takich jak suma lub liczyć do podsumowania wartości. W SQL grupy są unikalne kombinacje pól. Zamiast zwracać każdy wiersz tabeli, gdy wartości są zgrupowane, zwracane są tylko unikalne kombinacje.
Klauzula GROUP BY jest dodawana do instrukcji SQL po klauzuli WHERE., Oto przykład, w którym wymieniamy SalesOrderID, z wyłączeniem ilości większych niż 100.
jest kilka rzeczy do zapamiętania. Po pierwsze, kolumny, które chcemy podsumować, są wymienione, oddzielone przecinkami, w klauzuli GROUP BY. Po drugie, ta sama lista kolumn musi być wymieniona w instrukcji select; w przeciwnym razie instrukcja nie powiedzie się.,
Po uruchomieniu tej instrukcji zwracany jest nie każdy przefiltrowany wiersz. Tylko unikalne kombinacje SalesOrderID są uwzględniane w wyniku. To stwierdzenie jest bardzo podobne do
ale istnieje kluczowa różnica., Modyfikator DISTINCT zatrzymuje się przy wyprowadzaniu unikalnej kombinacji wierszy, podczas gdy za pomocą polecenia GROUP BY możemy obliczyć wartości na podstawie podstawowych filtrowanych wierszy dla każdej unikalnej kombinacji.
innymi słowy, korzystając z naszego przykładu, z grupą by, możemy obliczyć liczbę lub OrderDetails na zamówienie w następujący sposób:
COUNT jest przykładem funkcji agregującej.
funkcje agregujące SQL
niektóre funkcje, takie jak suma, są używane do wykonywania obliczeń na grupie wierszy, nazywane są funkcjami agregującymi. W większości przypadków funkcje te działają na grupie wartości, które są zdefiniowane za pomocą klauzuli GROUP BY., Gdy nie ma klauzuli GROUP BY, ogólnie przyjmuje się, że funkcja agregująca ma zastosowanie do wszystkich filtrowanych wyników.
niektóre z najczęstszych funkcji zbiorczych obejmują:
| AVG(wyrażenie) | Oblicz średnią wyrażenia. |
| COUNT(wyrażenie) | Count wystąpienia wartości innych niż null zwracanych przez wyrażenie. |
| COUNT (*) | zlicza wszystkie wiersze podanej tabeli., |
| MIN(wyrażenie) | znajduje minimalną wartość wyrażenia. |
| MAX(wyrażenie) | znajduje maksymalną wartość wyrażenia. |
| suma(wyrażenie) | Oblicz sumę wyrażenia. |
funkcje te mogą być używane samodzielnie w połączeniu z klauzulą GROUP BY. Same działają w całej tabeli, jednak gdy są używane z GROUP BY, ich obliczenia są „resetowane” za każdym razem, gdy zmienia się grupowanie., W ten sposób działają one jako sumy cząstkowe.
Prosta funkcja agregująca
podczas korzystania z funkcji agregującej można obliczyć wynik na wszystkich wartościach lub odrębnych wartościach. Na przykład, aby zliczyć wszystkie rekordy SalesOrderDetails możemy użyć wyrażenia:
SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
spowoduje to zwrócenie liczby 121317.
aby policzyć poszczególne zamówienia składające się na szczegóły, użyjemy następującego wzoru:
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
liczba wynosi teraz 31465.,
SQL grupowanie przez i agregowanie funkcji
do agregowania oznacza tworzenie całości z poszczególnych części. Funkcje agregujące to funkcje, które działają na więcej niż jednym wierszu, aby zwrócić wynik.
AVG i SUM
funkcja SUM sumuje zwrócone wartości, w podobny sposób AVG oblicza średnią.
zobaczmy, czy możemy obliczyć całkowitą kwotę zamówienia z OrderDetails., Z poprzednich lekcji wiemy, jak obliczyć całkowitą kwotę dla każdego szczegółu jako:
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
ponieważ możemy zastosować funkcję agregacji do wyrażeń, możemy ustawić grupowanie na OrderID, aby obliczyć całkowitą cenę za zamówienie jako
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
możemy nawet sortować według sumy, aby najpierw uzyskać najwyższe zamówienia
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC
w podobny sposób możemy obliczyć średnią szczegółowość zamówienia za pomocą AVG. Dlaczego warto spróbować poniżej! Jeśli utkniesz, Przełącz odpowiedź.
dla ciekawskich, ponieważ średnia jest obliczana jako suma próbki podzielona przez liczbę próbek, to użycie AVG w powyższej instrukcji jest takie samo jak:
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
omówiliśmy wiele w tym dziale., Oto kilka kluczowych punktów do zapamiętania:
- funkcja agregująca może ocenić wyrażenie, takie jak SUM(A + B)
- powinieneś używać aliasów funkcji agregujących, więc nazwy kolumn są znaczące
- podczas pracy z funkcjami agregującymi i grupowaniem według, czasami łatwiej jest najpierw pomyśleć o szczegółach, czyli napisać prostą instrukcję SELECT, sprawdzić wyniki, a następnie dodać fantazyjne rzeczy. Opisuję to w jak pisać lepsze zapytania.
SQL COUNT
funkcja COUNT jest używana, gdy musisz wiedzieć, ile rekordów istnieje w tabeli lub w grupie., COUNT (*) zliczy każdy rekord w grupie; podczas gdy count (wyrażenie) zliczy każdy rekord, w którym wynik wyrażenia nie jest null. Możesz również użyć Distinct z COUNT, aby znaleźć liczbę unikalnych wartości w grupie.
aby znaleźć liczbę linii SalesOrderDetail na zamówienie
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
aby znaleźć liczbę unikalnych zamówień sprzedaży na produkt
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
MIN i MAX
użyj MIN i MAX, aby znaleźć odpowiednio najmniejsze i największe wartości w tabeli lub grupie.,
na przykład, aby znaleźć najmniejsze i największe ilości produktów zamówionych w ramach zamówienia spróbuj
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Możesz również znaleźć minimalną lub maksymalną wartość obliczeń. Tutaj znajdujemy najwyższą ilość zamówionych produktów:
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
klauzula HAVING
klauzula HAVING SQL służy do filtrowania grup według wyników funkcji agregowanych. Umożliwia to rozwiązywanie problemów, takich jak wybór wszystkich zamówień, które mają więcej niż dwie linie szczegółów zamówienia.,
Ten przykład wygląda tak:
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
jeśli chcemy znaleźć wszystkie zamówienia większe niż $1000, piszemy
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
zauważ, że możemy użyć aliasu TotalPrice w klauzuli ORDER BY, ale wtedy posiadanie klauzuli musi użyć wyrażenia.
do domu hammer mając, chcę pokazać jeden ostatni przykład. Tutaj zobaczysz polecenie HAVING zawiera funkcję agregującą, której nie ma na liście SELECT.,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
w powyższym zapytaniu otrzymujemy całkowitą cenę dla zamówień, w których średnia kwota sprzedaży jest większa niż $500.00.
końcowe stwierdzenie o posiadaniu
chociaż spełniają podobną funkcję, istnieje kluczowe rozróżnienie między klauzulą WHERE a HAVING. Klauzula WHERE do filtrowania poszczególnych rekordów; natomiast klauzula HAVING filtruje grupy.
aby utrzymać to prosto w głowie, lubię myśleć o klauzuli WHERE wykonującej swoją pracę przed jakimikolwiek grupami, a następnie o klauzuli HAVING przejmującej po utworzeniu grup.,
ćwiczenia
warto ćwiczyć! Użyj przykładowej bazy danych, aby odpowiedzieć na te pytania.
- HR chce raport o liczbie aktywnych pracowników według stanowiska pracy. Jakiego SQL byś użył?
- wyświetla minimalną, maksymalną i średnią ilość zamówioną dla każdego produktu w SalesOrderDetails.
- Wymień wszystkie stanowiska pracy pracowników oraz liczbę pracowników, w których średnia liczba godzin zwolnień lekarskich jest mniejsza lub równa czterdziestu.
- dla tytułu pracy zwróconego w #3 powyżej, czy liczba jest taka sama dla wyniku odpowiadającego tytułu pracy # 1?
odpowiedzi już są!,
Leave a Reply