Adatok összegzése a GROUP BY használatával

Befejeződött

Bár az aggregátumfüggvények hasznosak az elemzéshez, érdemes lehet az adatokat részhalmazokba rendezni, mielőtt összeadja őket. Ebben a szakaszban megtanulhatja, hogyan végezheti el ezt a GROUP BY záradék használatával.

A GROUP BY záradék használata

Mint megtudta, a SELECT utasítás feldolgozása után a FROM záradék és a WHERE záradék kiértékelése után létrejön egy virtuális tábla. A virtuális tábla tartalma mostantól további feldolgozásra is elérhető. A GROUP BY záradék használatával a virtuális tábla tartalmát sorok csoportjaira oszthatja.

Sorok csoportosításához adjon meg egy vagy több elemet a GROUP BY záradékban:

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

A GROUP BY csoportokat hoz létre, és sorokat helyez el az egyes csoportokban a záradékban meghatározott elemek alapján.

A következő lekérdezés például csoportosított sorokat eredményez, a Sales.SalesOrderHeader táblában ügyfélazonosítónként egy sort. A GROUP BY folyamat egy másik módja, hogy a CustomerID értékével megegyező értékű összes sort csoportosítja a rendszer, és egyetlen eredménysorba adja vissza.

SELECT CustomerID
FROM Sales.SalesOrderHeader
GROUP BY CustomerID;

A fenti lekérdezés egyenértékű a következő lekérdezésével:

SELECT DISTINCT CustomerID
FROM Sales.SalesOrderHeader

Miután a GROUP BY záradék feldolgozása megtörtént, és minden sor egy csoporthoz lett társítva, a lekérdezés későbbi fázisainak összesítenie kell a FORRÁSsorok összesítő elemeit, amelyek a SELECT listában szerepelnek, de nem jelennek meg a GROUP BY listában. Ez a követelmény hatással lesz a SELECT és HAVING záradékok írására.

Mi a különbség a lekérdezés GROUP BY vagy DISTINCT használatával történő írása között? Ha csak a CustomerID különböző értékeit szeretné tudni, nincs különbség. A GROUP BY használatával azonban más elemeket is hozzáadhatunk a SELECT listához, amelyeket aztán összesítünk az egyes csoportokhoz.

A legegyszerűbb összesítő függvény a COUNT(*). Az alábbi lekérdezés az eredeti 830 forrássort veszi át a CustomerID-ből , és a CustomerID értékek alapján 89 csoportba csoportosítja őket. Minden különálló CustomerID-érték egy kimenetsort hoz létre a GROUP BY lekérdezésben

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

Minden CustomerID-érték esetében a lekérdezés összesíti és megszámolja a sorokat, így az eredmény azt mutatja, hogy a SalesOrderHeader tábla hány sora tartozik az egyes ügyfelekhez.

Ügyfélazonosító

Rendelésszám

1234

3

1005

1

Vegye figyelembe, hogy a GROUP BY nem garantálja az eredmények sorrendjét. A csoportosítási műveletet a lekérdezésfeldolgozó gyakran a csoportértékek sorrendjében adja vissza. Erre a viselkedésre azonban nem szabad támaszkodnia. Ha rendezni szeretné az eredményeket, kifejezetten tartalmaznia kell egy ORDER záradékot.

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

Ezúttal a rendszer a megadott sorrendben adja vissza az eredményeket:

Ügyfélazonosító

Rendelésszám

1005

1

1234

3

A SELECT utasítás záradékai a következő sorrendben lesznek alkalmazva:

1. tól
2. HOL
3. CSOPORTOSÍTÁS ALAPJÁN
4. MIUTÁN
5. SELECT
6. Rendezés szerint

Az oszlop aliasai a SELECT záradékban vannak hozzárendelve, amelyek a GROUP BY záradék után , de az ORDER BY záradék előtt fordulnak elő. Az ORDER BY záradékban hivatkozhat egy oszlop aliasára, de a GROUP BY záradékban nem. A következő lekérdezés érvénytelen oszlopnév-hibát eredményez:

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

A következő lekérdezés azonban sikeres lesz, és csoportosítja és rendezi az eredményeket az ügyfélazonosító szerint.

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

A GROUP BY hibáinak elhárítása

A SELECT utasításokban a GROUP BY kényelmes használatának gyakori akadálya az, hogy megértsük, miért fordul elő a következő típusú hibaüzenet:

Az Msg 8120, Level 16, State 1, Line 2 Column <column_name> érvénytelen a kiválasztási listában, mert nem szerepel sem összesítő függvényben, sem a GROUP BY záradékban.

A következő lekérdezés például azért engedélyezett, mert a SELECT lista minden oszlopa vagy a GROUP BY záradék oszlopa, vagy az egyes csoportokon működő összesítő függvény:

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

A következő lekérdezés hibát ad vissza, mert a PurchaseOrderNumber nem része a GROUP BY függvénynek, és nem használja összesítő függvényhez.

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

Ez a lekérdezés a következő hibát adja vissza:

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.

Érdemes ezt másképp is átgondolni. Ez a lekérdezés minden CustomerID-értékhez egy sort ad vissza. Ugyanannak a CustomerID-nek a sorai azonban eltérő PurchaseOrderNumber értékekkel rendelkezhetnek, tehát melyik értéket kell visszaadni?

Ha vevőazonosítónként és beszerzési rendelésenként szeretné megtekinteni a rendeléseket, az alábbiak szerint hozzáadhatja a PurchaseOrderNumber oszlopot a GROUP BY záradékhoz:

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

Ez a lekérdezés egy sort ad vissza minden egyes ügyfélhez és az egyes beszerzési rendelések kombinációihoz, valamint az adott kombináció rendeléseinek számát.