Pisanie zapytań określających wiele grup z zestawami grupowania
Klauzula GROUP BY w instrukcji SELECT w języku Transact-SQL służy do rozmieszczania wierszy w grupach, zwykle w celu obsługi agregacji. Jeśli jednak musisz grupować według różnych atrybutów w tym samym czasie, na przykład w celu raportowania na różnych poziomach, zwykle potrzebujesz wielu zapytań połączonych z UNION ALL. Zamiast tego, jeśli chcesz utworzyć agregacje wielu grup w tym samym zapytaniu, możesz użyć podklucza GRUPOWANIA klauzuli GROUP BY w języku Transact-SQL. ZESTAWY GRUPOWANIA udostępniają alternatywę dla używania FUNKCJI UNION ALL do łączenia wyników z wielu pojedynczych zapytań, z których każda ma własną klauzulę GROUP BY.
Używanie podklasy GRUPOWANIA ZESTAWÓW
Aby użyć zestawów GRUPOWANIA, należy określić kombinacje atrybutów, dla których ma być grupowana, jak w poniższym przykładzie składni:
SELECT <column list with aggregate(s)>
FROM <source>
GROUP BY
GROUPING SETS(
(<column_name>),--one or more columns
(<column_name>),--one or more columns
() -- empty parentheses if aggregating all rows
);
Załóżmy na przykład, że chcesz użyć zestawów GRUPOWANIA do agregowania kolumn Category i Cust z tabeli Sales.CategorySales, oprócz pustej notacji nawiasów w celu agregowania wszystkich wierszy:
SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY
GROUPING SETS((Category),(Cust),())
ORDER BY Category, Cust;
Wynik może wyglądać następująco:
| Kategoria | Cust | TotalQty |
|---|---|---|
| NULL | NULL | 999 |
| NULL | 1 | 80 |
| NULL | 2 | 12 |
| NULL | 3 | 154 |
| NULL | 100 | 241 |
| NULL | 5 | 512 |
| Beverages | NULL | 513 |
| Condiments | NULL | 114 |
| Confections | NULL | 372 |
Zwróć uwagę na obecność NULLs w wynikach. Listy NUL mogą być zwracane, ponieważ wartość NULL była przechowywana w bazowym źródle lub jest symbolem zastępczym w wierszu wygenerowanym w wyniku agregacji. Na przykład w poprzednich wynikach pierwszy wiersz wyświetla wartość NULL, NULL, 999. Reprezentuje to wiersz sumy końcowej. Wartości NULL w kolumnach Category i Cust są symbolami zastępczymi, ponieważ ani kategoria, ani Cust nie biorą udziału w agregacji.
Napiwek
Jeśli chcesz wiedzieć, czy symbol zastępczy oznacza wartość NULL, czy pochodzi z danych bazowych, możesz użyć GROUPING_ID. Aby uzyskać więcej informacji, odwiedź stronę referencyjną GROUPING_ID .
Używanie podklas modułów CUBE i ROLLUP
Podobnie jak ZESTAWY GRUPOWANIA, podklasy CUBE i ROLLUP umożliwiają również wiele grupowania dla agregacji danych. Jednak moduł CUBE i ROLLUP nie muszą określać każdego zestawu atrybutów do grupy. Zamiast tego, biorąc pod uwagę zestaw kolumn, moduł CUBE określi wszystkie możliwe kombinacje i grupowania danych wyjściowych. Funkcja ROLLUP tworzy kombinacje, przy założeniu, że kolumny wejściowe reprezentują hierarchię. W związku z tym moduły CUBE i ROLLUP można traktować jako skróty do ZESTAWÓW GRUPOWANIA.
Aby użyć modułu CUBE, dołącz słowo kluczowe CUBE do klauzuli GROUP BY i podaj listę kolumn do grupy. Na przykład aby pogrupować wszystkie kombinacje kolumn Category i Cust, należy użyć następującej składni w zapytaniu:
SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY CUBE(Category,Cust);
Te dane wyjściowe grupują następujące kombinacje: (Kategoria, Cust), (Cust, Category), (Cust), (Kategoria), (Kategoria) i agregacja dla wszystkich pustych ():
| Kategoria | Cust | TotalQty |
|---|---|---|
| Beverages | 1 | 36 |
| Condiments | 1 | 44 |
| NULL | 1 | 80 |
| Beverages | 2 | 5 |
| Confections | 2 | 7 |
| NULL | 2 | 12 |
| Beverages | 3 | 105 |
| Condiments | 3 | 100 |
| Confections | 3 | 45 |
| NULL | 3 | 154 |
| ... | ||
| NULL | NULL | 999 |
| Beverages | NULL | 513 |
| Condiments | NULL | 114 |
| Confections | NULL | 372 |
Aby użyć funkcji ROLLUP, należy dołączyć słowo kluczowe ROLLUP do klauzuli GROUP BY i podać listę kolumn do grupy. Aby na przykład pogrupować kombinacje kolumn Category (Kategoria), Subcategory (Podkategoria) i Product (Produkt), należy użyć następującej składni w zapytaniu:
SELECT Category, Subcategory, Product, SUM(Qty) AS TotalQty
FROM Sales.ProductSales
GROUP BY ROLLUP(Category,Subcategory, Product);
Spowoduje to grupowanie dla następujących kombinacji: (Kategoria, Podkategoria, Produkt), (Kategoria, Podkategoria), (Kategoria, Podkategoria), (Kategoria) i agregacja dla wszystkich pustych (). Kolejność, w której podano kolumny, ma znaczenie: FUNKCJA ROLLUP zakłada, że kolumny są wymienione w kolejności, która wyraża hierarchię. Zapewnia sumy częściowe dla każdego grupowania wraz z sumą końcową dla wszystkich grup na końcu.