SELECT — GRUPUJ WEDŁUG— Transact-SQL

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)Punkt końcowy analizy SQL w usłudze Microsoft FabricMagazyn w usłudze Microsoft FabricBaza danych SQL w usłudze Microsoft Fabric (wersja zapoznawcza)

Klauzula instrukcji SELECT, która dzieli wynik zapytania na grupy wierszy, zwykle wykonując co najmniej jedną agregację w każdej grupie. Instrukcja SELECT zwraca jeden wiersz na grupę.

Syntax

Transact-SQL konwencje składni

-- Syntax for SQL Server and Azure SQL Database   
-- ISO-Compliant Syntax  
  
GROUP BY {
      column-expression  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
    | GROUPING SETS ( <grouping_set> [ ,...n ]  )  
    | () --calculates the grand total 
} [ ,...n ] 
 
<group_by_expression> ::=  
      column-expression  
    | ( column-expression [ ,...n ] )    
   
<grouping_set> ::=  
      () --calculates the grand total  
    | <grouping_set_item>  
    | ( <grouping_set_item> [ ,...n ] )  
  
<grouping_set_item> ::=  
      <group_by_expression>  
    | ROLLUP ( <group_by_expression> [ ,...n ] )  
    | CUBE ( <group_by_expression> [ ,...n ] )  
  

-- For backward compatibility only.
-- Non-ISO-Compliant Syntax for SQL Server and Azure SQL Database 
  
GROUP BY {
       ALL column-expression [ ,...n ] 
    | column-expression [ ,...n ]  WITH { CUBE | ROLLUP }    
       }

-- Syntax for Azure Synapse Analytics 
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
    | ROLLUP ( <group_by_expression> [ ,...n ] ) 
} [ ,...n ]

-- Syntax for Parallel Data Warehouse  
  
GROUP BY {
      column-name [ WITH (DISTRIBUTED_AGG) ]  
    | column-expression
} [ ,...n ]

Arguments

column-expression

Określa kolumnę lub niegregowane obliczenie w kolumnie. Ta kolumna może należeć do tabeli, tabeli pochodnej lub widoku. Kolumna musi być wyświetlana w klauzuli FROM instrukcji SELECT, ale nie jest wymagana do wyświetlenia na liście SELECT.

Aby uzyskać prawidłowe wyrażenia, zobacz wyrażenie.

Kolumna musi być wyświetlana w klauzuli FROM instrukcji SELECT, ale nie jest wymagana do wyświetlenia na liście SELECT. Jednak każda kolumna tabeli lub widoku w dowolnym wyrażeniu innym niż wyrażeniu <wyboru> musi znajdować się na liście GROUP BY:

Dozwolone są następujące instrukcje:

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA, ColumnB;  
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA, ColumnB;  
SELECT ColumnA + ColumnB FROM T GROUP BY ColumnA + ColumnB;  
SELECT ColumnA + ColumnB + constant FROM T GROUP BY ColumnA, ColumnB;  

Następujące instrukcje są niedozwolone:

SELECT ColumnA, ColumnB FROM T GROUP BY ColumnA + ColumnB;  
SELECT ColumnA + constant + ColumnB FROM T GROUP BY ColumnA + ColumnB;  

Wyrażenie kolumny nie może zawierać:

• Alias kolumny zdefiniowany na liście SELECT. Może użyć aliasu kolumny dla tabeli pochodnej zdefiniowanej w klauzuli FROM.
• Kolumna typu tekst, ntekst lub obraz. Można jednak użyć kolumny tekstowej, ntekstu lub obrazu jako argumentu funkcji zwracającej wartość prawidłowego typu danych. Na przykład wyrażenie może używać funkcji SUBSTRING() i CAST(). Dotyczy to również wyrażeń w klauzuli HAVING.
• metody typów danych xml. Może zawierać funkcję zdefiniowaną przez użytkownika, która używa metod typów danych XML. Może zawierać kolumnę obliczeniową korzystającą z metod typów danych XML.
• A subquery. Zwracany jest błąd 144.
• Kolumna z indeksowanego widoku.

WYRAŻENIE KOLUMNY GRUPUJ WEDŁUG [ ,... n ]

Grupuje wyniki instrukcji SELECT zgodnie z wartościami na liście co najmniej jednego wyrażenia kolumny.

Na przykład to zapytanie tworzy tabelę Sales (Sprzedaż) z kolumnami Country (Kraj), Region (Region) i Sales (Sprzedaż). Wstawia cztery wiersze i dwa wiersze mają pasujące wartości dla pola Kraj i Region.

CREATE TABLE Sales ( Country VARCHAR(50), Region VARCHAR(50), Sales INT );

INSERT INTO sales VALUES (N'Canada', N'Alberta', 100);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 200);
INSERT INTO sales VALUES (N'Canada', N'British Columbia', 300);
INSERT INTO sales VALUES (N'United States', N'Montana', 100);

Tabela Sales (Sprzedaż) zawiera następujące wiersze:

To następne zapytanie grupuje kraj i region i zwraca sumę agregacji dla każdej kombinacji wartości.

SELECT Country, Region, SUM(sales) AS TotalSales
FROM Sales
GROUP BY Country, Region;

Wynik zapytania zawiera 3 wiersze, ponieważ istnieje 3 kombinacje wartości dla pola Kraj i Region. Suma TotalSales dla Kanady i Kolumbii Brytyjskiej jest sumą dwóch wierszy.

GRUPUJ WEDŁUG ZESTAWIENIA

Tworzy grupę dla każdej kombinacji wyrażeń kolumn. Ponadto "rzutuje" wyniki na sumy częściowe i sumy końcowe. W tym celu przechodzi od prawej do lewej, zmniejszając liczbę wyrażeń kolumn, w których tworzy grupy i agregacje.

Kolejność kolumn wpływa na dane wyjściowe ROLLUP i może mieć wpływ na liczbę wierszy w zestawie wyników.

Na przykład GROUP BY ROLLUP (col1, col2, col3, col4) tworzy grupy dla każdej kombinacji wyrażeń kolumn na poniższych listach.

• col1, col2, col3, col4
• col1, col2, col3, NULL
• col1, col2, NULL, NULL
• col1, NULL, NULL, NULL
• NULL, NULL, NULL, NULL — jest to suma końcowa

Przy użyciu tabeli z poprzedniego przykładu ten kod uruchamia operację GROUP BY ROLLUP zamiast prostej funkcji GROUP BY.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region);

Wynik zapytania ma te same agregacje co prosta funkcja GROUP BY bez funkcji ROLLUP. Ponadto tworzy sumy częściowe dla każdej wartości Country. Na koniec daje sumę końcową dla wszystkich wierszy. Wynik wygląda następująco:

GRUPUJ WEDŁUG MODUŁU ( )

FUNKCJA GROUP BY CUBE tworzy grupy dla wszystkich możliwych kombinacji kolumn. W przypadku modułu GROUP BY (a, b) wyniki mają grupy dla unikatowych wartości (a, b), (NULL, b), (a, NULL) i (NULL, NULL).

Korzystając z tabeli z poprzednich przykładów, ten kod uruchamia operację MODUŁU GROUP BY w kraju i regionie.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

Wynik zapytania zawiera grupy dla unikatowych wartości (Country, Region), (NULL, Region), (Country, NULL) i (NULL, NULL). Wyniki wyglądają następująco:

GRUPUJ WEDŁUG ZESTAWÓW GRUPOWANIA ( )

Opcja ZESTAWY GRUPOWANIA umożliwia łączenie wielu klauzul GROUP BY w jedną klauzulę GROUP BY. Wyniki są odpowiednikiem UNION ALL określonych grup.

Na przykład GROUP BY ROLLUP (Country, Region) i GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) zwróć te same wyniki.

Gdy zestawy GRUPOWANIA mają co najmniej dwa elementy, wyniki są połączeniem elementów. W tym przykładzie zwracany jest związek wyników ROLLUP i CUBE dla pola Kraj i Region.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( ROLLUP (Country, Region), CUBE (Country, Region) );

Wyniki są takie same jak to zapytanie, które zwraca połączenie dwóch instrukcji GROUP BY.

SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY ROLLUP (Country, Region)
UNION ALL
SELECT Country, Region, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY CUBE (Country, Region);

Program SQL nie konsoliduje zduplikowanych grup wygenerowanych dla listy ZESTAWÓW GRUPOWANIA. Na przykład w pliku GROUP BY ( (), CUBE (Country, Region) )oba elementy zwracają wiersz sumy końcowej, a oba wiersze zostaną wyświetlone w wynikach.

GRUPUJ WEDŁUG ()

Określa pustą grupę, która generuje sumę końcową. Jest to przydatne jako jeden z elementów zestawu GRUPOWANIA. Na przykład ta instrukcja zawiera łączną sprzedaż dla każdego kraju/regionu, a następnie daje sumę końcową dla wszystkich krajów/regionów.

SELECT Country, SUM(Sales) AS TotalSales
FROM Sales
GROUP BY GROUPING SETS ( Country, () );

GRUPUJ WEDŁUG WSZYSTKICH wyrażeń kolumn [ ,... n ]

Dotyczy: SQL Server i Azure SQL Database

Określa, aby uwzględnić wszystkie grupy w wynikach niezależnie od tego, czy spełniają kryteria wyszukiwania w klauzuli WHERE. Grupy, które nie spełniają kryteriów wyszukiwania, mają wartość NULL dla agregacji.

GRUPUJ WEDŁUG WSZYSTKICH:

• Nie jest obsługiwana w zapytaniach, które uzyskują dostęp do tabel zdalnych, jeśli w zapytaniu istnieje również klauzula WHERE.
• Nie powiedzie się w kolumnach, które mają atrybut FILESTREAM.

WYRAŻENIE KOLUMNY GRUPUJ WEDŁUG [ ,... n ] Z { CUBE | ROLLUP }

Dotyczy: SQL Server i Azure SQL Database

WITH (DISTRIBUTED_AGG)

Dotyczy: Azure Synapse Analytics and Analytics Platform System (PDW)

Wskazówka zapytania DISTRIBUTED_AGG wymusza system masowego przetwarzania równoległego (MPP) w celu ponownego dystrybuowania tabeli w określonej kolumnie przed przeprowadzeniem agregacji. Tylko jedna kolumna w klauzuli GROUP BY może mieć DISTRIBUTED_AGG wskazówkę zapytania. Po zakończeniu zapytania redystrybucyjna tabela zostanie porzucona. Oryginalna tabela nie jest zmieniana.

UWAGA: Wskazówki dotyczące zapytań DISTRIBUTED_AGG są udostępniane w celu zapewnienia zgodności z poprzednimi wersjami systemu platformy analizy (PDW) i nie poprawi wydajności większości zapytań. Domyślnie program MPP redystrybuuje już dane w razie potrzeby w celu zwiększenia wydajności agregacji.

General Remarks

Jak funkcja GROUP BY współdziała z instrukcją SELECT

SELECT list:

• Vector aggregates. Jeśli funkcje agregujące znajdują się na liście SELECT, funkcja GROUP BY oblicza wartość podsumowania dla każdej grupy. Są one nazywane agregacjami wektorów.
• Distinct aggregates. Agregacje AVG (DISTINCT column_name), COUNT (DISTINCT column_name) i SUM (DISTINCT column_name) są obsługiwane w zestawach ROLLUP, CUBE i GROUPING SETS.

WHERE clause:

• Program SQL usuwa wiersze, które nie spełniają warunków w klauzuli WHERE przed wykonaniem jakiejkolwiek operacji grupowania.

HAVING clause:

• Język SQL używa klauzuli having do filtrowania grup w zestawie wyników.

KLAUZULA ORDER BY:

• Użyj klauzuli ORDER BY, aby zamówić zestaw wyników. Klauzula GROUP BY nie porządkuje zestawu wyników.

NULL values:

• Jeśli kolumna grupowania zawiera wartości NULL, wszystkie wartości NULL są traktowane jako równe i są zbierane w jednej grupie.

Ograniczenia i ograniczenia

Dotyczy: SQL Server (począwszy od 2008 r.) i Azure Synapse Analytics

Maximum capacity

W przypadku klauzuli GROUP BY używającej funkcji ROLLUP, CUBE lub GROUPING SETS maksymalna liczba wyrażeń wynosi 32. Maksymalna liczba grup to 4096 (2¹²). Poniższe przykłady kończą się niepowodzeniem, ponieważ klauzula GROUP BY ma więcej niż 4096 grup.

• Poniższy przykład generuje zestawy grupowania 4097 (2¹² + 1) i zakończy się niepowodzeniem.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), b )  
  

• Poniższy przykład generuje 4097 (2¹² + 1) grup i zakończy się niepowodzeniem. Zarówno CUBE () , jak i () zestaw grupowania tworzą wiersz sumy końcowej i zduplikowane zestawy grupowania nie są wyeliminowane.

  GROUP BY GROUPING SETS( CUBE(a1, ..., a12), ())  
  

• W tym przykładzie użyto składni zgodnej z poprzednimi wersjami. Generuje ona zestawy grupowania 8192 (2¹³) i zakończy się niepowodzeniem.

  GROUP BY CUBE (a1, ..., a13)   
  GROUP BY a1, ..., a13 WITH CUBE   
  

  W przypadku klauzul GROUP BY zgodnych z poprzednimi wersjami, które nie zawierają modułu CUBE lub ROLLUP, liczba grup według elementów jest ograniczona przez rozmiary kolumn GROUP BY, zagregowane kolumny i wartości agregujące zaangażowane w zapytanie. Ten limit pochodzi z limitu 8060 bajtów w pośredniej tabeli roboczej wymaganej do przechowywania wyników zapytania pośredniego. Maksymalna liczba wyrażeń grupowania wynosząca 12 jest dozwolona, gdy określono moduł CUBE lub ROLLUP.

Obsługa funkcji ISO i ANSI SQL-2006 GROUP BY

Klauzula GROUP BY obsługuje wszystkie funkcje GROUP BY zawarte w standardzie SQL-2006 z następującymi wyjątkami składni:

• Zestawy grupowania nie są dozwolone w klauzuli GROUP BY, chyba że są częścią jawnej listy ZESTAWÓW GRUPOWANIA. Na przykład wartość ) jest dozwolona w warstwie Standardowa, GROUP BY Column1, (Column2, ...ColumnNale nie w języku Transact-SQL. Transact-SQL obsługuje i GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN))GROUP BY Column1, Column2, ... ColumnN, które są semantycznie równoważne. Są one semantycznie równoważne z poprzednim GROUP BY przykładem. Jest to możliwe, że GROUP BY Column1, (Column2, ...ColumnNelement ) może zostać błędnie zinterpretowany jako GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)), który nie jest semantycznie równoważny.

• Zestawy grupowania nie są dozwolone w zestawach grupowania. Na przykład jest dozwolona w standardzie SQL-2006, GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)) ale nie w języku Transact-SQL. Transact-SQL umożliwia lub GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn )GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ), które są semantycznie równoważne z pierwszym przykładem GROUP BY i mają bardziej wyraźną składnię.

• Funkcja GROUP BY [ALL/DISTINCT] jest dozwolona tylko w prostej klauzuli GROUP BY, która zawiera wyrażenia kolumn. Nie jest dozwolone w przypadku zestawów GRUPOWANIA, ZESTAWIEŃ, MODUŁÓW, Z MODUŁEM LUB KONSTRUKCJI ZESTAWIEŃ. ALL jest wartością domyślną i jest niejawna. Jest ona również dozwolona tylko w składni zgodnej z poprzednimi wersjami.

Porównanie obsługiwanych funkcji GROUP BY

W poniższej tabeli opisano funkcje GROUP BY, które są obsługiwane na podstawie wersji SQL i poziomu zgodności bazy danych.

Examples

A. Używanie prostej klauzuli GROUP BY

Poniższy przykład pobiera sumę dla każdej SalesOrderID z SalesOrderDetail tabeli. W tym przykładzie użyto bazy danych AdventureWorks.

SELECT SalesOrderID, SUM(LineTotal) AS SubTotal  
FROM Sales.SalesOrderDetail AS sod  
GROUP BY SalesOrderID  
ORDER BY SalesOrderID;  

B. Używanie klauzuli GROUP BY z wieloma tabelami

Poniższy przykład pobiera liczbę pracowników dla każdego City z Address tabeli dołączonych do EmployeeAddress tabeli. W tym przykładzie użyto bazy danych AdventureWorks.

SELECT a.City, COUNT(bea.AddressID) EmployeeCount  
FROM Person.BusinessEntityAddress AS bea   
    INNER JOIN Person.Address AS a  
        ON bea.AddressID = a.AddressID  
GROUP BY a.City  
ORDER BY a.City;  

C. Używanie klauzuli GROUP BY z wyrażeniem

Poniższy przykład pobiera łączną sprzedaż dla każdego roku przy użyciu DATEPART funkcji . To samo wyrażenie musi być obecne zarówno na liście, jak i GROUP BY w klauzuli SELECT .

SELECT DATEPART(yyyy,OrderDate) AS N'Year'  
    ,SUM(TotalDue) AS N'Total Order Amount'  
FROM Sales.SalesOrderHeader  
GROUP BY DATEPART(yyyy,OrderDate)  
ORDER BY DATEPART(yyyy,OrderDate);  

D. Używanie klauzuli GROUP BY z klauzulą HAVING

W poniższym przykładzie użyto klauzuli , HAVING aby określić, które grupy wygenerowane w klauzuli powinny zostać uwzględnione w GROUP BY zestawie wyników.

SELECT DATEPART(yyyy,OrderDate) AS N'Year'  
    ,SUM(TotalDue) AS N'Total Order Amount'  
FROM Sales.SalesOrderHeader  
GROUP BY DATEPART(yyyy,OrderDate)  
HAVING DATEPART(yyyy,OrderDate) >= N'2003'  
ORDER BY DATEPART(yyyy,OrderDate);  

Przykłady: Azure Synapse Analytics i Parallel Data Warehouse

E. Podstawowe użycie klauzuli GROUP BY

Poniższy przykład znajduje łączną kwotę dla wszystkich sprzedaży każdego dnia. Jeden wiersz zawierający sumę wszystkich sprzedaży jest zwracany dla każdego dnia.

-- Uses AdventureWorksDW  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales FROM FactInternetSales  
GROUP BY OrderDateKey ORDER BY OrderDateKey;  

F. Podstawowe użycie wskazówki DISTRIBUTED_AGG

W tym przykładzie użyto wskazówki DISTRIBUTED_AGG zapytania, aby wymusić na urządzeniu mieszanie tabeli w CustomerKey kolumnie przed wykonaniem agregacji.

-- Uses AdventureWorksDW  
  
SELECT CustomerKey, SUM(SalesAmount) AS sas  
FROM FactInternetSales  
GROUP BY CustomerKey WITH (DISTRIBUTED_AGG)  
ORDER BY CustomerKey DESC;  

G. Odmiany składni dla GRUPUJ WEDŁUG

Gdy lista wyboru nie ma agregacji, każda kolumna na liście wyboru musi być uwzględniona na liście GROUP BY. Kolumny obliczane na liście wyboru można wyświetlić, ale nie są wymagane, na liście GROUP BY. Oto przykłady składniowo prawidłowych instrukcji SELECT:

-- Uses AdventureWorks  
  
SELECT LastName, FirstName FROM DimCustomer GROUP BY LastName, FirstName;  
SELECT NumberCarsOwned FROM DimCustomer GROUP BY YearlyIncome, NumberCarsOwned;  
SELECT (SalesAmount + TaxAmt + Freight) AS TotalCost FROM FactInternetSales GROUP BY SalesAmount, TaxAmt, Freight;  
SELECT SalesAmount, SalesAmount*1.10 SalesTax FROM FactInternetSales GROUP BY SalesAmount;  
SELECT SalesAmount FROM FactInternetSales GROUP BY SalesAmount, SalesAmount*1.10;  

H. Używanie elementu GROUP BY z wieloma wyrażeniami GROUP BY

Poniższy przykład grupuje wyniki przy użyciu wielu GROUP BY kryteriów. Jeśli w każdej OrderDateKey grupie istnieją podgrupy, które mogą być zróżnicowane według DueDateKey, nowe grupowanie zostanie zdefiniowane dla zestawu wyników.

-- Uses AdventureWorks  
  
SELECT OrderDateKey, DueDateKey, SUM(SalesAmount) AS TotalSales   
FROM FactInternetSales
GROUP BY OrderDateKey, DueDateKey   
ORDER BY OrderDateKey;  

I. Używanie klauzuli GROUP BY z klauzulą HAVING

W poniższym przykładzie użyto klauzuli HAVING , aby określić grupy wygenerowane w GROUP BY klauzuli , która powinna zostać uwzględniona w zestawie wyników. W wynikach zostaną uwzględnione tylko te grupy z datami zamówienia w 2004 r. lub nowszym.

-- Uses AdventureWorks  
  
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales   
FROM FactInternetSales  
GROUP BY OrderDateKey   
HAVING OrderDateKey > 20040000   
ORDER BY OrderDateKey;  

See Also

GROUPING_ID (Transact-SQL)
GROUPING (Transact-SQL)
SELECT (Transact-SQL)
klauzula SELECT (Transact-SQL)