in de les van vandaag gaat u meer leren over het groeperen van resultaten van uw query ‘ s met behulp van de SQL GROUP op clausule.
de doelstellingen van de les van vandaag zijn:
- leer hoe je resultaten groepeert met behulp van groep door
- gebruik geaggregeerde functies om berekeningen uit te voeren
- begrijp hoe je groepen filtert met behulp van de bezittende clausule
belangrijk! Volg alstublieft mee en doe de voorbeelden in uw database. Als je dit nog niet hebt gedaan, meld je dan aan voor mijn handleiding om aan de slag te gaan met SQL Server., U krijgt instructies over het installeren van de gratis tools en voorbeelddatabase.
SQL GROUP BY Clause
De SQL GROUP BY Clause wordt gebruikt om een rij uit te voeren over opgegeven kolomwaarden. Het wordt meestal gebruikt in combinatie met geaggregeerde functies zoals som of telling om waarden samen te vatten. In SQL groepen zijn unieke combinaties van velden. In plaats van elke rij in een tabel te retourneren, worden wanneer waarden worden gegroepeerd, alleen de unieke combinaties geretourneerd.
de groep door clausule wordt toegevoegd aan de SQL Statement na de WHERE clausule., Hier is een voorbeeld waar we een lijst van SalesOrderID, met uitzondering van hoeveelheden groter dan 100.
SELECT SalesOrderIDFROM Sales.SalesOrderDetailWHERE OrderQty <= 100GROUP BY SalesOrderID
Er zijn een paar dingen op te merken. Ten eerste worden de kolommen die we willen samenvatten weergegeven, gescheiden door komma ‘ s, in de groep per clausule. Ten tweede moet dezelfde lijst met kolommen worden weergegeven in de SELECT statement; anders mislukt de statement.,
wanneer dit statement wordt uitgevoerd, wordt niet elke gefilterde rij geretourneerd. Alleen unieke combinaties van SalesOrderID zijn opgenomen in het resultaat. Deze verklaring is zeer vergelijkbaar met
-- AnswerSELECT DISTINCT SalesOrderIDFROM Sales.SalesOrderDetailWHERE OrderQty <= 100
maar er is een belangrijk verschil., De afzonderlijke modifier stopt bij het uitvoeren van een unieke combinatie van rijen, terwijl we met het statement GROUP BY waarden kunnen berekenen op basis van de onderliggende gefilterde rijen voor elke unieke combinatie.
met andere woorden, met behulp van ons voorbeeld, met de groep door, kunnen we het aantal of OrderDetails per bestelling berekenen als volgt:
-- AnswerSELECT SalesOrderID, COUNT(SalesOrderID) recCountFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
COUNT is een voorbeeld van een aggregate functie, Deze geven de groep per statement zijn speciale waarde.
SQL Aggregate Functions
sommige functies, zoals SUM, worden gebruikt om berekeningen uit te voeren op een groep rijen, deze worden aggregate functions genoemd. In de meeste gevallen werken deze functies op een groep van waarden die worden gedefinieerd met behulp van de groep per clausule., Wanneer er geen groep per clausule is, is het algemeen begrepen dat de aggregate functie van toepassing is op alle gefilterde resultaten.
enkele van de meest voorkomende geaggregeerde functies zijn:
| AVG(expressie) | Bereken het gemiddelde van de expressie. |
| aantal (expressie) | aantal gevallen van niet-nulwaarden die door de expressie worden geretourneerd. |
| COUNT (*) | telt alle rijen in de opgegeven tabel., |
| MIN(expressie) | vindt de minimale expressiewaarde. |
| MAX(expressie) | vindt de maximale expressiewaarde. |
| Som (expressie) | Bereken de som van de expressie. |
Deze functies kunnen afzonderlijk worden gebruikt in combinatie met de groep per-clausule. Op hun eigen, ze werken over de hele tabel; echter, wanneer gebruikt met groep door, hun berekeningen worden “reset” elke keer dat de groepering verandert., Op deze manier fungeren ze als subtotalen.
eenvoudige Aggregaatfunctie
wanneer u de aggregaatfunctie gebruikt, kunt u het resultaat berekenen op alle waarden of afzonderlijke waarden. Om bijvoorbeeld alle salesorderdetails records te tellen, kunnen we de uitdrukking gebruiken:
SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
Dit resulteert in een telling van 121317 die wordt geretourneerd.
om de verschillende orders te tellen die de details vormen, gebruiken we het volgende:
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
de telling is nu 31465.,
SQL GROUP BY and Aggregate Functions
to aggregate means to make whole from individual parts. Aggregate functies zijn functies die op meer dan één rij werken om een resultaat te retourneren.
AVG en SUM
De functie SOM telt totalen van de geretourneerde waarden, op dezelfde manier berekent AVG het gemiddelde.
eens kijken of we het totale orderbedrag uit de OrderDetails kunnen berekenen., Uit vorige lessen weten we hoe we het totale bedrag voor elk detail moeten berekenen als:
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
aangezien we de aggregate functie kunnen toepassen op expressies, kunnen we een groep op OrderID Instellen om de totale prijs per bestelling te berekenen als
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
We kunnen zelfs Sorteren op het totaal om de hoogste orders eerst te krijgen
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC
op dezelfde manier kunnen we de gemiddelde orderdetail berekenen met behulp van AVG. Waarom probeer je het hieronder! Als je vast komt te zitten schakel het antwoord.
-- AnswerSELECT SalesOrderID, AVG(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderIDORDER BY TotalPrice DESC
voor nieuwsgierigen, aangezien een gemiddelde wordt berekend als de som van het monster gedeeld door het aantal monsters, is het gebruik van AVG in het bovenstaande statement hetzelfde als:
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
we hebben veel besproken in deze sectie., Hier zijn enkele belangrijke punten om te onthouden:
- een geaggregeerde functie kan een expressie evalueren zoals SUM (a + B)
- u moet geaggregeerde functies een alias geven, dus de kolomnamen zijn betekenisvol
- bij het werken met geaggregeerde functies en groeperen door, is het soms gemakkelijker om eerst na te denken over de details, dat is het schrijven van een eenvoudig SELECT statement, inspecteer de resultaten en voeg vervolgens de fancy stuff toe. Ik versla dit in het schrijven van betere vragen.
SQL COUNT
de COUNT functie wordt gebruikt wanneer u wilt weten hoeveel records er bestaan in een tabel of binnen een groep., COUNT (*) telt elk record in de groepering; terwijl COUNT (expressie) elk record telt waar het resultaat van de expressie niet null is. U kunt ook onderscheiden met tellen gebruiken om het aantal unieke waarden binnen een groep te vinden.
om het aantal Verkooporderdetaillijnen per bestelling te vinden
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
om het aantal unieke verkooporders per product te vinden
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
MIN en MAX
Gebruik MIN en MAX om de kleinste en grootste waarden te vinden, respectievelijk binnen een tabel of groep.,
om bijvoorbeeld de kleinste en grootste bestelde producthoeveelheden binnen een bestelling te vinden, probeer
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
U kunt ook de MIN of MAX waarde van een berekening vinden. Hier vinden we de hoogste bestelde producthoeveelheid binnen een product:
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
met clausule
De SQL met clausule wordt gebruikt om groepen te filteren op basis van de resultaten van de geaggregeerde functies. Dit maakt het mogelijk om problemen op te lossen, zoals het selecteren van alle bestellingen die meer dan twee order detail lijnen.,
dat voorbeeld ziet eruit als
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
als we alle orders groter dan $1000 willen vinden, schrijven we
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
merk op dat we de alias TotalPrice kunnen gebruiken in de ORDER BY-clausule, maar dan moet het hebben van een clausule de expressie gebruiken.
om thuis te hameren wil ik nog een laatste voorbeeld laten zien. Hier zul je zien dat het hebben statement een aggregaat functie bevat die niet in de SELECT lijst staat.,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
in de bovenstaande query krijgen we de totale prijs voor bestellingen waarbij het gemiddelde Verkooporderdetailbedrag groter is dan $500.00.
Eindafschrift over het hebben van
hoewel ze een soortgelijke functie uitvoeren, is er een belangrijk onderscheid tussen de WHERE-clausule en het hebben. De WHERE-clausule om afzonderlijke records te filteren; terwijl de HAVING-clausule filtert op de groepen.
om het recht in mijn hoofd te houden denk ik graag aan de WHERE-clausule die zijn werk doet voordat er groeperingen plaatsvinden, en dan de HAVING-clausule die het overneemt nadat de groepen zijn gevormd.,
oefeningen
Het is belangrijk om te oefenen! Gebruik de voorbeelddatabase om deze vragen te beantwoorden.
- HR wil een overzicht van het aantal actieve werknemers per functie. Welke SQL zou je gebruiken?
- Geef de MIN, Max en gemiddelde bestelde hoeveelheid voor elk product weer in Verkooporderdetails.
- som alle functietitels van werknemers op, en het aantal werknemers met een gemiddeld aantal ziekteverlof van minder dan of gelijk aan veertig.
- voor een functie die hierboven in #3 wordt geretourneerd, is de telling hetzelfde voor het resultaat van het overeenkomende functietitel antwoord # 1?
antwoorden zijn hier!,
Leave a Reply