Laten we leren samenvatten. Nee, dit zijn niet de resultaten van het bestuderen van SQL, maar de resultaten van de waarden van de kolommen van de databasetabellen. SQL-aggregaatfuncties werken op de waarden van een kolom om één enkele resulterende waarde te produceren. De meest gebruikte SQL-aggregaatfuncties zijn SUM, MIN, MAX, AVG en COUNT. Er moet onderscheid worden gemaakt tussen twee gevallen waarin aggregatiefuncties worden gebruikt. Ten eerste worden aggregatiefuncties afzonderlijk gebruikt en retourneren ze één enkele resulterende waarde. Ten tweede worden aggregatiefuncties gebruikt met de SQL GROUP BY-clausule, dat wil zeggen groeperen op velden (kolommen) om de resulterende waarden in elke groep te verkrijgen. Laten we eerst eens kijken naar gevallen waarin aggregatiefuncties zonder groepering worden gebruikt.
SQL SUM-functie
De SQL SOM-functie retourneert de som van de waarden in een databasetabelkolom. Het kan alleen worden toegepast op kolommen waarvan de waarden getallen zijn. De SQL-query's om de resulterende som te verkrijgen, beginnen als volgt:
SELECTEER SUM(COLUMN_NAME) ...
Deze expressie wordt gevolgd door FROM (TABLE_NAME), en vervolgens kan een voorwaarde worden opgegeven met behulp van de WHERE-clausule. Bovendien kan de kolomnaam worden voorafgegaan door DISTINCT, wat betekent dat alleen unieke waarden worden geteld. Standaard wordt rekening gehouden met alle waarden (hiervoor kunt u specifiek niet DISTINCT opgeven, maar ALL, maar het woord ALL is niet vereist).
Voorbeeld 1. Er is een bedrijfsdatabase met gegevens over de divisies en medewerkers. De Personeelstabel bevat ook een kolom met gegevens over de salarissen van werknemers. De selectie uit de tabel ziet er als volgt uit (om de afbeelding te vergroten, klik erop met de linkermuisknop):
Gebruik de volgende query om de som van alle salarissen te verkrijgen:
SELECTEER SUM(Salaris) VAN Personeel
Deze query retourneert de waarde 287664.63.
En nu. In de oefeningen beginnen we de taken al ingewikkelder te maken, waardoor ze dichter bij de taken komen die we in de praktijk tegenkomen.
SQL MIN-functie
De SQL MIN-functie werkt ook op kolommen waarvan de waarden getallen zijn en retourneert het minimum van alle waarden in de kolom. Deze functie heeft een syntaxis die vergelijkbaar is met die van de SUM-functie.
Voorbeeld 3. De database en tabel zijn hetzelfde als in voorbeeld 1.
We moeten het minimumloon voor werknemers van afdeling nummer 42 achterhalen. Schrijf hiervoor het volgende verzoek:
De query retourneert de waarde 10505.90.
En opnieuw oefening voor zelfoplossing. In deze en enkele andere oefeningen heb je niet alleen de Personeelstabel nodig, maar ook de Org-tabel, die gegevens bevat over de divisies van het bedrijf:
Voorbeeld 4. De Org-tabel wordt toegevoegd aan de Personeelstabel, die gegevens over de afdelingen van het bedrijf bevat. Druk het minimumaantal jaren af dat één werknemer op een afdeling in Boston heeft gewerkt.
SQL MAX-functie
De SQL MAX-functie werkt op dezelfde manier en heeft een vergelijkbare syntaxis, die wordt gebruikt wanneer u de maximale waarde van alle waarden in een kolom moet bepalen.
Voorbeeld 5.
U moet het maximale salaris van medewerkers van afdeling 42 weten. Schrijf hiervoor het volgende verzoek:
De query retourneert de waarde 18352.80
De tijd is gekomen oefeningen voor onafhankelijke oplossing.
Voorbeeld 6. We werken opnieuw met twee tabellen: Staff en Org. Geef de naam van de afdeling weer en de maximale waarde van de commissie die één medewerker ontvangt in de afdeling behorend tot de groep afdelingen (Divisie) Oost. Gebruik JOIN (tabellen samenvoegen) .
SQL AVG-functie
Wat wordt gezegd over de syntaxis van de eerder beschreven functies, geldt ook voor de SQL AVG-functie. Deze functie retourneert het gemiddelde van alle waarden in een kolom.
Voorbeeld 7. De database en tabel zijn hetzelfde als in de voorgaande voorbeelden.
Stel dat u de gemiddelde anciënniteit van werknemers op afdeling nummer 42 wilt weten. Schrijf hiervoor de volgende vraag:
Het resultaat is 6,33
Voorbeeld 8. Wij werken met één tafel - Personeel. Geef het gemiddelde salaris weer van medewerkers met 4 tot 6 jaar ervaring.
SQL COUNT-functie
De SQL COUNT-functie retourneert het aantal records in een databasetabel. Als u SELECT COUNT(COLUMN_NAME) ... opgeeft in de query, is het resultaat het aantal records, zonder rekening te houden met de records waarin de kolomwaarde NULL is (ongedefinieerd). Als u een asterisk als argument gebruikt en een query SELECT COUNT(*) ... start, is het resultaat het aantal records (rijen) van de tabel.
Voorbeeld 9. De database en tabel zijn hetzelfde als in de voorgaande voorbeelden.
U wilt het aantal medewerkers weten dat commissie ontvangt. Het aantal werknemers waarvan de Comm-kolomwaarden niet NULL zijn, wordt geretourneerd door de volgende query:
SELECTEER AANTAL(Comm) VAN Staf
Het resultaat zal 11 zijn.
Voorbeeld 10. De database en tabel zijn hetzelfde als in de voorgaande voorbeelden.
Als u het totale aantal records in de tabel wilt weten, gebruikt u een query met een asterisk als argument voor de functie AANTAL:
SELECTEER AANTAL(*) VAN Personeel
Het resultaat zal 17 zijn.
In de volgende oefening voor een onafhankelijke oplossing je zult een subquery moeten gebruiken.
Voorbeeld 11. Wij werken met één tafel - Personeel. Geef het aantal medewerkers op de planningsafdeling (Plains) weer.
Verzamel functies met SQL GROUP BY
Laten we nu eens kijken naar het gebruik van aggregatiefuncties samen met de SQL GROUP BY-instructie. De SQL GROUP BY-instructie wordt gebruikt om resultaatwaarden te groeperen op kolommen in een databasetabel.
Voorbeeld 12. Er is een database van het advertentieportaal. Het heeft een Advertentietabel met gegevens over advertenties die voor de week zijn ingediend. De kolom Categorie bevat gegevens over grote advertentiecategorieën (bijvoorbeeld Onroerend goed) en de kolom Onderdelen bevat gegevens over kleinere onderdelen die in de categorieën zijn opgenomen (de onderdelen Appartementen en Zomerhuizen zijn bijvoorbeeld onderdelen van de categorie Onroerend goed). De kolom Eenheden bevat gegevens over het aantal ingediende advertenties, en de kolom Geld bevat gegevens over de hoeveelheid geld die is ontvangen voor het indienen van advertenties.
| Categorie | Deel | Eenheden | Geld |
| Vervoer | Auto's | 110 | 17600 |
| Vastgoed | Appartementen | 89 | 18690 |
| Vastgoed | Dacha's | 57 | 11970 |
| Vervoer | motorfietsen | 131 | 20960 |
| Bouwmaterialen | Planken | 68 | 7140 |
| Elektrotechniek | TV's | 127 | 8255 |
| Elektrotechniek | Koelkasten | 137 | 8905 |
| Bouwmaterialen | Registratie | 112 | 11760 |
| Vrije tijd | Boeken | 96 | 6240 |
| Vastgoed | Thuis | 47 | 9870 |
| Vrije tijd | Muziek | 117 | 7605 |
| Vrije tijd | Spellen | 41 | 2665 |
Met behulp van de SQL GROUP BY-instructie kunt u het geldbedrag vinden dat u verdient door advertenties in elke categorie te plaatsen. Wij schrijven het volgende verzoek.
- Geaggregeerde functies worden op dezelfde manier gebruikt als veldnamen in een SELECT-instructie, met één uitzondering: ze nemen de veldnaam als argument. Met functies SOM En AVG Alleen numerieke velden kunnen worden gebruikt. Met functies AANTAL, MAX en MIN Zowel numerieke als karaktervelden kunnen worden gebruikt. Bij gebruik met tekenvelden MAX En MIN zal ze vertalen naar het equivalent van ASCII-code en ze in alfabetische volgorde verwerken. Sommige DBMS'en staan het gebruik van geneste aggregaten toe, maar dit is een afwijking van de ANSI-standaard met alle gevolgen van dien.
Je kunt bijvoorbeeld per vakgebied berekenen hoeveel studenten examens hebben afgelegd. Om dit te doen, moet u een zoekopdracht uitvoeren, gegroepeerd op het veld “Discipline” en als resultaat de naam van de discipline en het aantal rijen in de groep voor deze discipline weergeven. Het gebruik van het *-teken als argument voor de COUNT-functie betekent dat alle rijen in de groep worden geteld.
KIES R1. Discipline, COUNT(*)
GROEP OP R1. Discipline;
Resultaat:
SELECTEER R1.Discipline, COUNT (*)
WAAR R1. Evaluatie IS NIET NUL
GROEP OP R1. Discipline;
Resultaat:
zal niet worden opgenomen in de set van tupels vóór het groeperen, dus het aantal tupels in de groep voor de discipline “Informatietheorie” zal 1 minder zijn.
Een soortgelijk resultaat kan worden verkregen als u de query op de volgende manier schrijft:
KIES R1. Discipline, COUNT(R1. Beoordeling)
GROEP OP R1. Discipline;
Functie COUNT (ATTRIBUTENAAM) telt het aantal specifieke waarden in een groep, in tegenstelling tot een functie GRAAF(*), die het aantal rijen in een groep telt. In een groep met de discipline “Informatietheorie” zullen er inderdaad 4 regels zijn, maar slechts 3 specifieke waarden voor het attribuut “Evaluatie”.
Regels voor het verwerken van NULL-waarden in aggregatiefuncties
Als alle waarden in de kolom gelijk zijn NUL Bij het berekenen van het resultaat van de functie worden ze uitgesloten.
Als alle waarden in een kolom gelijk zijn NUL, Dat Max. Min. som Gem = NUL aantal = 0 (nul).
Als de tafel leeg is, aantal(*) = 0 .
U kunt ook aggregatiefuncties gebruiken zonder de bewerking vooraf te groeperen. In dat geval wordt de gehele relatie als één groep behandeld en kan voor deze groep één waarde per groep worden berekend.
Regels voor het interpreteren van aggregatiefuncties
Geaggregeerde functies kunnen in de uitvoerlijst worden opgenomen en vervolgens op de hele tabel worden toegepast.
KIES MAX (Evaluatie) uit R1 geeft tijdens de sessie het maximale oordeel;
SELECTEER SOM (Score) uit R1 geeft de som van alle cijfers voor de sessie;
SELECTEER AVG(Rating) uit R1 geeft een gemiddelde score voor de gehele sessie.
2; Resultaat: " breedte = "640" Als we weer naar de database “Session” kijken (tabel R1), vinden we het aantal met succes afgelegde examens:
SELECTEER AANTAL(*) Als Afgeleverd _ examens
WAAR Score 2;
Resultaat:
Argumenten voor het aggregeren van functies kunnen individuele tabelkolommen zijn. Om bijvoorbeeld het aantal afzonderlijke waarden van een bepaalde kolom in een groep te berekenen, moet u het trefwoord DISTINCT samen met de kolomnaam gebruiken. Laten we het aantal verschillende cijfers berekenen dat in elke discipline is ontvangen:
SELECTEER R1.Discipline, COUNT (ONDERSCHEIDEN R1.Evaluatie)
WAAR R1. Evaluatie IS NIET NUL
GROEP OP R1. Discipline;
Resultaat:
Hetzelfde resultaat wordt verkregen als we de expliciete voorwaarde in het WHERE-gedeelte uitsluiten, in welk geval de query er als volgt uit zal zien:
KIES R1. Discipline, COUNT(DISTINCT R1. Beoordeling)
GROEP OP R1. Discipline;
Functie AANTAL (ONDERSCHEIDEN R1.Evaluatie) telt slechts zeker verscheidene betekenissen.
Om in dit geval het gewenste resultaat te verkrijgen, is het noodzakelijk om een voorlopige transformatie uit te voeren van het gegevenstype van de kolom "Rating", waardoor het naar een reëel type wordt gebracht. Het resultaat van de berekening van het gemiddelde zal dan geen resultaat zijn. geheel getal. In dit geval ziet het verzoek er als volgt uit:
2 Groepeer op R2. Groep, R1. Discipline; Hier converteert de functie CAST() de kolom Score naar een geldig gegevenstype. "breedte = "640" Selecteer R2.Groep, R1.Discipline,Count(*) als Totaal, AVG(cast(Score als decimaal(3,1))) als Gemiddelde_score
Van R1,R2
waar R1. Volledige naam = R2. Volledige naam en R1. score is niet nul
en R1. Score 2
Groepeer op R2. Groep, R1. Discipline;
Hier is de functie VORM() Converteert de kolom Beoordeling naar een geldig gegevenstype.
U kunt geen aggregatiefuncties gebruiken in een WHERE-clausule, omdat de voorwaarden in deze sectie worden geëvalueerd in termen van één enkele rij, en aggregatiefuncties worden geëvalueerd in termen van groepen rijen.
Met de GROUP BY-clausule kunt u een subset van de waarden in een bepaald veld definiëren in termen van een ander veld en een aggregatiefunctie op de subset toepassen. Dit maakt het mogelijk om velden en aggregatiefuncties in één enkele SELECT-clausule te combineren. Aggregaatfuncties kunnen zowel worden gebruikt in de expressie voor het uitvoeren van de resultaten van de SELECT-regel, als in de expressie voor de voorwaarde voor het verwerken van gevormde HAVING-groepen. In dit geval wordt elke aggregatiefunctie berekend voor elke geselecteerde groep. De waarden die worden verkregen uit de berekening van aggregatiefuncties kunnen worden gebruikt om de overeenkomstige resultaten weer te geven of om de selectie van groepen te conditioneren.
Laten we een query maken die groepen weergeeft waarin meer dan één slecht cijfer is behaald in één discipline tijdens de examens:
1; Resultaat: " breedte = "640" SELECTEER R2. Groep
VAN R1,R2
WAAR R1. Volledige naam = R2. Volledige naam EN
R1.Score = 2
GROEPEREN OP R2.Groep, R1.Discipline
MET telling(*) 1;
Resultaat:
We hebben een database “Bank”, bestaande uit één tabel F, waarin de relatie F is opgeslagen met informatie over rekeningen in filialen van een bepaalde bank:
Vind het totale saldo op rekeningen in filialen. U kunt voor elk van deze een afzonderlijke query uitvoeren door SUM (Remaining) uit de tabel voor elke vertakking te selecteren, maar met de GROUP BY-bewerking kunt u ze allemaal in één opdracht plaatsen:
SELECTEER Tak , SOM( Rest )
GROEPEREN OP Filiaal;
GROEP DOOR past aggregatiefuncties onafhankelijk toe op elke groep die wordt geïdentificeerd door de waarde van het veld Vertakking. De groep bestaat uit rijen met dezelfde Branch-veldwaarde en de functie SOM wordt voor elke dergelijke groep afzonderlijk toegepast, d.w.z. dat het totale rekeningsaldo voor elk filiaal afzonderlijk wordt berekend. De waarde van het veld waarop het van toepassing is GROEP DOOR heeft per definitie slechts één waarde per uitvoergroep, net als het resultaat van een aggregatiefunctie.
5.000; Argumenten in een HAVING-clausule volgen dezelfde regels als in een SELECT-clausule die GROUP BY gebruikt. Ze moeten één waarde per uitvoergroep hebben. "breedte = "640" Laten we aannemen dat we alleen die filialen selecteren waarvan het totale rekeningsaldo hoger is dan $ 5.000, evenals de totale saldi voor de geselecteerde filialen. Om resultaten weer te geven voor filialen met een totaal saldo van meer dan $ 5.000, moet u de HAVING-clausule gebruiken. De HAVING-clausule specificeert de criteria die worden gebruikt om bepaalde groepen uit de uitvoer te verwijderen, net zoals de WHERE-clausule dat doet voor individuele rijen.
Het juiste commando zou zijn:
SELECTEER vertakking, SUM(resterend)
GROEP DOOR Tak
SOM HEBBEN ( Rest ) 5 000;
Argumenten in een zin HEBBEN volg dezelfde regels als in de zin SELECTEER waar gebruikt GROEP DOOR. Ze moeten één waarde per uitvoergroep hebben.
Het volgende commando is verboden:
SELECTEER Tak,SUM(Resterend)
GROEP OP Filiaal
MET openingsdatum = 27-12-2004 ;
Veld Openingsdatum kan niet in een zin worden gebruikt HEBBEN, omdat het meer dan één waarde per uitvoergroep kan hebben. Om een dergelijke situatie te voorkomen, de suggestie HEBBEN mag alleen verwijzen naar de geselecteerde aggregaten en velden GROEP DOOR. Er is een correcte manier om de bovenstaande query uit te voeren:
SELECTEER Tak,SUM(Resterend)
WHEREOpenDatum = '27/12/2004'
GROEPEREN OP Filiaal;
De betekenis van deze vraag is als volgt: zoek de som van de saldi voor elk filiaal van rekeningen geopend op 27 december 2004.
Zoals eerder vermeld, kan HAVING alleen argumenten accepteren die één waarde per uitvoergroep hebben. In de praktijk komen verwijzingen naar aggregatiefuncties het meest voor, maar velden die met GROUP BY zijn geselecteerd, zijn ook geldig. We willen bijvoorbeeld de totale saldi zien op de rekeningen van filialen in Sint-Petersburg, Pskov en Uryupinsk:
SELECTEER vertakking, SUM(resterend)
VAN F,Q
WAAR F. Tak = Q. Tak
GROEP OP Filiaal
HAVING Branch IN (‘St. Petersburg’, ‘Pskov’, ‘Uryupinsk’);
100.000; Als het totale saldo meer dan $100.000 bedraagt, zien we dit in de resulterende relatie, anders krijgen we een lege relatie. "breedte = "640"
Daarom kunnen alleen de specificaties van de kolommen die zijn opgegeven als groeperingskolommen in de GROUP BY-clausule rechtstreeks worden gebruikt in rekenkundige predicaatuitdrukkingen die zijn opgenomen in de selectieclausule van de HAVING-clausule. De overige kolommen kunnen alleen worden gespecificeerd binnen de specificaties van de aggregatiefuncties COUNT, SUM, AVG, MIN en MAX, die in dit geval een aggregatiewaarde berekenen voor de gehele groep rijen. Het resultaat van het uitvoeren van de HAVING-clausule is een gegroepeerde tabel die alleen die groepen rijen bevat waarvoor het resultaat van het berekenen van de selectievoorwaarde in de HAVING-clausule TRUE is. Als er in het bijzonder een HAVING-clausule aanwezig is in een query die geen GROUP BY bevat, zal het resultaat van de uitvoering ervan ofwel een lege tabel zijn, ofwel het resultaat van het uitvoeren van de voorgaande secties van de tabelexpressie, behandeld als een enkele groep zonder kolommen te groeperen. Laten we eens kijken naar een voorbeeld. Laten we zeggen dat we het totale aantal saldi voor alle takken willen weergeven, maar alleen als dit meer dan $100.000 bedraagt. In dit geval bevat onze query geen groeperingsbewerkingen, maar een HAVING-sectie en ziet er als volgt uit:
SELECTEER SOM( Rest )
SOM HEBBEN( Rest ) 100 000;
Als het totale saldo meer dan $100.000 bedraagt, zien we dit in de resulterende relatie, anders krijgen we een lege relatie.
door de waarde van de kolom Discipline. We krijgen 4 groepen waarvoor we enkele groepswaarden kunnen berekenen, zoals het aantal tupels in de groep, de maximale of minimale waarde van de Score-kolom. | Functie | Resultaat |
|---|---|
| GRAAF | Aantal rijen of niet-lege veldwaarden die door de query zijn geselecteerd |
| SOM | De som van alle geselecteerde waarden voor dit veld |
| AVG | Het rekenkundig gemiddelde van alle geselecteerde waarden voor dit veld |
| MIN | De kleinste van alle geselecteerde waarden voor dit veld |
| MAX | De grootste van alle geselecteerde waarden voor dit veld |
| R1 | |||
|---|---|---|---|
| Volledige naam | Discipline | Cijfer | |
| Groep 1 | Petrov F.I. | Databases | 5 |
| Sidorov K.A. | Databases | 4 | |
| Mironov A.V. | Databases | 2 | |
| Stepanova K.E. | Databases | 2 | |
| Krylova T.S. | Databases | 5 | |
| Vladimirov V.A. | Databases | 5 | |
| Groep 2 | Sidorov K.A. | Informatie theorie | 4 |
| Stepanova K.E. | Informatie theorie | 2 | |
| Krylova T.S. | Informatie theorie | 5 | |
| Mironov A.V. | Informatie theorie | Nul | |
| Groep 3 | Trofimov P.A. | Netwerken en telecommunicatie | 4 |
| Ivanova E.A. | Netwerken en telecommunicatie | 5 | |
| Utkina N.V. | Netwerken en telecommunicatie | 5 | |
| Groep 4 | Vladimirov V.A. | Engelse taal | 4 |
| Trofimov P.A. | Engelse taal | 5 | |
| Ivanova E.A. | Engelse taal | 3 | |
| Petrov F.I. | Engelse taal | 5 | |
Geaggregeerde functies worden op dezelfde manier gebruikt als veldnamen in een SELECT-instructie, maar met één uitzondering: ze nemen de veldnaam als argument. Alleen numerieke velden kunnen worden gebruikt met de functies SUM en AVG. Zowel numerieke als tekenvelden kunnen worden gebruikt met de functies COUNT, MAX en MIN. Bij gebruik met tekenvelden vertalen MAX en MIN deze naar de equivalente ASCII-code en verwerken ze in alfabetische volgorde. Sommige DBMS'en staan het gebruik van geneste aggregaten toe, maar dit is een afwijking van de ANSI-standaard met alle gevolgen van dien.
Je kunt bijvoorbeeld per vakgebied berekenen hoeveel studenten examens hebben afgelegd. Om dit te doen, moet u een query uitvoeren, gegroepeerd op het veld "Discipline", en als resultaat de naam van de discipline en het aantal rijen in de groep voor deze discipline weergeven. Het gebruik van het *-teken als argument voor de COUNT-functie betekent dat alle rijen in de groep worden geteld.
SELECTEER R1.Discipline, COUNT(*) UIT R1 GROEP OP R1.Discipline
Resultaat:
Als we het aantal mensen willen tellen dat geslaagd is voor het examen in welke discipline dan ook, moeten we onzekere waarden uitsluiten van de oorspronkelijke verhouding voordat we gaan groeperen. In dit geval ziet het verzoek er als volgt uit:
We krijgen het resultaat:
In dit geval de lijn met de leerling
| Mironov A.V. | Informatie theorie | Nul |
|---|
zal niet in de set tupels vallen vóór het groeperen, dus het aantal tupels in de groep dat moet worden gedisciplineerd " Informatie theorie"zal er 1 minder zijn.
Kan worden gebruikt geaggregeerde functies ook zonder de voorgroeperingsoperatie, in welk geval de gehele relatie als één groep wordt beschouwd en voor deze groep één waarde per groep kan worden berekend.
Als we weer naar de database “Session” gaan (tabellen R1, R2, R3), vinden we het aantal met succes afgelegde examens:
Dit is natuurlijk anders dan het selecteren van een veld, omdat er altijd één enkele waarde wordt geretourneerd, ongeacht hoeveel rijen er in de tabel staan. Argument geaggregeerde functies Er kunnen afzonderlijke tabelkolommen zijn. Maar om bijvoorbeeld het aantal afzonderlijke waarden van een bepaalde kolom in een groep te berekenen, moet u het trefwoord DISTINCT samen met de kolomnaam gebruiken. Laten we het aantal verschillende cijfers berekenen dat in elke discipline is ontvangen:
Resultaat:
Het resultaat kan de groeperingsveldwaarde en meerdere bevatten geaggregeerde functies en bij groeperingsvoorwaarden kunt u meerdere velden gebruiken. In dit geval worden groepen gevormd volgens een reeks gespecificeerde groeperingsvelden. Geaggregeerde functiebewerkingen kunnen worden toegepast om meerdere brontabellen samen te voegen. Laten we bijvoorbeeld de vraag stellen: bepaal per groep en per vakgebied het aantal studenten dat het examen met goed gevolg heeft afgelegd en de gemiddelde score in het vakgebied.
Resultaat:
Wij kunnen het niet gebruiken geaggregeerde functies in de WHERE-clausule omdat predikaten worden geëvalueerd in termen van een enkele regel, en geaggregeerde functies- in termen van groepen lijnen.
Met de GROUP BY-clausule kunt u een subset van de waarden in een bepaald veld definiëren in termen van een ander veld en een aggregatiefunctie op de subset toepassen. Hierdoor is het mogelijk om velden en geaggregeerde functies in één enkele SELECT-clausule. Geaggregeerde functies kan zowel worden gebruikt in de expressie voor het uitvoeren van de resultaten van de SELECT-regel, als in de expressie voor de verwerkingsvoorwaarde van de gegenereerde HAVING-groepen. In dit geval wordt elke aggregatiefunctie berekend voor elke geselecteerde groep. Waarden verkregen uit de berekening geaggregeerde functies, kan worden gebruikt om de bijbehorende resultaten weer te geven of om de selectie van groepen te conditioneren.
Laten we een query maken die groepen weergeeft waarin meer dan één slecht cijfer is behaald in één discipline tijdens de examens:
In de toekomst zullen we bijvoorbeeld niet werken met de database “Session”, maar met de database “Bank”, bestaande uit één tabel F, waarin de relatie F wordt opgeslagen met informatie over rekeningen in filialen van een bepaalde bank:
F = (N, volledige naam, filiaal, openingsdatum, sluitingsdatum, saldo); Q = (filiaal, stad);
omdat het op deze basis mogelijk is om het werk duidelijker te illustreren met geaggregeerde functies en groeperingen.
Stel dat we bijvoorbeeld het totale saldo van bankrekeningen willen vinden. U kunt voor elk van deze een afzonderlijke query maken door SUM(Balans) te selecteren in de tabel voor elke vertakking. Met GROUP BY kunt u ze echter allemaal in één opdracht plaatsen:
SELECTEER Tak, SOM (Resterend) VAN F GROEP OP Tak;
GROUP BY is van toepassing geaggregeerde functies onafhankelijk voor elke groep die is gedefinieerd met behulp van de veldwaarde Branch. De groep bestaat uit rijen met dezelfde Branch-veldwaarde, en
De ISO-norm definieert de volgende vijf aggregatiefuncties:
GRAAF– retourneert het aantal waarden in de opgegeven kolom;
SOM– retourneert de som van de waarden in de opgegeven kolom;
AVG– retourneert de gemiddelde waarde in de opgegeven kolom;
MIN– retourneert de minimumwaarde in de opgegeven kolom;
MAX– retourneert de maximale waarde in de opgegeven kolom.
Al deze functies werken op waarden in een enkele tabelkolom en retourneren een enkele waarde. De functies COUNT, MIN en MAX zijn van toepassing op zowel numerieke als niet-numerieke velden, terwijl de functies SUM en AVG alleen voor numerieke velden kunnen worden gebruikt. Met uitzondering van COUNT(*), worden bij het evalueren van de resultaten van een functie eerst alle nulwaarden geëlimineerd en vervolgens wordt de vereiste bewerking alleen toegepast op de resterende niet-lege waarden in de kolom. De optie COUNT(*) is een speciaal gebruik van de functie COUNT: het doel ervan is om alle rijen in een tabel te tellen, ongeacht of deze null-waarden, duplicaten of andere waarden bevat. Als u dubbele waarden wilt elimineren voordat u een aggregatiefunctie gebruikt, moet u de kolomnaam in de functiedefinitie vooraf laten gaan door het trefwoord DISTINCT. De ISO-standaard staat het gebruik van het trefwoord ALL toe om expliciet aan te geven dat uitsluiting van dubbele waarden niet vereist is, hoewel dit trefwoord standaard wordt geïmpliceerd als er geen andere kwalificatie is opgegeven. Het trefwoord DISTINCT heeft geen betekenis voor de functies MIN en MAX. Het gebruik ervan kan echter de resultaten van de SUM- en AVG-functies beïnvloeden, dus u moet van tevoren overwegen of dit in elk specifiek geval aanwezig moet zijn. Bovendien kan het trefwoord DISTINCT slechts één keer per verzoek worden opgegeven.
Opgemerkt moet worden dat aggregatiefuncties alleen kunnen worden gebruikt in de SELECT-lijst en in de HAVING-clausule (zie Paragraaf 5.3.4). In alle andere gevallen is het gebruik van deze functies onaanvaardbaar. Als de SELECT-lijst een aggregatiefunctie bevat en de querytekst geen GROUP BY-clausule bevat waarmee de gegevens kunnen worden gegroepeerd, kan geen enkel element in de SELECT-lijst kolomverwijzingen bevatten, tenzij de kolom wordt gebruikt als parameter voor de aggregatiefunctie. De volgende query is bijvoorbeeld onjuist:
SELECTEERpersoneelNee,GRAAF (salaris)
VANPersoneel;
De fout is dat er geen constructie in dit verzoek zit GROEP DOOR en de kolom staffNo in de SELECT-lijst is toegankelijk zonder gebruik te maken van een aggregatiefunctie.
Voorbeeld 13: De functie COUNT(*) gebruiken.Bepaal hoeveel huurwoningen een huurprijs hebben van meer dan € 350 per maand,
SELECTEER AANTAL(*) AS-telling
VANPropertyForRent
WAARhuur > 350;
De beperking om alleen huurwoningen mee te tellen waarvan de huur meer dan £350 per maand bedraagt, wordt geïmplementeerd door het gebruik van een WHERE-clausule. Het totaal aantal huurwoningen dat aan een bepaalde voorwaarde voldoet, kan worden bepaald met behulp van de aggregatiefunctie AANTAL. De resultaten van de zoekopdracht worden weergegeven in een tabel. 23.
Tabel 23
| graaf |
Voorbeeld 14. De functie AANTAL(DISTINCT) gebruiken.Bepaal hoeveel verschillende huurwoningen er in mei 2001 door klanten zijn bekeken.
SELECTEER AANTAL(ONDERSCHEIDpropertyNo) AS-telling
VANBekijken
Ook hier wordt het beperken van de zoekresultaten tot alleen de huurwoningen die in mei 2001 zijn geïnspecteerd bereikt door gebruik te maken van de WHERE-clausule. Het totale aantal onderzochte objecten dat aan de opgegeven voorwaarde voldoet, kan worden bepaald met behulp van de aggregatiefunctie AANTAL. Omdat hetzelfde object echter meerdere keren door verschillende clients kan worden bekeken, is het noodzakelijk om het trefwoord DISTINCT in de functiedefinitie op te geven - hierdoor kunnen dubbele waarden van de berekening worden uitgesloten. De resultaten van de zoekopdracht worden weergegeven in een tabel. 24.
Tabel 24
Voorbeeld 16. Gebruik van de MIN-, MAXnAVG-functies.Bereken het minimum, maximum en gemiddelde loon.
SELECTEER MIN(salaris) ALS min, MAX(salaris) ALS maximaal, AVG(salaris) ALS gem
VANPersoneel;
In dit voorbeeld moet u informatie over al het personeel van het bedrijf verwerken, dus u hoeft de WHERE-clausule niet te gebruiken. De vereiste waarden kunnen worden berekend met behulp van de MIN-, MAX- en AVG-functies die zijn toegepast op de salariskolom van de Personeelstabel. De resultaten van de zoekopdracht worden weergegeven in een tabel. 26.
Tabel 26.
Resultaat van het verzoek
| min | maximaal | gem |
| 9000.00 | 30000.00 | 17000.00 |
Resultaten groeperen (GROUP BY-construct). De bovenstaande voorbeelden van samenvattingsgegevens zijn vergelijkbaar met de samenvattingsregels die doorgaans aan het einde van rapporten te vinden zijn. Als gevolg hiervan worden alle gedetailleerde rapportgegevens gecomprimeerd in één samenvattingsregel. Vaak is het echter nodig om subtotalen in rapporten te genereren. Hiervoor kan een GROUP BY-clausule worden opgegeven in een SELECT-instructie. Er wordt een query aangeroepen die een GROUP BY-clausule bevat groeperingsverzoek, omdat het de gegevens groepeert die het resultaat zijn van een SELECT-bewerking en vervolgens een enkele totaalrij creëert voor elke individuele groep. Kolommen die in de GROUP BY-clausule worden vermeld, worden aangeroepen gegroepeerde kolommen. De ISO-standaard vereist dat de SELECT- en GROUP BY-clausules nauw verwant zijn. Wanneer u de GROUP BY-clausule in een SELECT-instructie gebruikt, moet elk lijstelement in de SELECT-lijst het volgende hebben de enige betekenis voor de hele groep. Bovendien kan de SELECT-clausule alleen de volgende typen elementen bevatten:
Kolomnamen;
aggregatiefuncties;
Constanten;
Expressies die combinaties van de hierboven genoemde elementen bevatten.
Alle kolomnamen die in de SELECT-lijst voorkomen, moeten ook voorkomen in de GROUP BY-clausule, tenzij de kolomnaam alleen in een aggregatiefunctie wordt gebruikt. Het tegenovergestelde is niet altijd waar: de GROUP BY-clausule kan kolomnamen bevatten die niet in de SELECT-lijst staan. Als de WHERE-clausule wordt gebruikt in combinatie met de GROUP BY-clausule, wordt deze eerst verwerkt en worden alleen de rijen die aan de zoekvoorwaarde voldoen, gegroepeerd. De ISO-norm specificeert dat bij het groeperen alle ontbrekende waarden als gelijk worden behandeld. Als twee tabelrijen in dezelfde groeperingskolom NULL-waarden bevatten en identieke waarden in alle andere niet-null-groeperingskolommen, worden ze in dezelfde groep geplaatst.
Voorbeeld 17: Het GROUP BY-construct gebruiken.Bepaal het aantal personeelsleden dat op elk van de afdelingen van het bedrijf werkt, evenals hun totale loon.
SELECTEERvestigingNee, GRAAF(personeelNee) ALS graaf, SOM(salaris) ALS som
VANPersoneel
GROEP DOORfiliaalnr
BESTEL DOORfiliaalnr;
Het is niet nodig om de namen van de staffNo- en salariskolommen op te nemen in de GROUP BY-elementenlijst, omdat deze alleen verschijnen in een SELECT-lijst met aggregatiefuncties. Tegelijkertijd is de kolom branchNo in de lijst van de SELECT-clausule niet gekoppeld aan een aggregatiefunctie en moet deze om deze reden worden opgegeven in de GROUP BY-clausule. De resultaten van de zoekopdracht worden weergegeven in een tabel. 27.
Tabel 27
Resultaat van het verzoek
| filiaalnr | Graaf | Som |
| B003 | 54000.00 | |
| B005 | 39000.00 | |
| B007 | 9000.00 |
Conceptueel worden bij het verwerken van dit verzoek de volgende acties uitgevoerd:
1. De rijen van de Personeelstabel worden in groepen verdeeld volgens de waarden in de kolom Bedrijfsafdelingsnummer. Binnen elke groep zijn er gegevens over al het personeel van een van de afdelingen van het bedrijf. In ons voorbeeld worden drie groepen gemaakt, zoals weergegeven in Fig. 1.
2. Voor elke groep wordt het totale aantal rijen berekend, gelijk aan het aantal werknemers op de afdeling, evenals de som van de waarden in de salariskolom, wat de som is van de salarissen van alle werknemers in de afdeling die ons interesseert. Er wordt vervolgens één overzichtsrij gegenereerd voor de hele groep bronrijen.
3. De resulterende rijen van de resulterende tabel worden gesorteerd in oplopende volgorde van het filiaalnummer dat is opgegeven in de kolom branchNo.
| filiaalnr | personeelnr | Salaris |
| В00З | SG37 | 12000.00 |
| В00З | SG14 | 18000.00 |
| В00З | SG5 | 24000.00 |
| B005 | SL21 | 30000.00 |
| B005 | SL41 | 9000.00 |
| B007 | SA9 | 9000.00 |
| COUNT(personeelnr) | SOM(salaris) |
| 54000.00 | |
| 39000.00 | |
| 9000.00 |
Rijst. 1. Drie groepen records die worden gemaakt wanneer een query wordt uitgevoerd
De SQL-standaard maakt het mogelijk om geneste queries in een SELECT-lijst te plaatsen. De bovenstaande vraag kan dus ook als volgt worden weergegeven:
SELECTEERfiliaalnr, (SELECTEER AANTAL(personeelsnr)ALS graaf
VANPersoneel s
WAARs.branchNo = b.branchNo),
(SELECTEER SUM(salaris) AS som
VANPersoneel s
WAARs.branchNo = b.branchNo)
VANTak b
BESTEL DOORfiliaalnr;
Deze versie van de query creëert echter twee aggregatiefunctieresultaten voor elk van de filialen van het bedrijf die worden beschreven in de Branch-tabel, dus in sommige gevallen is het mogelijk om rijen te zien die null-waarden bevatten.
Beperkingen op de uitvoering van groeperingen (HAVING-constructie). De HAVING-clausule is bedoeld om te worden gebruikt in combinatie met de GROUP BY-clausule om beperkingen in te stellen die zijn gespecificeerd met als doel deze te selecteren groepen, die in de resulterende querytabel wordt geplaatst. Hoewel de clausules HAVING en WHERE een vergelijkbare syntaxis hebben, zijn hun doeleinden verschillend. De WHERE-clausule is ontworpen om individuele rijen te selecteren om de resulterende querytabel te vullen, en de HAVING-clausule wordt gebruikt om groepen, in de resulterende querytabel geplaatst. De ISO-standaard vereist dat kolomnamen die in de HAVING-clausule worden gebruikt, moeten voorkomen in de GROUP BY-elementenlijst of moeten worden gebruikt in aggregatiefuncties. In de praktijk bevatten zoektermen in een HAVING-clausule altijd minimaal één aggregatiefunctie; anders moeten deze zoektermen in een WHERE-clausule worden geplaatst en worden gebruikt om individuele rijen te selecteren. (Houd er rekening mee dat aggregatiefuncties niet kunnen worden gebruikt in een WHERE-clausule.) De HAVING-clausule is geen noodzakelijk onderdeel van de SQL-taal; elke query die is geschreven met behulp van de HAVING-clausule kan in een andere vorm worden geschreven zonder deze te gebruiken.
Voorbeeld 18. Gebruik van het HAVING-construct.Bepaal voor elke vestiging van een bedrijf met meer dan één werknemer het aantal werknemers en de hoogte van hun loon.
SELECTEERvestigingNee, RAAD T(personeelNee) ALS graaf, SOM(salaris) ALS som
VANPersoneel
GROEP DOORfiliaalnr
TEL HEBBEN(personeelnr) > 1
BESTEL DOORfiliaalnr;
Dit voorbeeld is vergelijkbaar met het vorige, maar gebruikt aanvullende beperkingen die aangeven dat we alleen geïnteresseerd zijn in informatie over die afdelingen van het bedrijf waar meer dan één persoon werkzaam is. Een soortgelijke vereiste is van toepassing op groepen, dus de query moet de HAVING-constructie gebruiken. De resultaten van de zoekopdracht worden weergegeven in een tabel. 28.
Tabel 28
| brancheGeen telsom |
| В00З 3 54000,00 |
| B005 2 39000,00 |
Subquery's. In deze sectie bespreken we het gebruik van volledige SELECT-instructies die zijn ingebed in de hoofdtekst van een andere SELECT-instructie. Extern De (tweede) SELECT-instructie gebruikt het resultaat van de uitvoering intern(eerste) operator die de inhoud van het eindresultaat van de gehele operatie bepaalt. Innerlijke queries zijn te vinden in de WHERE- en HAVING-clausules van de buitenste SELECT-instructie. In dit geval heten ze subquery's, of geneste zoekopdrachten. Bovendien kunnen innerlijke SELECT-instructies worden gebruikt in INSERT-, UPDATE- en DELETE-instructies . Er zijn drie soorten subquery's.
Scalaire subquery retourneert de waarde die is geselecteerd op het snijpunt van één kolom met één rij, d.w.z. de enige betekenis. In principe kan een scalaire subquery overal worden gebruikt waar één enkele waarde vereist is. Varianten van scalaire subquery's worden gegeven in voorbeelden 13 en 14.
Tekenreeks-subquery retourneert de waarden van meerdere tabelkolommen, maar als één rij. Een string-subquery kan overal worden gebruikt waar een stringwaarde-constructor wordt gebruikt, meestal predikaten. Een variant van een string-subquery wordt getoond in voorbeeld 15.
Tabel-subquery retourneert de waarden van een of meer tabelkolommen over meer dan één rij. Een tabelsubquery kan overal worden gebruikt waar een tabel kan worden gespecificeerd, bijvoorbeeld als operand voor een IN-predikaat.
Voorbeeld 19: Een subquery gebruiken met een gelijkheidstest. Componeren lijst van personeel dat werkzaam is in de vestiging van het bedrijf op 463 Main St1.
SELECTEER
VANPersoneel
WAARfiliaalnr = (SELECT filiaalnr
VANTak
WAARstraat = "Hoofdstraat 163" );
De interne SELECT-instructie (SELECT branchNo FROM Branch ...) is bedoeld om het nummer van het bedrijfsfiliaal op het adres "163 Main St" te bepalen. (Er bestaat slechts één zo'n filiaal van het bedrijf, dus dit voorbeeld is een voorbeeld van een scalaire subquery.) Nadat het nummer van het vereiste filiaal is verkregen, wordt een externe subquery uitgevoerd om gedetailleerde informatie over de werknemers van dat filiaal op te halen. Met andere woorden: de binnenste SELECT-instructie retourneert een tabel die bestaat uit een enkele waarde "BOOV". Dit vertegenwoordigt het nummer van de bedrijfsvestiging op het adres "163 Main St1. Als gevolg hiervan heeft de buitenste SELECT-instructie de volgende vorm:
SELECTEERstaffNo, fName, IName, positie
VANPersoneel
WAARbranchNo = "B0031;
De resultaten van deze zoekopdracht worden weergegeven in een tabel. 29.
Tabel 29
Resultaat van het verzoek
| personeelnr | fNaam | INaam | positie |
| SG37 | Ann | Beuken | Assistent |
| SG14 | David | Ford | Toezichthouder |
| SG5 | Susan | Merk | Manager |
Een subquery is een hulpmiddel voor het maken van een tijdelijke tabel waarvan de inhoud wordt opgehaald en verwerkt door een externe operator. Een subquery kan direct na vergelijkingsoperatoren worden gespecificeerd (d.w.z. operatoren =,<, >, <=, >=, <>) in de WHERE- of HAVING-clausule. De subquerytekst moet tussen haakjes staan.
Voorbeeld 20. Subquery's gebruiken met aggregatiefuncties. Maak een lijst van alle werknemers met een salaris dat boven het gemiddelde ligt, en geef aan hoeveel hun salaris hoger is dan het gemiddelde salaris voor de onderneming.
SELECTEERpersoneelsnr, fName, IName, functie, salaris - ( SELECTEER AVG(salaris) VAN Personeel) ALS zoutVerschil
VANPersoneel
WAARsalaris > ( SELECTEER AVG(salaris) VAN Personeel) ;
Opgemerkt moet worden dat u dit niet rechtstreeks kunt doen opnemen in query-expressie"WAAR salaris > AVG (salaris)", omdat ik aggregatie gebruik functies in de WHERE-clausule zijn verboden. Om het gewenste resultaat te bereiken, moet u een subquery maken die het gemiddelde jaarsalaris berekent, en deze vervolgens gebruiken in een buitenste SELECT-instructie die informatie ophaalt over de werknemers in het bedrijf wier salaris dit gemiddelde overschrijdt. Met andere woorden: de subquery retourneert het gemiddelde salaris voor het bedrijf per jaar, gelijk aan € 17.000.
Het resultaat van deze scalaire subquery wordt in de buitenste SELECT-instructie gebruikt om zowel de afwijking van de lonen van het gemiddelde niveau te berekenen als om informatie over werknemers te selecteren. Daarom heeft de buitenste SELECT-instructie de volgende vorm:
SELECTEERpersoneelNee, fName, IName, functie, salaris - 17000 Als zoutVerschil
VANPersoneel
WAARsalaris > 17000;
De resultaten van de zoekopdracht worden weergegeven in een tabel. 30.
Tabel 30.
Resultaat van het verzoek
| personeelnr | fNaam | INaam | positie | zoutVerschil |
| SL21 | John | Wit | Manager | 13000.00 |
| SG14 | David | Ford | Toezichthouder | 1000.00 |
| SG5 | Susan | Merk | Manager | 7000.00 |
Geldt voor subquery's volgende regels en beperkingen.
1. Subquery's mogen de ORDER BY-clausule niet gebruiken, hoewel deze mogelijk aanwezig is in de buitenste SELECT-instructie.
2. De SELECT-lijst van een subquery moet bestaan uit de namen van individuele kolommen of uitdrukkingen die daaruit zijn samengesteld, behalve wanneer het sleutelwoord EXISTS in de subquery wordt gebruikt.
3. Kolomnamen in een subquery verwijzen standaard naar de tabel waarvan de naam is opgegeven in de FROM-clausule van de subquery. Het is echter ook mogelijk om te verwijzen naar kolommen van een tabel die is opgegeven in de FROM-clausule van een buitenste query door gekwalificeerde kolomnamen te gebruiken (zoals hieronder beschreven).
4. Als een subquery een van de twee operanden is die betrokken zijn bij een vergelijkingsbewerking, moet de subquery aan de rechterkant van deze bewerking worden opgegeven. De volgende querynotatie uit het vorige voorbeeld is bijvoorbeeld onjuist omdat de subquery aan de linkerkant van de vergelijkingsbewerking is geplaatst ten opzichte van de waarde van de salariskolom.
SELECTEER
VANPersoneel
WAAR(SELECTEER AVG(salaris) VAN Personeel)< salary;
Voorbeeld 21. Geneste subquery's en het gebruik van het IN-predikaat. Maak een lijst van huurwoningen waarvoor medewerkers van de bedrijfsvestiging op Main st1 163 verantwoordelijk zijn.
SELECTEERwoningNee, straat, plaats, postcode, type, kamers, huur
VANPropertyForRent
Hoofdstuk 5. SQL-taal: datamanipulatie 189
WAARpersoneelsnr IN (SELECT personeelsnr
VANPersoneel
WAARbrancliNo = (SELECTeer taknr
VANTak
WAARstraat = "163 Hoofdstraat "));
De eerste, meest interne, zoekopdracht is bedoeld om het nummer van het filiaal van het bedrijf op 463 Main St. te bepalen. De tweede, tussenliggende, zoekopdracht haalt informatie op over het personeel dat in dit filiaal werkt. In dit geval zijn er meer dan één rij gegevens geselecteerd en dus in de externe zoekopdracht. U kunt de vergelijkingsoperator = niet gebruiken. In plaats daarvan moet u het trefwoord IN gebruiken. De externe zoekopdracht haalt informatie op over de gehuurde objecten waarvoor die medewerkers van het bedrijf verantwoordelijk zijn, waarvan de gegevens zijn verkregen als een resultaat van het uitvoeren van de tussenquery. De resultaten van de query worden weergegeven in tabel 31.
Tabel 31
Resultaat van het verzoek
| eigendomnr | straat | stad | postcode | type | kamers | huur |
| PG16 | 5 Novar Dr | Glasgow | G129AX | Vlak | ||
| PG36 | Herenweg 2 | Glasgow | G324QX | Vlak | ||
| PG21 | Dalestraat 18 | Glasgow | G12 | Huis |
Trefwoorden ALLES en ALLES. De trefwoorden ANY en ALL kunnen worden gebruikt met subquery's die één kolom met getallen retourneren. Als de subquery wordt voorafgegaan door het trefwoord ALL, wordt alleen aan de vergelijkingsvoorwaarde voldaan als deze waar is voor alle waarden in de resultaatkolom van de subquery. Als de tekst van een subquery wordt voorafgegaan door het trefwoord ANY, wordt de vergelijkingsvoorwaarde geacht te zijn vervuld als aan ten minste enkele (een of meer) waarden in de resulterende kolom van de subquery is voldaan. Als het resultaat van het uitvoeren van een subquery resulteert in een lege waarde, wordt voor het trefwoord ALL als voldaan aan de vergelijkingsvoorwaarde beschouwd, en voor het trefwoord ANY als niet-vervuld. Volgens de ISO-norm kunt u bovendien het trefwoord SOME gebruiken, wat een synoniem is voor het trefwoord ANY.
Voorbeeld 22. Gebruik de trefwoorden ELKE en SOMMIGE. Vind alle werknemers wier salaris minimaal hoger is dan het salaris een medewerker van de bedrijfstak onder het nummer "booz".
SELECTEERpersoneelsnr, fName, IName, functie, salaris
VANPersoneel
WAARsalaris > SOMMIGE(SELECTEER salaris
VANPersoneel
WAARfiliaalnr = "B003");
Hoewel deze query zou kunnen worden geschreven met behulp van een subquery die het minimumsalaris specificeert voor afdelingspersoneelsnummer "BOHO", waarna de buitenste subquery informatie zou kunnen selecteren over al het bedrijfspersoneel wiens salaris deze waarde overschrijdt (zie voorbeeld 20), is een andere benadering mogelijk, die bestaat uit het gebruik van de SOME/ANY trefwoorden. In dit geval creëert de binnenste subquery een reeks waarden (12000, 18000, 24000) en selecteert de buitenste query informatie over die werknemers wier salaris hoger is dan een van de waarden in deze
ingesteld (eigenlijk meer dan de minimumwaarde - 12000). Deze alternatieve methode kan als natuurlijker worden beschouwd dan het definiëren van het minimumsalaris in een subquery. Maar in beide gevallen worden dezelfde zoekopdrachtresultaten geproduceerd, die in een tabel worden weergegeven. 32 .
Tabel 32
Resultaat van het verzoek
| personeelnr | fNaam | INaam | positie | salaris |
| SL21 | John | Wit | Manager | 30000.00 |
| SG14 | David | Ford | Toezichthouder | 18000.00 |
| SG5 | Susan | Merk | Manager | 24000.00 |
Voorbeeld 23. Gebruik het trefwoord ALL. Vind alle werknemers wier loon hoger is dan het loon van welke werknemer dan ook in het bedrijfsfiliaalnummer "booz".
SELECTEERpersoneelNee, fName, INarae, functie, salaris
VANPersoneel
WAARsalaris > ALLE(SELECTEER salaris
VANPersoneel
WAARbranchNo = "BOG3");
Over het algemeen is dit verzoek vergelijkbaar met het vorige. En in dit geval zou het mogelijk zijn om een subquery te gebruiken die de maximale waarde van het salaris van afdelingspersoneel onder het nummer "BOZ" bepaalt, en vervolgens, met behulp van een externe query, informatie te selecteren over alle werknemers van het bedrijf wier salaris hoger is dan deze waarde. In dit voorbeeld wordt echter gekozen voor de trefwoordbenadering ALL. De resultaten van de zoekopdracht worden weergegeven in een tabel. 33 .
Tabel 33
Resultaat van het verzoek
| personeelnr | INaam | fNaam | positie | salaris |
| SL21 | Wit | John | Manager | 30000,00 |
Query's met meerdere tabellen. Alle hierboven besproken voorbeelden hebben dezelfde belangrijke beperking: de kolommen die in de resulterende tabel worden geplaatst, worden altijd uit één enkele tabel geselecteerd. In veel gevallen is dit echter niet voldoende. Om kolommen uit verschillende brontabellen in de resulterende tabel te combineren, moet u de bewerking uitvoeren verbindingen. In SQL wordt de join-bewerking gebruikt om informatie uit twee tabellen te combineren door paren van gerelateerde rijen te vormen die uit elke tabel zijn geselecteerd. Paren rijen die in de gecombineerde tabel zijn geplaatst, worden samengesteld op basis van de gelijkheid van de waarden van de opgegeven kolommen die daarin zijn opgenomen.
Als u informatie uit meerdere tabellen wilt halen, kunt u een subquery gebruiken of de tabellen samenvoegen. Als de resulterende querytabel kolommen uit verschillende brontabellen moet bevatten, is het raadzaam een tabelkoppelingsmechanisme te gebruiken. Om een join uit te voeren volstaat het om de namen van twee of meer tabellen op te geven in de FROM-clausule, deze te scheiden met komma's, en vervolgens in de query de WHERE-clausule op te nemen die de kolommen definieert die worden gebruikt om de opgegeven tabellen samen te voegen. Bovendien kunt u in plaats van tabelnamen gebruiken pseudoniemen, die aan hen zijn toegewezen in de FROM-clausule. In dit geval moeten de tabelnamen en de daaraan toegewezen aliassen door spaties worden gescheiden. Aliassen kunnen worden gebruikt om kolomnamen te verduidelijken in alle gevallen waarin er onduidelijkheid kan bestaan over tot welke tabel een bepaalde kolom behoort. Bovendien kunnen aliassen worden gebruikt om tabelnamen in te korten. Als er een alias voor een tabel is gedefinieerd, kan deze overal worden gebruikt waar de naam van die tabel moet worden opgegeven.
Voorbeeld 24. Eenvoudige verbinding. Maak een lijst met de namen van alle opdrachtgevers die al minimaal één huurwoning hebben bezichtigd en hun mening hierover hebben gegeven.
SELECTEERc.clientNo, fName, IName, propertyNo, commentaar
VANKlant c, Bezichtiging v
WAARc.clientNee = v.clientNee;
Voor dit rapport is informatie nodig uit zowel de tabel Client als de tabel Viewing. Daarom gebruiken we een mechanisme voor het samenvoegen van tabellen om de query samen te stellen. De SELECT-clausule vermeldt alle kolommen die in de resulterende querytabel moeten worden geplaatst. Houd er rekening mee dat de clientNo-kolom kwalificatie vereist, omdat de kolom ook aanwezig kan zijn in een andere tabel die deelneemt aan de join. Daarom is het noodzakelijk om expliciet aan te geven in welke tabelwaarden we geïnteresseerd zijn. (In dit voorbeeld had u net zo gemakkelijk de clientNo-kolomwaarden uit de weergavetabel kunnen selecteren.) De naam wordt opgegeven door de naam van de overeenkomstige tabel (of de alias ervan) op te geven als voorvoegsel vóór de kolomnaam. In ons voorbeeld wordt de waarde 'c' gebruikt die is opgegeven als alias voor de tabel Client. Om de resulterende rijen te genereren, worden de rijen van de brontabellen gebruikt die een identieke waarde hebben in de clientNo-kolom. Deze voorwaarde wordt bepaald door de zoekvoorwaarde op te geven met.clientNo=v.clientNo. Soortgelijke kolommen met brontabellen worden aangeroepen bijpassende kolommen. De beschreven werking is gelijkwaardig aan de werking verbindingen door gelijkheid relationele algebra. De resultaten van de zoekopdracht worden weergegeven in een tabel. 34.
Tabel 34
Resultaat van het verzoek
| klantnr | fNaam | INaam | eigendomnr | opmerking |
| CR56 | Aline | Steward | PG36 | |
| CR56 | Aline | Steward | PA14 | te klein |
| CR56 | Aline | Steward | PG4 | |
| CR62 | Maria | Tregear | PA14 | geen eetkamer |
| CR76 | John | Kaj | PG4 | te afgelegen |
Meestal worden query's met meerdere tabellen uitgevoerd op twee tabellen die met elkaar zijn verbonden door een één-op-veel (1:*) of ouder-kind-relatie. In het bovenstaande voorbeeld, waarbij toegang wordt verkregen tot de Client- en Viewing-tabellen, zijn deze laatste met elkaar verbonden door precies zo'n relatie. Elke rij van de Viewing-tabel (kind) is gekoppeld aan slechts één rij van de client-tabel (bovenliggende tabel), terwijl dezelfde rij van de client-tabel (bovenliggende tabel) kan worden gekoppeld
met veel rijen van de Kijktafel (onderliggend). De rijparen die worden gegenereerd wanneer een query wordt uitgevoerd, zijn het resultaat van alle geldige combinaties van rijen in de onderliggende en bovenliggende tabellen. In paragraaf 3.2.5 wordt gedetailleerd beschreven hoe in een relationele database de primaire en refererende sleutels van tabellen een ouder-kindrelatie creëren. Een tabel met een externe sleutel is meestal een kind, terwijl een tabel met een primaire sleutel altijd een ouder zal zijn. Als u een ouder-kindrelatie in een SQL-query wilt gebruiken, moet u een zoekvoorwaarde opgeven die de refererende sleutel en de primaire sleutel vergelijkt. Voorbeeld 24 vergelijkt de primaire sleutel van de Client-tabel (v. clientNo) met de refererende sleutel van de Viewing-tabel (v. clientNo).
De SQL-standaard biedt bovendien de volgende manieren om deze verbinding te definiëren:
VANKlant bij MEEDOEN V bekijken OP c.clientnr = v.clientnr
VANKlant J OIN Bekijken GEBRUIKEN klantnr
VANCliënt NATUURLIJKE MOEITE Bekijken
In elk geval vervangt de FROM-clausule de oorspronkelijke FROM- en WHERE-clausules. De eerste optie creëert echter een tabel met twee identieke clientNo-kolommen, terwijl in de andere twee gevallen de resulterende tabel slechts één clientNo-kolom zal bevatten.
Voorbeeld 25. De resultaten van het samenvoegen van tabellen sorteren. Vermeld voor elke vestiging van het bedrijf de personeelsnummers en namen van werknemers die verantwoordelijk zijn voor eventuele huurwoningen, en geef ook aan voor welke faciliteiten
waarop zij antwoorden.
SELECTEERs.branchNo, s.staffNo, fName, IName, propertyNo
VANPersoneel, PropertyForRent p
WAARs.staffnr = p.staffnr
BESTEL DOORs.branchNo, s.staffNo, propertyNo;
Om de resultaten gemakkelijker leesbaar te maken, wordt de resulterende uitvoer gesorteerd met behulp van het afdelingsnummer als de belangrijkste sorteersleutel en het personeelsnummer en het objectnummer als de secundaire sleutels. De resultaten van de zoekopdracht worden weergegeven in een tabel. 35.
Tabel 35
Resultaat van het verzoek
| filiaalnr | Personeelnr | fNaam | INaam | eigendomnr |
| WHO | SG14 | David | Ford | PG16 |
| WHO | SG37 | Ann | Beuken | PG21 |
| WHO | SG37 | Ann | Beuken | PG36 |
| BOO5 | SL41 | Maria | Lee | PL94 |
| SBI7 | SA9 | Julia | Hoe | PA14 |
Voorbeeld 26. Drie tafels samenvoegen. Vermeld voor elke vestiging van het bedrijf de personeelsnummers en namen van de werknemers die verantwoordelijk zijn voor eventuele huurwoningen, met vermelding van de stad waar de bedrijfsvestiging is gevestigd en de nummers van de faciliteiten waarvoor elke werknemer verantwoordelijk is.
SELECTEER b.branchNo, b.city, s.staffNo, fName, IName, propertyNo
VAN Tak b, Personeelszaken, PropertyForRent p
WAAR b.branchNo = s.branchNo EN s.staffNo = p.staffNo
BESTEL DOOR b.filiaalnr, s.staffnr, eigendomnr;
De resulterende tabel moet kolommen uit drie brontabellen bevatten: Branch, Staff en PropertyForRent. De query moet dus aan deze tabellen worden toegevoegd. De filiaal- en personeelstabellen kunnen worden samengevoegd met behulp van de voorwaarde b.branchNo=*s .branchNo, waardoor bedrijfsfilialen worden gekoppeld aan het personeel dat daarin werkt. De tabellen Staff en PropertyForRent kunnen worden samengevoegd met de voorwaarde s.staffNo=p.staffNo. Hierdoor wordt iedere medewerker verbonden aan de huurwoningen waarvoor hij verantwoordelijk is. De resultaten van de zoekopdracht worden weergegeven in een tabel. 36.
Tabel 36
Resultaten opvragen
| filiaalnr | stad | personeelMo | fNaam | INaam | eigendomnr |
| B003 | Glasgow | SG14 | David | Ford | PG16 |
| B003 | Glasgow | SG37 | Ann | Beuken | PG21 |
| B003 | Glasgow | SG37 | Ann | Beuken | PG36 |
| B005 | Londen | SL41 | Julia | Lee | PL94 |
| B007 | Aberdeen | SA9 | Maria | Hoe | PA14 |
Merk op dat de SQL-standaard het gebruik van een alternatieve formulering van de FROM- en WHERE-constructies toestaat:
VAN(Branch b JOIN Staff s USING branchNo) ALS bs
MEEDOENVastgoedTe Huur p GEBRUIKEN personeelnr
Voorbeeld 27. Groeperen op meerdere kolommen. Bepaal het aantal huurwoningen waarvoor elk van de werknemers van het bedrijf verantwoordelijk is.
SELECTEERs.filiaalNee, S.staffNee, GRAAF(*) ALS graaf
VAN Medewerkers, PropertyForRent p
WAAR S.staffnr = p.staffnr
GROEP DOORs.filiaalNee, s.personeelNee
BESTEL DOORs.filiaalNee, s.staffNee;
Om het vereiste rapport op te stellen, moet u eerst weten welke medewerker van het bedrijf verantwoordelijk is voor de huurwoningen. Dit probleem kan worden opgelost door de tabellen Staff en PropertyForRent samen te voegen met behulp van de kolom staffNo in de FROM/WHERE-clausules. Vervolgens moet u groepen aanmaken bestaande uit het afdelingsnummer en personeelsnummers van de medewerkers, waarvoor u de constructie GROUP BY moet gebruiken. Ten slotte moet de resulterende tabel worden gesorteerd met behulp van de ORDER BY-clausule. De resultaten van de zoekopdracht worden weergegeven in een tabel. 37.
Tabel 37
Resultaat van het verzoek
| filiaalnr | personeelnr | graaf |
| В00З | SG14 | |
| В00З | SG37 | |
| B005 | SL41 | |
| B007 | SA9 |
Verbindingen maken. Een join is een subset van een meer algemene combinatie van gegevens uit twee tabellen Cartesisch. Het cartesiaanse product van twee tabellen is een andere tabel die bestaat uit alle mogelijke paren rijen die deel uitmaken van beide tabellen. De kolommenset van de resultaattabel bestaat uit alle kolommen van de eerste tabel, gevolgd door alle kolommen van de tweede tabel. Als u een query voor twee tabellen invoert zonder een WHERE-clausule op te geven, zal het resultaat van de query in de SQL-omgeving het cartesiaanse product van deze tabellen zijn. Bovendien biedt de ISO-standaard een speciaal formaat voor de SELECT-instructie waarmee u het Cartesiaanse product van twee tabellen kunt berekenen:
SELECT(*j kolomLijst]
VAN tabelNaam CROSS JOINCaYeUlte2
Laten we nog eens kijken naar een voorbeeld waarin de verbinding tussen de client en het bekijken van tabellen wordt uitgevoerd met behulp van de gemeenschappelijke clientNo-kolom bij het werken met tabellen waarvan de inhoud in tabel wordt gegeven. 3.6 en 3.8 zal het Cartesiaanse product van deze tabellen 20 rijen bevatten (4 klanttabelrijen x 5 weergavetabelrijen = 20 rijen). Dit komt overeen met het uitvoeren van de query die in voorbeeld 5.24 wordt gebruikt, maar zonder de WHERE-clausule te gebruiken. De procedure voor het genereren van een tabel met de resultaten van het samenvoegen van twee tabellen met behulp van de SELECT-instructie is als volgt.
1. Er wordt een Cartesisch product gevormd van de tabellen die zijn opgegeven in de FROM-constructie.
2. Als de query een WHERE-clausule bevat, worden zoekvoorwaarden toegepast op elke rij van de cartesiaanse producttabel en worden in de tabel alleen die rijen opgeslagen die aan de opgegeven voorwaarden voldoen. In termen van relationele algebra wordt deze bewerking genoemd beperking Cartesisch product.
3. Voor elke resterende rij wordt de waarde van elk element dat is opgegeven in de SELECT-lijst bepaald, wat resulteert in een afzonderlijke rij van de resulterende tabel.
4. Als de oorspronkelijke query de constructie SELECT DISTINCT bevat, worden alle dubbele rijen uit de resulterende tabel verwijderd.
5. Als de query die u uitvoert een ORDER BY-clausule bevat,
©2015-2019 website
Alle rechten behoren toe aan hun auteurs. Deze site claimt geen auteurschap, maar biedt gratis gebruik.
Aanmaakdatum van de pagina: 07-08-2016
Hoe kom ik erachter hoeveel pc-modellen een bepaalde leverancier heeft geproduceerd? Hoe bepaal je de gemiddelde prijs van computers met dezelfde technische kenmerken? Deze en vele andere vragen met betrekking tot bepaalde statistische informatie kunnen worden beantwoord met behulp van laatste (geaggregeerde) functies. De standaard biedt de volgende aggregatiefuncties:
Al deze functies retourneren één enkele waarde. Tegelijkertijd de functies AANTAL, MIN En MAX toepasbaar op elk gegevenstype, while SOM En AVG worden alleen gebruikt voor numerieke velden. Verschil tussen functie GRAAF(*) En GRAAF(<имя поля>) is dat de tweede bij de berekening geen rekening houdt met NULL-waarden.
Voorbeeld. Vind de minimum- en maximumprijs voor personal computers:
Voorbeeld. Zoek het beschikbare aantal computers geproduceerd door fabrikant A:
Voorbeeld. Als we geïnteresseerd zijn in het aantal verschillende modellen geproduceerd door fabrikant A, dan kan de vraag als volgt worden geformuleerd (gebruik makend van het feit dat in de Producttabel elk model één keer wordt geregistreerd):
Voorbeeld. Zoek het aantal beschikbare verschillende modellen geproduceerd door fabrikant A. De zoekopdracht is vergelijkbaar met de vorige, waarin het totale aantal modellen moest worden bepaald dat door fabrikant A is geproduceerd. Hier moet u ook het aantal verschillende modellen vinden in de PC-tafel (dat wil zeggen, die beschikbaar zijn voor verkoop).
Om ervoor te zorgen dat alleen unieke waarden worden gebruikt bij het verkrijgen van statistische indicatoren, wanneer argument van aggregatiefuncties kan worden gebruikt DISTINCT-parameter. Een andere parameter ALLES is de standaard en gaat ervan uit dat alle geretourneerde waarden in de kolom worden geteld. Exploitant,
Als we het aantal geproduceerde pc-modellen willen weten iedereen fabrikant, die u moet gebruiken GROUP BY-clausule, syntactisch volgend WHERE-clausules.
GROUP BY-clausule
GROUP BY-clausule gebruikt om groepen uitvoerreeksen te definiëren waarop kan worden toegepast aggregatiefuncties (COUNT, MIN, MAX, AVG en SUM). Als deze clausule ontbreekt en er aggregatiefuncties worden gebruikt, worden alle kolommen met namen vermeld in SELECTEER, moet worden opgenomen geaggregeerde functies, en deze functies worden toegepast op de volledige set rijen die voldoen aan het querypredikaat. Anders alle kolommen van de SELECT-lijst niet inbegrepen in totaal moeten functies worden gespecificeerd in de GROUP BY-clausule. Als gevolg hiervan zijn alle uitvoerqueryrijen verdeeld in groepen die worden gekenmerkt door dezelfde combinaties van waarden in deze kolommen.Hierna worden op elke groep aggregatiefuncties toegepast. Houd er rekening mee dat voor GROUP BY alle NULL-waarden als gelijk worden behandeld, d.w.z. bij groeperen op een veld dat NULL-waarden bevat, vallen al dergelijke rijen in één groep. Als als er een GROUP BY-clausule aanwezig is , in de SELECT-clausule geen geaggregeerde functies
, dan retourneert de query eenvoudigweg één rij uit elke groep. Deze functie kan, samen met het trefwoord DISTINCT, worden gebruikt om dubbele rijen in een resultatenset te elimineren.
| Laten we een eenvoudig voorbeeld bekijken: SELECTEER model, COUNT(model) AS Aantal_model, AVG(prijs) AS Gem._prijs VANAF PC |
GROEP OP model;
| In dit verzoek worden voor elk pc-model het aantal en de gemiddelde kosten bepaald. Alle rijen met dezelfde modelwaarde vormen een groep, en de uitvoer van SELECT berekent het aantal waarden en de gemiddelde prijswaarden voor elke groep. Het resultaat van de query is de volgende tabel: | model | Aantal_model |
| 1121 | 3 | 850.0 |
| 1232 | 4 | 425.0 |
| 1233 | 3 | 843.33333333333337 |
| 1260 | 1 | 350.0 |
Als de SELECT een datumkolom zou hebben, zou het mogelijk zijn om deze indicatoren voor elke specifieke datum te berekenen. Om dit te doen, moet u datum toevoegen als groeperingskolom, waarna de aggregatiefuncties worden berekend voor elke combinatie van waarden (modeldatum).
Er zijn verschillende specifieke regels voor het uitvoeren van aggregatiefuncties:
- Indien als gevolg van het verzoek geen rijen ontvangen(of meer dan één rij voor een bepaalde groep), dan zijn er geen brongegevens voor het berekenen van de aggregatiefuncties. In dit geval is het resultaat van de COUNT-functies nul en is het resultaat van alle andere functies NULL.
- Argument geaggregeerde functie kan zelf geen aggregatiefuncties bevatten(functie van functie). Die. bij één zoekopdracht is het bijvoorbeeld onmogelijk om het maximum aan gemiddelde waarden te verkrijgen.
- Het resultaat van het uitvoeren van de COUNT-functie is geheel getal(GEHEEL GETAL). Andere aggregatiefuncties erven de gegevenstypen van de waarden die ze verwerken.
- Als de SOM-functie een resultaat oplevert dat groter is dan de maximale waarde van het gebruikte gegevenstype, fout.
Dus als het verzoek niet bevat GROUP BY-clausules, Dat geaggregeerde functies opgenomen in SELECT-clausule, worden uitgevoerd op alle resulterende queryrijen. Als het verzoek bevat GROUP BY-clausule, elke set rijen die dezelfde waarden heeft van een kolom of groep kolommen die is opgegeven in GROUP BY-clausule, vormt een groep, en geaggregeerde functies worden voor elke groep afzonderlijk uitgevoerd.
AANBOD HEBBEN
Hierna worden op elke groep aggregatiefuncties toegepast. Houd er rekening mee dat voor GROUP BY alle NULL-waarden als gelijk worden behandeld, d.w.z. bij groeperen op een veld dat NULL-waarden bevat, vallen al dergelijke rijen in één groep. WHERE-clausule definieert vervolgens een predikaat voor het filteren van rijen AANBOD HEBBEN is van toepassing na groepering om een soortgelijk predikaat te definiëren dat groepen op waarden filtert geaggregeerde functies. Deze clausule is nodig om de waarden te valideren die worden verkregen met behulp van geaggregeerde functie niet uit individuele rijen van de recordbron gedefinieerd in FROM-clausule, en van groepen van dergelijke lijnen. Een dergelijke controle kan daarom niet worden opgenomen WHERE-clausule.
