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 gebruiksscenario 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 uitsluiten 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 wordt gebruikt 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 persoon 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 |
| В005 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-constructie 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 c.clientNo=v.clientNo op te geven. 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 reeks kolommen in de resulterende tabel 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 van de client en het bekijken van tabellen wordt uitgevoerd met behulp van de gemeenschappelijke kolom clientNo. 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
GROUP BY-clausule Met (SELECT-instructies) kunt u gegevens (rijen) groeperen op basis van de waarde van een kolom of meerdere kolommen of expressies. Het resultaat is een reeks samenvattingsrijen.
Elke kolom in de selectielijst moet voorkomen in de GROUP BY-clausule, met uitzondering van constanten en kolommen met geaggregeerde functie-operand.
U kunt een tabel groeperen op elke combinatie van de kolommen.
Geaggregeerde functies worden gebruikt om één enkele totale waarde uit een groep rijen te verkrijgen. Alle aggregatiefuncties voeren berekeningen uit op één enkel argument, dat een kolom of een expressie kan zijn. Het resultaat van elke aggregatiefunctieberekening is een constante waarde die in een aparte resultaatkolom wordt weergegeven.
Geaggregeerde functies worden gespecificeerd in de kolomlijst van een SELECT-instructie, die ook een GROUP BY-clausule kan bevatten. Als de SELECT-instructie geen GROUP BY-clausule bevat en de lijst met geselecteerde kolommen ten minste één aggregatiefunctie bevat, mag deze geen eenvoudige kolommen bevatten. Aan de andere kant kan de kolomselectielijst kolomnamen bevatten die geen argumenten zijn voor de aggregatiefunctie als die kolommen argumenten zijn voor de GROUP BY-clausule.
Als de query een WHERE-clausule bevat, berekenen de aggregatiefuncties de waarde voor de selectieresultaten.
Geaggregeerde functies MIN en MAX bereken respectievelijk de kleinste en de grootste waarde van de kolom. Argumenten kunnen getallen, tekenreeksen en datums zijn. Alle NULL-waarden worden vóór de berekening verwijderd (dat wil zeggen dat er geen rekening mee wordt gehouden).
Geaggregeerde functie SOM berekent de totale som van de waarden van een kolom. Argumenten kunnen alleen maar getallen zijn. Door de parameter DISTINCT te gebruiken, worden alle dubbele waarden in een kolom geëlimineerd voordat de functie SOM wordt toegepast. Op dezelfde manier worden alle NULL-waarden verwijderd voordat deze aggregatiefunctie wordt toegepast.
AVG-aggregaatfunctie retourneert het gemiddelde van alle waarden in een kolom. Argumenten kunnen ook alleen maar getallen zijn en alle NULL-waarden worden vóór evaluatie verwijderd.
Geaggregeerde functie AANTAL kent twee verschillende vormen:
- COUNT(col_name) - telt het aantal waarden in de kolom col_name, er wordt geen rekening gehouden met NULL-waarden
- COUNT(*) - telt het aantal rijen in de tabel, er wordt ook rekening gehouden met NULL-waarden
Als de zoekopdracht het trefwoord DISTINCT gebruikt, worden alle dubbele waarden in de kolom verwijderd voordat de functie COUNT wordt gebruikt.
COUNT_BIG-functie vergelijkbaar met de COUNT-functie. Het enige verschil tussen beide is het type resultaat dat ze retourneren: de functie COUNT_BIG retourneert altijd BIGINT-waarden, terwijl de functie COUNT INTEGER-gegevenswaarden retourneert.
IN AANBOD HEBBEN definieert een voorwaarde die van toepassing is op een groep rijen. Het heeft voor groepen rijen dezelfde betekenis als de WHERE-clausule voor de inhoud van de overeenkomstige tabel (WHERE is van toepassing vóór het groeperen, HAVING erna).
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.
Om de informatie in de database samen te vatten, biedt SQL aggregatiefuncties. Een aggregatiefunctie neemt een volledige gegevenskolom als argument en retourneert een enkele waarde die die kolom op een specifieke manier samenvat.
De aggregatiefunctie AVG() neemt bijvoorbeeld een kolom met getallen als argument en berekent hun gemiddelde.
Om het gemiddelde inkomen per hoofd van de bevolking van een inwoner van Zelenograd te berekenen, heeft u de volgende vraag nodig:
SELECTEER ‘GEMIDDELD INKOMEN PER CAPITA=’, AVG(SUMD)
SQL heeft zes aggregatiefuncties die verschillende soorten samenvattende informatie bieden (Afbeelding 1):
– SUM() berekent de som van alle waarden in een kolom;
– AVG() berekent het gemiddelde van de waarden in de kolom;
– MIN() vindt de kleinste van alle waarden in de kolom;
– MAX() vindt de grootste van alle waarden in de kolom;
– COUNT() telt het aantal waarden in een kolom;
– COUNT(*) telt het aantal rijen in de tabel met queryresultaten.
Het argument voor een aggregatiefunctie kan een eenvoudige kolomnaam zijn, zoals in het vorige voorbeeld, of een expressie, zoals in de volgende query, die de berekening van de gemiddelde belasting per hoofd van de bevolking specificeert:
SELECTEER AVG(SUMD*0,13)
Met deze query wordt een tijdelijke kolom gemaakt met de waarden (SUMD*0,13) voor elke rij van de tabel PERSON, en wordt vervolgens het gemiddelde van de tijdelijke kolom berekend.
Het inkomensbedrag voor alle inwoners van Zelenograd kan worden berekend met behulp van de aggregatiefunctie SOM:
SELECTEER SUM(SUMD) VAN PERSOON
Een aggregatiefunctie kan ook worden gebruikt om totalen te berekenen uit een resultatentabel die is verkregen door verschillende brontabellen samen te voegen. U kunt bijvoorbeeld het totale inkomensbedrag berekenen dat bewoners ontvingen uit een bron die 'Beurzen' heet:
SELECTEER SOM(GELD)
VAN WINST, HAVE_D
WAAR PROFIT.ID=HAVE_D.ID
AND PROFIT.SOURCE=’Beurzenschap’
Met de aggregatiefuncties MIN() en MAX() kunt u respectievelijk de kleinste en grootste waarden in een tabel vinden. De kolom kan numerieke waarden of tekenreekswaarden bevatten, of datum- of tijdwaarden.
U kunt bijvoorbeeld het volgende definiëren:
(a) het laagste totale inkomen dat de inwoners ontvangen en de hoogste te betalen belasting:
SELECTEER MIN(SUMD), MAX(SUMD*0,13)
b) geboortedata van de oudste en jongste inwoner:
SELECTEER MIN(RDATUM), MAX(RDATUM)
c) achternamen, voornamen en patroniemen van de allereerste en laatste bewoners van de lijst, alfabetisch geordend:
KIES MIN(FIO), MAX(FIO)
Wanneer u deze aggregatiefuncties gebruikt, moet u onthouden dat numerieke gegevens worden vergeleken met behulp van rekenregels, datums opeenvolgend worden vergeleken (eerdere datumwaarden worden als kleiner beschouwd dan latere) en tijdsintervallen worden vergeleken op basis van hun duur.
Wanneer u de functies MIN() en MAX() gebruikt met stringgegevens, hangt het resultaat van het vergelijken van twee strings af van de gebruikte tekencoderingstabel.
De aggregatiefunctie COUNT() telt het aantal waarden in een kolom van welk type dan ook:
(a) Hoeveel appartementen zijn er in het eerste microdistrict?
SELECTEER AANTAL(ADR) VAN FLAT WAAR ADR ZOALS "%, 1_ _-%"
(b) hoeveel inwoners hebben inkomstenbronnen?
SELECTEER COUNT(DISTINCT NOM) VAN HAVE_D
(c) Hoeveel bronnen van inkomsten worden door inwoners gebruikt?
SELECT COUNT(DISTINCT ID) FROM HAVE_D (Het DISTINCT trefwoord geeft aan dat niet-dubbele waarden in de kolom worden geteld).
De speciale aggregatiefunctie COUNT(*) telt de rijen in de resultatentabel, niet de gegevenswaarden:
(a) Hoeveel appartementen zijn er in het tweede microdistrict?
SELECTEER AANTAL(*) VAN FLAT WAAR ADR ZOALS "%, 2__-%"
(b) Hoeveel inkomstenbronnen heeft Ivan Ivanovitsj Ivanov?
SELECTEER AANTAL(*) VAN PERSON, HAVE_D WHERE FIO="Ivanov Ivan Ivanovitsj" AND PERSON.NOM=HAVE_D.NOM
(c) Hoeveel bewoners wonen er in een appartement op een bepaald adres?
SELECTEER AANTAL(*) VAN PERSOON WAAR ADR="Zelenograd, 1001-45"
Eén manier om te begrijpen hoe samenvattende query's met aggregatiefuncties worden uitgevoerd, is door de query-uitvoering op te splitsen in twee delen. Eerst bepalen we hoe de query zou werken zonder aggregatiefuncties, waarbij meerdere rijen met resultaten worden geretourneerd. Geaggregeerde functies worden vervolgens toegepast op de queryresultaten, waarbij één enkele resulterende rij wordt geretourneerd.
Neem bijvoorbeeld de volgende complexe vraag: bepaal het gemiddelde totale inkomen per hoofd van de bevolking, de som van het totale inkomen van de bewoners en de gemiddelde bronopbrengst als percentage van het totale inkomen van de bewoner. De telefoniste geeft het antwoord
SELECTEER AVG(SUMD), SUM(SUMD), (100*AVG(GELD/SUMD)) VAN PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM EN HAVE_D.ID=PROFIT.ID
Zonder aggregatiefuncties zou de query er als volgt uitzien:
SELECTEER SUMD, SUMD, GELD/SUMD VAN PERSOON, WINST, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM EN HAVE_D.ID=PROFIT.ID
en zou één rij resultaten opleveren voor elke inwoner en specifieke inkomstenbron. Geaggregeerde functies gebruiken de kolommen van de resultatentabel van de query om een tabel met één rij te produceren met de samenvattende resultaten.
U kunt een aggregatiefunctie opgeven in de geretourneerde kolomrij in plaats van een willekeurige kolomnaam. Het kan bijvoorbeeld deel uitmaken van een expressie die de waarden van twee aggregatiefuncties optelt of aftrekt:
SELECTEER MAX(SUMD)-MIN(SUMD) VAN PERSOON
Een aggregatiefunctie kan echter geen argument zijn voor een andere aggregatiefunctie, d.w.z. Geneste aggregatiefuncties zijn verboden.
Bovendien kan de lijst met geretourneerde kolommen niet tegelijkertijd aggregatiefuncties en reguliere kolomnamen gebruiken, omdat dit bijvoorbeeld geen zin heeft:
SELECTEER FIO, SUM(SUMD) VAN PERSOON
Hier instrueert het eerste element van de lijst het DBMS om een tabel te maken die uit verschillende rijen zal bestaan en één rij voor elke bewoner zal bevatten. Het tweede element van de lijst vraagt het DBMS om één resultaatwaarde te verkrijgen, namelijk de som van de waarden in de SUMD-kolom. Deze twee instructies spreken elkaar tegen, wat resulteert in een fout.
Het bovenstaande is niet van toepassing op de gevallen waarin subquery's en query's met groepering worden verwerkt.
Geaggregeerde functies gebruiken
SQL definieert veel ingebouwde functies van verschillende categorieën, waaronder een speciale plaats wordt ingenomen door aggregatiefuncties, die werken op de waarden van kolommen van veel rijen en een enkele waarde retourneren. Argumenten voor het aggregeren van functies kunnen zowel tabelkolommen als de resultaten van expressies daarover zijn. Geaggregeerde functies zelf kunnen worden opgenomen in andere rekenkundige uitdrukkingen. De volgende tabel toont de meest gebruikte standaard unaire aggregatiefuncties.
Het algemene formaat van een unaire aggregatiefunctie is als volgt:
function_name([ALL | DISTINCT] expressie)
waarbij DISTINCT specificeert dat de functie alleen verschillende waarden van het argument in overweging moet nemen, en ALL alle waarden specificeert, inclusief duplicaten (dit is de standaard). De AVG-functie met het trefwoord DISTINCT voor kolomrijen met waarden 1, 1, 1 en 3 retourneert bijvoorbeeld 2, en als het trefwoord ALL aanwezig is, retourneert deze 1,5.
Aggregaatfuncties worden gebruikt in de SELECT- en HAVING-clausules. Hier zullen we kijken naar het gebruik ervan in de SELECT-clausule. In dit geval is de expressie in het functieargument van toepassing op alle rijen in de invoertabel van de SELECT-clausule. Bovendien kunt u niet zowel aggregatiefuncties als tabelkolommen (of expressies daarmee) gebruiken in een SELECT-clausule, tenzij u een GROUP BY-clausule heeft, die we in de volgende sectie zullen bekijken.
De COUNT-functie heeft twee formaten. In het eerste geval wordt het aantal rijen in de invoertabel geretourneerd; in het tweede geval wordt het aantal argumentwaarden in de invoertabel geretourneerd:
- GRAAF(*)
- AANTAL(expressie)
De eenvoudigste manier om deze functie te gebruiken is door het aantal rijen in een tabel te tellen (alle rijen of rijen die aan een bepaalde voorwaarde voldoen). Hiervoor wordt de eerste syntaxisoptie gebruikt.
Query: Aantal productsoorten waarvoor informatie beschikbaar is in de database.
SELECTEER AANTAL(*) ALS "Aantal productsoorten"
VAN Product
De tweede versie van de syntaxis van de functie AANTAL kan de naam van een enkele kolom als argument gebruiken. In dit geval wordt het aantal van alle waarden in deze kolom van de invoertabel geteld, of alleen niet-herhalende waarden (met behulp van het trefwoord DISTINCT).
Query: het aantal afzonderlijke namen in de tabel Klant.
SELECTEER AANTAL (ONDERSCHEIDENDE FNAME)
VAN Klant
Het gebruik van de resterende unaire aggregatiefuncties is vergelijkbaar met COUNT, behalve dat voor de MIN- en MAX-functies het gebruik van de trefwoorden DISTINCT en ALL geen zin heeft. Met de functies COUNT, MAX en MIN kunnen naast numerieke velden ook tekenvelden worden gebruikt. Als het argument voor een aggregatiefunctie geen waarden bevat, retourneert de functie AANTAL 0 en retourneren alle andere NULL.
SELECTEER MAX (OrderDatum)
VAN
WAAR OrdDatum"1.09.2010"
Opdracht voor zelfstandig werk: Formuleer queries in SQL om de volgende gegevens op te halen:
- Totale kosten van alle bestellingen;
- Het aantal verschillende steden in de tabel Klanten.
