Podsumowywanie danych za pomocą funkcji GROUP BY

  • 3 min

Chociaż funkcje agregujące są przydatne do analizy, możesz rozmieścić dane w podzestawy przed ich podsumowaniem. W tej sekcji dowiesz się, jak to zrobić przy użyciu klauzuli GROUP BY.

Używanie klauzuli GROUP BY

Jak już wiesz, po przetworzeniu instrukcji SELECT po ocenie klauzuli FROM i klauzuli WHERE tworzona jest tabela wirtualna. Zawartość tabeli wirtualnej jest teraz dostępna do dalszego przetwarzania. Możesz użyć klauzuli GROUP BY, aby podzielić zawartość tej tabeli wirtualnej na grupy wierszy.

Aby zgrupować wiersze, określ co najmniej jeden element w klauzuli GROUP BY:

GROUP BY <value1> [, <value2>, …]

Funkcja GROUP BY tworzy grupy i umieszcza wiersze w każdej grupie zgodnie z elementami określonymi w klauzuli .

Na przykład następujące zapytanie spowoduje utworzenie zestawu pogrupowanych wierszy, jednego wiersza na Identyfikator klienta w tabeli Sales.SalesOrderHeader . Innym sposobem przyjrzenia się procesowi GROUP BY jest to, że wszystkie wiersze o tej samej wartości identyfikatora CustomerID zostaną zgrupowane i zwrócone w jednym wierszu wyników.

SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Powyższe zapytanie jest równoważne następującemu zapytaniu:

SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader

Po przetworzeniu klauzuli GROUP BY i skojarzeniu każdego wiersza z grupą kolejne fazy zapytania muszą agregować wszystkie elementy wierszy źródłowych, które znajdują się na liście SELECT, ale nie są wyświetlane na liście GROUP BY. To wymaganie będzie miało wpływ na sposób pisania klauzul SELECT i HAVING.

Jaka jest różnica między pisaniem zapytania za pomocą funkcji GROUP BY lub DISTINCT? Jeśli wszystko, co chcesz wiedzieć, to odrębne wartości identyfikatora CustomerID, nie ma różnicy. Jednak za pomocą funkcji GROUP BY możemy dodać inne elementy do listy SELECT, które są następnie agregowane dla każdej grupy.

Najprostszą funkcją agregacji jest COUNT(*). Poniższe zapytanie pobiera oryginalne 830 wierszy źródłowych z identyfikatora CustomerID i grupuje je do 89 grup na podstawie wartości CustomerID. Każda unikatowa wartość CustomerID generuje jeden wiersz danych wyjściowych w zapytaniu GROUP BY

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Dla każdej wartości CustomerID zapytanie agreguje i zlicza wiersze, dlatego wynik pokazuje, ile wierszy w tabeli SalesOrderHeader należy do każdego klienta.

CustomerID (Identyfikator klienta)

OrderCount

1234

3

1005

1

Należy pamiętać, że funkcja GROUP BY nie gwarantuje kolejności wyników. Często w wyniku sposobu wykonywania operacji grupowania przez procesor zapytań wyniki są zwracane w kolejności wartości grupy. Nie należy jednak polegać na tym zachowaniu. Jeśli chcesz posortować wyniki, musisz jawnie dołączyć klauzulę ORDER:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY CustomerID;

Tym razem wyniki są zwracane w określonej kolejności:

CustomerID (Identyfikator klienta)

OrderCount

1005

1

1234

3

Klauzule w instrukcji SELECT są stosowane w następującej kolejności:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Aliasy kolumn są przypisywane w klauzuli SELECT, która występuje po klauzuli GROUP BY, ale przed klauzulą ORDER BY. Alias kolumny można odwołać się do klauzuli ORDER BY, ale nie w klauzuli GROUP BY. Następujące zapytanie spowoduje błąd nieprawidłowej nazwy kolumny:

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY Customer
ORDER BY Customer;

Jednak następujące zapytanie powiedzie się, grupuje i sortuje wyniki według identyfikatora klienta.

SELECT CustomerID AS Customer,
       COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY Customer;

Rozwiązywanie problemów z błędami GROUP BY

Powszechną przeszkodą, która utrudnia korzystanie z funkcji GROUP BY w instrukcjach SELECT, jest zrozumienie, dlaczego występuje następujący typ komunikatu o błędzie:

Msg 8120, Level 16, State 1, Line 2 Column <column_name> jest nieprawidłowy na liście select, ponieważ nie jest zawarty w funkcji agregującej lub klauzuli GROUP BY.

Na przykład następujące zapytanie jest dozwolone, ponieważ każda kolumna na liście SELECT jest kolumną w klauzuli GROUP BY lub funkcją agregacji działającą w każdej grupie:

SELECT CustomerID, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Następujące zapytanie zwróci błąd, ponieważ PurchaseOrderNumber nie jest częścią funkcji GROUP BY i nie jest używana z funkcją agregacji.

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

To zapytanie zwraca błąd:

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesOrderHeader.PurchaseOrderNumber' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Oto inny sposób, aby o tym pomyśleć. To zapytanie zwraca jeden wiersz dla każdej wartości CustomerID . Ale wiersze dla tego samego identyfikatora CustomerID mogą mieć różne wartości PurchaseOrderNumber , więc które z wartości są zwracane?

Jeśli chcesz zobaczyć zamówienia na identyfikator klienta i zamówienie zakupu, możesz dodać kolumnę PurchaseOrderNumber do klauzuli GROUP BY w następujący sposób:

SELECT CustomerID, PurchaseOrderNumber, COUNT(*) AS OrderCount
FROM Sales.SalesOrderHeader
GROUP BY CustomerID, PurchaseOrderNumber;

To zapytanie zwróci jeden wiersz dla każdego klienta i każdą kombinację zamówienia zakupu wraz z liczbą zamówień dla tej kombinacji.