Shrnutí dat pomocí funkce GROUP BY

  • 3 min

Agregační funkce jsou sice užitečné pro analýzu, ale před jejich shrnutím můžete data uspořádat do podmnožina. V této části se dozvíte, jak toho dosáhnout pomocí klauzule GROUP BY.

Použití klauzule GROUP BY

Jak jste se dozvěděli, když se příkaz SELECT zpracuje, po vyhodnocení klauzule FROM a klauzule WHERE se vytvoří virtuální tabulka. Obsah virtuální tabulky je nyní k dispozici pro další zpracování. Klauzuli GROUP BY můžete použít k rozdělení obsahu této virtuální tabulky do skupin řádků.

Chcete-li seskupit řádky, zadejte jeden nebo více prvků v klauzuli GROUP BY:

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

GROUP BY vytvoří skupiny a umístí řádky do každé skupiny podle prvků zadaných v klauzuli.

Například následující dotaz bude mít za následek sadu seskupených řádků, jeden řádek na ID zákazníka v tabulce Sales.SalesOrderHeader . Dalším způsobem, jak se podívat na proces GROUP BY, je, že všechny řádky se stejnou hodnotou pro CustomerID se seskupí a vrátí se do jednoho řádku výsledků.

SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

Výše uvedený dotaz odpovídá následujícímu dotazu:

SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader

Jakmile bude klauzule GROUP BY zpracována a každý řádek je přidružený ke skupině, pozdější fáze dotazu musí agregovat všechny prvky zdrojových řádků, které jsou v seznamu SELECT, ale které se nezobrazují v seznamu GROUP BY. Tento požadavek bude mít vliv na to, jak píšete klauzule SELECT a HAVING.

Jaký je tedy rozdíl mezi zápisem dotazu pomocí GROUP BY nebo DISTINCT? Pokud chcete jen vědět, že jsou jedinečné hodnoty id zákazníka, neexistuje žádný rozdíl. Pomocí funkce GROUP BY ale můžeme do seznamu SELECT přidat další prvky, které se pak agregují pro každou skupinu.

Nejjednodušší agregační funkce je COUNT(*). Následující dotaz vezme původní 830 zdrojových řádků z ID zákazníka a seskupí je do 89 skupin na základě hodnot CustomerID. Každá jedinečná hodnota CustomerID vygeneruje jeden řádek výstupu v dotazu GROUP BY.

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

Pro každou hodnotu CustomerID dotaz agreguje a spočítá řádky, takže výsledek ukazuje, kolik řádků v tabulce SalesOrderHeader patří každému zákazníkovi.

CustomerID

OrderCount

1 234

3

1005

0

Všimněte si, že funkce GROUP BY nezaručuje pořadí výsledků. Výsledky se často vrací v důsledku způsobu, jakým se operace seskupení provádí procesorem dotazů, výsledky se vrátí v pořadí hodnot skupiny. Na toto chování byste se ale neměli spoléhat. Pokud potřebujete seřazené výsledky, musíte explicitně zahrnout klauzuli ORDER:

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

Tentokrát se výsledky vrátí v zadaném pořadí:

CustomerID

OrderCount

1005

0

1 234

3

Klauzule v příkazu SELECT se použijí v následujícím pořadí:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. VÝBĚR
  6. ORDER BY

Aliasy sloupců jsou přiřazeny v klauzuli SELECT, která nastane za klauzulí GROUP BY, ale před klauzulí ORDER BY. Alias sloupce můžete odkazovat v klauzuli ORDER BY, ale ne v klauzuli GROUP BY. Následující dotaz způsobí chybu neplatného názvu sloupce:

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

Následující dotaz však bude úspěšný, seskupí a seřadí výsledky podle ID zákazníka.

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

Řešení chyb GROUP BY

Běžná překážka, která se při používání funkce GROUP BY v příkazech SELECT stává překážkou, je pochopit, proč dochází k následujícímu typu chybové zprávy:

Položka Msg 8120, level 16, State 1, Line 2 Column <column_name> je neplatná v seznamu select, protože není obsažena v agregační funkci nebo klauzuli GROUP BY.

Například následující dotaz je povolený, protože každý sloupec v seznamu SELECT je buď sloupec v klauzuli GROUP BY, nebo agregační funkce fungující na každé skupině:

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

Následující dotaz vrátí chybu, protože Argument PurchaseOrderNumber není součástí funkce GROUP BY a nepoužívá se s agregační funkcí.

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

Tento dotaz vrátí chybu:

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.

Tady je další způsob, jak na to myslet. Tento dotaz vrátí jeden řádek pro každou hodnotu CustomerID . Ale řádky pro stejné ID zákazníka můžou mít různé hodnoty PurchaseOrderNumber , takže která z hodnot je ta, která by se měla vrátit?

Pokud chcete zobrazit objednávky podle ID zákazníka a na nákupní objednávku, můžete do klauzule GROUP BY přidat sloupec PurchaseOrderNumber následujícím způsobem:

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

Tento dotaz vrátí jeden řádek pro každého zákazníka a každou kombinaci nákupní objednávky spolu s počtem objednávek pro danou kombinaci.