Funkcje agregujące w języku SQL i ich zastosowanie

W tym samouczku omówimy różne funkcje agregujące w języku SQL oraz sposób, w jaki możemy ich używać. W języku SQL funkcje agregujące służą do gromadzenia danych z wielu wierszy w jednym wierszu podsumowania. 

Funkcje agregujące w SQL mogą być bardzo pomocne dla analityków podczas podsumowywania danych.

Spis treści

• Powszechnie używane funkcje agregujące w języku SQL
• Funkcje agregujące i klauzula GROUP BY
• Funkcje agregujące w SQL Server Management Studio (SSMS)
• Korzystanie z funkcji agregujących w pojedynczym zapytaniu
• Przykładowy scenariusz z klauzulą ​​GROUP BY
• Wniosek

Powszechnie używane funkcje agregujące w języku SQL

Poniżej przedstawiono niektóre z powszechnie używanych funkcji agregujących w języku SQL .

Zwróć uwagę, że funkcje i są powszechnie używane w walutach lub wartościach całkowitych. Funkcja AVG zwraca nie tylko średnią, ale także średnią z kolumny. Ponadto funkcja agregująca jest jedyną, która nie ignoruje wartości pustych podczas wykonywania obliczeń.

Utwórzmy kilka przykładowych poleceń przy użyciu naszych pięciu funkcji agregujących z przykładową tabelą poniżej.

Najpierw utwórzmy polecenie za pomocą funkcji MAX . Powiedzmy, że chcemy znaleźć produkt o najwyższej wartości w kolumnie ProductName. Nasze polecenie powinno wyglądać mniej więcej tak, jak na obrazku poniżej.

Ponieważ znajdujemy produkt o najwyższej wartości, musimy uzyskać ich ceny, które znajdują się w kolumnie ProductPrice . Zawsze umieszczaj alias, aby podać nazwę kolumny wyjściowej. Jeśli wykonamy to polecenie, pokaże nam najwyżej ceniony produkt z tylko jedną wartością. 

Następnie utwórzmy polecenie za pomocą funkcji MIN . Ponieważ funkcja MIN jest nieco podobna do funkcji MAX , użyjemy tego samego przykładu, co w przypadku funkcji MAX , ale tym razem znajdziemy iloczyn o najniższej wartości.

Kiedy wykonamy to polecenie, pobierze ono produkt o najniższej wartości z naszej przykładowej tabeli.

Teraz utwórzmy polecenie za pomocą funkcji LICZ . Załóżmy, że chcemy znaleźć ilość produktów w kolumnie ProductID . Nasze polecenie powinno wyglądać mniej więcej tak, jak na obrazku poniżej.

Jeśli wykonamy to polecenie, pokaże nam ilość każdego produktu w kolumnie ProductID i ich odpowiednią cenę.

Funkcje agregujące i klauzula GROUP BY

Teraz omówmy, jak możemy używać wielu kolumn z funkcjami agregującymi . Aby to zrobić, musimy użyć klauzuli GROUP BY .

Funkcja GROUP BY jest najczęściej używana w funkcjach agregujących, ponieważ zwraca jeden rekord z każdej grupy. Ponadto wszystkie kolumny w instrukcji SELECT, nawet bez agregacji, powinny nadal być uwzględnione.

Omówmy, jak będziemy używać funkcji agregujących z klauzulą ​​GROUP BY w naszej przykładowej tabeli poniżej.

Najpierw spróbujmy wykonać polecenie bez użycia klauzuli GROUP BY, aby zobaczyć różnicę. Znajdźmy całkowitą kwotę SaleAmount w kolumnie ProductName . Nasze polecenie powinno wyglądać mniej więcej tak, jak na obrazku poniżej.

Jak zauważyłeś, polecenie jest podobne do poprzednich przykładów, o których wspomnieliśmy na początku tego samouczka. Gdy wykonamy to polecenie, po prostu obliczy całkowitą kwotę w kolumnie SaleAmount , która będzie wynosić 28 .

Teraz posłużmy się innym przykładem użycia klauzuli GROUP BY . 

Ponieważ używamy klauzuli GROUP BY , możemy teraz używać wielu kolumn. Tym razem chcemy zobaczyć całkowitą kwotę SaleAmount na kategorię.

Klauzula GROUP BY sprawi, że wynik pokaże podsumowanie sprzedaży dla każdej kategorii, a jeśli zostanie wykonany, wynikiem będzie poniższy obraz.

W tym przykładzie ważne jest użycie klauzuli GROUP BY, ponieważ w przeciwnym razie wystąpi błąd. Dzieje się tak, ponieważ Kategoria nie jest zawarta w klauzuli GROUP BY ani w funkcji agregującej.

Weźmy ten sam przykład, ale tym razem otrzymamy również całkowitą kwotę SaleAmount według nazwy produktu . Nasze polecenie powinno wyglądać mniej więcej tak, jak na obrazku poniżej.

Gdy wykonamy nasze polecenie, wynik pokaże sumę sprzedaży według kategorii i ProductName .

Pamiętaj, że nie możesz używać ani uzyskiwać dostępu do wielu kolumn bez użycia klauzuli GROUP BY , ponieważ jeśli to zrobisz, spowoduje to błąd po uruchomieniu polecenia.

Funkcje agregujące w SQL Server Management Studio (SSMS)

Omówmy teraz, jak możemy używać funkcji agregujących w SQL Server Management Studio . W naszych przykładach użyjemy poniższej tabeli.

W naszej tabeli SalesOrderHeader mamy 31 465 wierszy danych . W naszym pierwszym przykładzie zademonstrujemy, jak możemy użyć funkcji LICZ , licząc wiersze w naszej tabeli. Nasze polecenie powinno wyglądać jak na obrazku poniżej.

Pomyślnie policzyliśmy wszystkie wiersze w naszym nagłówku SalesOrderHeader i nazwaliśmy go countofrows . Gdy wykonamy to polecenie, otrzymamy wynik 31 465 wierszy .

Wróćmy do naszego zestawu danych. Korzystając z tej samej funkcji agregującej , policzmy, ile SalesPersonID jest w naszej tabeli. Nasze polecenie powinno brzmieć:

Kiedy wykonamy nasze polecenie, pobierze ono konkretnie dane z kolumny SalesPersonsID i da nam kolumnę o nazwie countofsalespersons z wynikiem 3806 .

Korzystanie z funkcji agregujących w pojedynczym zapytaniu

Przejdźmy dalej, omówmy, jak możemy użyć wielu funkcji agregujących w jednym zapytaniu. Będziemy używać funkcji SUM , AVG i MAX do agregowania naszych kolumn TotalDue i TaxAmt . 

Możemy użyć wielu funkcji agregujących w jednym zapytaniu, umieszczając przecinek po każdej funkcji. 

Po wykonaniu tego polecenia nasz wynik powinien wyglądać jak na poniższym obrazku. 

Teraz możemy zobaczyć różne kolumny wraz z ich wartościami za pomocą wielu funkcji agregujących . 

Spróbujmy wykonać polecenie, które spowoduje błąd, aby podkreślić, dlaczego powinniśmy używać klauzuli GROUP BY podczas wybierania kolumn. Użyjemy tego samego przykładu z wieloma funkcjami, ale tym razem wybierzemy CustomerID .

Jak widać, mówi, że polecenie jest nieprawidłowe, ponieważ CustomerID nie jest zawarty ani w funkcji agregującej , ani w klauzuli GROUP BY .

Przykładowy scenariusz z klauzulą ​​GROUP BY

W naszym następnym przykładzie naprawmy nasz błąd, podając klauzulę GROUP BY w naszym poleceniu.

Właśnie dodaliśmy CustomerID w klauzuli GROUP BY i teraz możemy zobaczyć sumę, średnią i maksymalną wartość naszego TotalDue i TaxAmt według CustomerID. Widzimy również, że mamy w sumie 19119 wierszy .

Weźmy nasz ostatni przykład, omawiając, dlaczego ważne jest, abyśmy upewnili się, że kolumny i wyrażenia nieagregujące są zawarte w klauzuli GROUP BY .

W naszej tabeli SalesOrderHeader mamy kolumnę OrderDate . Powiedzmy, że chcemy uzyskać sumę TotalDue według roku. Jeśli nie będziemy wskazywać wszystkich nieagregujących kolumn i wyrażeń, musimy użyć polecenia z obrazka poniżej.

Zwróć uwagę, w jaki sposób jesteśmy w stanie uzyskać wynik, a nie błąd. Mamy jednak wiele raportów za ten sam rok, a naszym celem jest, aby raporty były podsumowywane w jednej określonej wartości rocznie.

Aby poprawić nasz błąd, musimy upewnić się, że uwzględniliśmy również wyrażenie w naszej klauzuli GROUP BY . Nasze nowe polecenie powinno brzmieć:

Po wykonaniu polecenia widzimy, że udało nam się uzyskać sumę OrderDate na rok. 

Klauzula HAVING w SQL Funkcje agregujące
Procedury składowane w SQL | Omówienie
Skuteczne sposoby korzystania z funkcji GROUPBY w usłudze LuckyTemplates

Wniosek

Podsumowując, poznałeś podstawową wiedzę dotyczącą korzystania z funkcji agregujących w języku SQL. Funkcje agregujące mogą być bardzo przydatne podczas pracy z dużymi raportami lub zestawami danych, ponieważ zapewniają analitykom wygodę podczas analizy i podsumowywania danych. 

Mamy nadzieję, że ten samouczek pomoże Ci lepiej ćwiczyć korzystanie z różnych funkcji agregujących w SQL. Jeśli chcesz dowiedzieć się więcej na ten temat i inne powiązane treści, z pewnością możesz sprawdzić listę odpowiednich linków poniżej.

Wszystkiego najlepszego,

Hafiz