Agregační funkce jazyka SQL. Agregační funkce v jazyce SQL. Agregační funkce v SQL

Norma ISO definuje následujících pět agregační funkce:

POČET– vrátí počet hodnot v zadaný sloupec;

SOUČET– vrátí součet hodnot v zadaném sloupci;

AVG– vrátí průměrnou hodnotu v zadaném sloupci;

MIN– vrátí minimální hodnotu v zadaném sloupci;

MAX– vrací maximální hodnota v uvedeném sloupci.

Všechny tyto funkce pracují s hodnotami v jednom sloupci tabulky a vracejí jedinou hodnotu. Funkce COUNT, MIN a MAX platí pro numerická i nenumerická pole, zatímco funkce SUM a AVG lze použít pouze pro numerická pole. S výjimkou COUNT(*) jsou při vyhodnocování výsledků libovolné funkce nejprve odstraněny všechny hodnoty null a poté je požadovaná operace aplikována pouze na zbývající neprázdné hodnoty ve sloupci. Volba COUNT(*) je speciální použití funkce COUNT – jejím účelem je spočítat všechny řádky v tabulce bez ohledu na to, zda obsahuje hodnoty null, duplikáty nebo jakékoli jiné hodnoty. Pokud chcete eliminovat duplicitní hodnoty před použitím agregační funkce, musíte před název sloupce v definici funkce zadat klíčové slovo DISTINCT. Norma ISO umožňuje použití klíčového slova ALL to explicitní označeníže vyloučení duplicitních hodnot není vyžadováno, ačkoli toto klíčové slovo je implicitně implikováno, pokud nejsou zadány žádné další kvalifikátory. Klíčové slovo DISTINCT nemá žádný význam funkce MIN a MAX. Jeho použití však může ovlivnit výsledky funkcí SUM a AVG, proto byste měli předem zvážit, zda má být v každém konkrétním případě přítomen. Kromě toho lze klíčové slovo DISTINCT zadat v každém požadavku pouze jednou.

Je třeba poznamenat, že agregační funkce lze použít pouze v seznamu SELECT a v klauzuli HAVING (viz oddíl 5.3.4). Ve všech ostatních případech je použití těchto funkcí nepřijatelné. Pokud seznam SELECT obsahuje agregační funkci a tělo dotazu neobsahuje klauzuli GROUP BY umožňující seskupování dat, pak žádný prvek v seznamu SELECT nemůže obsahovat žádné odkazy na sloupce, pokud sloupec není použit jako parametr agregační funkce. Například následující dotaz je nesprávný:

VYBRATzaměstnanciNe,POČET (plat)

ZPersonál;

Chyba je v tom, že v tomto požadavku není žádná konstrukce SKUPINA VYTVOŘENÁ a sloupec staffNo v seznamu SELECT je přístupný bez použití agregační funkce.

Příklad 13: Použití funkce COUNT(*).Určete, kolik nemovitostí k pronájmu má nájem vyšší než 350 GBP měsíčně,

VYBERTE POČET(*) Počet AS

ZPropertyForRent

KDEnájem > 350;

Omezení započítávání pouze těch nemovitostí k pronájmu, jejichž nájemné je vyšší než 350 GBP za měsíc, je implementováno pomocí klauzule WHERE. Celkový počet nemovitostí k pronájmu, které splňují zadanou podmínku, lze určit pomocí agregační funkce POČET. Výsledky dotazu jsou uvedeny v tabulce. 23.

Tabulka 23

počet

Příklad 14. Pomocí funkce COUNT(DISTINCT).Zjistěte, kolik různých nemovitostí k pronájmu si zákazníci prohlíželi v květnu 2001.

VYBRAT POČET(ODLIŠpropertyNo) AS počet

ZProhlížení

Opět platí, že omezení výsledků dotazu pouze na ty nemovitosti k pronájmu, které byly zkontrolovány v květnu 2001, je dosaženo použitím klauzule WHERE. Celkový počet zkoumaných objektů, které splňují zadanou podmínku, lze určit pomocí agregační funkce COUNT. Protože však lze kontrolovat stejný objekt různé klienty několikrát je nutné v definici funkce zadat klíčové slovo DISTINCT - to umožní vyloučit duplicitní hodnoty z výpočtu. Výsledky dotazu jsou uvedeny v tabulce. 24.

Tabulka 24

Příklad 16. Použití funkcí MIN, MAXnAVG.Spočítejte si minimální, maximální a průměrnou mzdu.

ZVOLTE MIN(plat) TAK JAKO min, MAX(plat) TAK JAKO max, AVG(plat) TAK JAKO prům

ZPersonál;

V tomto příkladu potřebujete zpracovat informace o všech zaměstnancích společnosti, takže nemusíte používat klauzuli WHERE. Požadované hodnoty lze vypočítat pomocí funkcí MIN, MAX a AVG aplikovaných na sloupec platů v tabulce Zaměstnanci. Výsledky dotazu jsou uvedeny v tabulce. 26.

Tabulka 26.

Výsledek požadavku

min	max	prům
9000.00	30000.00	17000.00

Výsledky seskupení (konstrukt GROUP BY). Výše uvedené příklady souhrnných údajů jsou podobné řádkům souhrnu, které se obvykle nacházejí na konci sestav. V důsledku toho jsou všechna podrobná data sestavy komprimována do jednoho souhrnného řádku. Velmi často je však nutné v reportech generovat mezisoučty. Pro tento účel lze v příkazu SELECT zadat klauzuli GROUP BY. Je volán dotaz, který obsahuje klauzuli GROUP BY žádost o seskupení, protože seskupuje data získaná jako výsledek operace SELECT, po které je pro každého jednotlivce vytvořena jediná skupina souhrnný řádek. Volají se sloupce uvedené v klauzuli GROUP BY seskupené sloupce. Norma ISO vyžaduje, aby klauzule SELECT a GROUP BY úzce souvisely. Při použití klauzule GROUP BY v příkazu SELECT musí mít každý prvek seznamu v seznamu SELECT jediný význam pro celou skupinu. Navíc klauzule SELECT může obsahovat pouze následující typy Prvky:

Názvy sloupců;

agregační funkce;

Konstanty;

Výrazy, které obsahují kombinace prvků uvedených výše.

Všechny názvy sloupců, které se objevují v seznamu SELECT, se musí také objevit v klauzuli GROUP BY, pokud není název sloupce použit pouze v agregační funkci. Opak není vždy pravdou – klauzule GROUP BY může obsahovat názvy sloupců, které nejsou v seznamu SELECT. Pokud je klauzule WHERE použita ve spojení s klauzulí GROUP BY, je zpracována jako první a seskupeny jsou pouze ty řádky, které splňují podmínku vyhledávání. Norma ISO specifikuje, že při seskupování jsou všechny chybějící hodnoty považovány za stejné. Pokud dva řádky tabulky ve stejném seskupovacím sloupci obsahují hodnoty NULL a identické hodnoty ve všech ostatních nenulových sloupcích seskupení, jsou umístěny do stejné skupiny.

Příklad 17: Použití konstrukce GROUP BY.Určete počet zaměstnanců pracujících v každém z oddělení společnosti a také jejich celkový počet mzdy.

VYBRATčíslo pobočky, POČET(personál č.) TAK JAKO počet, SOUČET(plat) TAK JAKO součet

ZPersonál

SKUPINA VYTVOŘENÁpobočka č

SEŘADIT PODLEčíslo pobočky;

Do seznamu prvků GROUP BY není nutné uvádět názvy sloupců staffNo a plat, protože se objevují pouze v seznamu SELECT s agregačními funkcemi. Zároveň sloupec branchNo v seznamu klauzule SELECT není spojen s žádnou agregační funkcí az tohoto důvodu musí být uveden v klauzuli GROUP BY. Výsledky dotazu jsou uvedeny v tabulce. 27.

Tabulka 27

Výsledek požadavku

pobočka č	Počet	Součet
B003		54000.00
B005		39000.00
B007		9000.00

Koncepčně se při zpracování tohoto požadavku provádějí následující akce:

1. Řádky tabulky Zaměstnanci jsou rozděleny do skupin podle hodnot ve sloupci čísla oddělení společnosti. V každé skupině jsou údaje o všech zaměstnancích jednoho z oddělení společnosti. V našem příkladu budou vytvořeny tři skupiny, jak je znázorněno na Obr. 1.

2. Pro každou skupinu se vypočítá celkový počet řádků, který se rovná počtu zaměstnanců v oddělení, a také součet hodnot ve sloupci plat, což je součet platů všech zaměstnanců v oddělení, které nás zajímá. Pro celou skupinu zdrojových řádků se pak vygeneruje jeden souhrnný řádek.

3. Výsledné řádky výsledné tabulky jsou seřazeny vzestupně podle čísla pobočky uvedeného ve sloupci branchNo.

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

POČET(personál č.)	SUM(plat)
	54000.00
	39000.00
	9000.00

Rýže. 1. Tři skupiny záznamů vytvořené při provádění dotazu

Standard SQL umožňuje umístit vnořené dotazy do seznamu SELECT. Takže výše uvedený dotaz může být také reprezentován takto:

VYBRATčíslo pobočky, (VYBRAT POČET(číslo personálu)TAK JAKO počet

ZZaměstnanci s

KDEs.branchNo = b.branchNo),

(SELECT SUM(plat) JAKO součet

ZZaměstnanci s

KDEs.branchNo = b.branchNo)

ZPobočka b

SEŘADIT PODLEčíslo pobočky;

Tato verze dotazu však vytváří dva výsledky agregační funkce pro každou z poboček společnosti popsaných v tabulce Poboček, takže v některých případech je možné vidět řádky obsahující hodnoty null.

Omezení provádění seskupování (konstrukt HAVING). Klauzule HAVING je určena k použití ve spojení s klauzulí GROUP BY k nastavení omezení specifikovaných za účelem výběru těch skupiny, který bude umístěn ve výsledné tabulce dotazu. Přestože klauzule HAVING a WHERE mají podobnou syntaxi, jejich účel je odlišný. Klauzule WHERE se používá k výběru jednotlivých řádků k naplnění výsledné tabulky dotazů a klauzule HAVING se používá k výběru skupiny, umístěn ve výsledné tabulce dotazů. Norma ISO vyžaduje, aby se názvy sloupců použité v klauzuli HAVING objevily v seznamu prvků GROUP BY nebo byly použity v agregačních funkcích. V praxi vyhledávací termíny v klauzuli HAVING vždy obsahují alespoň jednu agregační funkci; jinak musí být tyto hledané výrazy umístěny do klauzule WHERE a použity k výběru jednotlivých řádků. (Pamatujte, že agregační funkce nelze použít v klauzuli WHERE.) Klauzule HAVING není nezbytnou součástí jazyka SQL – jakýkoli dotaz napsaný pomocí klauzule HAVING lze napsat v jiné formě, aniž byste ji použili.

Příklad 18. Použití konstruktu HAVING.Pro každou pobočku firmy s více zaměstnanci určete počet zaměstnanců a výši jejich mezd.

VYBRATčíslo pobočky, COUN T (č. personálu) TAK JAKO počet, SOUČET(plat) TAK JAKO součet

ZPersonál

SKUPINA VYTVOŘENÁpobočka č

POČÍTAT(číslo personálu) > 1

SEŘADIT PODLEčíslo pobočky;

Tento příklad je podobný předchozímu, ale zde jej použijeme dodatečná omezení, což naznačuje, že nás zajímají pouze informace o těch odděleních společnosti, která zaměstnávají více než jednu osobu. Podobný požadavek platí pro skupiny, takže dotaz by měl používat konstrukci HAVING. Výsledky dotazu jsou uvedeny v tabulce. 28.

Tabulka 28

branchNo count sum

В00З 3 54000,00

B005 2 39000,00

Dílčí dotazy. V této části probereme použití úplných příkazů SELECT vložených do těla jiného příkazu SELECT. Externí(Druhý) příkaz SELECT používá výsledek provedení vnitřní(prvního) operátora k určení obsahu konečného výsledku celé operace. Vnitřní dotazy lze nalézt v klauzulích WHERE a HAVING vnějšího příkazu SELECT – v tomto případě se nazývají dílčí dotazy, nebo vnořené dotazy. Vnitřní příkazy SELECT lze navíc použít v příkazech INSERT, UPDATE a DELETE . Existují tři typy poddotazů.

Skalární poddotaz vrátí hodnotu vybranou z průsečíku jednoho sloupce s jedním řádkem, tzn. jediný význam. V zásadě lze skalární poddotaz použít všude tam, kde je vyžadována jediná hodnota. Varianty skalárních dílčích dotazů jsou uvedeny v příkladech 13 a 14.

Řetězcový poddotaz vrátí hodnoty více sloupců tabulky, ale jako jeden řádek. Řetězcový poddotaz lze použít kdekoli, kde se používá konstruktor hodnot řetězce, obvykle predikáty. Varianta poddotazu řetězce je uvedena v příkladu 15.

Poddotaz tabulky vrátí hodnoty jednoho nebo více sloupců tabulky ve více než jednom řádku. Poddotaz tabulky lze použít všude tam, kde lze zadat tabulku, například jako operand k predikátu IN.

Příklad 19: Použití poddotazu s testem rovnosti. Komponovat seznam zaměstnanců pracujících v pobočce společnosti na adrese 463 Main St1.

VYBRAT

ZPersonál

KDEčíslo větve = (VYBRAT číslo větve

ZVětev

KDEulice = "163 Main St");

Interní příkaz SELECT (SELECT branchNo FROM Branch ...) je určen k určení čísla firemní pobočky umístěné na adrese "163 Main St". (Existuje pouze jedna taková pobočka společnosti, takže tento příklad je příkladem skalárního dílčího dotazu.) Po získání požadovaného čísla pobočky se provede vnější dílčí dotaz pro výběr detailní informace o zaměstnancích tohoto oddělení. Jinými slovy, vnitřní příkaz SELECT vrací tabulku skládající se z jediné hodnoty „BOOV“, která představuje číslo pobočky společnosti umístěné na adrese „163 Main St1. Výsledkem je, že vnější příkaz SELECT má následující podobu:

VYBRATstaffNo, fName, Iname, position

ZPersonál

KDEčíslo větve = "B0031;

Výsledky tohoto dotazu jsou uvedeny v tabulce. 29.

Tabulka 29

Výsledek požadavku

personál č	fName	Iname	pozice
SG37	Ann	Buk	Asistent
SG14	Davide	Brod	Dozorce
SG5	Susan	Značka	Manažer

Poddotaz je nástroj pro vytvoření dočasné tabulky, jejíž obsah načte a zpracuje externí operátor. Poddotaz lze zadat přímo za operátory porovnání (tj. operátory =,<, >, <=, >=, <>) v klauzuli WHERE nebo HAVING. Text dílčího dotazu musí být uzavřen v závorkách.

Příklad 20. Použití poddotazů s agregačními funkcemi. Udělejte si seznam všech zaměstnanců, kteří mají nadprůměrný plat, a uveďte, o kolik přesahuje jejich plat průměrná mzda podle podniku.

VYBRATstaffNo, fName, INname, pozice, plat - ( VYBERTE AVG(plat) Z Personál) TAK JAKO salDiff

ZPersonál

KDEplat > ( VYBERTE AVG(plat) Z Personál) ;

Je třeba poznamenat, že nemůžete přímo zahrnout do dotazovací výraz"WHERE plat > AVG (plat)", protože používat agregaci funkce v klauzuli WHERE jsou zakázány. Chcete-li dosáhnout požadovaného výsledku, měli byste vytvořit poddotaz, který vypočítá průměrnou roční mzdu, a poté jej použít ve vnějším příkazu SELECT, který získá informace o těch zaměstnancích ve společnosti, jejichž mzda tento průměr přesahuje. Jinými slovy, dílčí dotaz vrátí průměrnou mzdu společnosti za rok, která se rovná 17 000 GBP.

Výsledek tohoto skalárního poddotazu slouží ve vnějším příkazu SELECT jak k výpočtu odchylky mezd od průměrné úrovně, tak k výběru informací o zaměstnancích. Vnější příkaz SELECT má tedy následující podobu:

VYBRATstaffNo, fName, INname, pozice, plat - 17000 Tak jako salDiff

ZPersonál

KDEmzda > 17000;

Výsledky dotazu jsou uvedeny v tabulce. třicet.

Tabulka 30.

Výsledek požadavku

personál č	fName	Iname	pozice	salDiff
SL21	John	Bílý	Manažer	13000.00
SG14	Davide	Brod	Dozorce	1000.00
SG5	Susan	Značka	Manažer	7000.00

Platí pro poddotazy dodržování pravidel a omezení.

1. Poddotazy by neměly používat klauzuli ORDER BY, i když může být přítomna ve vnějším příkazu SELECT.

2. Seznam SELECT poddotazu se musí skládat z názvů jednotlivých sloupců nebo výrazů z nich složených, kromě případů, kdy je v poddotazu použito klíčové slovo EXISTS.

3. Názvy sloupců v poddotazu standardně odkazují na tabulku, jejíž název je uveden v klauzuli FROM poddotazu. Je však také možné odkazovat na sloupce tabulky uvedené v klauzuli FROM externí požadavek, který používá kvalifikované názvy sloupců (jak je popsáno níže).

4. Pokud je poddotaz jedním ze dvou operandů zahrnutých do operace porovnání, pak musí být poddotaz specifikován na pravé straně této operace. Například následující zápis dotazu z předchozího příkladu je nesprávný, protože poddotaz je umístěn na levé straně operace porovnání proti hodnotě sloupce platu.

VYBRAT

ZPersonál

KDE(VYBRAT AVG (plat) ZE zaměstnanců)< salary;

Příklad 21. Vnořené poddotazy a použití predikátu IN. Udělejte si seznam pronájmů nemovitostí, za které zodpovídají zaměstnanci pobočky společnosti na adrese 163 Main st1.

VYBRATpropertyNo, ulice, město, PSČ, typ, pokoje, nájem

ZPropertyForRent

Kapitola 5. Jazyk SQL: manipulace s daty 189

KDEstaffNo IN (SELECT staffNo

ZPersonál

KDEbrancliNo = (SELECT branchNo

ZVětev

KDEulice = "163 Main S t "));

První, nejvnitřnější, dotaz je určen k určení čísla pobočky společnosti na adrese 463 Main St. Druhý, mezilehlý, dotaz získá informace o zaměstnancích pracujících v této pobočce vybráno, a proto v externím dotazu nemůžete použít operátor porovnání =. Místo toho musíte použít klíčové slovo IN. Externí dotaz získá informace o pronajatých objektech, za které jsou odpovědní tito zaměstnanci společnosti, o kterých byla získána data. o provedení přechodného dotazu Výsledky dotazu jsou uvedeny v tabulce 31.

Tabulka 31

Výsledek požadavku

č. nemovitosti	ulice	město	PSČ	typ	pokoje	pronajmout si
PG16	5 Novar Dr	Glasgow	G129AX	Byt
PG36	2 Manor Road	Glasgow	G324QX	Byt
PG21	18 Dale Rd	Glasgow	G12	Dům

Klíčová slova ANY a ALL. Klíčová slova ANY a ALL lze použít s poddotazy, které vracejí jeden sloupec čísel. Pokud poddotazu předchází klíčové slovo ALL, je podmínka porovnání splněna pouze v případě, že platí pro všechny hodnoty ve sloupci výsledků poddotazu. Pokud textu poddotazu předchází klíčové slovo ANY, bude podmínka porovnání považována za splněnou, pokud bude splněna alespoň pro některé (jednu nebo více) hodnot ve výsledném sloupci poddotazu. Pokud je výsledek provedení dílčího dotazu: prázdná hodnota, pak pro klíčové slovo ALL bude podmínka porovnání považována za splněnou a pro klíčové slovo ANY bude považována za nesplněnou. Podle normy ISO můžete navíc použít klíčové slovo SOME, které je synonymem pro klíčové slovo ANY.

Příklad 22. Pomocí klíčových slov ANY a SOME. Najděte všechny pracovníky, jejichž plat alespoň převyšuje plat jeden pracovník pobočky firmy pod číslem „booz“.

VYBRATstaffNo, fName, INname, pozice, plat

ZPersonál

KDEplat > NĚJAKÉ(VYBRAT plat

ZPersonál

KDEčíslo větve = "B003");

Ačkoli by tento dotaz mohl být zapsán pomocí poddotazu, který specifikuje minimální mzdu pro zaměstnance oddělení číslo "BOHO", po kterém by vnější poddotaz mohl vybrat informace o všech zaměstnancích společnosti, jejichž mzda tuto hodnotu přesahuje (viz příklad 20), je možný jiný přístup, která spočívá v použití klíčových slov SOME/ANY. V tomto případě vnitřní poddotaz vytvoří sadu hodnot (12000, 18000, 24000) a vnější dotaz vybere informace o těch zaměstnancích, jejichž plat je větší než kterákoli z hodnot v tomto

set (ve skutečnosti více než minimální hodnota - 12000). Jako alternativní metoda lze považovat za přirozenější než definování minimální mzdy v dílčím dotazu. Ale v obou případech se vytvoří stejné výsledky dotazu, které jsou uvedeny v tabulce. 32 .

Tabulka 32

Výsledek požadavku

personál č	fName	Iname	pozice	plat
SL21	John	Bílý	Manažer	30000.00
SG14	Davide	Brod	Dozorce	18000.00
SG5	Susan	Značka	Manažer	24000.00

Příklad 23. Pomocí klíčového slova ALL. Najděte všechny zaměstnance, jejichž mzda je vyšší než mzda kteréhokoli zaměstnance v pobočkovém čísle společnosti "chlast".

VYBRATstaffNo, fName, INarae, pozice, plat

ZPersonál

KDEplat > VŠECHNO(VYBRAT plat

ZPersonál

KDEčíslo větve = "BOG3");

Obecně je tento požadavek podobný předchozímu. A v tomto případě by bylo možné použít poddotaz, který určí maximální hodnotu mzdy pracovníků oddělení pod číslem „BOZ“, a následně pomocí externího dotazu vybrat informace o všech zaměstnancích společnosti, jejichž mzda převyšuje tuto hodnotu. Nicméně, v v tomto příkladu Zvoleným přístupem je použití klíčového slova ALL. Výsledky dotazu jsou uvedeny v tabulce. 33 .

Tabulka 33

Výsledek požadavku

personál č	Iname	fName	pozice	plat
SL21	Bílý	John	Manažer	30000,00

Vícetabulkové dotazy. Všechny výše uvedené příklady mají stejnou věc důležité omezení: Sloupce umístěné ve výsledné tabulce jsou vždy vybírány z jedné tabulky. To však v mnoha případech nestačí. Chcete-li ve výsledné tabulce zkombinovat sloupce z několika zdrojových tabulek, musíte operaci provést spojení. V jazyk SQL Operace spojení se používá ke spojení informací ze dvou tabulek vytvořením dvojic související řetězce, vybrané z každé tabulky. Páry řádků umístěné v kombinované tabulce jsou sestaveny na základě rovnosti hodnot zadaných sloupců, které jsou v nich obsaženy.

Pokud potřebujete získat informace z několika tabulek, můžete buď použít poddotaz, nebo tabulky spojit. Pokud výsledná tabulka dotazu musí obsahovat sloupce z různých zdrojových tabulek, pak je vhodné použít mechanismus spojení tabulek. Chcete-li provést spojení, stačí zadat názvy dvou nebo více tabulek v klauzuli FROM, oddělit je čárkami, a poté zahrnout do dotazu klauzuli WHERE definující sloupce použité ke spojení zadaných tabulek. Kromě toho můžete místo názvů tabulek použít pseudonymy, jim přiřazena v klauzuli FROM. V tomto případě musí být názvy tabulek a k nim přiřazené aliasy odděleny mezerami. Aliasy lze použít k objasnění názvů sloupců, kdykoli mohou existovat nejasnosti ohledně toho, do které tabulky sloupec patří. Kromě toho lze aliasy použít ke zkrácení názvů tabulek. Pokud je pro tabulku definován alias, lze jej použít kdekoli, kde je třeba zadat název této tabulky.

Příklad 24. Jednoduché připojení. Udělejte si jmenný seznam všech klientů, kteří si již alespoň jednu pronajímanou nemovitost prohlédli a vyjádřili se k věci.

VYBRATc.clientNo, fName, Iname, propertyNo, comment

ZKlient c, Prohlížení v

KDEc.clientNo = v.clientNo;

Tato sestava vyžaduje informace z tabulky Klient i prohlížecí tabulky, takže k vytvoření dotazu použijeme mechanismus spojení tabulek. Klauzule SELECT uvádí seznam všech sloupců, které by měly být umístěny ve výsledné tabulce dotazu. Všimněte si, že sloupec clientNo vyžaduje kvalifikaci, protože sloupec může být také přítomen v jiné tabulce účastnící se spojení. Proto je nutné výslovně uvést, o které tabulkové hodnoty máme zájem. (V tomto příkladu byste mohli stejně snadno vybrat hodnoty sloupce clientNo z tabulky zobrazení.) Název je určen uvedením názvu odpovídající tabulky (nebo jejího aliasu) jako předpony před názvem sloupce. Náš příklad používá hodnotu "c" zadanou jako alias pro tabulku Klient. Ke generování výsledných řádků se použijí ty řádky zdrojových tabulek, které mají identickou hodnotu ve sloupci clientNo. Tato podmínka je určena zadáním vyhledávací podmínky with.clientNo=v.clientNo. Jsou volány podobné sloupce zdrojových tabulek odpovídající sloupce. Popsaná operace je ekvivalentní operaci spojení podle rovnosti relační algebra. Výsledky dotazu jsou uvedeny v tabulce. 34.

Tabulka 34

Výsledek požadavku

klient č	fName	Iname	č. nemovitosti	komentář
CR56	Aline	Stewarte	PG36
CR56	Aline	Stewarte	PA14	příliš malá
CR56	Aline	Stewarte	PG4
CR62	Marie	Tregear	PA14	žádná jídelna
CR76	John	Kay	PG4	příliš vzdálené

Dotazy na více tabulek se nejčastěji provádějí na dvou tabulkách spojených vztahem jedna ku mnoha (1:*) nebo rodič-dítě. Ve výše uvedeném příkladu, který zahrnuje přístup k tabulkám Klient a Prohlížení, jsou tyto tabulky spojeny právě takovým vztahem. Každý řádek prohlížené (podřízené) tabulky je přidružen pouze k jednomu řádku klientské (nadřazené) tabulky, zatímco stejný řádek klientské (nadřazené) tabulky může být přidružen.

s mnoha řádky tabulky Zobrazení (podřízené). Páry řádků, které se generují při provádění dotazu, jsou výsledkem všech platných kombinací řádků v podřízených a nadřazených tabulkách. Část 3.2.5 podrobně popisuje, jak v relační databázi primární a cizí klíče tabulek vytvářejí vztah rodič-potomek. Tabulka obsahující cizí klíč je obvykle potomek, zatímco tabulka obsahující primární klíč, bude vždy rodič. Chcete-li použít vztah rodič-potomek v dotazu SQL, musíte zadat podmínku hledání, která porovná cizí klíč a primární klíč. Příklad 24 porovnává primární klíč tabulky Klient (v. clientNo) s cizím klíčem tabulky Viewing (v. clientNo).

Standard SQL navíc poskytuje následující metody definice tohoto spojení:

ZKlient s PŘIPOJIT Prohlížení v NA c.clientNo = v.clientNo

ZKlient J OIN Prohlížení POUŽITÍM klient č

ZKlient PŘIROZENÉ SPOJENÍ Prohlížení

V každém případě klauzule FROM nahrazuje původní klauzule FROM a WHERE. První možnost však vytvoří tabulku se dvěma stejnými sloupci clientNo, zatímco v dalších dvou případech bude výsledná tabulka obsahovat pouze jeden sloupec clientNo.

Příklad 25.Řazení výsledků spojování tabulek. U každé pobočky společnosti uveďte osobní čísla a jména zaměstnanců odpovědných za případné pronajímání nemovitostí a uveďte také zařízení, pro která

na které odpovídají.

VYBRATs.branchNo, s.staffNo, fName, Iname, propertyNo

ZStaff s, PropertyForRent p

KDEs.staffNo = p.staffNo

SEŘADIT PODLEs.branchNo, s.personalNo, propertyNo;

Pro snazší čtení výsledků je výsledný výstup setříděn pomocí čísla oddělení jako hlavního klíče řazení a čísla personálu a čísla majetku jako vedlejších klíčů. Výsledky dotazu jsou uvedeny v tabulce. 35.

Tabulka 35

Výsledek požadavku

pobočka č	StaffNo	fName	Iname	č. nemovitosti
SZO	SG14	Davide	Brod	PG16
SZO	SG37	Ann	Buk	PG21
SZO	SG37	Ann	Buk	PG36
BOO5	SL41	Marie	Závětří	PL94
SBI7	SA9	Julie	Howe	PA14

Příklad 26. Spojení tří stolů. U každé pobočky společnosti uveďte osobní čísla a jména zaměstnanců odpovědných za případné pronajímání nemovitostí s uvedením města, ve kterém se pobočka společnosti nachází, a čísla zařízení, za která každý zaměstnanec odpovídá.

VYBRAT b.branchNo, b.city, s.staffNo, fName, INname, propertyNo

Z Pobočka b, Staff s, PropertyForRent p

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

SEŘADIT PODLE b.pobočka, s.staffNo, propertyNo;

Výsledná tabulka musí obsahovat sloupce ze tří zdrojových tabulek – Branch, Staff a PropertyForRent – ​​takže dotaz musí tyto tabulky spojit. Tabulky Pobočka a Zaměstnanci lze spojit pomocí podmínky b.pobočka=*s .Číslo pobočky, čímž dojde k přidružení poboček společnosti k pracovníkům, kteří v nich pracují. Tabulky Staff a PropertyForRent lze spojit pomocí podmínky s.staffNo=p.staffNo. V důsledku toho bude každý zaměstnanec spojen s pronájmem nemovitostí, za které odpovídá. Výsledky dotazu jsou uvedeny v tabulce. 36.

Tabulka 36

Výsledky dotazu

pobočka č	město	zaměstnanciMo	fName	Iname	č. nemovitosti
B003	Glasgow	SG14	Davide	Brod	PG16
B003	Glasgow	SG37	Ann	Buk	PG21
B003	Glasgow	SG37	Ann	Buk	PG36
B005	Londýn	SL41	Julie	Závětří	PL94
B007	Aberdeen	SA9	Marie	Howe	PA14

Všimněte si, že standard SQL vám umožňuje používat Alternativní možnost formulace konstruktů FROM a WHERE:

Z(Pobočka b PŘIPOJTE SE k zaměstnancům POUŽÍVAJÍCÍ pobočka č.) TAK JAKO bs

PŘIPOJITPropertyForRent str POUŽITÍM personál č

Příklad 27. Seskupení podle více sloupců. Určete počet pronajímaných nemovitostí, za které je odpovědný každý ze zaměstnanců společnosti.

VYBRATs.branchNo, S.personalNo, POČET(*) TAK JAKO počet

FROM Staff s, PropertyForRent p

KDE S.staffNo = p.staffNo

SKUPINA VYTVOŘENÁs.branchNo, s.personalNo

SEŘADIT PODLEs.branchNo, s.personalNo;

Pro vypracování požadované zprávy je třeba nejprve zjistit, který ze zaměstnanců společnosti je zodpovědný za pronájem nemovitostí. Tento problém lze vyřešit spojením tabulek Staff a PropertyForRent pomocí sloupce staffNo v klauzuli FROM/WHERE. Poté musíte vytvořit skupiny složené z čísla oddělení a personálních čísel jeho zaměstnanců, pro které použijte konstrukci GROUP BY. Nakonec je třeba výslednou tabulku seřadit pomocí klauzule ORDER BY. Výsledky dotazu jsou uvedeny v tabulce. 37.

Tabulka 37

Výsledek požadavku

pobočka č	personál č	počet
В00 З	SG14
В00 З	SG37
B005	SL41
B007	SA9

Navazování spojení. Spojení je podmnožinou obecnější kombinace dat ze dvou tzv. tabulek karteziánský. Kartézský součin dvou tabulek je další tabulka skládající se ze všech možných dvojic řádků, které jsou součástí obou tabulek. Množinou sloupců ve výsledné tabulce jsou všechny sloupce první tabulky následované všemi sloupci druhé tabulky. Pokud zadáte dotaz na dvě tabulky bez určení klauzule WHERE, výsledek dotazu bude v SQL prostředí bude kartézský součin těchto tabulek. Kromě toho poskytuje norma ISO speciální formát Příkaz SELECT, který umožňuje vypočítat kartézský součin dvou tabulek:

SELECT(*j seznam sloupců]

FROM tableNamel CROSS JOINCaYeUlte2

Podívejme se znovu na příklad, ve kterém se spojení klienta a tabulky prohlížení provádí pomocí společného sloupce clientNo při práci s tabulkami, jejichž obsah je uveden v tabulce. 3.6 a 3.8, kartézský součin těchto tabulek bude obsahovat 20 řádků (4 řádky klientské tabulky x 5 řádků zobrazovací tabulky = 20 řádků). To je ekvivalentní zadání dotazu použitého v příkladu 5.24, ale bez použití klauzule WHERE. Postup pro generování tabulky obsahující výsledky spojení dvou tabulek pomocí příkazu SELECT je následující.

1. Vytvoří se kartézský součin tabulek specifikovaných v konstrukci FROM.

2. Pokud dotaz obsahuje klauzuli WHERE, aplikujte podmínky vyhledávání na každý řádek kartézské tabulky součinů a do tabulky uložte pouze ty řádky, které splňují zadané podmínky. Z hlediska relační algebry se tato operace nazývá omezení Kartézský součin.

3. Pro každý zbývající řádek je určena hodnota každého prvku zadaného v seznamu SELECT, což vede k vytvoření samostatná linka výsledná tabulka.

4. Pokud původní dotaz obsahuje konstrukci SELECT DISTINCT, budou z výsledné tabulky odstraněny všechny duplicitní řádky.

5. Pokud dotaz, který provádíte, obsahuje klauzuli ORDER BY,

©2015-2019 web
Všechna práva náleží jejich autorům. Tato stránka si nečiní nárok na autorství, ale poskytuje bezplatné použití.
Datum vytvoření stránky: 2016-08-07

Může provádět zobecněné skupinové zpracování hodnot polí. To se provádí pomocí agregačních funkcí. Agregační funkce vytvářejí jednu hodnotu pro celou skupinu tabulek. SQL poskytuje následující agregační funkce:

• POČET– počítá počet řádků tabulky s nenulovými hodnotami pole zadaného jako argument.
• SOUČET– vypočítá aritmetický součet všech vybraných hodnot pro dané pole.
• AVG– zprůměruje všechny vybrané hodnoty tohoto pole.
• MAX– zobrazí nejvyšší hodnotu ze všech vybraných hodnot tohoto pole.
• MIN– zobrazí nejmenší hodnotu ze všech vybraných hodnot tohoto pole.

  Použití agregačních funkcí

  Agregační funkce se používají podobně jako názvy polí v klauzuli SELECT dotazu, s jednou výjimkou: berou názvy polí jako argument. S lze použít pouze číselná pole SOUČET A AVG. S POČET, MAX, A MIN Lze použít jak číselná, tak znaková pole. Při použití s ​​poli znaků MAX A MIN převede je na ekvivalent ASCII. Znamená to, že MIN vybere první a MAX poslední hodnota PROTI abecední pořadí.

  Abychom zjistili celkovou částku prodeje v tabulce prodejů, musíme napsat následující dotaz:

  SELECT SUM(SSum) FROM Prodává

  V důsledku toho dostaneme:

  Tento dotaz spočítal počet nenulových hodnot v poli SNum tabulky Sells. Pokud dotaz přepíšeme takto:

  SELECT COUNT(SDate) FROM Prodává

  V důsledku toho dostaneme:

  COUNT OF SDdate

  4

  Při výpočtu toho, co se zdá být stejné, jsou získány různé výsledky dotazu, protože jedna z hodnot pole SDate je prázdná ( NULA). Při používání takových dotazů buďte opatrní.

Naučme se shrnout. Ne, to ještě nejsou výsledky učení SQL a součty hodnot sloupců databázových tabulek. Agregát SQL funkce působit na hodnoty sloupce a vytvořit jedinou výslednou hodnotu. Nejčastěji používané agregační funkce SQL jsou SUM, MIN, MAX, AVG a COUNT. Je nutné rozlišovat dva případy použití agregačních funkcí. Nejprve se agregační funkce používají samy o sobě a vracejí jedinou výslednou hodnotu. Za druhé, agregační funkce se používají s klauzulí SQL GROUP BY, to znamená seskupování podle polí (sloupců) k získání výsledných hodnot v každé skupině. Podívejme se nejprve na případy použití agregačních funkcí bez seskupování.

Funkce SQL SUM

Funkce SQL SUM vrací součet hodnot ve sloupci databázové tabulky. Lze jej použít pouze na sloupce, jejichž hodnoty jsou čísla. Dotazy SQL pro získání výsledného součtu začínají takto:

VYBRAT SOUČET (NÁZEV COLUMN_NAME)...

Za tímto výrazem následuje FROM (TABLE_NAME) a ​​poté lze zadat podmínku pomocí klauzule WHERE. Kromě toho může před názvem sloupce předcházet DISTINCT, což znamená, že se budou počítat pouze jedinečné hodnoty. Ve výchozím nastavení se berou v úvahu všechny hodnoty (k tomu můžete konkrétně zadat ne DISTINCT, ale ALL, ale slovo ALL není povinné).

Příklad 1 Existuje firemní databáze s údaji o jejích divizích a zaměstnancích. V tabulce Zaměstnanci je také sloupec s údaji o platech zaměstnanců. Výběr z tabulky vypadá takto (pro zvětšení obrázku na něj klikněte levým tlačítkem myši):

Chcete-li získat součet všech mezd, použijte následující dotaz:

VYBERTE SOUČTU (Plat) OD zaměstnanců

Tento dotaz vrátí hodnotu 287664,63.

A teď . Ve cvičeních již začínáme úkoly komplikovat a přibližovat je těm, se kterými se setkáváme v praxi.

Funkce SQL MIN

Funkce SQL MIN funguje také na sloupcích, jejichž hodnoty jsou čísla a vrací minimum ze všech hodnot ve sloupci. Tato funkce má podobnou syntaxi jako funkce SUM.

Příklad 3 Databáze a tabulka jsou stejné jako v příkladu 1.

Potřebujeme zjistit minimální mzdu pro zaměstnance oddělení číslo 42. K tomu vypisujeme následující požadavek:

Dotaz vrátí hodnotu 10505,90.

A znovu cvičení pro sebeřešení. V tomto a některých dalších cvičeních budete potřebovat nejen tabulku Zaměstnanci, ale také tabulku Org obsahující údaje o divizích společnosti:

Příklad 4. Do tabulky Zaměstnanci je přidána tabulka Org, která obsahuje údaje o odděleních společnosti. Vytiskněte minimální počet let odpracovaných jedním zaměstnancem v oddělení se sídlem v Bostonu.

Funkce SQL MAX

Funkce SQL MAX funguje podobně a má podobnou syntaxi, která se používá, když potřebujete určit maximální hodnotu mezi všemi hodnotami ve sloupci.

Příklad 5.

Potřebujeme zjistit maximální plat zaměstnanců oddělení číslo 42. K tomu napište následující žádost:

Dotaz vrátí hodnotu 18352,80

Je čas cvičení k samostatnému řešení.

Příklad 6. Opět pracujeme se dvěma tabulkami – Staff a Org. Zobrazte název oddělení a maximální hodnotu provize, kterou obdrží jeden pracovník na oddělení patřící do skupiny oddělení (Divize) Východní. Použití JOIN (připojení ke stolům) .

Funkce SQL AVG

To, co je uvedeno ohledně syntaxe pro předchozí popsané funkce, platí také pro funkci SQL AVG. Tato funkce vrací průměr všech hodnot ve sloupci.

Příklad 7. Databáze a tabulka jsou stejné jako v předchozích příkladech.

Předpokládejme, že chcete zjistit průměrnou délku služby zaměstnanců v oddělení číslo 42. K tomu napište následující dotaz:

Výsledek bude 6,33

Příklad 8. Pracujeme s jedním stolem – Staff. Zobrazte průměrnou mzdu zaměstnanců s praxí 4 až 6 let.

Funkce SQL COUNT

Funkce SQL COUNT vrací počet záznamů v databázové tabulce. Pokud v dotazu zadáte SELECT COUNT(COLUMN_NAME) ..., výsledkem bude počet záznamů bez zohlednění těch záznamů, ve kterých je hodnota sloupce NULL (nedefinováno). Pokud jako argument použijete hvězdičku a spustíte dotaz SELECT COUNT(*) ..., výsledkem bude počet všech záznamů (řádků) tabulky.

Příklad 9. Databáze a tabulka jsou stejné jako v předchozích příkladech.

Chcete znát počet všech zaměstnanců, kteří dostávají provize. Počet zaměstnanců, jejichž hodnoty sloupce Comm nejsou NULL, vrátí následující dotaz:

VYBERTE POČET (Komunikace) OD personálu

Výsledkem bude 11.

Příklad 10. Databáze a tabulka jsou stejné jako v předchozích příkladech.

Pokud chcete zjistit celkový počet záznamů v tabulce, použijte dotaz s hvězdičkou jako argument funkce COUNT:

VYBERTE POČET (*) OD zaměstnanců

Výsledkem bude 17.

V dalším cvičení pro samostatné řešení budete muset použít poddotaz.

Příklad 11. Pracujeme s jedním stolem – Staff. Zobrazení počtu zaměstnanců v oddělení plánování (Plains).

Agregační funkce s SQL GROUP BY

Nyní se podívejme na použití agregačních funkcí spolu s příkazem SQL GROUP BY. Příkaz SQL GROUP BY se používá k seskupení výsledných hodnot podle sloupců v databázové tabulce.

Příklad 12. Existuje databáze inzertního portálu. Obsahuje tabulku reklam obsahující údaje o reklamách odeslaných za daný týden. Sloupec Kategorie obsahuje údaje o velkých kategoriích inzerátů (například Nemovitosti) a Sloupec Díly obsahuje údaje o menších částech zařazených do kategorií (například části Byty a Chaty jsou součástí kategorie Nemovitosti). Sloupec Jednotky obsahuje údaje o počtu podaných inzerátů a sloupec Peníze údaje o výši přijatých peněz za podání inzerátu.

Kategorie	Část	Jednotky	Peníze
Doprava	Auta	110	17600
Nemovitost	Byty	89	18690
Nemovitost	chaty	57	11970
Doprava	Motocykly	131	20960
Konstrukční materiály	Desky	68	7140
Elektrotechnika	televizory	127	8255
Elektrotechnika	Ledničky	137	8905
Konstrukční materiály	Regips	112	11760
Volný čas	knihy	96	6240
Nemovitost	Doma	47	9870
Volný čas	Hudba	117	7605
Volný čas	Hry	41	2665

Použitím SQL příkaz GROUP BY, zjistěte částku peněz přijatou za zveřejňování reklam v každé kategorii. Píšeme následující žádost.

• Agregační funkce se používají podobně jako názvy polí v příkazu SELECT, s jednou výjimkou: berou název pole jako argument. S funkcemi SOUČET A AVG Lze použít pouze číselná pole. S funkcemi COUNT, MAX a MIN Lze použít jak číselná, tak znaková pole. Při použití s ​​poli znaků MAX A MIN přeloží je do ekvivalentu kódu ASCII a zpracuje je v abecedním pořadí. Některé DBMS umožňují použití vnořených agregátů, ale to je odchylka od standardu ANSI se všemi z toho vyplývajícími důsledky.

Můžete například vypočítat počet studentů, kteří složili zkoušky v jednotlivých oborech. Chcete-li to provést, musíte spustit dotaz seskupený podle pole „Disciplína“ a jako výsledek zobrazit název disciplíny a počet řádků ve skupině pro tuto disciplínu. Použití znaku * jako argumentu funkce COUNT znamená sčítání všech řádků ve skupině.

VYBERTE R1. Disciplína, COUNT(*)

GROUP BY R1.Disciplína;

Výsledek:

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

KDE R1. Hodnocení NENÍ NULL

GROUP BY R1.Disciplína;

Výsledek:

nebudou zahrnuty do množiny n-tic před seskupením, takže počet n-tic ve skupině pro disciplínu „Teorie informací“ bude o 1 menší.

Podobný výsledek lze získat, pokud dotaz napíšete následujícím způsobem:

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

GROUP BY R1. Disciplína;

Funkce POČET (NÁZEV ATRIBUTU) počítá počet konkrétních hodnot ve skupině, na rozdíl od funkce POČET(*), který počítá počet řádků ve skupině. Ve skupině s disciplínou „Teorie informací“ budou skutečně 4 řádky, ale pouze 3 určité hodnoty atribut "Hodnocení".

Pravidla pro zpracování hodnot NULL v agregačních funkcích

Pokud jsou některé hodnoty ve sloupci stejné NULA Při výpočtu výsledku funkce jsou vyloučeny.

Pokud jsou všechny hodnoty ve sloupci stejné NULA, Že Max Min. Součet Prům = NULL počet = 0 (nula).

Pokud je stůl prázdný, počet(*) = 0 .

Můžete také použít agregační funkce bez operace předběžného seskupení, v takovém případě je celý vztah považován za jednu skupinu a pro tuto skupinu lze vypočítat jednu hodnotu na skupinu.

Pravidla pro interpretaci agregačních funkcí

Agregační funkce lze zahrnout do seznamu výstupů a poté je aplikovat na celou tabulku.

VYBERTE MAX (Vyhodnocení) z R1 dá maximální skóre na zasedání;

VYBERTE SOUČET (Skóre) z R1 uvede součet všech známek za relaci;

VYBERTE AVG (hodnocení) z R1 poskytne průměrné skóre za celou relaci.

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

Když se znovu vrátíme do databáze „Session“ (tabulka R1), zjistíme počet úspěšně složených zkoušek:

SELECT COUNT(*) As Doručeno _ zkoušky

KDE skóre 2;

Výsledek:

Argumenty k agregačním funkcím mohou být samostatné sloupce tabulky. Chcete-li například vypočítat počet odlišných hodnot určitého sloupce ve skupině, musíte spolu s názvem sloupce použít klíčové slovo DISTINCT. Spočítejme si počet různých známek získaných v každé disciplíně:

VYBERTE R1.Disciplína, POČET (ODLIŠNÁ R1.Hodnocení)

KDE R1. Hodnocení NENÍ NULL

GROUP BY R1.Disciplína;

Výsledek:

Stejného výsledku dosáhneme, pokud vyloučíme explicitní podmínku v části WHERE, v takovém případě bude dotaz vypadat takto:

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

GROUP BY R1. Disciplína;

Funkce POČET (ODLIŠNÉ R1.Hodnocení) počítá jen jisté rozličný významy.

Aby bylo možné v tomto případě získat kýžený výsledek, je nutné provést předběžný převod datového typu sloupce „Hodnocení“ a uvést jej na reálný typ, pak výsledkem výpočtu průměru nebude celé číslo. V tomto případě bude žádost vypadat takto:

2 Seskupit podle R2. Skupina, R1. Disciplína; Zde funkce CAST() převede sloupec Score na platný datový typ. "width="640"

Vyberte R2.Group, R1.Discipline,Count(*) jako Total, AVG(cast(Score as decimal(3,1))) jako Average_score

Od R1,R2

kde R1. Celé jméno = R2. Celé jméno a R1. skóre není nulové

a R1. skóre 2

Seskupit podle R2. Skupina, R1. Disciplína;

Tady je funkce OBSAZENÍ() Převede sloupec Hodnocení na platný datový typ.

V klauzuli WHERE nelze použít agregační funkce, protože podmínky v této části se vyhodnocují z hlediska jednoho řádku a agregační funkce se vyhodnocují z hlediska skupin řádků.

Klauzule GROUP BY vám umožňuje definovat podmnožinu hodnot v konkrétním poli z hlediska jiného pole a aplikovat na podmnožinu agregační funkci. To umožňuje kombinovat pole a agregační funkce do jednoho klauzule SELECT. Agregační funkce lze použít jak ve výrazu pro výstup výsledků řádku SELECT, tak ve výrazu pro podmínku zpracování vytvořených skupin HAVING. V tomto případě se každá agregační funkce vypočítá pro každou vybranou skupinu. Hodnoty získané z výpočtu agregačních funkcí lze použít k zobrazení odpovídajících výsledků nebo k podmínění výběru skupin.

Pojďme sestavit dotaz, který zobrazí skupiny, ve kterých bylo v jedné disciplíně ve zkouškách obdrženo více než jedna špatná známka:

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

VYBERTE R2. Skupina

OD R1,R2

KDE R1. Celé jméno = R2. Celé jméno AND

R1. Skóre = 2

SKUPINA PODLE R2.Skupina, R1.Disciplína

HAVING count(*) 1;

Výsledek:

Máme databázi „Banka“ složenou z jedné tabulky F, která uchovává relaci F obsahující informace o účtech v pobočkách určité banky:

Zjistěte celkový zůstatek na účtech v pobočkách. Pro každou z nich můžete provést samostatný dotaz výběrem SUM (Zbývající) z tabulky pro každou větev, ale operace GROUP BY vám umožní umístit je všechny do jednoho příkazu:

VYBRAT Větev , SUM( Zbytek )

GROUP BY Branch;

SKUPINA VYTVOŘENÁ aplikuje agregační funkce nezávisle na každou skupinu identifikovanou hodnotou pole Pobočka. Skupina se skládá z řádků se stejnou hodnotou pole Branch a funkcí SOUČET se uplatňuje samostatně pro každou takovou skupinu, tj. celkový zůstatek účtu se počítá samostatně pro každou pobočku. Hodnota pole, na které se vztahuje SKUPINA VYTVOŘENÁ, podle definice má pouze jednu hodnotu na výstupní skupinu, stejně jako výsledek agregační funkce.

5 000; Argumenty v klauzuli HAVING se řídí stejnými pravidly jako v klauzuli SELECT, která používá GROUP BY . Musí mít jednu hodnotu na výstupní skupinu. "width="640"

Předpokládejme, že vybereme pouze ty pobočky, jejichž celkové zůstatky na účtech přesahují 5 000 USD, a také celkové zůstatky za vybrané pobočky. Chcete-li zobrazit výsledky pro pobočky s celkovými zůstatky nad 5 000 USD, musíte použít MÁME nabídku. Klauzule HAVING určuje kritéria použitá k odstranění určitých skupin z výstupu, stejně jako klauzule WHERE pro jednotlivé řádky.

Správný příkaz by byl:

SELECT větev, SUM(zbývající)

SKUPINA VYTVOŘENÁ Větev

MÁTE SOUČET ( Zbytek ) 5 000;

Argumenty ve větě MÍT dodržovat stejná pravidla jako ve větě VYBRAT kde se používá SKUPINA VYTVOŘENÁ. Musí mít jednu hodnotu na výstupní skupinu.

Následující příkazy budou zakázány:

SELECT Branch,SUM(Zbývající)

SKUPINA PODLE pobočky

HAVING Datum otevření = 27.12.2004 ;

Pole Datum otevření nelze použít ve větě MÍT, protože může mít více než jednu hodnotu na výstupní skupinu. Aby se předešlo takové situaci, návrh MÍT by měl odkazovat pouze na vybrané agregáty a pole SKUPINA VYTVOŘENÁ. Existuje správný způsob, jak provést výše uvedený dotaz:

SELECT Branch,SUM(Zbývající)

WHEREOpenDate = '27/12/2004'

GROUP BY Branch;

Význam této žádosti dále: najděte součet zůstatků pro každou pobočku účtů otevřených 27. prosince 2004.

Jak bylo uvedeno dříve, HAVING může převzít pouze argumenty, které mají jednu hodnotu na výstupní skupinu. V praxi jsou nejběžnější odkazy na agregační funkce, ale platná jsou i pole vybraná pomocí GROUP BY. Chceme například vidět celkové zůstatky na účtech poboček v Petrohradu, Pskově a Urjupinsku:

SELECT větev, SUM(zbývající)

OD F,Q

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

SKUPINA PODLE pobočky

MAJÍCÍ pobočku (‚Petrohrad‘, ‚Pskov‘, ‚Urjupinsk‘);

100 000; Pokud je celkový zůstatek větší než 100 000 $, pak to uvidíme ve výsledném vztahu, jinak dostaneme prázdný vztah. "width="640"

V predikátových aritmetických výrazech obsažených v klauzuli výběru klauzule HAVING lze tedy přímo použít pouze specifikace sloupců určených jako seskupovací sloupce v klauzuli GROUP BY. Zbývající sloupce lze zadat pouze v rámci specifikací agregačních funkcí COUNT, SUM, AVG, MIN a MAX, které v tomto případě vypočítávají nějakou agregovanou hodnotu pro celou skupinu řádků. Výsledkem provedení klauzule HAVING je seskupená tabulka obsahující pouze ty skupiny řádků, pro které je výsledek výpočtu podmínky výběru v klauzuli HAVING TRUE. Zejména pokud je klauzule HAVING přítomna v dotazu, který neobsahuje GROUP BY, bude výsledkem jejího provedení buď prázdný stůl, nebo výsledek provádění předchozích sekcí tabulkového výrazu, které jsou považovány za jednu skupinu bez seskupování sloupců. Podívejme se na příklad. Řekněme, že chceme zobrazit celkovou částku zůstatků napříč všemi pobočkami, ale pouze pokud je vyšší než 100 000 $ V tomto případě náš dotaz nebude obsahovat seskupovací operace, ale bude obsahovat sekci HAVING a bude vypadat takto:

VYBERTE SOUČET( Zbytek )

MÁTE SOUČET ( Zbytek ) 100 000;

Pokud je celkový zůstatek větší než 100 000 $, pak to uvidíme ve výsledném vztahu, jinak dostaneme prázdný vztah.

Použití agregačních funkcí

SQL definuje mnoho vestavěných funkcí různých kategorií, mezi nimiž zvláštní místo zaujímají agregační funkce, které pracují s hodnotami sloupců mnoha řádků a vracejí jedinou hodnotu. Argumenty agregačních funkcí mohou být jak sloupce tabulky, tak výsledky výrazů nad nimi. Samotné agregované funkce lze zahrnout do jiných aritmetických výrazů. Následující tabulka ukazuje nejčastěji používané standardní unární agregační funkce.

Obecný formát unární agregační funkce je následující:

název_funkce([VŠECHNY | DISTINCT] výraz)

kde DISTINCT označuje, že funkce by měla pouze uvažovat různé významy argument a ALL - všechny hodnoty, včetně duplikátů (tato možnost se používá ve výchozím nastavení). Například, Funkce AVG s klíčovým slovem DISTINCT pro řádky sloupců s hodnotami 1, 1, 1 a 3 vrátí 2 a s klíčovým slovem ALL vrátí 1,5.

Agregační funkce se používají v klauzulích SELECT a HAVING. Zde se podíváme na jejich použití v klauzuli SELECT. V tomto případě se výraz v argumentu funkce vztahuje na všechny řádky ve vstupní tabulce klauzule SELECT. Kromě toho nemůžete v klauzuli SELECT používat agregační funkce i sloupce tabulky (nebo výrazy s nimi), pokud nemáte klauzuli GROUP BY, na kterou se podíváme v další části.

Funkce COUNT má dva formáty. V prvním případě je vrácen počet řádků ve vstupní tabulce, ve druhém případě je vrácen počet hodnot argumentů ve vstupní tabulce:

• POČET(*)
• POČET(výraz)

Nejjednodušším způsobem použití této funkce je spočítat počet řádků v tabulce (všech nebo těch, které splňují zadanou podmínku). K tomu se používá první možnost syntaxe.

Dotaz: Počet typů produktů, pro které jsou dostupné informace v databázi.

SELECT COUNT(*) AS "Počet typů produktů"

OD Produktu

Druhá verze syntaxe funkce COUNT může mít jako argument název jednoho sloupce. V tomto případě se počítá buď všech hodnot v tomto sloupci vstupní tabulky, nebo pouze neopakujících se (pomocí klíčového slova DISTINCT).

Dotaz: Počet odlišných jmen obsažených v tabulce Zákazník.

VYBRAT POČET (DISTINCT FNAME)

OD ZÁKAZNÍKA

Použití dalších unárních agregačních funkcí je podobné jako COUNT, s výjimkou MIN a MAX využití Klíčová slova DISTINCT a ALL jsou bezvýznamná. S funkcemi COUNT, MAX a MIN lze kromě číselných polí použít i pole znaků. Pokud argument agregační funkce neobsahuje žádné hodnoty, Funkce COUNT vrátí 0 a všechny ostatní - Hodnota NULL.

SELECT MAX (OrdDate)

Z

KDE Datum objednávky"1.09.2010"

Zadání pro samostatná práce: Formulujte dotazy v SQL pro získání následujících dat:

• Celkové náklady na všechny objednávky;
• Počet různých měst obsažených v tabulce Zákazník.