Norma ISO definuje nasledujúcich päť agregačné funkcie:

COUNT- vráti počet hodnôt v zadanom stĺpci;

SUM- vráti súčet hodnôt v zadanom stĺpci;

AVG- vráti priemernú hodnotu v zadanom stĺpci;

MIN- vráti minimálnu hodnotu v zadanom stĺpci;

MAX- vráti maximálnu hodnotu v zadanom stĺpci.

Všetky tieto funkcie pracujú s hodnotami v jednom stĺpci tabuľky a vracajú jednu hodnotu. Funkcie COUNT, MIN a MAX sa vzťahujú na numerické aj nenumerické polia, zatiaľ čo funkcie SUM a AVG možno použiť iba v prípade numerických polí. S výnimkou COUNT (*) sa pri vyhodnocovaní výsledkov akýchkoľvek funkcií najskôr vylúčia všetky hodnoty null, potom sa požadovaná operácia použije iba na zostávajúce hodnoty stĺpca, ktoré nie sú nulové. Možnosť COUNT (*) je špeciálne použitie funkcie COUNT — jej účelom je spočítať všetky riadky v tabuľke, či už obsahujú prázdne hodnoty, duplicitné hodnoty alebo akúkoľvek inú hodnotu. Ak chcete pred použitím agregačnej funkcie odstrániť duplicitné hodnoty, umiestnite kľúčové slovo DISTINCT pred názov stĺpca v definícii funkcie. Norma ISO umožňuje použitie kľúčového slova ALL na explicitné označenie toho, že odstránenie duplicitných hodnôt nie je potrebné, hoci toto kľúčové slovo sa predvolene predpokladá, ak nie je špecifikovaný žiadny iný kvalifikátor. Kľúčové slovo DISTINCT nemá pre funkcie MIN a MAX význam. Jeho použitie však môže ovplyvniť výsledky vykonávania funkcií SUM a AVG, preto by ste mali vopred zvážiť, či by mal byť prítomný v každom konkrétnom prípade. Okrem toho kľúčové slovo DISTINCT možno zadať maximálne raz v každej požiadavke.

Treba poznamenať, že agregačné funkcie možno použiť iba v zozname SELECT a v klauzule HAVING (pozri časť 5.3.4). Vo všetkých ostatných prípadoch je použitie týchto funkcií neplatné. Ak zoznam SELECT obsahuje agregačnú funkciu a text dotazu neobsahuje klauzulu GROUP BY, ktorá poskytuje agregáciu údajov do skupín, potom žiadny z prvkov zoznamu SELECT nemôže obsahovať žiadne odkazy na stĺpce, pokiaľ sa tento stĺpec nepoužije ako parameter agregačná funkcia. Napríklad nasledujúca žiadosť je neplatná:

VYBRAŤpersonálNie,COUNT (plat)

ODpersonál;

Chyba je v tom, že táto požiadavka neobsahuje konštrukciu GROUP BY a prístup k stĺpcu staffNo v zozname SELECT sa vykoná bez použitia agregačnej funkcie.

Príklad 13. Použitie funkcie COUNT (*).Zistite, koľko nehnuteľností na prenájom má nájomnú cenu vyššiu ako 350 £ mesačne,

VYBERTE POČET(*) Počet AS

ODPropertyForRent

KDEnájom> 350;

Klauzula WHERE je obmedzená na započítanie iba položiek na prenájom, ktoré sú viac ako 350 £ mesačne. Celkový počet nehnuteľností na prenájom, ktoré spĺňajú zadanú podmienku, je možné určiť pomocou agregačnej funkcie COUNT. Výsledky vykonania dotazu sú uvedené v tabuľke. 23.

Tabuľka 23

počítať

Príklad 14. Pomocou funkcie COUNT (DISTINCT).Zistite, koľko rôznych nehnuteľností na prenájom si zákazníci prezerali v máji 2001.

VYBERTE POČET (ODLIŠNÉpropertyNo) AS počítať

ODPrezeranie

Obmedzenie výsledkov dotazu na analýzu len tých prenájmov, ktoré boli zobrazené v máji 2001, sa opäť dosiahne použitím klauzuly WHERE. Celkový počet skúmaných objektov, ktoré spĺňajú zadanú podmienku, možno určiť pomocou agregačnej funkcie COUNT. Keďže však rovnaký objekt môžu rôzni klienti vidieť niekoľkokrát, musíte v definícii funkcie zadať kľúčové slovo DISTINCT, aby ste z výpočtu vylúčili duplicitné hodnoty. Výsledky vykonania dotazu sú uvedené v tabuľke. 24.

Tabuľka 24

Príklad 16. Použitie funkcií MIN, MAXnAVG.Vypočítajte hodnotu minimálnej, maximálnej a priemernej mzdy.

VYBERTE MIN(plat) AS min, MAX(plat) AS max, AVG(plat) AS priem

ODpersonál;

V tomto príklade musíte spracovať informácie o všetkých zamestnancoch spoločnosti, takže nemusíte použiť klauzulu WHERE. Požadované hodnoty je možné vypočítať pomocou funkcií MIN, MAX a AVG aplikovaných na stĺpec platov v tabuľke Zamestnanci. Výsledky vykonania dotazu sú uvedené v tabuľke. 26.

Tabuľka 26.

Výsledok dotazu

min	max	priem
9000.00	30000.00	17000.00

Výsledky zoskupovania (klauzula GROUP BY). Príklady súhrnných údajov vyššie sú podobné ako súhrnné riadky, ktoré sa zvyčajne nachádzajú na konci prehľadov. V súčtoch sú všetky podrobné údaje zostavy skomprimované do jedného súhrnného riadku. Veľmi často je však potrebné vytvárať medzisúčty v prehľadoch. Na tento účel možno v príkaze SELECT zadať klauzulu GROUP BY. Zavolá sa dotaz, ktorý obsahuje klauzulu GROUP BY dotaz na zoskupenie pretože zoskupuje údaje vyplývajúce z operácie SELECT a potom vytvára jeden súhrnný riadok pre každú jednotlivú skupinu. Stĺpce uvedené v klauzule GROUP BY sú pomenované zoskupené stĺpce. Norma ISO vyžaduje, aby klauzuly SELECT a GROUP BY spolu úzko súviseli. Pri použití klauzuly GROUP BY v príkaze SELECT musí mať každá položka zoznamu v zozname SELECT jediný význam pre celú skupinu. Okrem toho môže klauzula SELECT obsahovať iba nasledujúce typy prvkov:

názvy stĺpcov;

agregačné funkcie;

konštanty;

Výrazy, ktoré zahŕňajú kombinácie vyššie uvedeného.

Všetky názvy stĺpcov uvedené v zozname SELECT sa musia objaviť aj v klauzule GROUP BY, pokiaľ sa názov stĺpca nepoužíva iba v agregačnej funkcii. Opak nie je vždy pravdou — klauzula GROUP BY môže obsahovať názvy stĺpcov, ktoré nie sú v zozname SELECT. Ak sa klauzula WHERE použije v spojení s klauzulou GROUP BY, potom sa spracuje ako prvá a zoskupia sa len tie riadky, ktoré spĺňajú podmienku vyhľadávania. Norma ISO špecifikuje, že keď sa vykonáva zoskupovanie, všetky chýbajúce hodnoty sa považujú za rovnaké. Ak dva riadky tabuľky v rovnakom stĺpci zoskupenia obsahujú hodnoty null a identické hodnoty vo všetkých ostatných neprázdnych zoskupených stĺpcoch, umiestnia sa do rovnakej skupiny.

Príklad 17. Použitie klauzuly GROUP BY.Určite počet zamestnancov pracujúcich v každom z oddelení spoločnosti, ako aj ich celkové platy.

VYBRAŤčíslo pobočky, COUNT(počet zamestnancov) AS počítať, SUM(plat) AS súčet

ODpersonál

GROUP BYpobočka č

ZORADIŤ PODĽAčíslo pobočky;

Do klauzuly GROUP BY nie je potrebné zahrnúť názvy stĺpcov staffNo a plat, pretože sa zobrazujú iba v zozname SELECT s agregačnými funkciami. Stĺpec branchNo v zozname klauzuly SELECT zároveň nie je priradený k žiadnej agregačnej funkcii az tohto dôvodu musí byť špecifikovaný v klauzule GROUP BY. Výsledky vykonania dotazu sú uvedené v tabuľke. 27.

Tabuľka 27

Výsledok dotazu

pobočka č	počítať	Sum
B003		54000.00
B005		39000.00
B007		9000.00

Koncepčne sa pri vybavovaní tejto požiadavky robia nasledovné kroky.

1. Riadky tabuľky Zamestnanci sú rozdelené do skupín podľa hodnôt v stĺpci číslo oddelenia spoločnosti. V rámci každej zo skupín sú údaje o všetkých pracovníkoch jedného z oddelení spoločnosti. V našom príklade sa vytvoria tri skupiny, ako je znázornené na obr. 1.

2. Pre každú zo skupín sa vypočíta celkový počet riadkov, ktorý sa rovná počtu zamestnancov v oddelení, ako aj súčtu hodnôt v stĺpci mzdy, čo je súčet miezd všetkých zamestnancov oddelenia, o ktorých máme záujem. Potom sa vygeneruje jeden súhrnný riadok pre celú skupinu zdrojových riadkov.

3. Výsledné riadky výslednej tabuľky sú zoradené vzostupne podľa čísla pobočky uvedeného v stĺpci branchNo.

pobočka č	personál č	Plat
V00Z	SG37	12000.00
V00Z	SG14	18000.00
V00Z	SG5	24000.00
B005	SL21	30000.00
B005	SL41	9000.00
B007	SA9	9000.00

COUNT (počet zamestnancov)	SUM (plat)
	54000.00
	39000.00
	9000.00

Ryža. 1. Tri skupiny záznamov vytvorené pri vykonávaní dotazu

Štandard SQL umožňuje umiestniť poddotazy do zoznamu SELECT. Preto vyššie uvedený dotaz môže byť reprezentovaný aj takto:

VYBRAŤčíslo pobočky, (VYBRAŤ POČET (počet zamestnancov)AS počítať

ODZamestnanci s

KDEs.branchNo = b.branchNo),

(SELECT SUM (plat) AS sum

ODZamestnanci s

KDEs.branchNo = b.branchNo)

ODPobočka b

ZORADIŤ PODĽAčíslo pobočky;

V tejto verzii dotazu sa však pre každé z oddelení spoločnosti opísaných v tabuľke pobočiek vygenerujú dva výsledky výpočtu agregačných funkcií, preto sa v niektorých prípadoch môžu zobraziť riadky s nulovými hodnotami.

Obmedzenia zoskupovania (klauzula HAVING). Klauzula HAVING je určená na použitie v spojení s klauzulou GROUP BY na špecifikovanie obmedzení určených na výber tých skupiny, ktorý bude umiestnený vo výslednej tabuľke dotazov. Hoci klauzuly HAVING a WHERE majú podobnú syntax, ich účel je odlišný. Klauzula WHERE je určená na výber jednotlivých riadkov na vyplnenie výslednej tabuľky dotazov, zatiaľ čo klauzula HAVING sa používa na výber skupiny, umiestnené vo výslednej tabuľke dotazov. Norma ISO vyžaduje, aby sa názvy stĺpcov použité v klauzule HAVING objavili v zozname položiek GROUP BY alebo aby sa použili v agregačných funkciách. V praxi vyhľadávané výrazy v klauzule HAVING vždy obsahujú aspoň jednu súhrnnú funkciu; inak sa tieto podmienky vyhľadávania musia umiestniť do klauzuly WHERE a použiť na výber jednotlivých riadkov. (Pamätajte, že agregačné funkcie nemožno použiť v klauzule WHERE.) Klauzula HAVING nie je nevyhnutnou súčasťou jazyka SQL – každý dotaz napísaný pomocou klauzuly HAVING môže byť reprezentovaný inak bez jej použitia.

Príklad 18. Použitie konštrukcie HAVING.Pre každé oddelenie spoločnosti s viac ako jedným personálom určite počet zamestnancov a výšku ich miezd.

VYBRAŤčíslo pobočky, COUN T (č. personálu) AS počítať, SUM(plat) AS súčet

ODpersonál

GROUP BYpobočka č

S POČÍTANÍM(číslo personálu)> 1

ZORADIŤ PODĽAčíslo pobočky;

Tento príklad je podobný predchádzajúcemu, ale sú tu použité ďalšie obmedzenia, ktoré naznačujú, že nás zaujímajú iba informácie o tých oddeleniach spoločnosti, v ktorých pracuje viac ako jedna osoba. Podobná požiadavka je uložená na skupiny, takže v dotaze by sa mala použiť klauzula HAVING. Výsledky vykonania dotazu sú uvedené v tabuľke. 28.

Tabuľka 28

branchNo count sum

V00Z 3 54000,00

B005 2 39000,00

Poddotazy. V tejto časti budeme diskutovať o použití úplných príkazov SELECT vložených do tela iného príkazu SELECT. Vonkajšie(druhý) príkaz SELECT používa výsledok vykonania interné(prvého) operátora na určenie obsahu konečného výsledku celej operácie. Vnútorné dotazy možno nájsť v klauzulách WHERE a HAVING vonkajšieho príkazu SELECT – v tomto prípade sú pomenované poddotazy, alebo vnorené dopyty. Okrem toho možno vnútorné príkazy SELECT použiť v príkazoch INSERT, UPDATE a DELETE . Existujú tri typy poddotazov.

Skalárny poddotaz vráti hodnotu vybranú z priesečníka jedného stĺpca s jedným riadkom, t.j. jediný význam. V zásade je možné skalárny poddotaz použiť všade tam, kde sa vyžaduje jedna hodnota. Príklady skalárnych poddotazov sú uvedené v príkladoch 13 a 14.

Poddotaz reťazca vráti hodnoty viacerých stĺpcov v tabuľke, ale ako jeden riadok. Reťazcový poddotaz možno použiť všade tam, kde sa používa konštruktor hodnoty reťazca — zvyčajne predikáty. Variant poddotazu reťazca je zobrazený v príklade 15.

Poddotaz tabuľky vráti hodnoty jedného alebo viacerých stĺpcov tabuľky rozložené vo viac ako jednom riadku. Poddotaz tabuľky možno použiť všade tam, kde je možné zadať tabuľku, napríklad ako operand predikátu IN.

Príklad 19. Použitie poddotazu s kontrolou rovnosti. Makeup zoznam zamestnancov pracujúcich v pobočke spoločnosti na adrese 463 Main St1.

VYBRAŤ

ODpersonál

KDEčíslo pobočky = (VYBERTE číslo pobočky

ODPobočka

KDEulica = "163 Main S t");

Interný príkaz SELECT (SELECT branchNo FROM Branch ...) sa používa na určenie čísla pobočky spoločnosti so sídlom na 163 Main St. (V spoločnosti je len jedno takéto oddelenie, takže tento príklad je príkladom skalárneho poddotazu.) Po získaní požadovaného čísla oddelenia sa vykoná externý poddotaz na získanie podrobností o pracovníkoch v tomto oddelení. Inými slovami, vnútorný príkaz SELECT vráti tabuľku pozostávajúcu z jedinej hodnoty "BOOV. Toto je číslo pobočky spoločnosti umiestnenej na" 163 Main St1. Výsledkom je, že vonkajší príkaz SELECT bude vyzerať takto:

VYBRAŤstaffNo, fName, Iname, position

ODpersonál

KDEčíslo pobočky = "B0031;

Výsledky tohto dotazu sú uvedené v tabuľke. 29.

Tabuľka 29

Výsledok dotazu

personál č	fName	Volám sa	pozíciu
SG37	Ann	Buk	asistent
SG14	David	Ford	Dozorca
SG5	Susan	Značka	manažér

Poddotaz je nástroj na vytváranie dočasnej tabuľky, ktorej obsah získava a spracováva externý operátor. Poddotaz možno zadať ihneď po porovnávacích operátoroch (t. j. operátory =,<, >, <=, >=, <>) v klauzule WHERE alebo HAVING. Text poddotazu musí byť uzavretý v zátvorkách.

Príklad 20. Používanie poddotazov s agregačnými funkciami. Uveďte všetkých zamestnancov s nadpriemernými platmi a uveďte, o koľko je ich plat vyšší ako priemerný plat v podniku.

VYBRAŤstaffNo, fName, INname, pozícia, plat - ( VYBERTE AVG(plat) OD personál) AS salDiff

ODpersonál

KDEplat> ( VYBERTE AVG(plat) OD S t a f f);

Treba si uvedomiť, že človek nemôže priamo zahrnúť do výraz dotazu"KDE plat> AVG (plat)", od používania agregácie funkcie v klauzule WHERE sú zakázané. Ak chcete dosiahnuť požadovaný výsledok, vytvorte poddotaz, ktorý vypočíta priemernú ročnú mzdu a potom ho použite v externom príkaze SELECT na získanie informácií o tých zamestnancoch v spoločnosti, ktorých platy sú vyššie ako tento priemer. Inými slovami, poddotaz vráti priemernú mzdu spoločnosti za rok, čo je 17 000 GBP.

Výsledok tohto skalárneho poddotazu sa používa vo vonkajšom príkaze SELECT na výpočet rozptylu miezd od priemeru a na výber informácií o zamestnancoch. Preto sa vonkajší príkaz SELECT zmení na:

VYBRAŤstaffNo, fName, INname, pozícia, plat - 17000 Ako salDiff

ODpersonál

KDEplat> 17000;

Výsledky vykonania dotazu sú uvedené v tabuľke. tridsať.

Tabuľka 30.

Výsledok dotazu

personál č	fName	Volám sa	pozíciu	salDiff
SL21	John	biely	manažér	13000.00
SG14	David	Ford	Dozorca	1000.00
SG5	Susan	Značka	manažér	7000.00

Použiť na poddotazy dodržiavanie pravidiel a obmedzenia.

1. Klauzula ORDER BY by sa nemala používať v poddotazoch, hoci sa môže objaviť vo vonkajšom príkaze SELECT.

2. Zoznam SELECT poddotazu musí pozostávať z názvov jednotlivých stĺpcov alebo výrazov z nich zložených, pokiaľ nie je v poddotaze použité kľúčové slovo EXISTS.

3. Názvy stĺpcov v poddotaze štandardne odkazujú na tabuľku pomenovanú v klauzule FROM poddotazu. Môžete však tiež odkazovať na stĺpce tabuľky špecifikované v klauzule FROM vonkajšieho dotazu pomocou kvalifikovaných názvov stĺpcov (ako je popísané nižšie).

4. Ak je poddopyt jedným z dvoch operandov zahrnutých v operácii porovnania, potom musí byť poddotaz špecifikovaný na pravej strane tejto operácie. Napríklad nasledujúci príklad písania dotazu z predchádzajúceho príkladu je nesprávny, pretože poddotaz je umiestnený na ľavej strane operácie porovnania s hodnotou stĺpca mzdy.

VYBRAŤ

ODpersonál

KDE(VYBERTE AVG (plat) OD zamestnancov)< salary;

Príklad 21... Vnorené poddotazy a použitie predikátu IN. Urobte si zoznam nehnuteľností na prenájom, za ktoré sú zamestnanci zodpovední, v pobočke na adrese 163 Main st1.

VYBRAŤpropertyNo, ulica, mesto, PSČ, typ, izby, nájomné

ODPropertyForRent

Kapitola 5... Jazyk SQL: Manipulácia s údajmi 189

KDEstaffNo IN (SELECT staffNo

ODpersonál

KDEbrancliNo = (VYBERTE číslo pobočky

ODPobočka

KDEulica = "163 Main S t"));

Prvý, najinternejší dotaz je určený na určenie čísla pobočky spoločnosti na adrese 463 Main St. vybraté a preto v externom dotaze nemôžete použiť operátor porovnávania =. Namiesto neho musíte použiť kľúčové slovo IN. Vonkajší dotaz získava informácie o prenájmoch, za ktoré sú zodpovední zamestnanci spoločnosti, údaje o ktorých boli získané ako výsledok priebežného dopytu. Výsledky dopytu sú uvedené v tabuľke. 31.

Tabuľka 31

Výsledok dotazu

č	ulica	mesto	PSČ	typu	izby	nájomné
PG16	5 Novar Dr	Glasgow	G129AX	Plochý
PG36	2 Kaštieľ Rd	Glasgow	G324QX	Plochý
PG21	18 Dale Rd	Glasgow	G12	Dom

Kľúčové slová ANY and ALL. Kľúčové slová ANY a ALL možno použiť s poddotazmi, ktoré vracajú jeden stĺpec čísel. Ak poddotazu predchádza kľúčové slovo ALL, podmienka porovnania je splnená iba vtedy, ak je splnená pre všetky hodnoty vo výslednom stĺpci poddotazu. Ak pred textom poddotazu predchádza kľúčové slovo ANY, potom sa podmienka porovnania bude považovať za splnenú, ak bude splnená aspoň pre jednu (jednu alebo niekoľko) hodnôt vo výslednom stĺpci poddotazu. Ak je výsledkom poddotazu nulová hodnota, potom sa podmienka porovnania pre kľúčové slovo VŠETKY bude považovať za splnenú a pre kľúčové slovo ANY bude považovaná za nesplnenú. Podľa normy ISO môžete navyše použiť kľúčové slovo SOME, ktoré je synonymom pre kľúčové slovo ANY.

Príklad 22. Pomocou kľúčových slov ANY a SOME. Nájdite všetkých zamestnancov, ktorých plat prevyšuje aspoň plat jeden zamestnanec pobočky firmy s číslom „booz“.

VYBRAŤstaffNo, fName, INname, pozícia, plat

ODpersonál

KDEplat> NIEKTORÉ (VYBERTE plat

ODpersonál

KDEčíslo pobočky = "B003");

Tento dotaz je síce možné napísať pomocou poddotazu, ktorý určuje minimálnu mzdu zamestnancov oddelenia s číslom „BOOZ“, po ktorom môže externý poddotaz vybrať informácie o všetkých zamestnancoch spoločnosti, ktorých mzda je väčšia ako táto hodnota (pozri príklad 20), je možný aj iný prístup, ktorý spočíva v použití kľúčových slov SOME / ANY. V tomto prípade vnútorný poddotaz vytvorí množinu hodnôt (12 000, 18 000, 24 000) a vonkajší dotaz vyberie informácie o tých zamestnancoch, ktorých plat je väčší ako ktorákoľvek z hodnôt v tomto

set (v skutočnosti viac ako minimálna hodnota - 12000). Tento alternatívny spôsob možno považovať za prirodzenejší ako definovanie minimálnej mzdy v poddotazi. Ale v oboch prípadoch sa vygenerujú rovnaké výsledky dotazu, ktoré sú uvedené v tabuľke. 32 .

Tabuľka 32

Výsledok dotazu

personál č	fName	Volám sa	pozíciu	plat
SL21	John	biely	manažér	30000.00
SG14	David	Ford	Dozorca	18000.00
SG5	Susan	Značka	manažér	24000.00

Príklad 23. Pomocou kľúčového slova ALL. Nájdite všetkých zamestnancov, ktorých mzdy sú vyššie ako mzdy ktoréhokoľvek zamestnanca v pobočke firmy.

VYBRAŤstaffNo, fName, INarae, pozícia, plat

ODpersonál

KDEplat> VŠETKY(VYBERTE plat

ODpersonál

KDEčíslo pobočky = "BOG3");

Vo všeobecnosti je táto žiadosť podobná predchádzajúcej. A v tomto prípade by bolo možné použiť poddotaz, ktorý určí maximálnu hodnotu platu pre zamestnancov oddelenia pod číslom „WOOZ“ a následne pomocou externého dotazu vybrať informácie o všetkých zamestnancoch spoločnosti, ktorých platy presahujú túto hodnotu. V tomto príklade je však zvolený prístup kľúčových slov VŠETKY. Výsledky vykonania dotazu sú uvedené v tabuľke. 33 .

Tabuľka 33

Výsledok dotazu

personál č	Volám sa	fName	pozíciu	plat
SL21	biely	John	manažér	30000,00

Dotazy s viacerými tabuľkami. Všetky vyššie uvedené príklady majú rovnaké dôležité obmedzenie: stĺpce umiestnené vo výslednej tabuľke sú vždy vybrané z jednej tabuľky. V mnohých prípadoch to však nestačí. Ak chcete vo výslednej tabuľke skombinovať stĺpce z niekoľkých zdrojových tabuliek, musíte vykonať operáciu spojenia. V SQL sa operácia spojenia používa na spojenie informácií z dvoch tabuliek vytvorením párov spojených riadkov vybraných z každej tabuľky. Páry riadkov umiestnené v kombinovanej tabuľke sú zostavené podľa rovnosti hodnôt špecifikovaných stĺpcov, ktoré sú v nich zahrnuté.

Ak potrebujete získať informácie z niekoľkých tabuliek, môžete použiť poddotaz alebo vykonať spojenie medzi tabuľkami. Ak výsledná tabuľka dotazu musí obsahovať stĺpce z rôznych zdrojových tabuliek, potom je vhodné použiť mechanizmus spájania tabuliek. Ak chcete vykonať spojenie, stačí zadať názvy dvoch alebo viacerých tabuliek v klauzule FROM, oddeliť ich čiarkami, a potom zahrnúť do dotazu klauzulu WHERE, ktorá definuje stĺpce použité na spojenie špecifikovaných tabuliek. Okrem toho môžete namiesto názvov tabuliek použiť prezývky, im priradené v doložke FROM. V tomto prípade musia byť názvy tabuliek a k nim priradené aliasy oddelené medzerami. Aliasy možno použiť na kvalifikovanie názvov stĺpcov vždy, keď existuje nejednoznačnosť, do ktorej tabuľky konkrétny stĺpec patrí. Okrem toho je možné použiť aliasy na skrátenie názvov tabuliek. Ak je pre tabuľku definovaný alias, možno ho použiť kdekoľvek, kde sa vyžaduje názov tejto tabuľky.

Príklad 24. Jednoduché pripojenie. Uveďte mená všetkých zákazníkov, ktorí si prezreli aspoň jednu nehnuteľnosť na prenájom a poskytli k nej spätnú väzbu.

VYBRAŤc.clientNo, fName, Iname, propertyNo, comment

ODKlient c, Prehliadka v

KDEc.clientNo = v.clientNo;

Tento prehľad vyžaduje informácie z tabuľky Klient aj z tabuľky zobrazenia, preto pri vytváraní dotazu použijeme mechanizmus spájania tabuliek. Klauzula SELECT uvádza zoznam všetkých stĺpcov, ktoré by mali byť umiestnené vo výslednej tabuľke dotazov. Všimnite si, že stĺpec čísla klienta (clientNo) potrebuje spresnenie, pretože takýto stĺpec sa môže nachádzať aj v inej tabuľke, ktorá sa zúčastňuje spojenia. Preto je potrebné výslovne uviesť, o ktoré hodnoty tabuľky máme záujem. (V tomto príklade by ste mohli rovnako dobre vybrať hodnoty pre stĺpec clientNo z tabuľky zobrazenia). Názov je určený predponou názvu stĺpca s názvom zodpovedajúcej tabuľky (alebo jej aliasu). V našom príklade je použitá hodnota "c", zadaná ako alias pre tabuľku Klient. Na vytvorenie výsledných riadkov sa použijú tie riadky zdrojových tabuliek, ktoré majú rovnakú hodnotu v stĺpci clientNo. Táto podmienka je určená nastavením podmienky vyhľadávania with.clientNo = v.clientNo. Podobné stĺpce v pôvodných tabuľkách sú tzv kompatibilné stĺpce. Opísaná operácia je ekvivalentná operácii rovnosť sa spája relačná algebra. Výsledky vykonania dotazu sú uvedené v tabuľke. 34.

Tabuľka 34

Výsledok dotazu

klient č	fName	Volám sa	č	komentovať
CR56	Aline	Stewart	PG36
CR56	Aline	Stewart	PA14	príliš malá
CR56	Aline	Stewart	PG4
CR62	Mary	Tregear	PA14	žiadna jedáleň
CR76	John	Kay	PG4	príliš vzdialené

Dopyty s viacerými tabuľkami sa najčastejšie vykonávajú na dvoch tabuľkách spojených vzťahom jedna k mnohým (1: *) alebo rodič-dieťa. Vo vyššie uvedenom príklade, ktorý zahŕňa prístup k tabuľkám Klient a Prezeranie, sú tieto tabuľky spojené práve takýmto vzťahom. Každý riadok v tabuľke Prezeranie (podriadený) je prepojený iba s jedným riadkom v tabuľke Klient (nadradená), zatiaľ čo rovnaký riadok v tabuľke Klient (nadradenej) môže byť prepojený.

s mnohými riadkami tabuľky zobrazenia (dieťa). Páry riadkov, ktoré sa vygenerujú pri vykonávaní dotazu, sú výsledkom všetkých platných kombinácií riadkov v podriadených a rodičovských tabuľkách. Časť 3.2.5 podrobne opísala, ako v relačnej databáze primárny a cudzí kľúč tabuliek vytvárajú vzťah „rodič-dieťa“. Tabuľka obsahujúca cudzí kľúč je zvyčajne potomok, zatiaľ čo tabuľka obsahujúca primárny kľúč bude vždy nadradená. Ak chcete použiť vzťah rodič-podriadený v dotaze SQL, musíte zadať podmienku vyhľadávania, v ktorej sa budú porovnávať cudzí a primárny kľúč. Príklad 24 porovnáva primárny kľúč tabuľky Client (s. ClientNo) s cudzím kľúčom tabuľky Viewing (v. ClientNo).

Štandard SQL navyše poskytuje nasledujúce spôsoby definovania daného pripojenia:

ODKlient s PRIPOJTE SA Prezeranie v ON c.clientNo = v.clientNo

ODKlient J OIN Prezeranie POUŽITÍM klient č

ODZákazník PRIRODZENÉ SPOJENIE Prezeranie

V každom prípade klauzula FROM nahrádza pôvodné klauzuly FROM a WHERE. Prvá možnosť však vytvorí tabuľku s dvoma rovnakými stĺpcami clientNo, zatiaľ čo v ďalších dvoch prípadoch bude výsledná tabuľka obsahovať iba jeden stĺpec clientNo.

Príklad 25. Triedenie výsledkov spájania tabuliek. Pre každú pobočku spoločnosti uveďte personálne čísla a mená zamestnancov, ktorí majú na starosti prípadné prenajaté priestory, a uveďte aj zariadenia pre

na ktoré odpovedajú.

VYBRAŤs.branchNo, s.staffNo, fName, INname, propertyNo

ODStaff s, PropertyForRent s

KDEs.staffNo = p.staffNo

ZORADIŤ PODĽAs.pobočka, s.staffNo, propertyNo;

Na uľahčenie čítania výsledkov sa výstup triedi pomocou čísla oddelenia ako hlavného kľúča triedenia a čísla personálu a čísla majetku ako vedľajších kľúčov. Výsledky vykonania dotazu sú uvedené v tabuľke. 35.

Tabuľka 35

Výsledok dotazu

pobočka č	StaffNo	fName	Volám sa	č
SZO	SG14	David	Ford	PG16
SZO	SG37	Ann	Buk	PG21
SZO	SG37	Ann	Buk	PG36
BOO5	SL41	Mary	Lee	PL94
SBI7	SA9	Julie	Howe	PA14

Príklad 26. Spojenie troch stolov. Pri každej pobočke spoločnosti uveďte personálne čísla a mená zamestnancov, ktorí majú na starosti prípadné prenajaté priestory, s uvedením mesta, v ktorom sa pobočka nachádza, a čísel zariadení, za ktoré je každý zamestnanec zodpovedný.

VYBRAŤ b.pobočkaNo, b.city, s.staffNo, fName, INname, propertyNo

OD Pobočka b, Staff s, PropertyForRent s

KDEč. b.pobočky = č. č. pobočky A č. č. č. = č. č

ZORADIŤ PODĽA b.pobočka č, s.staffNo, propertyNo;

Do výslednej tabuľky je potrebné umiestniť stĺpce z troch zdrojových tabuliek – Pobočka, Zamestnanci a PropertyForRent, takže tieto tabuľky by ste mali vo svojom dotaze spojiť. Tabuľky Pobočka a Zamestnanci je možné prepojiť pomocou klauzuly b.branchNo = * s .branchNo, ktorá prepojí pobočky spoločnosti s pracovníkmi, ktorí v nich pracujú. Tabuľky Zamestnanci a PropertyForRent môžu byť prepojené pomocou podmienky s.staffNo = p.staffNo. V dôsledku toho bude každý zamestnanec spojený s nehnuteľnosťami na prenájom, za ktoré je zodpovedný. Výsledky vykonania dotazu sú uvedené v tabuľke. 36.

Tabuľka 36

Výsledky dotazu

pobočka č	mesto	zamestnanciMo	fName	Volám sa	č
B003	Glasgow	SG14	David	Ford	PG16
B003	Glasgow	SG37	Ann	Buk	PG21
B003	Glasgow	SG37	Ann	Buk	PG36
B005	Londýn	SL41	Julie	Lee	PL94
B007	Aberdeen	SA9	Mary	Howe	PA14

Všimnite si, že štandard SQL vám umožňuje použiť alternatívnu formuláciu klauzúl FROM a WHERE:

OD(Pobočka b PRIPOJTE SA Zamestnanci POUŽÍVAJÚ pobočku č.) AS bs

PRIPOJTE SAPropertyForRent str POUŽITÍM personál č

Príklad 27. Zoskupenie podľa viacerých stĺpcov. Určite počet nehnuteľností na prenájom, za ktoré je zodpovedný každý zamestnanec.

VYBRAŤč. pobočky, č. COUNT(*) AS počítať

OD Staff s, PropertyForRent s

KDE S.staffNo = p.staffNo

GROUP BYč. pobočky, č

ZORADIŤ PODĽAč. pobočky, č.

Na vypracovanie požadovaného posudku je potrebné v prvom rade zistiť, ktorý zo zamestnancov spoločnosti je zodpovedný za prenajaté predmety. Túto úlohu možno vyriešiť spojením tabuliek Staff a PropertyForRent stĺpcom staffNo v klauzulách FROM / WHERE. Potom je potrebné vytvoriť skupiny pozostávajúce z čísla oddelenia a personálnych čísiel jeho zamestnancov, pre ktoré by mala platiť doložka GROUP BY. Nakoniec je potrebné výslednú tabuľku zoradiť zadaním klauzuly ORDER BY. Výsledky vykonania dotazu sú uvedené v tabuľke. 37.

Tabuľka 37

Výsledok dotazu

pobočka č	personál č	počítať
V00Z	SG14
V00Z	SG37
B005	SL41
B007	SA9

Vytváranie spojení. Spojenie je podmnožinou všeobecnejšej kombinácie údajov z dvoch tabuliek, tzv karteziánsky... Kartézsky súčin dvoch tabuliek je ďalšia tabuľka, pozostávajúca zo všetkých možných párov riadkov, ktoré tvoria obe tabuľky. Množinou stĺpcov vo výslednej tabuľke sú všetky stĺpce v prvej tabuľke, po ktorých nasledujú všetky stĺpce v druhej tabuľke. Ak zadáte dotaz na dve tabuľky bez zadania klauzuly WHERE, výsledkom vykonania dotazu v prostredí SQL bude karteziánsky súčin týchto tabuliek. Okrem toho štandard ISO poskytuje špeciálny formát pre príkaz SELECT, ktorý vám umožňuje vypočítať karteziánsky súčin dvoch tabuliek:

SELECT (* j columnList]

FROM tableNamel CROSS JOINCaNeulte2

Uvažujme opäť o príklade, v ktorom sú klient a Zobrazovacie tabuľky spojené pomocou spoločného stĺpca clientNo Pri práci s tabuľkami, ktorých obsah je uvedený v tabuľke. 3.6 a 3.8, karteziánsky súčin týchto tabuliek bude 20 riadkov (4 riadky v tabuľke Klient x 5 riadkov v prezeracej tabuľke = 20 riadkov). Je to ekvivalentné zadávaniu dotazu použitého v príklade 5.24, ale bez použitia klauzuly WHERE. Postup na generovanie tabuľky obsahujúcej výsledky spojenia dvoch tabuliek pomocou príkazu SELECT je nasledovný.

1. Vznikne kartézsky súčin tabuliek špecifikovaných v klauzule FROM.

2. Ak dotaz obsahuje klauzulu WHERE, aplikujte podmienky vyhľadávania na každý riadok kartézskej tabuľky produktov a uložte do tabuľky iba tie riadky, ktoré spĺňajú zadané podmienky. Z hľadiska relačnej algebry sa táto operácia nazýva obmedzenie karteziánsky súčin.

3. Pre každý zostávajúci riadok sa určí hodnota každého prvku uvedeného v zozname SELECT, v dôsledku čoho sa vytvorí samostatný riadok výslednej tabuľky.

4. Ak sa v pôvodnom dotaze nachádza klauzula SELECT DISTINCT, z výslednej tabuľky sa odstránia všetky duplicitné riadky.

5. Ak vykonávaný dotaz obsahuje klauzulu ORDER BY,

© stránka 2015-2019
Všetky práva patria ich autorom. Táto stránka si nenárokuje autorstvo, ale poskytuje bezplatné používanie.
Dátum vytvorenia stránky: 2016-08-07

Na zhrnutie informácií obsiahnutých v databáze poskytuje SQL súhrnné funkcie. Agregačná funkcia berie ako argument celý stĺpec údajov a vracia jedinú hodnotu, ktorá sumarizuje daný stĺpec špecifickým spôsobom.

Napríklad agregačná funkcia AVG () berie stĺpec čísel ako argument a vypočíta ich priemer.

Na výpočet priemerného príjmu na hlavu obyvateľa Zelenogradu potrebujete nasledujúci dotaz:

VYBERTE „Priemerný príjem =“, AVG (SUMD)

SQL má šesť agregovaných funkcií, ktoré poskytujú rôzne druhy súhrnov (obrázok 1):

- SUM () vypočíta súčet všetkých hodnôt obsiahnutých v stĺpci;

- AVG () vypočíta priemer medzi hodnotami obsiahnutými v stĺpci;

- MIN () nájde najmenšiu spomedzi všetkých hodnôt obsiahnutých v stĺpci;

- MAX () nájde najväčšiu zo všetkých hodnôt obsiahnutých v stĺpci;

- COUNT () počíta počet hodnôt obsiahnutých v stĺpci;

- COUNT (*) počíta počet riadkov v tabuľke výsledkov dotazu.

Argumentom agregačnej funkcie môže byť jednoduchý názov stĺpca, ako v predchádzajúcom príklade, alebo výraz, ako v nasledujúcom dotaze, ktorý vypočítava daň na obyvateľa:

VYBERTE AVG (SUMD * 0,13)

Tento dotaz vytvorí dočasný stĺpec obsahujúci hodnoty (SUMD * 0,13) pre každý riadok v tabuľke PERSON a potom vypočíta priemer dočasného stĺpca.

Súčet príjmov všetkých obyvateľov Zelenogradu možno vypočítať pomocou súhrnnej funkcie SUM:

VYBERTE SÚČET (SUMD) OD OSOBY

Agregátnu funkciu možno použiť aj na výpočet súčtu pre tabuľku výsledkov získaných spojením niekoľkých zdrojových tabuliek. Môžete napríklad vypočítať celkovú výšku príjmu, ktorý obyvatelia dostávajú zo zdroja nazývaného „štipendium“:

VYBERTE SUMU (PENIAZE)

ZO ZISKU, HAVE_D

WHERE PROFIT.ID = HAVE_D.ID

AND PROFIT.SOURCE = 'Štipendium'

Súhrnné funkcie MIN () a MAX () vám umožňujú nájsť najmenšie a najväčšie hodnoty v tabuľke. Stĺpec však môže obsahovať číselné alebo reťazcové hodnoty alebo hodnoty dátumu alebo času.

Môžete napríklad definovať:

a) najnižší celkový príjem rezidentov a najvyššia splatná daň:

SELECT MIN (SUMD), MAX (SUMD * 0,13)

b) dátumy narodenia najstaršieho a najmladšieho obyvateľa:

SELECT MIN (RDATE), MAX (RDATE)

c) priezviská, krstné mená a priezviská úplne prvých a úplne posledných obyvateľov v zozname zoradené abecedne:

VYBERTE MIN (FIO), MAX (FIO)

Pri použití týchto agregačných funkcií je potrebné pamätať na to, že číselné údaje sa porovnávajú podľa aritmetických pravidiel, dátumy sa porovnávajú postupne (hodnoty skorších dátumov sa považujú za menšie ako neskoršie), časové intervaly sa porovnávajú na základe ich trvania.

Pri použití MIN () a MAX () s údajmi reťazca závisí výsledok porovnávania týchto dvoch reťazcov od použitej tabuľky kódovania znakov.

Agregačná funkcia COUNT () počíta počet hodnôt v stĺpci ľubovoľného typu:

a) koľko bytov je v 1. mikrookrese?

VYBERTE POČET (ADR) Z PLOCHÉHO, KDE ADR AKO "%, 1_ _-%"

b) koľko obyvateľov má zdroje príjmu?

VYBERTE POČET (ODLIŠNÉ NOM) OD HAVE_D

c) koľko zdrojov príjmu využívajú rezidenti?

SELECT COUNT (DISTINCT ID) FROM HAVE_D (Kľúčové slovo DISTINCT určuje, že sa počítajú neduplikované hodnoty v stĺpci).

Špeciálna agregačná funkcia COUNT (*) počíta riadky v tabuľke výsledkov, nie hodnoty údajov:

a) koľko bytov je v 2. mikroobvode?

VYBERTE POČET (*) Z PLOCHÉHO, KDE ADR AKO "%, 2 __-%"

b) koľko zdrojov príjmu má Ivan Ivanovič?

VYBERTE POČET (*) OD OSOBY, HAVE_D WHERE FIO = "Ivanov Ivan Ivanovič" A PERSON.NOM = HAVE_D.NOM

c) koľko obyvateľov býva v byte na konkrétnej adrese?

VYBERTE POČET (*) OD OSOBY, KDE ADR = "Zelenograd, 1001-45"

Jedným zo spôsobov, ako pochopiť, ako sú vykonávané súhrnné dotazy, je myslieť na vykonávanie dotazov v dvoch častiach. Najprv sa určí, ako by dotaz fungoval bez agregačných funkcií, ktoré by vracali viacero riadkov výsledkov. Súhrnné funkcie sa potom použijú na výsledky dotazu a vrátia jeden súhrnný riadok.

Zvážte napríklad nasledujúci zložitý dotaz: nájdite priemerný celkový príjem na obyvateľa, súčet celkových príjmov obyvateľov a priemerný príjem zdroja ako percento z celkového príjmu obyvateľa. Odpoveď dáva operátor

VYBERTE AVG (SUMD), SUM (SUMD), (100 * AVG (PENIAZE / SUMD)) OD OSOBY, PROFIT, HAVE_D KDE OSOBA.NOM = MAJ_D.NOM A HAVE_D.ID = PROFIT.ID

Bez agregačných funkcií by dotaz vyzeral takto:

VYBERTE SÚČET, SÚČET, PENIAZE / SÚČET OD OSOBY, ZISK, HAVE_D KDE PERSON.NOM = HAVE_D.NOM A HAVE_D.ID = PROFIT.ID

a vráti jeden riadok výsledkov pre každého rezidenta a konkrétny zdroj príjmu. Agregačné funkcie používajú stĺpce v tabuľke výsledkov tohto dotazu na vytvorenie jednoriadkovej tabuľky so súhrnnými výsledkami.

V riadku vrátených stĺpcov môžete namiesto názvu ľubovoľného stĺpca zadať súhrnnú funkciu. Môže byť napríklad zahrnutý do výrazu, ktorý pridáva alebo odčítava hodnoty dvoch agregovaných funkcií:

VYBERTE MAX (SUMD) -MIN (SUMD) OD OSOBY

Agregačná funkcia však nemôže byť argumentom inej agregačnej funkcie, t.j. vnorené agregované funkcie sú zakázané.

Okrem toho nemožno v zozname vrátených stĺpcov súčasne použiť agregačné funkcie a bežné názvy stĺpcov, pretože to nemá zmysel, napríklad:

VYBERTE FIO, SÚČET (SUMD) OD OSOBY

Tu prvý prvok zoznamu dáva pokyn DBMS, aby vytvoril tabuľku, ktorá bude pozostávať z niekoľkých riadkov a bude obsahovať jeden riadok pre každého rezidenta. Druhá položka v zozname žiada DBMS, aby získal jednu výslednú hodnotu, ktorá je súčtom hodnôt v stĺpci SUMD. Tieto dve usmernenia si navzájom odporujú, čo vedie k chybe.

Uvedené neplatí pre prípady spracovania poddotazov a dopytov so zoskupovaním.

• Súhrnné funkcie sa používajú ako názvy polí v príkaze SELECT, s jednou výnimkou: berú názov poľa ako argument. S funkciami SUM a AVG možno použiť iba číselné polia. S funkciami COUNT, MAX a MIN možno použiť číselné aj znakové polia. Pri použití so znakovými poľami MAX a MIN preloží ich do ASCII ekvivalentu a spracuje ich v abecednom poradí. Niektoré DBMS umožňujú použitie vnorených agregátov, ide však o odchýlku od štandardu ANSI so všetkými z toho vyplývajúcimi dôsledkami.

Môžete napríklad vypočítať počet študentov, ktorí zložili skúšky v jednotlivých disciplínach. Ak to chcete urobiť, musíte vykonať dotaz zoskupený podľa poľa "Disciplína" a zobraziť ako výsledok názov disciplíny a počet riadkov v skupine pre túto disciplínu. Použitie znaku * ako argumentu funkcie COUNT znamená, že sa spočítajú všetky riadky v skupine.

VYBERTE R1. Disciplína, COUNT (*)

GROUP BY R1. Disciplína;

výsledok:

SELECT R1 Disciplína, COUNT (*)

KDE R1. NIE JE NULL skóre

GROUP BY R1. Disciplína;

výsledok:

nebudú zaradené do množiny n-tic pred zoskupením, preto bude počet n-tic v skupine pre disciplínu „Teória informácie“ o 1 menší.

Podobný výsledok možno získať, ak žiadosť napíšete nasledujúcim spôsobom:

VYBERTE R1. Disciplína, POČET (R1. Hodnotenie)

SKUPINA PODĽA R1. Disciplína;

Funkcia COUNT (NÁZOV ATRIBUTU) počíta počet definovaných hodnôt v skupine, na rozdiel od funkcie COUNT (*), ktorý počíta počet riadkov v skupine. V skupine s disciplínou „Teória informácií“ budú skutočne 4 riadky, ale iba 3 špecifické hodnoty atribútu „Assessment“.

Pravidlá pre spracovanie hodnôt null v agregovaných funkciách

Ak sú niektoré hodnoty v stĺpci rovnaké NULOVÝ sú vylúčené pri výpočte výsledku funkcie.

Ak sú všetky hodnoty v stĺpci rovnaké NULOVÝ, potom Max. min. súčet priem = NULL, počet = 0 (nula).

Ak je stôl prázdny, počet (*) = 0 .

Agregátne funkcie možno použiť aj bez predbežnej operácie zoskupenia, v tomto prípade sa celý vzťah považuje za jednu skupinu a pre túto skupinu možno vypočítať jednu hodnotu na skupinu.

Pravidlá pre interpretáciu agregovaných funkcií

Agregátne funkcie môžu byť zahrnuté do zoznamu výstupov a potom sú aplikované na celú tabuľku.

VYBERTE MAX (Skóre) z R1 na stretnutí udelí maximálne hodnotenie;

VYBERTE SÚČET z R1 poskytne súčet všetkých hodnotení za sedenie;

VYBERTE AVG (Skóre) z R1 poskytne priemerné skóre za celú reláciu.

2; Výsledok: "width =" 640 "

Opäť s odkazom na databázu relácií (tabuľky R1) zistíme počet úspešne zložených skúšok:

VYBRAŤ POČET (*) Ako Prenajaté _ skúšok

WHERE stupeň 2;

výsledok:

Argumentom agregačných funkcií môžu byť jednotlivé stĺpce tabuliek. Na výpočet napríklad počtu odlišných hodnôt určitého stĺpca v skupine je potrebné použiť kľúčové slovo DISTINCT spolu s názvom stĺpca. Vypočítajme počet rôznych známok získaných za každú disciplínu:

SELECT R1 Disciplína, COUNT (ODLIŠNÉ R1 hodnotenie)

KDE R1. NIE JE NULL skóre

GROUP BY R1. Disciplína;

výsledok:

Rovnaký výsledok sa získa, ak vylúčite explicitnú podmienku v časti WHERE, v takom prípade bude dotaz vyzerať takto:

VYBERTE R1. Disciplína, POČET (DISTINCT R1. Hodnotenie)

SKUPINA PODĽA R1. Disciplína;

Funkcia COUNT (ODLIŠNÉ R1. Hodnotenie) považuje len za isté rôzne hodnoty.

Aby sa v tomto prípade dosiahol požadovaný výsledok, je potrebné vykonať predbežnú transformáciu typu údajov stĺpca "Skóre" a uviesť ho na skutočný typ, potom výsledkom výpočtu priemeru nebude celé číslo. V tomto prípade bude žiadosť vyzerať takto:

2 Zoskupiť podľa R2. Skupina, R1. Disciplína; Tu funkcia CAST () skonvertuje stĺpec "Skóre" na platný typ údajov. "width =" 640 "

Vyberte R2.Group, R1.Discipline, Count (*) ako Total, AVG (cast (Skóre ako desatinné (3,1))) ako Average_point

Od R1, R2

kde R1. Celé meno = R2. Meno a R1. skóre nie je nulové

a R1. 2. ročník

Zoskupiť podľa R2. Skupina, R1. Disciplína;

Tu je funkcia ODOSIELAŤ () skonvertuje stĺpec Skóre na platný typ údajov.

V klauzule WHERE nemôžete použiť agregačné funkcie, pretože podmienky v tejto časti sa vyhodnocujú z hľadiska jedného riadka a agregačné funkcie sa vyhodnocujú z hľadiska skupín riadkov.

Klauzula GROUP BY vám umožňuje definovať podmnožinu hodnôt v konkrétnom poli z hľadiska iného poľa a použiť na podmnožinu súhrnnú funkciu. To umožňuje kombinovať polia a agregačné funkcie do jednej klauzuly SELECT. Agregačné funkcie možno použiť ako vo výraze na výstup výsledkov riadku SELECT, tak aj vo výraze na spracovanie vygenerovaných skupín HAVING. V tomto prípade sa každá agregovaná funkcia vypočíta pre každú vybranú skupinu. Hodnoty získané pri výpočte agregačných funkcií možno použiť na zobrazenie zodpovedajúcich výsledkov alebo na podmienku výberu skupín.

Zostavme si dotaz, ktorý zobrazí skupiny, v ktorých bolo získaných viac ako jedna dvojka v jednej disciplíne na skúškach:

1; Výsledok: "width =" 640 "

VYBERTE R2. Skupina

OD R1, R2

KDE R1. Celé meno = R2. Názov A

Skóre R1 = 2

GROUP BY R2. Group, R1. Disciplína

HAVING count (*) 1;

výsledok:

Máme DB "Banka", pozostávajúcu z jednej tabuľky F, v ktorej je uložený vzťah F, obsahujúci informácie o účtoch v pobočkách určitej banky:

Zistite celkový zostatok na účte v pobočkách. Pre každú z nich môžete vytvoriť samostatný dotaz výberom položky SUM z tabuľky pre každú vetvu, ale operácia GROUP BY ich všetky umiestni do jedného príkazu:

VYBRAŤ Pobočka , SUM ( Zvyšok )

SKUPINA PODĽA pobočky;

GROUP BY aplikuje agregované funkcie nezávisle pre každú skupinu identifikovanú hodnotou poľa Pobočka. Skupina pozostáva z riadkov s rovnakou hodnotou poľa Vetva a funkcie SUM platí pre každú takúto skupinu samostatne, t. j. celkový zostatok na účte sa počíta samostatne pre každú pobočku. Hodnota poľa, na ktoré sa vzťahuje GROUP BY, má podľa definície iba jednu hodnotu na výstupnú skupinu, rovnako ako výsledok agregovanej funkcie.

5 000; Argumenty v klauzule HAVING sa riadia rovnakými pravidlami ako v klauzule SELECT, ktorá používa GROUP BY. Musia mať jednu hodnotu na výstupnú skupinu. "width =" 640 "

Predpokladajme, že vyberiete iba tie pobočky, ktorých celkové hodnoty zostatkov na účtoch presahujú 5 000 USD, ako aj celkové zostatky za vybrané pobočky. Ak chcete zobraziť pobočky s celkovými zostatkami nad 5 000 USD, musíte použiť klauzulu HAVING. Klauzula HAVING definuje kritériá použité na odstránenie konkrétnych skupín z výstupu, rovnako ako klauzula WHERE pre jednotlivé riadky.

Správny príkaz by bol nasledujúci:

SELECT vetva, SUM (zostatok)

GROUP BY Pobočka

MAJÚCI SÚČET ( Zvyšok ) 5 000;

Argumenty vo vete MAJÚCI dodržiavať rovnaké pravidlá ako vo vete VYBRAŤ kde sa používa GROUP BY... Musia mať jednu hodnotu na výstupnú skupinu.

Nasledujúci príkaz bude zakázaný:

SELECT vetva, SUM (zostatok)

SKUPINA PODĽA pobočky

Dátum otvorenia = 27.12.2004 ;

Lúka Dátum otvorenia nemožno použiť vo vete MAJÚCI pretože môže mať viac ako jednu hodnotu na výstupnú skupinu. Aby sa predišlo takejto situácii, návrh MAJÚCI by sa mali vzťahovať len na vybrané agregáty a polia GROUP BY... Existuje správny spôsob, ako urobiť vyššie uvedený dotaz:

SELECT vetva, SUM (zostatok)

WHERE Dátum otvorenia = '27/12/2004'

SKUPINA PODĽA pobočky;

Význam tohto dotazu je nasledovný: nájdite výšku zostatkov pre každú pobočku účtov otvorených 27. decembra 2004.

Ako už bolo uvedené, HAVING môže použiť iba argumenty, ktoré majú rovnakú hodnotu na výstupnú skupinu. V praxi sú odkazy na agregačné funkcie najvšeobecnejšie, ale platné sú aj polia vybrané pomocou GROUP BY. Chceme napríklad vidieť celkové zostatky účtov pobočiek v Petrohrade, Pskove a Uryupinsku:

SELECT vetva, SUM (zostatok)

OD F, Q

KDE F. Pobočka = Q. Pobočka

SKUPINA PODĽA pobočky

MAJÚCI pobočku (‚Petrohrad‘, ‚Pskov‘, ‚Uryupinsk‘);

100 000; Ak je celkový zostatok viac ako 100 000 $, potom to uvidíme vo výslednom pomere, inak dostaneme prázdny pomer. "width =" 640 "

Preto v aritmetických výrazoch predikátov zahrnutých v podmienke výberu klauzuly HAVING môžete priamo použiť iba špecifikácie stĺpcov špecifikovaných ako stĺpce zoskupenia v klauzule GROUP BY. Zvyšné stĺpce je možné zadať iba v rámci špecifikácií agregačných funkcií COUNT, SUM, AVG, MIN a MAX, ktoré v tomto prípade vypočítajú určitú súhrnnú hodnotu pre celú skupinu riadkov. Výsledkom vykonania klauzuly HAVING je zoskupená tabuľka obsahujúca len tie skupiny riadkov, pre ktoré je výsledok vyhodnotenia podmienky výberu v časti HAVING TRUE. Najmä, ak je klauzula HAVING prítomná v dotaze, ktorý neobsahuje GROUP BY, potom výsledkom jeho vykonania bude buď prázdna tabuľka, alebo výsledok predchádzajúcich sekcií tabuľkového výrazu, považovaný za jednu skupinu bez zoskupenie stĺpcov. Pozrime sa na príklad. Povedzme, že chceme zobraziť celkovú sumu zostatkov pre všetky pobočky, ale iba ak je vyššia ako 100 000 USD. V tomto prípade náš dotaz nebude obsahovať operácie zoskupovania, ale bude obsahovať sekciu HAVING a bude vyzerať takto:

VYBERTE SÚČET ( Zvyšok )

MAJÚCI SÚČET ( Zvyšok ) 100 000;

Ak je celkový zostatok viac ako 100 000 $, potom to uvidíme vo výslednom pomere, inak dostaneme prázdny pomer.

Ako zistím počet modelov PC vyrobených konkrétnym predajcom? Ako určiť priemernú cenu za počítače s rovnakými špecifikáciami? Na tieto a mnohé ďalšie otázky súvisiace s niektorými štatistickými informáciami možno odpovedať pomocou súhrnné (súhrnné) funkcie... Norma poskytuje nasledujúce súhrnné funkcie:

Všetky tieto funkcie vracajú jednu hodnotu. V tomto prípade funkcie COUNT, MIN a MAX sú použiteľné pre akýkoľvek typ údajov, pričom SUM a AVG sa používajú iba pre číselné polia. Rozdiel medzi funkciami COUNT (*) a COUNT (<имя поля>) je, že druhý neberie pri výpočte do úvahy hodnoty NULL.

Príklad. Nájdite minimálnu a maximálnu cenu osobných počítačov:

Príklad. Zistite počet dostupných počítačov od výrobcu A:

Príklad. Ak nás zaujíma počet rôznych modelov vyrobených výrobcom A, potom je možné dotaz formulovať nasledovne (s využitím skutočnosti, že v tabuľke Produkt je každý model zaznamenaný raz):

Príklad. Zistite počet rôznych modelov dostupných od výrobcu A. Dotaz je podobný predchádzajúcemu, v ktorom bolo potrebné určiť celkový počet modelov vyrobených výrobcom A. Tu je tiež potrebné nájsť počet rôznych modelov v PC stolík (tj dostupný na predaj).

Aby sa zabezpečilo, že pri získavaní štatistických ukazovateľov sa použijú iba jedinečné hodnoty argument agregovaných funkcií môže byť použité DISTINCT parameter... Ďalší parameter ALL je predvolená hodnota a predpokladá, že sa počítajú všetky návratové hodnoty v stĺpci. operátor,

Ak potrebujeme získať počet vyrobených modelov PC každý výrobcu, budete musieť použiť klauzula GROUP BY syntakticky nasledujúce klauzuly WHERE.

klauzula GROUP BY

klauzula GROUP BY používa sa na definovanie skupín výstupných riadkov, na ktoré možno použiť súhrnné funkcie (COUNT, MIN, MAX, AVG a SUM)... Ak táto klauzula chýba a sú použité agregačné funkcie, potom všetky stĺpce s názvami uvedenými v VYBRAŤ by mali byť zahrnuté v agregované funkcie a tieto funkcie sa použijú na celú množinu riadkov, ktoré spĺňajú predikát dotazu. V opačnom prípade všetky stĺpce zoznamu SELECT, nezahŕňa v agregovaných funkciách, musia byť špecifikované v klauzule GROUP BY... Výsledkom je, že všetky výstupné riadky dotazu sú rozdelené do skupín charakterizovaných rovnakými kombináciami hodnôt v týchto stĺpcoch. Potom budú agregované funkcie aplikované na každú skupinu. Všimnite si, že pre GROUP BY sa všetky hodnoty NULL považujú za rovnaké, t.j. pri zoskupení podľa poľa obsahujúceho hodnoty NULL budú všetky takéto riadky spadať do jednej skupiny.
Ak ak existuje klauzula GROUP BY, v klauzule SELECT žiadne agregované funkcie, potom dotaz jednoducho vráti jeden riadok z každej skupiny. Túto funkciu spolu s kľúčovým slovom DISTINCT možno použiť na odstránenie duplicitných riadkov v sade výsledkov.
Pozrime sa na jednoduchý príklad:

SELECT model, COUNT (model) AS Množstvo_model, AVG (cena) AS Priemerná_cena Z PC GROUP BY model;

V tejto žiadosti je pre každý model PC určený ich počet a priemerná cena. Všetky riadky s rovnakými hodnotami modelu tvoria skupinu a výstup SELECT vypočíta počet hodnôt a priemerné hodnoty cien pre každú skupinu. Výsledkom dopytu bude nasledujúca tabuľka:

Model	Model_množstva	Priemerná_cena
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Ak by SELECT mal stĺpec s dátumom, potom by bolo možné vypočítať tieto ukazovatele pre každý konkrétny dátum. Ak to chcete urobiť, musíte pridať dátum ako stĺpec zoskupenia a potom sa pre každú kombináciu hodnôt (model-dátum) vypočítajú agregované funkcie.

Špecifických je viacero pravidlá pre vykonávanie agregovaných funkcií:

• Ak je výsledkom dotazu neboli prijaté žiadne riadky(alebo viac ako jeden riadok pre danú skupinu), potom chýbajú počiatočné údaje na výpočet ktorejkoľvek z agregovaných funkcií. V tomto prípade bude výsledok vykonania funkcií COUNT nula a výsledok všetkých ostatných funkcií bude NULL.
• Argumentovať agregátna funkcia nemôže sám obsahovať agregované funkcie(funkcia z funkcie). Tie. v jednom dotaze nemôžete, povedzme, získať maximum priemerov.
• Výsledkom vykonania funkcie COUNT je celé číslo(INTEGER). Ostatné agregačné funkcie dedia typy údajov spracovávaných hodnôt.
• Ak sa pri vykonávaní funkcie SUM získal výsledok, ktorý prekračuje maximálnu hodnotu použitého typu údajov, chyba.

Ak teda žiadosť neobsahuje GROUP BY doložky, potom agregované funkcie zahrnuté v klauzula SELECT, sa vykonajú na všetkých výsledných riadkoch dotazu. Ak žiadosť obsahuje klauzula GROUP BY, každá sada riadkov, ktorá má rovnaké hodnoty stĺpca alebo skupiny stĺpcov špecifikované v klauzula GROUP BY, tvorí skupinu a agregované funkcie sa vykonávajú pre každú skupinu samostatne.

klauzula HAVING

Ak klauzula WHERE potom definuje predikát na filtrovanie riadkov MÁME ponuku aplikované po zoskupení na definovanie podobných predikátových skupín filtrovania podľa hodnôt agregované funkcie... Táto klauzula je potrebná na testovanie hodnôt, ktoré sa získajú pomocou agregátna funkcia nie zo samostatných riadkov zdroja záznamov definovaných v doložka FROM, a od skupiny takýchto liniek... Preto takáto kontrola nemôže byť obsiahnutá klauzula WHERE.

Dokáže vykonávať zovšeobecnené dávkové spracovanie hodnôt polí. To sa vykonáva pomocou agregačných funkcií. Súhrnné funkcie vytvárajú jednu hodnotu pre celú skupinu tabuľky. SQL poskytuje nasledujúce agregačné funkcie:

• COUNT- spočíta počet riadkov tabuľky s nenulovými hodnotami poľa zadaného ako argument.
• SUM- vypočíta aritmetický súčet všetkých vybraných hodnôt daného poľa.
• AVG- vykoná spriemerovanie všetkých vybraných hodnôt tohto poľa.
• MAX- zobrazí najväčšiu hodnotu zo všetkých vybratých hodnôt pre toto pole.
• MIN- Vypíše najmenšiu hodnotu zo všetkých vybratých hodnôt pre toto pole.

  Použitie agregačných funkcií

  Agregačné funkcie sa používajú podobne ako názvy polí v klauzule SELECT dotazu, s jednou výnimkou: berú názvy polí ako argumenty. Je možné použiť iba číselné polia SUM a AVG... S COUNT, MAX a MIN možno použiť číselné aj znakové polia. Pri použití so znakovými poľami MAX a MIN preloží ich do ASCII ekvivalentu. Znamená to, že MIN vyberie prvý a MAX posledná hodnota v abecednom poradí.

  Aby sme našli celkový predaj v tabuľke predajov, musíme napísať nasledujúci dotaz:

  SELECT SUM (SSum) FROM Predáva

  V dôsledku toho dostaneme:

  Tento dotaz spočítal počet neprázdnych hodnôt v poli SNum tabuľky Sells. Ak žiadosť prepíšeme takto:

  SELECT COUNT (SDate) FROM Predáva

  Potom ako výsledok dostaneme:

  COUNT OF SDdate

  4

  Rôzne výsledky dotazu pri výpočte zdanlivo rovnaké sa získajú, pretože jedna z hodnôt poľa SDate má prázdnu hodnotu ( NULOVÝ). Pri používaní takýchto žiadostí buďte opatrní.