Podmienené výrazy CASE. Podmienené výrazy CASE GROUP BY - zoskupenie údajov
- Prvá časť - habrahabr.ru/post/255361
- Druhá časť - habrahabr.ru/post/255523
O čom sa bude diskutovať v tejto časti
V tejto časti sa stretneme:- s výrazom CASE, ktorý vám umožňuje zahrnúť podmienené výrazy do dotazu;
- s agregačnými funkciami, ktoré vám umožňujú získať rôzne druhy súčtov (agregovaných hodnôt) vypočítaných na základe podrobných údajov získaných operátorom „SELECT … WHERE …“;
- s klauzulou GROUP BY, ktorá v kombinácii s agregačnými funkciami umožňuje získať súčty pre podrobné údaje v kontexte skupín;
- s klauzulou HAVING, ktorá umožňuje filtrovať podľa zoskupených údajov.
CASE výraz - podmienený príkaz SQL
Tento operátor vám umožňuje skontrolovať podmienky a vrátiť v závislosti od splnenia konkrétnej podmienky jeden alebo druhý výsledok.Príkaz CASE má 2 formy:
Výrazy tu môžu pôsobiť aj ako hodnoty.
Vezmime si ako príklad prvú formu CASE:
VYBERTE ID,Meno,Plat, PRÍPAD, KEĎ Mzda>=3000 POTOM "SR>= 3000" KEĎ Mzda>=2000 POTOM "2000<= ЗП < 3000"
ELSE "ЗП < 2000"
END SalaryTypeWithELSE,
CASE
WHEN Salary>=3000 POTOM "ZP >= 3000" KEĎ Mzda>=2000 POTOM "2000"<= ЗП < 3000"
END SalaryTypeWithoutELSE
FROM Employees
KEĎ sa podmienky kontrolujú postupne, zhora nadol. Keď sa dosiahne prvá vyhovujúca podmienka, ďalšia kontrola sa preruší a vráti sa hodnota špecifikovaná za slovom THEN pridruženým k tomuto bloku WHEN.
Ak nie je splnená žiadna z podmienok KEDY, potom sa vráti hodnota zadaná za slovom ELSE (čo v tomto prípade znamená "INŠANÁVRAT...").
Ak nie je zadaný blok ELSE a nie je splnená žiadna z podmienok WHEN, vráti sa NULL.
V prvej aj druhej forme prichádza blok ELSE na úplný koniec konštruktu CASE, t.j. po všetkých podmienkach KEDY.
Vezmime si ako príklad druhú formu CASE:
Predpokladajme, že na nový rok sa rozhodli odmeniť všetkých zamestnancov a požiadali o výpočet výšky odmien podľa nasledujúcej schémy:
- Dajte zamestnancom IT oddelenia 15 % mzdy;
- Zamestnanci účtovného oddelenia 10 % zo mzdy;
- Všetci ostatní dostanú 5% z platu.
Na túto úlohu používame dotaz s výrazom CASE:
SELECT ID,Meno,Plat,DepartmentID, -- pre prehľadnosť zobrazte percentá ako reťazec CASE DepartmentID -- hodnota, ktorá sa má skontrolovať, KEĎ 2 POTOM "10 %" -- 10 % z RFP dajte účtovníkom, KEĎ 3 POTOM "15 % " -- 15 % z RFP dajte IT personálu ELSE "5 %" -- všetci ostatní dostanú 5 % KONIEC NewYearBonusPercent, -- vytvorte výraz pomocou CASE, aby ste videli bonusovú čiastku Plat/100* PRÍPAD ID oddelenia KEĎ 2 POTOM 10 -- vydanie 10 % účtovníkov RFP, KEĎ 3, POTOM 15 – 15 % z platu pre IT zamestnancov OSTATNÉ 5 – všetci ostatní 5 % každý KONIEC BonusAmount OD Zamestnancov
Toto je sekvenčná kontrola hodnoty DepartmentID s hodnotami WHEN. Po dosiahnutí prvej rovnosti DepartmentID s hodnotou WHEN sa kontrola preruší a vráti sa hodnota špecifikovaná za slovom THEN pridruženým k tomuto bloku WHEN.
Preto sa hodnota bloku ELSE vráti, ak DepartmentID nezodpovedá žiadnej hodnote WHEN.
Ak neexistuje blok ELSE, vráti sa NULL, ak DepartmentID nezodpovedá žiadnej hodnote WHEN.
Druhá forma CASE sa dá ľahko reprezentovať pomocou prvej formy:
VYBERTE ID, Meno, Plat, ID oddelenia, PRÍPAD, KEĎ DepartmentID=2 POTOM "10 %" -- 10 % RFP na vystavenie účtovníkom, KEĎ DepartmentID=3 POTOM "15 %" -- 15 % RFP na vystavenie IT pracovníkom INAK "5 % " -- všetci ostatní dostanú 5 % KONIEC NewYearBonusPercent, -- vytvorte výraz pomocou CASE, aby ste videli výšku bonusu Plat/100* PRÍPAD, KEĎ DepartmentID=2 POTOM 10 -- dajte 10 % z platu účtovníkom, KEĎ ID oddelenia=3 POTOM 15 -- 15 % z platu dostane ALŠICH 5 IT pracovníkov -- všetkým ostatným 5 % každý KONIEC BonusAmount OD zamestnancov
Takže druhá forma je len zjednodušený zápis, keď potrebujeme urobiť porovnanie rovnosti rovnakej testovacej hodnoty s každou hodnotou/výrazom WHEN.
Poznámka. Prvá a druhá forma CASE sú súčasťou jazykového štandardu SQL, takže s najväčšou pravdepodobnosťou by mali byť použiteľné v mnohých DBMS.
S verziou MS SQL 2012 sa objavila zjednodušená notácia pre IIF. Môže sa použiť na zjednodušenie konštrukcie CASE, keď sa vrátia iba 2 hodnoty. Konštrukcia IIF vyzerá takto:
IIF(podmienka, pravdivá_hodnota, nepravdivá_hodnota)
Tie. toto je v podstate obal pre nasledujúcu konštrukciu CASE:
CASE WHEN podmienka THEN true_value ELSE false_value END
Pozrime sa na príklad:
SELECT ID,Meno,Plat, IIF(Plat>=2500,"Plat >= 2500","Plat< 2500") DemoIIF, CASE WHEN Salary>=2500 POTOM "WR >= 2500" ELSE "WR< 2500" END DemoCASE FROM Employees
Konštrukty CASE, IIF môžu byť vnorené do seba. Zvážte abstraktný príklad:
VYBERTE ID, Meno, Plat, CASE WHEN DepartmentID IN(1,2) THEN "A" WHEN DepartmentID=3 THEN CASE PositionID -- vnorené CASE WHEN 3 THEN "B-1" WHEN 4 THEN "B-2" END ELSE " C" END Demo1, IIF(DepartmentID IN(1,2),"A", IIF(DepartmentID=3,CASE PositionID WHEN 3 THEN "B-1" WHEN 4 THEN "B-2" END,"C")) Demo2 OD zamestnancov
Keďže konštrukcie CASE a IIF sú výrazy, ktoré vracajú výsledok, môžeme ich použiť nielen v bloku SELECT, ale aj v iných blokoch, ktoré umožňujú použitie výrazov, napríklad v blokoch WHERE alebo ORDER BY.
Povedzme napríklad, že sme dostali za úlohu vytvoriť zoznam na vystavenie RFP takto:
- V prvom rade by RFP mali dostávať zamestnanci, ktorých plat je nižší ako 2500
- Tí zamestnanci, ktorí majú plat väčší alebo rovný 2500, dostávajú plat až na druhom mieste
- V rámci týchto dvoch skupín je potrebné zoradiť riadky podľa priezviska (pole Názov)
Pokúsme sa tento problém vyriešiť pridaním výrazu CASE do bloku ORDER BY:
VYBERTE ID,Meno,Plat OD zamestnancov OBJEDNAJTE PODĽA PRÍPADU, KEĎ Mzda>=2500 POTOM 1 INAK 0 KONIEC, -- vydajte PO najprv tým, ktorí ho majú pod 2500 Meno -- ďalej zoraďte zoznam v poradí podľa celého mena
Ako vidíte, Ivanov a Sidorov odídu z práce poslední.
A abstraktný príklad použitia CASE v klauzule WHERE:
VYBERTE ID,Meno,Plat OD zamestnancov V PRÍPADE, KEĎ Mzda>=2500 TAK 1 ELSE 0 KONIEC=1 -- všetky záznamy s výrazom rovným 1
Posledné 2 príklady môžete skúsiť prerobiť sami pomocou funkcie IIF.
A nakoniec si ešte raz pripomeňme hodnoty NULL:
VYBERTE ID, Meno, Plat, ID oddelenia, PRÍPAD, KEĎ DepartmentID=2 POTOM "10 %" -- 10 % RFP na vystavenie účtovníkom, KEĎ DepartmentID=3 POTOM "15 %" -- 15 % RFP na vydanie IT zamestnanci KEĎ JE DepartmentID NULL POTOM "-" -- nedávame bonusy nezávislým pracovníkom (používame IS NULL) ELSE "5 %" -- všetci ostatní dostanú 5 % KONIEC NewYearBonusPercent1, -- inak nemôžete skontrolovať NULL , pamätajte, čo bolo povedané o NULL v druhej časti CASE DepartmentID - - hodnota, ktorá sa má skontrolovať KEĎ 2 TAK "10 %" KEĎ 3 TAK "15 %" KEĎ NULL POTOM "-" -- !!! v tomto prípade nie je vhodné použiť druhú formu CASE INAK "5 %" KONIEC NewYearBonusPercent2 OD Zamestnancov
Samozrejme, mohlo to byť napísané takto:
VYBERTE ID,Meno,Plat,ID oddelenia, CASE ISNULL(DepartmentID,-1) -- použite náhradu za NULL s -1, KEĎ 2, POTOM "10 %", KEĎ 3 POTOM "15 %", KEĎ -1 POTOM "-" -- ak sme si istí, že neexistuje a ani nebude oddelenie s ID rovným (-1) ELSE "5%" KONIEC NewYearBonusPercent3 OD Zamestnancov
Vo všeobecnosti let fantázie v tomto prípade nie je obmedzený.
Pozrime sa napríklad, ako môžeme modelovať funkciu ISNULL pomocou CASE a IIF:
SELECT ID,Meno,Priezvisko, ISNULL(Priezvisko,"Nešpecifikované") DemoISNULL, CASE WHEN Priezvisko JE NULL TAK "Nešpecifikované" ELSE Priezvisko END DemoCASE, IIF(Priezvisko JE NULL,"Nešpecifikované",Priezvisko) DemoIIF OD zamestnancov
Konštrukcia CASE je veľmi výkonná funkcia jazyka SQL, ktorá vám umožňuje zaviesť dodatočnú logiku na výpočet hodnôt sady výsledkov. V tejto časti sa nám bude držanie CASE-konštruktu stále hodiť, preto sa mu v tejto časti venujeme predovšetkým.
Súhrnné funkcie
Tu sa budeme venovať iba základným a najčastejšie používaným agregačným funkciám:| názov | Popis |
|---|---|
| COUNT(*) | Vráti počet riadkov získaných príkazom "SELECT ... WHERE ...". Ak chýba WHERE, počet všetkých záznamov v tabuľke. |
| COUNT(stĺpec/výraz) | Vráti počet hodnôt (nie null) v zadanom stĺpci/výraze |
| POČET (ODLIŠNÝ stĺpec/výraz) | Vráti počet jedinečných nenulových hodnôt v zadanom stĺpci/výraze |
| SUM(stĺpec/výraz) | Vráti súčet hodnôt stĺpca/výrazu |
| AVG(stĺpec/výraz) | Vráti priemernú hodnotu nad hodnotami stĺpca/výrazu. Hodnoty NULL sa nepočítajú. |
| MIN (stĺpec/výraz) | Vráti minimálnu hodnotu nad hodnotami stĺpca/výrazu |
| MAX(stĺpec/výraz) | Vráti maximálnu hodnotu nad hodnotami stĺpca/výrazu |
Agregačné funkcie nám umožňujú vypočítať celkovú hodnotu pre množinu riadkov získanú pomocou príkazu SELECT.
Pozrime sa na každú funkciu s príkladom:
SELECT COUNT(*) [Celkový počet zamestnancov], COUNT(DISTINCT DepartmentID) [Počet jedinečných oddelení], COUNT(DISTINCT PositionID) [Počet jedinečných pozícií], COUNT(BonusPercent) [Počet zamestnancov s bonusom %] , MAX (Bonus Percent) [Maximálne percento bonusu], MIN (Bonus Percent) [Minimálne percento bonusu], SUM (Plat/100*Bonus Percent) [Súčet všetkých bonusov], AVG (Plat/100*Bonus Percent) [Priemerná výška bonusu], AVG ( Plat) [Priemerný plat] OD zamestnancov
Kvôli prehľadnosti som sa rozhodol urobiť výnimku a na nastavenie aliasov stĺpcov som použil syntax […].
Poďme si rozobrať, ako každá vrátená hodnota dopadla, a po prvé si pripomeňme konštrukcie základnej syntaxe príkazu SELECT.
Po prvé, pretože Ak sme v dotaze nešpecifikovali podmienky KDE, potom sa pre podrobné údaje, ktoré sa získajú dotazom, vypočítajú súčty:
VYBERTE * OD zamestnancov
Tie. pre všetky riadky v tabuľke Zamestnanci.
Kvôli prehľadnosti vyberáme iba polia a výrazy, ktoré sa používajú v agregačných funkciách:
SELECT DepartmentID, PositionID, Bonus Percent, Plat/100*Bonus Percent , Plat OD zamestnancov
| ID oddelenia | PositionID | Bonus Percent | Plat/100*Bonus Percent | Plat |
|---|---|---|---|---|
| 1 | 2 | 50 | 2500 | 5000 |
| 3 | 3 | 15 | 225 | 1500 |
| 2 | 1 | NULOVÝ | NULOVÝ | 2500 |
| 3 | 4 | 30 | 600 | 2000 |
| 3 | 3 | NULOVÝ | NULOVÝ | 1500 |
| NULOVÝ | NULOVÝ | NULOVÝ | NULOVÝ | 2000 |
Toto sú počiatočné údaje (podrobné riadky), ktoré sa použijú na výpočet súčtu agregovaného dopytu.
Teraz rozoberme každú agregovanú hodnotu:
| COUNT(*)– pretože Ak sme v dotaze nešpecifikovali podmienky filtrovania v bloku WHERE, tak COUNT(*) nám dalo celkový počet záznamov v tabuľke, t.j. je počet riadkov, ktoré dotaz vráti: VYBERTE * OD zamestnancov |
| COUNT (DISTINCT ID oddelenia)- vrátil nám hodnotu 3, t.j. toto číslo zodpovedá počtu jedinečných hodnôt oddelenia špecifikovaných v stĺpci DepartmentID, s výnimkou hodnôt NULL. Poďme si prejsť hodnoty stĺpca DepartmentID a vyfarbiť rovnaké hodnoty rovnakou farbou (neváhajte, všetky metódy sú dobré na učenie): Zahodíme NULL, po čom dostaneme 3 jedinečné hodnoty (1, 2 a 3). Tie. hodnota získaná pomocou COUNT(DISTINCT DepartmentID) v rozšírenej forme môže byť reprezentovaná nasledujúcou vzorkou: VYBERTE DISTINCT DepartmentID -- 2. vezmite iba jedinečné hodnoty OD zamestnancov, KDE ID oddelenia NIE JE NULL -- 1. zahoďte hodnoty NULL
|
| POČET (DISTINCT ID pozície)- to isté, čo bolo povedané o COUNT(DISTINCT DepartmentID), len pre pole PositionID. Pozeráme sa na hodnoty stĺpca PositionID a nešetríme farbami:
|
| POČET (bonusové percentá)– vráti počet riadkov, ktoré majú hodnotu BonusPercent, t.j. započítava sa počet záznamov s BonusPercent NIE JE NULL. Tu to bude pre nás jednoduchšie, pretože. nemusíte počítať jedinečné hodnoty, stačí zahodiť záznamy s hodnotami NULL. Zoberieme hodnoty stĺpca BonusPercent a prečiarkneme všetky hodnoty NULL: Zostávajú 3 hodnoty. Tie. V rozšírenej forme môže byť vzorka reprezentovaná takto: SELECT BonusPercent -- 2. prevziať všetky hodnoty OD zamestnancov, KDE Bonus Percent NIE JE NULL -- 1. zahodiť hodnoty NULL
Pretože Keďže sme nepoužili slová DISTINCT, potom sa započítajú opakované BonusPercents, ak existujú, pričom sa budú ignorovať BonusPercents rovné NULL. Napríklad porovnajme výsledok s a bez DISTINCT. Pre lepšiu prehľadnosť použijeme hodnoty poľa DepartmentID: SELECT COUNT(*), -- 6 COUNT (DISTINCT DepartmentID), -- 3 COUNT (DepartmentID) -- 5 FROM Zamestnanci
|
| MAX (bonusové percento)- vráti maximálnu hodnotu BonusPercent, opäť bez hodnôt NULL. Berieme hodnoty stĺpca BonusPercent a hľadáme medzi nimi maximálnu hodnotu, nevenujeme pozornosť hodnotám NULL:
VYBERTE 1 NAJLEPŠIE Percento bonusu OD zamestnancov, KDE Percento bonusu NIE JE NULL OBJEDNÁVKA PODĽA BonusPercent DESC -- zoradiť zostupne |
| MIN (bonusové percentá)– vráti minimálnu hodnotu BonusPercent, pričom opäť ignoruje hodnoty NULL. Rovnako ako v prípade MAX, hľadá sa iba minimálna hodnota, pričom sa ignoruje NULL:
Tie. dostaneme nasledujúcu hodnotu: VYBERTE 1 NAJLEPŠIE Percento bonusu OD zamestnancov, KDE Percento bonusu NIE JE NULL OBJEDNÁVKA PODĽA BonusPercent - zoradiť vzostupne Vizuálne znázornenie MIN (bonusových percent) a MAX (bonusových percent):
|
| SUM(Plat/100*bonusové percentá)– vráti súčet všetkých hodnôt, ktoré nie sú NULL. Analýza hodnôt výrazu (Plat/100*Bonus Percent):
Tie. sčítavajú sa nasledujúce hodnoty: SELECT Plat/100*Bonus Percent OD zamestnancov, KDE Mzda/100*Bonus Percent NIE JE NULL
|
| AVG (plat/100*bonusové percentá)- vráti priemer hodnôt. Neberú sa do úvahy výrazy NULL, t.j. toto zodpovedá druhému výrazu: SELECT AVG(Plat/100*Bonus Percent), -- 1108,333333333333 SUM(Plat/100*Bonus Percent)/POČET (Plat/100*BonusPercent), -- 1108,333333333333 SUM(6 centov*6SUM) 60 Sk*6SUM OD zamestnancov
Tie. opäť hodnoty NULL sa nezapočítavajú do počtu. Ak potrebujete vypočítať priemer všetkých zamestnancov, ako v treťom výraze, ktorý dáva 554,166666666667, potom použite predbežný prevod hodnôt NULL na nulu: SELECT AVG(ISNULL(Plat/100*Bonus Percent,0)), -- 554,166666666667 SUM(Plat/100*Bonus Percent)/POČET (*) -- 554,166666666667 OD zamestnancov |
| AVG (plat)– v skutočnosti je tu všetko rovnaké ako v predchádzajúcom prípade, t.j. ak je mzda zamestnanca NULL, potom sa nebude započítavať. Ak chcete vziať do úvahy všetkých zamestnancov, vykonajte predbežnú NULL transformáciu hodnôt AVG(ISNULL(Plat,0)) |
Zhrňme si nejaké výsledky:
- COUNT(*) – slúži na počítanie celkového počtu riadkov, ktoré sú prijaté príkazom „SELECT ... WHERE ...“
- vo všetkých ostatných agregovaných funkciách uvedených vyššie sa pri výpočte súčtu neberú do úvahy hodnoty NULL
- ak potrebujeme vziať do úvahy všetky riadky, je to relevantnejšie pre funkciu AVG, potom je najprv potrebné spracovať hodnoty NULL, napríklad ako je uvedené vyššie "AVG (ISNULL (Mzda, 0))"
Preto pri zadávaní dodatočnej podmienky s agregačnými funkciami v bloku WHERE sa vypočítajú len súčty pre riadky, ktoré spĺňajú podmienku. Tie. súhrnné hodnoty sa vypočítajú pre konečný súbor, ktorý sa získa pomocou konštruktu SELECT. Urobme napríklad to isté, ale iba v kontexte oddelenia IT:
SELECT COUNT(*) [Celkový počet zamestnancov], COUNT(DISTINCT DepartmentID) [Počet jedinečných oddelení], COUNT(DISTINCT PositionID) [Počet jedinečných pozícií], COUNT(BonusPercent) [Počet zamestnancov s bonusom %] , MAX (Bonus Percent) [Maximálne percento bonusu], MIN (Bonus Percent) [Minimálne percento bonusu], SUM (Plat/100*Bonus Percent) [Súčet všetkých bonusov], AVG (Plat/100*Bonus Percent) [Priemerná výška bonusu], AVG ( Plat) [Priemerný plat] OD zamestnancov, KDE DepartmentID=3 -- berte do úvahy iba IT oddelenie
Odporúčam vám, aby ste lepšie porozumeli práci agregovaných funkcií, nezávisle analyzovať každú prijatú hodnotu. Tu vykonávame výpočty podľa podrobných údajov prijatých na žiadosť:
SELECT DepartmentID, PositionID, BonusPercent, Plat/100*BonusPercent , Plat OD zamestnancov WHERE DepartmentID=3 -- zvážte iba IT oddelenie
| ID oddelenia | PositionID | Bonus Percent | Plat/100*Bonus Percent | Plat |
|---|---|---|---|---|
| 3 | 3 | 15 | 225 | 1500 |
| 3 | 4 | 30 | 600 | 2000 |
| 3 | 3 | NULOVÝ | NULOVÝ | 1500 |
Pohni sa. Ak agregačná funkcia vráti NULL (napríklad všetci zamestnanci nemajú hodnotu Mzda), alebo vo výbere nie sú žiadne záznamy a v zostave pre tento prípad musíme zobraziť 0, potom funkcia ISNULL môže zabaliť súhrnný výraz:
SELECT SUM(Plat), AVG(Plat), -- spracovať súčet pomocou ISNULL ISNULL(SUM(Plat),0), ISNULL(AVG(Plat),0) FROM Zamestnanci WHERE DepartmentID=10 -- tu zadané neexistujúce oddelenie takže dotaz nevracia žiadne záznamy
| (Bez názvu stĺpca) | (Bez názvu stĺpca) | (Bez názvu stĺpca) | (Bez názvu stĺpca) |
|---|---|---|---|
| NULOVÝ | NULOVÝ | 0 | 0 |
Myslím si, že je veľmi dôležité pochopiť účel každej agregovanej funkcie a spôsob výpočtu, pretože v SQL je to hlavný nástroj používaný na výpočet súčtov.
V tomto prípade sme zvažovali, ako sa každá agregačná funkcia správa nezávisle, t.j. aplikovalo sa na hodnoty celej sady záznamov vrátené príkazom SELECT. Ďalej sa pozrieme na to, ako sa tieto isté funkcie používajú na výpočet súčtu skupín pomocou klauzuly GROUP BY.
GROUP BY - zoskupenie údajov
Predtým sme už vypočítali súčty pre konkrétne oddelenie, asi takto:SELECT COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci WHERE DepartmentID=3 -- údaje len pre IT oddelenie
Teraz si predstavte, že sme boli požiadaní, aby sme získali rovnaké čísla v kontexte každého oddelenia. Samozrejme si môžeme vyhrnúť rukávy a zadať rovnakú požiadavku pre každé oddelenie. Takže, povedané a hotovo, napíšeme 4 otázky:
SELECT "Administration" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Palary) PlatAmount FROM Zamestnanci WHERE DepartmentID=1 -- Údaje pre správu SELECT "Účtovníctvo" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT( * ) Počet zamestnancov, SÚČET (Mzd) Suma platu OD zamestnancov WHERE DepartmentID=2 -- Účtovné údaje VYBERTE "IT" Info, COUNT(DISTINCT PositionID) Počet pozícií, COUNT(*) Počet zamestnancov, SUM(Mzda) Suma platu OD zamestnancov WHERE DepartmentID=3 -- Údaje IT oddelenia VYBERTE "Iné" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci, KDE JE ID oddelenia NULL -- a nezabudnite na údaje na voľnej nohe
V dôsledku toho získame 4 súbory údajov:
Upozorňujeme, že môžeme použiť polia špecifikované ako konštanty - "Administrácia", "Účtovníctvo", ...
Vo všeobecnosti sme získali všetky čísla, na ktoré sme boli požiadaní, skombinovali všetko v Exceli a dali to riaditeľovi.
Riaditeľovi sa správa páčila a hovorí: "A pridajte ďalšiu kolónku s údajmi o priemernej mzde." A ako vždy to treba urobiť veľmi urgentne.
Hmm, čo robiť?! Navyše si predstavte, že nemáme 3 oddelenia, ale 15.
Presne na to slúži konštrukcia GROUP BY pre takéto prípady:
SELECT DepartmentID, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount, AVG(Malary) SalaryAvg -- plus splniť želanie riaditeľa FROM Zamestnanci GROUP BY DepartmentID
| ID oddelenia | Počet pozícií | EmplCount | Výška platu | Priem. plat |
|---|---|---|---|---|
| NULOVÝ | 0 | 1 | 2000 | 2000 |
| 1 | 1 | 1 | 5000 | 5000 |
| 2 | 1 | 1 | 2500 | 2500 |
| 3 | 2 | 3 | 5000 | 1666.66666666667 |
Dostali sme všetky rovnaké údaje, ale teraz používame iba jednu požiadavku!
Zatiaľ nevenujte pozornosť tomu, že naše oddelenia sú zobrazené vo forme čísel, potom sa naučíme, ako všetko krásne zobraziť.
V klauzule GROUP BY môžete zadať niekoľko polí „GROUP BY pole1, pole2, ..., poleN“, v tomto prípade sa zoskupenie uskutoční v skupinách, ktoré tvoria hodnoty týchto polí „pole1, pole2, .. ., pole N".
Napríklad zoskupme údaje v kontexte oddelení a pozícií:
SELECT DepartmentID,PositionID, COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci GROUP BY DepartmentID,PositionID
Potom sa vykoná beh každej kombinácie a vypočítajú sa súhrnné funkcie:
SELECT COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci WHERE DepartmentID JE NULL A PositionID JE NULL SELECT COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci WHERE DepartmentID=1 AND PositionID=2 -- ... SELECT COUNT(*) EmplCount, SUM(Plat) MzdaSuma OD zamestnancov WHERE DepartmentID=3 AND PositionID=4
A potom sa všetky tieto výsledky skombinujú a dajú nám ako jeden súbor:
Z toho hlavného stojí za zmienku, že v prípade zoskupenia (GROUP BY) v zozname stĺpcov v bloku SELECT:
- Môžeme použiť iba stĺpce uvedené v bloku GROUP BY
- Môžete použiť výrazy s poľami z bloku GROUP BY
- Môžete použiť konštanty, pretože nemajú vplyv na výsledok zoskupenia
- Všetky ostatné polia (neuvedené v bloku GROUP BY) možno použiť iba s agregačnými funkciami (COUNT, SUM, MIN, MAX, ...)
- V zozname stĺpcov SELECT nie je potrebné uvádzať všetky stĺpce z bloku GROUP BY
A ukážka toho všetkého:
SELECT "Konštanta reťazca" Const1, -- konštanta ako reťazec 1 Const2, -- konštanta ako číslo -- výraz využívajúci polia zahrnuté v zoskupení CONCAT("Department № ",DepartmentID) ConstAndGroupField, CONCAT("Department № ", DepartmentID ,", Position № ",PositionID) ConstAndGroupFields, DepartmentID, -- pole zo zoznamu polí participujúcich na zoskupení -- PositionID, -- pole participujúce na zoskupení, tu nie je potrebné duplikovať COUNT(*) EmplCount , -- počet riadkov v skupine -- ostatné polia je možné použiť iba s agregačnými funkciami: COUNT, SUM, MIN, MAX, … SUM(Mzda) Suma mzdy, MIN(ID) MINID OD zamestnancov GROUP BY DepartmentID,PositionID -- zoskupenie podľa polí DepartmentID, PositionID
Za zmienku tiež stojí, že zoskupovanie možno vykonávať nielen podľa polí, ale aj podľa výrazov. Napríklad zoskupme údaje podľa zamestnancov podľa roku narodenia:
SELECT CONCAT("Rok narodenia - ",YEAR(Narodeniny)) YearOfBirthday, COUNT(*) EmplCount FROM Zamestnanci GROUP PODĽA ROKU (Narodeniny)
Pozrime sa na príklad so zložitejším výrazom. Napríklad dostaneme odstupňovanie zamestnancov podľa roku narodenia:
VYBERTE PRÍPAD KEDY ROK(Narodeniny)>=2000 THEN "od 2000" KEDY ROK (Narodeniny)>=1990 THEN "1999-1990" KEDY ROK(Narodeniny)>=1980 THEN "1989-1980" KEDY NARODENIE ROK>( 1970 POTOM "1979-1970" KEĎ NARODENIA NIE SÚ NULL POTOM "pred rokom 1970" ELSE "nešpecifikované" KONIEC Názov rozsahu, POČET(*) Počet zamestnancov SKUPINA PODĽA PRÍPADU KEĎ ROK(Narodeniny)>=2000 TENTO ROK"od 2000 ROK"OD 2000 (Narodeniny)>=1990 POTOM "1999-1990" KEĎ ROK (Narodeniny)>=1980 POTOM "1989-1980" KEĎ ROK (Narodeniny)>=1970 POTOM "1979-1970", KEĎ NARODENIA NIE SÚ 7 NULL ELSE "nešpecifikované" KONIEC
| RangeName | EmplCount |
|---|---|
| 1979-1970 | 1 |
| 1989-1980 | 2 |
| nešpecifikované | 2 |
| pred rokom 1970 | 1 |
Tie. v tomto prípade sa zoskupenie vykoná podľa výrazu CASE, ktorý bol predtým vypočítaný pre každého zamestnanca:
VYBERTE ID, PRÍPAD KEDY ROK(Narodeniny)>=2000 THEN "od 2000" KEDY ROK(Narodeniny)>=1990 THEN "1999-1990" KEDY ROK(Narodeniny)>=1980 POTOM "1989-1980" KEDY ROK >=1970 POTOM "1979-1970" KEĎ NARODENIA NIE SÚ NULL POTOM "pred rokom 1970" INAK "nešpecifikované" KONIEC OD zamestnancov
A samozrejme môžete kombinovať výrazy s poľami v bloku GROUP BY:
SELECT DepartmentID, CONCAT("Rok narodenia - ",YEAR(Birthday)) YearOfBirthday, COUNT(*) EmplCount FROM Zamestnanci GROUP BY YEAR(Birthday),DepartmentID -- objednávka sa nemusí zhodovať s objednávkou, v ktorej sú použité v SELECT ORDER BY Blok DepartmentID, YearOfBirthday -- konečne môžeme zoradiť výsledok
Vráťme sa k našej pôvodnej úlohe. Ako už vieme, riaditeľovi sa správa veľmi páčila a požiadal nás, aby sme ju robili týždenne, aby mohol sledovať zmeny vo firme. Aby sme zakaždým v Exceli neprerušili digitálnu hodnotu oddelenia s jeho názvom, využijeme znalosti, ktoré už máme, a vylepšíme náš dotaz:
SELECT CASE DepartmentID WHEN 1 THEN "Administration" WHEN 2 THEN "Accounting" WHEN 3 THEN "IT" ELSE "Iné" END Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount, AVG(Malary ) PlatAvg -- plus splníme želanie riaditeľa OD zamestnancov SKUPINA PODĽA ID oddelenia OBJEDNAŤ PODĽA Info -- pre väčšie pohodlie pridajte triedenie podľa stĺpca Info
Aj keď to zvonku môže vyzerať strašidelne, stále je to lepšie ako pôvodne. Nevýhodou je, že ak sa spustí nové oddelenie a jeho zamestnanci, budeme musieť pridať výraz CASE, aby zamestnanci nového oddelenia nespadali do skupiny „Iné“.
Ale nič, časom sa naučíme robiť všetko krásne, aby náš výber nezávisel od objavenia sa nových údajov v databáze, ale bol dynamický. Trochu preskočím, aby som ukázal, aké druhy dopytov sa snažíme dostať do písania:
SELECT ISNULL(dep.Name,"Others") DepName, COUNT(DISTINCT emp.PositionID) PositionCount, COUNT(*) EmplCount, SUM(emp.Salary) PlatAmount, AVG(emp.Mold) PlatAvg -- plus splniť želanie riaditeľa OD Zamestnanci zam. LEFT PRIPOJTE SA K oddeleniam dep ON emp.DepartmentID=dep.ID GROUP BY emp.DepartmentID,dep.Name ORDER BY DepName
Vo všeobecnosti sa nebojte - každý začal s jednoduchým. Zatiaľ vám stačí pochopiť podstatu konštrukcie GROUP BY.
Nakoniec sa pozrime, ako môžete zostaviť súhrnné zostavy pomocou GROUP BY.
Ukážme si napríklad kontingenčnú tabuľku rozdelenú podľa oddelení, aby sa vypočítali celkové mzdy zamestnancov podľa pozície:
SELECT DepartmentID, SUM(CASE WHEN PositionID=1 THEN Plat END) [Účtovníci], SUM(CASE WHEN PositionID=2 THEN Plat END) [Riaditelia], SUM(CASE WHEN PositionID=3 THEN Plat END) [Programátori], SUM( PRÍPAD, KEĎ ID pozície=4 POTOM KONIEC platu) [Senior programátori], SÚČET (plat) [Celkom oddelenia] OD zamestnancov SKUPINA PODĽA ID oddelenia
Tie. v rámci agregovaných funkcií môžeme voľne používať akékoľvek výrazy.
Samozrejme, môžete ho prepísať aj pomocou IIF:
VYBERTE ID oddelenia, SUM(IIF(ID pozície=1,Plat,NULL)) [účtovník], SUM(IIF(ID pozície=2,Plat,NULL)) [Riaditeľ], SUM(IIF(ID pozície=3,Plat,NULL)) [Programátori], SUM(IIF(ID pozície=4,Plat,NULL)) [Senior programátori], SUM(Plat) [Celkom oddelenia] OD zamestnancov SKUPINA PODĽA ID oddelenia
Ale v prípade IIF budeme musieť explicitne špecifikovať NULL, ktorá sa vráti, ak podmienka nie je splnená.
V podobných prípadoch radšej používam CASE bez bloku ELSE, ako znova písať NULL. Ale to je samozrejme vec vkusu, ktorá nie je spochybnená.
A pamätajme, že hodnoty NULL sa počas agregácie nezohľadňujú v agregovaných funkciách.
Ak chcete konsolidovať, vykonajte nezávislú analýzu prijatých údajov na podrobnú požiadavku:
SELECT DepartmentID, CASE WHEN PositionID=1 THEN Plat END [účtovník], CASE WHEN PositionID=2 THEN Plat END [Riaditelia], CASE WHEN PositionID=3 THEN Plat END [Programátori], CASE WHEN PositionID=4 THEN Plat END [Senior programátori ] ], Plat [Celkom podľa oddelení] OD zamestnancov
| ID oddelenia | účtovník | riaditeľov | programátorov | senior programátorov | Celkom podľa oddelení |
|---|---|---|---|---|---|
| 1 | NULOVÝ | 5000 | NULOVÝ | NULOVÝ | 5000 |
| 3 | NULOVÝ | NULOVÝ | 1500 | NULOVÝ | 1500 |
| 2 | 2500 | NULOVÝ | NULOVÝ | NULOVÝ | 2500 |
| 3 | NULOVÝ | NULOVÝ | NULOVÝ | 2000 | 2000 |
| 3 | NULOVÝ | NULOVÝ | 1500 | NULOVÝ | 1500 |
| NULOVÝ | NULOVÝ | NULOVÝ | NULOVÝ | NULOVÝ | 2000 |
A tiež si pamätajme, že ak namiesto NULL chceme vidieť nuly, tak vieme spracovať hodnotu vrátenú agregovanou funkciou. Napríklad:
SELECT DepartmentID, ISNULL(SUM(IIF(PozíciaID=1,Plat,NULL)),0) [Účtovník], ISNULL(SUM(IIF(PositionID=2,Plat,NULL)),0) [Riaditeľ], ISNULL(SUM (IIF(ID pozície=3,Plat,NULL)),0) [Programátori], ISNULL(SUM(IIF(ID pozície=4,Plat,NULL)),0) [Senior programátori], ISNULL(SUM(Plat),0 ) [Celkom podľa oddelenia] OD zamestnancov SKUPINA PODĽA ID oddelenia
Teraz môžete na precvičenie:
- názvy výstupných oddelení namiesto ich ID, napríklad pridaním príkazu CASE, ktorý spracováva ID oddelenia v bloku SELECT
- pridajte triedenie podľa názvu oddelenia pomocou ORDER BY
GROUP BY je v spojení s agregačnými funkciami jedným z hlavných nástrojov používaných na získanie súhrnných údajov z databázy, pretože údaje sa zvyčajne používajú v tejto forme, pretože. zvyčajne sme povinní poskytovať súhrnné správy, nie podrobné údaje (hárky). A samozrejme, všetko sa to točí okolo poznania základného dizajnu, pretože. predtým, ako niečo zhrniete (agregujete), musíte to najskôr správne vybrať pomocou „SELECT ... WHERE ...“.
Prax tu hrá dôležitú úlohu, takže ak si stanovíte cieľ porozumieť jazyku SQL, nie študovať, ale rozumieť – cvičiť, cvičiť a cvičiť, triediť cez tie najrôznejšie možnosti, aké vás napadnú.
V počiatočných fázach, ak si nie ste istí správnosťou získaných agregovaných údajov, vykonajte podrobný výber vrátane všetkých hodnôt, ktoré sa agregujú. A podľa týchto podrobných údajov ručne skontrolujte správnosť výpočtov. V tomto prípade môže byť použitie Excelu veľmi užitočné.
Povedzme, že ste dosiahli tento bod
Povedzme, že ste účtovník Sidorov S.S., ktorý sa rozhodol naučiť písať SELECT dopyty.Povedzme, že ste si tento návod už prečítali až sem a už s istotou používate všetky vyššie uvedené základné konštrukcie, t.j. môžeš:
- Vyberte podrobné údaje podľa klauzuly WHERE z jednej tabuľky
- Vedieť používať agregačné funkcie a zoskupovanie z jednej tabuľky
Áno, ale nebrali do úvahy, že aj tak nevieš zostaviť dotazy z viacerých tabuliek, ale len z jednej, t.j. nemôžete urobiť niečo také:
SELECT zam.*, -- vráťte všetky polia tabuľky Zamestnanci dep.Name DepartmentName, -- do týchto polí pridajte pole Názov z tabuľky Oddelenia poz.Name PozíciaName -- a pridajte pole Názov z tabuľky Pozície FROM Zamestnanci zam. LEFT JOIN Oddelenia dep ON emp.DepartmentID=dep.ID LEFT JOIN Pozícia ON emp.PositionID=poz.ID
Napriek tomu, že neviete, ako to urobiť, verte mi, urobili ste dobre a dosiahli ste už toľko.
Ako teda môžete využiť svoje súčasné znalosti a zároveň dosiahnuť ešte produktívnejšie výsledky?! Využime silu kolektívnej mysle – ideme za programátormi, ktorí pre vás pracujú, t.j. Andreevovi A.A., Petrovovi P.P. alebo Nikolaev N.N., a požiadajte jedného z nich, aby vám napísal pohľad (POHĽAD alebo len „Zobraziť“, takže vám dokonca, myslím, rýchlejšie porozumejú), ktorý vám okrem hlavných polí z tabuľky Zamestnanci vrátiť aj polia s „Názov oddelenia“ a „Názov pozície“, ktoré vám teraz tak chýbajú v týždennom prehľade, ktorý vám naložil Ivanov I.I.
Pretože všetko ste správne vysvetlili, potom IT špecialisti okamžite pochopili, čo od nich chcú a špeciálne pre vás vytvorili pohľad s názvom ViewEmployeesInfo.
Predstavujeme si, že nasledujúci príkaz nevidíte, pretože Ľudia z IT to robia:
CREATE VIEW ViewEmployeesInfo AS SELECT emp.*, -- vráti všetky polia tabuľky Zamestnanci dep.Name DepartmentName, -- do týchto polí pridajte pole Name z tabuľky Departments poz.Name PositionName -- a tiež pridajte pole Name z poľa Name Tabuľka pozícií OD Zamestnanci zam. LEFT JOIN Oddelenia dep ON emp.DepartmentID=dep.ID LEFT JOIN Pozícia ON emp.PositionID=poz.ID
Tie. pre vás celý tento, doteraz strašidelný a nezrozumiteľný text ostáva v zákulisí a ITčkári vám dajú len názov pohľadu ViewEmployeesInfo, ktorý vráti všetky vyššie uvedené údaje (teda to, čo ste od nich žiadali).
Teraz môžete s týmto zobrazením pracovať ako s bežnou tabuľkou:
SELECT * FROM ViewEmployeesInfo
Pretože teraz sú všetky údaje potrebné pre zostavu v jednej „tabuľke“ (a la zobrazenie), potom môžete jednoducho znova vykonať svoj týždenný prehľad:
SELECT DepartmentName, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount, AVG(Malary) PlatAvg FROM ViewEmployeesInfo emp GROUP BY DepartmentID,DepartmentName ORDER BY DepartmentName
Teraz sú všetky názvy oddelení na mieste a dopyt sa stal dynamickým a zmení sa, keď pribudnú nové oddelenia a ich zamestnanci, t.j. teraz už nemusíte nič prerábať, ale stačí požiadavku vykonať raz týždenne a výsledok odovzdať riaditeľovi.
Tie. pre vás v tomto prípade, akoby sa nič nezmenilo, naďalej pracujete s jednou tabuľkou rovnakým spôsobom (len je to správnejšie povedať so zobrazením ViewEmployeesInfo), ktorá vráti všetky potrebné údaje. Vďaka pomoci IT ľudí zostali pre vás podrobnosti o získaní DepartmentName a PositionName v čiernej skrinke. Tie. pohľad vám vyzerá ako bežná tabuľka, predstavte si to ako rozšírenú verziu tabuľky Zamestnanci.
Vytvorte napríklad vyhlásenie, aby ste sa uistili, že všetko platí tak, ako som povedal (že celý výber pochádza z jedného pohľadu):
VYBERTE ID, Meno, Mzda OD ZobraziťInformácie o zamestnancoch, KDE Mzda NIE JE NULL A Mzda>0 OBJEDNAŤ PODĽA mena
Dúfam, že tejto žiadosti rozumiete.
Použitie pohľadov v niektorých prípadoch umožňuje výrazne rozšíriť hranice používateľov, ktorí vedia písať základné SELECT dotazy. V tomto prípade je pohľadom plochá tabuľka so všetkými údajmi, ktoré používateľ potrebuje (pre tých, ktorí rozumejú OLAP, to možno porovnať s približnou podobnosťou kocky OLAP s faktami a rozmermi).
Výstrižok z Wikipédie. Hoci SQL bol koncipovaný ako nástroj pre koncového používateľa, nakoniec sa stal natoľko zložitým, že sa stal nástrojom programátora.
Ako vidíte, milí používatelia, jazyk SQL bol pôvodne koncipovaný ako nástroj pre vás. Takže všetko je vo vašich rukách a túžbe, nenechajte sa ísť.
HAVING - použitie podmienky výberu na zoskupené údaje
V skutočnosti, ak rozumiete tomu, čo je zoskupovanie, potom na HAVING nie je nič zložité. HAVING - niečo podobné ako WHERE, iba ak sa podmienka WHERE aplikuje na podrobné dáta, potom sa podmienka HAVING aplikuje na už zoskupené dáta. Z tohto dôvodu môžeme v podmienkach bloku HAVING použiť buď výrazy s poľami zahrnutými v zoskupení, alebo výrazy uzavreté v agregačných funkciách.Zvážte príklad:
VYBERTE ID oddelenia, SÚČET (Plat) PlatSuma OD zamestnancov SKUPINA PODĽA ID oddelenia SÚČET (Mzda)>3000
| ID oddelenia | Výška platu |
|---|---|
| 1 | 5000 |
| 3 | 5000 |
Tie. tento dotaz nám vrátil zoskupené údaje len za tie oddelenia, ktorých celková mzda všetkých zamestnancov presahuje 3000, t.j. "SÚČET (Plat) > 3000".
Tie. tu najskôr dochádza k zoskupovaniu a vypočítavajú sa údaje pre všetky oddelenia:
VYBERTE ID oddelenia, SÚČET (Plat) Suma mzdy OD zamestnancov GROUP BY DepartmentID -- 1. získajte zoskupené údaje naprieč všetkými oddeleniami
A na tieto údaje sa už vzťahuje podmienka špecifikovaná v bloku HAVING:
SELECT DepartmentID, SUM(Mold) PlatAmount FROM Zamestnanci GROUP BY DepartmentID -- 1. získajte zoskupené údaje pre všetky oddelenia MAJÚCE SÚČET (plat)>3000 -- 2. podmienka pre filtrovanie zoskupených údajov
V podmienke HAVING môžete tiež vytvoriť zložité podmienky pomocou operátorov AND, OR a NOT:
VYBERTE ID oddelenia, SÚČET (Plat) Suma mzdy OD zamestnancov SKUPINA PODĽA ID oddelenia S SÚČETOU (Plat) > 3000 A POČET (*)<2 -- и число людей меньше 2-х
Ako môžete vidieť tu, agregačná funkcia (pozri "COUNT(*)") môže byť špecifikovaná iba v bloku HAVING.
Podľa toho môžeme zobraziť iba číslo oddelenia, ktoré spadá pod podmienku HAVING:
VYBERTE ID oddelenia OD zamestnancov SKUPINA PODĽA ID oddelenia S SÚČETOU (Plat) > 3000 A POČET (*)<2 -- и число людей меньше 2-х
Príklad použitia podmienky HAVING pre pole zahrnuté v GROUP BY:
SELECT DepartmentID, SUM(Mold) PlatAmount FROM Zamestnanci GROUP BY DepartmentID -- 1. vytvorte zoskupenie MAJÚCI ID oddelenia=3 -- 2. použite filter na výsledok zoskupenia
Toto je len príklad, pretože v tomto prípade by bolo logickejšie skontrolovať podmienku WHERE:
VYBRAŤ ID oddelenia, SUM(Mzda) MzdaSuma OD Zamestnancov KDE IDoddelu=3 -- 1. filtrovať podrobné údaje GROUP BY DepartmentID -- 2. skupina len podľa vybraných záznamov
Tie. najprv vyfiltrujte zamestnancov podľa oddelenia 3 a až potom vykonajte výpočet.
Poznámka. V skutočnosti, aj keď tieto dva dotazy vyzerajú odlišne, optimalizátor DBMS ich môže vykonať rovnakým spôsobom.
Myslím, že toto je koniec príbehu o MAJTE podmienky.
Zhrnutie
Zhrňme si údaje získané v druhej a tretej časti a zvážme konkrétne umiestnenie každej štruktúry, ktorú sme študovali, a uveďte poradie, v ktorom sa vykonávajú:| Štruktúra/blok | Poriadok vykonania | Spustiteľná funkcia |
|---|---|---|
| SELECT návratové výrazy | 4 | Vrátenie údajov prijatých žiadosťou |
| ZO zdroja | 0 | V našom prípade sú to zatiaľ všetky riadky tabuľky |
| WHERE podmienka pre načítanie zo zdroja | 1 | Vyberú sa iba riadky zodpovedajúce podmienke |
| GROUP BY zoskupovacie výrazy | 2 | Vytvorte skupiny na základe zadaného výrazu zoskupenia. Výpočet agregovaných hodnôt pre tieto skupiny používané v blokoch SELECT alebo HAVING |
| HAVING filtrovať podľa zoskupených údajov | 3 | Filtrovanie použité na zoskupené údaje |
| ORDER BY výraz triedenia výsledkov | 5 | Zoradiť údaje podľa zadaného výrazu |
Samozrejme, môžete tiež použiť klauzuly DISTINCT a TOP naučené v časti 2 na zoskupené údaje.
Tieto návrhy v tomto prípade budú platiť pre konečný výsledok:
VYBERTE TOP 1 -- 6. použite poslednú SÚČET (Plat) MzdaSuma OD zamestnancov ZOSKUPENIE PODĽA ID oddelenia S SÚČETOU (Plat)>3000 OBJEDNAŤ PODĽA ID oddelenia -- 5. Zoradiť výsledok
Ako ste sa k týmto výsledkom dostali, analyzujte sami.
Záver
Hlavným cieľom, ktorý som si v tejto časti stanovil, je odhaliť vám podstatu agregovaných funkcií a zoskupení.Ak nám základný návrh umožnil získať potrebné podrobné údaje, potom použitie agregovaných funkcií a zoskupení na týchto podrobných údajoch nám umožnilo získať o nich súhrnné údaje. Takže, ako vidíte, všetko je tu dôležité, pretože. jedno sa spolieha na druhé - bez znalosti základného dizajnu nebudeme vedieť napríklad správne vybrať údaje, pre ktoré potrebujeme vypočítať výsledky.
Tu sa zámerne snažím ukázať len základy, aby som začiatočníkov zameral na najdôležitejšie konštrukcie a nezahltil ich zbytočnými informáciami. Dôkladné pochopenie základných konštrukcií (o ktorých budem pokračovať v ďalších častiach) vám dá príležitosť vyriešiť takmer akýkoľvek problém získavania údajov z RDB. Základné konštrukcie príkazu SELECT sú v rovnakej podobe použiteľné takmer vo všetkých DBMS (rozdiely sú najmä v detailoch napr. v implementácii funkcií – pre prácu s reťazcami, časom a pod.).
Dobrá znalosť databázy vám v budúcnosti poskytne príležitosť ľahko sa naučiť rôzne rozšírenia jazyka SQL, ako napríklad:
- SKUPINA PODĽA ROLLUPU (…), SKUPINA PODĽA SKUPENIA SÚPRAV (…), …
- PIVOT, NEPIVOT
- atď.
Ak robíte prvé kroky v SQL, tak sa v prvom rade zamerajte na naučenie sa základných štruktúr, pretože. vlastniť základňu, všetko ostatné bude pre vás oveľa jednoduchšie pochopiť, a navyše, na vlastnú päsť. V prvom rade treba do hĺbky pochopiť možnosti jazyka SQL, t.j. aké operácie vo všeobecnosti umožňuje vykonávať s údajmi. Sprostredkovať informácie začiatočníkom v objemovej forme je ďalší dôvod, prečo ukážem len najdôležitejšie (železné) štruktúry.
Veľa šťastia pri učení a pochopení jazyka SQL.
Štvrtá časť -
Ale vyhlásenie Oracle CASE je ešte lepšie. Vydanie Oracle 8i zaviedlo výraz CASE. Príkazy Oracle CASE dokážu všetko, čo robí DECODE, plus množstvo ďalších vecí vrátane analýzy IF-THEN, použitia ľubovoľného porovnávacieho operátora a kontroly viacerých podmienok, to všetko v samotnom SQL dotaze. Navyše pomocou funkcie CASE je možné spojiť viacero podmienok poskytnutých v samostatných SQL dotazoch do jednej, čím sa predíde viacerým príkazom v tej istej tabuľke (príklad je uvedený nižšie). Funkcia je dostupná od Oracle 8i a vyššie.
Oracle case statement základná syntax
Syntax výrazu Oracle CASE je podobná príkazu IF-THEN-ELSE. Oracle kontroluje každú podmienku od prvej podmienky (zľava doprava). Keď je splnená konkrétna podmienka (časť WHEN), výraz vráti označenú hodnotu (časť THEN). Ak nie je splnená žiadna z podmienok, vráti sa hodnota uvedená v časti ELSE. Časť výrazu ELSE nie je povinná -- výraz CASE vráti hodnotu null, ak nie je splnené nič. Tu je základná syntax príkazu Oracle CASE When:
Prípad kedy
kedy
...
inak
koniec
Príklady
Nasledujúce príklady objasnia používanie výrazu CASE pomocou príkazov Oracle CASE select.
Napr.: Vrátenie kategórií na základe platu zamestnanca.
Vyberte predaj, prípad, keď predaj< 2000 then "category 1"
keď sal< 3000 then "category 2"
keď sal< 4000 then "category 3"
inak "kategória 4"
koniec
z emp;
Napr.: Požiadavkou je zistiť počet zamestnancov pre rôzne podmienky, ako je uvedené nižšie. Existuje niekoľko spôsobov, ako získať tento výstup. Je možné napísať päť rôznych vyhlásení, aby ste našli počet zamestnancov na základe platových a províznych podmienok, alebo je možné napísať jeden výber s výbermi na úrovni stĺpcov.
vybrať počet (1)
z emp
kde sal< 2000
a comm nie je null;
vybrať počet (1)
z emp
kde sal< 2000
a comm je null;
vybrať počet (1)
z emp
kde sal< 5000
a comm nie je null;
vybrať počet (1)
z emp
kde sal< 5000
a comm je null;
vybrať počet (1)
z emp
kde sal > 5000;
Vybrať (vybrať počet(1)
z emp
kde sal< 2000
a comm nie je null)
(vyberte počet (1)
z emp
kde sal< 2000
a comm je null) b,
(vyberte počet (1)
z emp
kde sal< 5000
a comm nie je null) c,
(vyberte počet (1)
z emp
kde sal< 5000
a comm je null) d,
(vyberte počet (1)
z emp
kde sal > 5000) e
z duálu
S výrazom CASE sa možno vyhnúť viacerým vyššie uvedeným príkazom v tej istej tabuľke pomocou Oracle select case.
Vyberte počet (prípad pri predaji< 2000 and comm is not null then 1
inak null
koniec),
počítať (prípad, keď sal< 2000 and comm is null then 1
inak null
koniec),
počítať (prípad, keď sal< 5000 and comm is not null then 1
inak null
koniec),
počítať (prípad, keď sal< 5000 and comm is null then 1
inak null
koniec),
počet (prípad, keď predaj > 5000, potom 1
inak null
koniec)
z emp;
Vyberte počet (prípad pri predaji< 2000 and comm is not null then 1
koniec) cnt1,
počítať (prípad, keď sal< 2000 and comm is null then 1
koniec) cnt2,
počítať (prípad, keď sal< 5000 and comm is not null then 1
koniec) cnt3,
počítať (prípad, keď sal< 5000 and comm is null then 1
koniec) cnt4,
počet (prípad, keď predaj > 5000, potom 1
koniec) cnt5
z emp;
Napr.: Výraz CASE môže byť tiež vnorený.
Vyberte (prípad, keď mnozstvo_menej6mesiacov< 0 and qty_6to12months < 0 then
(prípad, keď je kód sezóny v ("0", "1", "2", "3", "4") potom "hodnota je záporná"
inak "žiadne zásoby"
koniec)
keď qty_1 to 2 years< 0 and qty_2to3years < 0 then
(prípad, keď je kód sezóny v ("A", "B", "C", "D", "E") potom "hodnota je záporná"
inak "žiadne zásoby"
koniec)
inak "Na sklade"
end) stock_check
od jnc_lots_ageing_mexx_asof
kde číslo riadok< 20
a qty_less6 months< 0 and qty_6to12months < 0
Napr.: Dátové typy vrátených hodnôt by mali byť rovnaké. V nižšie uvedenom príklade je jednému argumentu priradená číselná hodnota, čo vedie k chybe.
SQL> vyberte sal, prípad, keď sal< 2000 then "category 1"
2 keď sal< 3000 then 0
3 keď sal< 4000 then "category 3"
4 inak "kategória 4"
5 koniec
6 z emp;
keď sal< 3000 then 0
*
CHYBA v riadku 2:
ORA-00932: nekonzistentné dátové typy
- s výrazom CASE, ktorý vám umožňuje zahrnúť podmienené výrazy do dotazu;
- s agregačnými funkciami, ktoré vám umožňujú získať rôzne druhy súčtov (agregovaných hodnôt) vypočítaných na základe podrobných údajov získaných operátorom „SELECT … WHERE …“;
- s klauzulou GROUP BY, ktorá v kombinácii s agregačnými funkciami umožňuje získať súčty pre podrobné údaje v kontexte skupín;
- s klauzulou HAVING, ktorá umožňuje filtrovať podľa zoskupených údajov.
CASE výraz - podmienený príkaz SQL
Tento operátor vám umožňuje skontrolovať podmienky a vrátiť v závislosti od splnenia konkrétnej podmienky jeden alebo druhý výsledok.Príkaz CASE má 2 formy:
Výrazy tu môžu pôsobiť aj ako hodnoty.
Vezmime si ako príklad prvú formu CASE:
VYBERTE ID,Meno,Plat, PRÍPAD, KEĎ Mzda>=3000 POTOM "SR>= 3000" KEĎ Mzda>=2000 POTOM "2000<= ЗП < 3000"
ELSE "ЗП < 2000"
END SalaryTypeWithELSE,
CASE
WHEN Salary>=3000 POTOM "ZP >= 3000" KEĎ Mzda>=2000 POTOM "2000"<= ЗП < 3000"
END SalaryTypeWithoutELSE
FROM Employees
KEĎ sa podmienky kontrolujú postupne, zhora nadol. Keď sa dosiahne prvá vyhovujúca podmienka, ďalšia kontrola sa preruší a vráti sa hodnota špecifikovaná za slovom THEN pridruženým k tomuto bloku WHEN.
Ak nie je splnená žiadna z podmienok KEDY, potom sa vráti hodnota zadaná za slovom ELSE (čo v tomto prípade znamená "INŠANÁVRAT...").
Ak nie je zadaný blok ELSE a nie je splnená žiadna z podmienok WHEN, vráti sa NULL.
V prvej aj druhej forme prichádza blok ELSE na úplný koniec konštruktu CASE, t.j. po všetkých podmienkach KEDY.
Vezmime si ako príklad druhú formu CASE:
Predpokladajme, že na nový rok sa rozhodli odmeniť všetkých zamestnancov a požiadali o výpočet výšky odmien podľa nasledujúcej schémy:
- Dajte zamestnancom IT oddelenia 15 % mzdy;
- Zamestnanci účtovného oddelenia 10 % zo mzdy;
- Všetci ostatní dostanú 5% z platu.
Na túto úlohu používame dotaz s výrazom CASE:
SELECT ID,Meno,Plat,DepartmentID, -- pre prehľadnosť zobrazte percentá ako reťazec CASE DepartmentID -- hodnota, ktorá sa má skontrolovať, KEĎ 2 POTOM "10 %" -- 10 % z RFP dajte účtovníkom, KEĎ 3 POTOM "15 % " -- 15 % z RFP dajte IT personálu ELSE "5 %" -- všetci ostatní dostanú 5 % KONIEC NewYearBonusPercent, -- vytvorte výraz pomocou CASE, aby ste videli bonusovú čiastku Plat/100* PRÍPAD ID oddelenia KEĎ 2 POTOM 10 -- vydanie 10 % účtovníkov RFP, KEĎ 3, POTOM 15 – 15 % z platu pre IT zamestnancov OSTATNÉ 5 – všetci ostatní 5 % každý KONIEC BonusAmount OD Zamestnancov
Toto je sekvenčná kontrola hodnoty DepartmentID s hodnotami WHEN. Po dosiahnutí prvej rovnosti DepartmentID s hodnotou WHEN sa kontrola preruší a vráti sa hodnota špecifikovaná za slovom THEN pridruženým k tomuto bloku WHEN.
Preto sa hodnota bloku ELSE vráti, ak DepartmentID nezodpovedá žiadnej hodnote WHEN.
Ak neexistuje blok ELSE, vráti sa NULL, ak DepartmentID nezodpovedá žiadnej hodnote WHEN.
Druhá forma CASE sa dá ľahko reprezentovať pomocou prvej formy:
VYBERTE ID, Meno, Plat, ID oddelenia, PRÍPAD, KEĎ DepartmentID=2 POTOM "10 %" -- 10 % RFP na vystavenie účtovníkom, KEĎ DepartmentID=3 POTOM "15 %" -- 15 % RFP na vystavenie IT pracovníkom INAK "5 % " -- všetci ostatní dostanú 5 % KONIEC NewYearBonusPercent, -- vytvorte výraz pomocou CASE, aby ste videli výšku bonusu Plat/100* PRÍPAD, KEĎ DepartmentID=2 POTOM 10 -- dajte 10 % z platu účtovníkom, KEĎ ID oddelenia=3 POTOM 15 -- 15 % z platu dostane ALŠICH 5 IT pracovníkov -- všetkým ostatným 5 % každý KONIEC BonusAmount OD zamestnancov
Takže druhá forma je len zjednodušený zápis, keď potrebujeme urobiť porovnanie rovnosti rovnakej testovacej hodnoty s každou hodnotou/výrazom WHEN.
Poznámka. Prvá a druhá forma CASE sú súčasťou jazykového štandardu SQL, takže s najväčšou pravdepodobnosťou by mali byť použiteľné v mnohých DBMS.
S verziou MS SQL 2012 sa objavila zjednodušená notácia pre IIF. Môže sa použiť na zjednodušenie konštrukcie CASE, keď sa vrátia iba 2 hodnoty. Konštrukcia IIF vyzerá takto:
IIF(podmienka, pravdivá_hodnota, nepravdivá_hodnota)
Tie. toto je v podstate obal pre nasledujúcu konštrukciu CASE:
CASE WHEN podmienka THEN true_value ELSE false_value END
Pozrime sa na príklad:
SELECT ID,Meno,Plat, IIF(Plat>=2500,"Plat >= 2500","Plat< 2500") DemoIIF, CASE WHEN Salary>=2500 POTOM "WR >= 2500" ELSE "WR< 2500" END DemoCASE FROM Employees
Konštrukty CASE, IIF môžu byť vnorené do seba. Zvážte abstraktný príklad:
VYBERTE ID, Meno, Plat, CASE WHEN DepartmentID IN(1,2) THEN "A" WHEN DepartmentID=3 THEN CASE PositionID -- vnorené CASE WHEN 3 THEN "B-1" WHEN 4 THEN "B-2" END ELSE " C" END Demo1, IIF(DepartmentID IN(1,2),"A", IIF(DepartmentID=3,CASE PositionID WHEN 3 THEN "B-1" WHEN 4 THEN "B-2" END,"C")) Demo2 OD zamestnancov
Keďže konštrukcie CASE a IIF sú výrazy, ktoré vracajú výsledok, môžeme ich použiť nielen v bloku SELECT, ale aj v iných blokoch, ktoré umožňujú použitie výrazov, napríklad v blokoch WHERE alebo ORDER BY.
Povedzme napríklad, že sme dostali za úlohu vytvoriť zoznam na vystavenie RFP takto:
- V prvom rade by RFP mali dostávať zamestnanci, ktorých plat je nižší ako 2500
- Tí zamestnanci, ktorí majú plat väčší alebo rovný 2500, dostávajú plat až na druhom mieste
- V rámci týchto dvoch skupín je potrebné zoradiť riadky podľa priezviska (pole Názov)
Pokúsme sa tento problém vyriešiť pridaním výrazu CASE do bloku ORDER BY:
VYBERTE ID,Meno,Plat OD zamestnancov OBJEDNAJTE PODĽA PRÍPADU, KEĎ Mzda>=2500 POTOM 1 INAK 0 KONIEC, -- vydajte PO najprv tým, ktorí ho majú pod 2500 Meno -- ďalej zoraďte zoznam v poradí podľa celého mena
Ako vidíte, Ivanov a Sidorov odídu z práce poslední.
A abstraktný príklad použitia CASE v klauzule WHERE:
VYBERTE ID,Meno,Plat OD zamestnancov V PRÍPADE, KEĎ Mzda>=2500 TAK 1 ELSE 0 KONIEC=1 -- všetky záznamy s výrazom rovným 1
Posledné 2 príklady môžete skúsiť prerobiť sami pomocou funkcie IIF.
A nakoniec si ešte raz pripomeňme hodnoty NULL:
VYBERTE ID, Meno, Plat, ID oddelenia, PRÍPAD, KEĎ DepartmentID=2 POTOM "10 %" -- 10 % RFP na vystavenie účtovníkom, KEĎ DepartmentID=3 POTOM "15 %" -- 15 % RFP na vydanie IT zamestnanci KEĎ JE DepartmentID NULL POTOM "-" -- nedávame bonusy nezávislým pracovníkom (používame IS NULL) ELSE "5 %" -- všetci ostatní dostanú 5 % KONIEC NewYearBonusPercent1, -- inak nemôžete skontrolovať NULL , pamätajte, čo bolo povedané o NULL v druhej časti CASE DepartmentID - - hodnota, ktorá sa má skontrolovať KEĎ 2 TAK "10 %" KEĎ 3 TAK "15 %" KEĎ NULL POTOM "-" -- !!! v tomto prípade nie je vhodné použiť druhú formu CASE INAK "5 %" KONIEC NewYearBonusPercent2 OD Zamestnancov
Samozrejme, mohlo to byť napísané takto:
VYBERTE ID,Meno,Plat,ID oddelenia, CASE ISNULL(DepartmentID,-1) -- použite náhradu za NULL s -1, KEĎ 2, POTOM "10 %", KEĎ 3 POTOM "15 %", KEĎ -1 POTOM "-" -- ak sme si istí, že neexistuje a ani nebude oddelenie s ID rovným (-1) ELSE "5%" KONIEC NewYearBonusPercent3 OD Zamestnancov
Vo všeobecnosti let fantázie v tomto prípade nie je obmedzený.
Pozrime sa napríklad, ako môžeme modelovať funkciu ISNULL pomocou CASE a IIF:
SELECT ID,Meno,Priezvisko, ISNULL(Priezvisko,"Nešpecifikované") DemoISNULL, CASE WHEN Priezvisko JE NULL TAK "Nešpecifikované" ELSE Priezvisko END DemoCASE, IIF(Priezvisko JE NULL,"Nešpecifikované",Priezvisko) DemoIIF OD zamestnancov
Konštrukcia CASE je veľmi výkonná funkcia jazyka SQL, ktorá vám umožňuje zaviesť dodatočnú logiku na výpočet hodnôt sady výsledkov. V tejto časti sa nám bude držanie CASE-konštruktu stále hodiť, preto sa mu v tejto časti venujeme predovšetkým.
Súhrnné funkcie
Tu sa budeme venovať iba základným a najčastejšie používaným agregačným funkciám:| názov | Popis |
|---|---|
| COUNT(*) | Vráti počet riadkov získaných príkazom "SELECT ... WHERE ...". Ak chýba WHERE, počet všetkých záznamov v tabuľke. |
| COUNT(stĺpec/výraz) | Vráti počet hodnôt (nie null) v zadanom stĺpci/výraze |
| POČET (ODLIŠNÝ stĺpec/výraz) | Vráti počet jedinečných nenulových hodnôt v zadanom stĺpci/výraze |
| SUM(stĺpec/výraz) | Vráti súčet hodnôt stĺpca/výrazu |
| AVG(stĺpec/výraz) | Vráti priemernú hodnotu nad hodnotami stĺpca/výrazu. Hodnoty NULL sa nepočítajú. |
| MIN (stĺpec/výraz) | Vráti minimálnu hodnotu nad hodnotami stĺpca/výrazu |
| MAX(stĺpec/výraz) | Vráti maximálnu hodnotu nad hodnotami stĺpca/výrazu |
Agregačné funkcie nám umožňujú vypočítať celkovú hodnotu pre množinu riadkov získanú pomocou príkazu SELECT.
Pozrime sa na každú funkciu s príkladom:
SELECT COUNT(*) [Celkový počet zamestnancov], COUNT(DISTINCT DepartmentID) [Počet jedinečných oddelení], COUNT(DISTINCT PositionID) [Počet jedinečných pozícií], COUNT(BonusPercent) [Počet zamestnancov s bonusom %] , MAX (Bonus Percent) [Maximálne percento bonusu], MIN (Bonus Percent) [Minimálne percento bonusu], SUM (Plat/100*Bonus Percent) [Súčet všetkých bonusov], AVG (Plat/100*Bonus Percent) [Priemerná výška bonusu], AVG ( Plat) [Priemerný plat] OD zamestnancov
Kvôli prehľadnosti som sa rozhodol urobiť výnimku a na nastavenie aliasov stĺpcov som použil syntax […].
Poďme si rozobrať, ako každá vrátená hodnota dopadla, a po prvé si pripomeňme konštrukcie základnej syntaxe príkazu SELECT.
Po prvé, pretože Ak sme v dotaze nešpecifikovali podmienky KDE, potom sa pre podrobné údaje, ktoré sa získajú dotazom, vypočítajú súčty:
VYBERTE * OD zamestnancov
Tie. pre všetky riadky v tabuľke Zamestnanci.
Kvôli prehľadnosti vyberáme iba polia a výrazy, ktoré sa používajú v agregačných funkciách:
SELECT DepartmentID, PositionID, Bonus Percent, Plat/100*Bonus Percent , Plat OD zamestnancov
| ID oddelenia | PositionID | Bonus Percent | Plat/100*Bonus Percent | Plat |
|---|---|---|---|---|
| 1 | 2 | 50 | 2500 | 5000 |
| 3 | 3 | 15 | 225 | 1500 |
| 2 | 1 | NULOVÝ | NULOVÝ | 2500 |
| 3 | 4 | 30 | 600 | 2000 |
| 3 | 3 | NULOVÝ | NULOVÝ | 1500 |
| NULOVÝ | NULOVÝ | NULOVÝ | NULOVÝ | 2000 |
Toto sú počiatočné údaje (podrobné riadky), ktoré sa použijú na výpočet súčtu agregovaného dopytu.
Teraz rozoberme každú agregovanú hodnotu:
| COUNT(*)– pretože Ak sme v dotaze nešpecifikovali podmienky filtrovania v bloku WHERE, tak COUNT(*) nám dalo celkový počet záznamov v tabuľke, t.j. je počet riadkov, ktoré dotaz vráti: VYBERTE * OD zamestnancov |
| COUNT (DISTINCT ID oddelenia)- vrátil nám hodnotu 3, t.j. toto číslo zodpovedá počtu jedinečných hodnôt oddelenia špecifikovaných v stĺpci DepartmentID, s výnimkou hodnôt NULL. Poďme si prejsť hodnoty stĺpca DepartmentID a vyfarbiť rovnaké hodnoty rovnakou farbou (neváhajte, všetky metódy sú dobré na učenie): Zahodíme NULL, po čom dostaneme 3 jedinečné hodnoty (1, 2 a 3). Tie. hodnota získaná pomocou COUNT(DISTINCT DepartmentID) v rozšírenej forme môže byť reprezentovaná nasledujúcou vzorkou: VYBERTE DISTINCT DepartmentID -- 2. vezmite iba jedinečné hodnoty OD zamestnancov, KDE ID oddelenia NIE JE NULL -- 1. zahoďte hodnoty NULL
|
| POČET (DISTINCT ID pozície)- to isté, čo bolo povedané o COUNT(DISTINCT DepartmentID), len pre pole PositionID. Pozeráme sa na hodnoty stĺpca PositionID a nešetríme farbami:
|
| POČET (bonusové percentá)– vráti počet riadkov, ktoré majú hodnotu BonusPercent, t.j. započítava sa počet záznamov s BonusPercent NIE JE NULL. Tu to bude pre nás jednoduchšie, pretože. nemusíte počítať jedinečné hodnoty, stačí zahodiť záznamy s hodnotami NULL. Zoberieme hodnoty stĺpca BonusPercent a prečiarkneme všetky hodnoty NULL: Zostávajú 3 hodnoty. Tie. V rozšírenej forme môže byť vzorka reprezentovaná takto: SELECT BonusPercent -- 2. prevziať všetky hodnoty OD zamestnancov, KDE Bonus Percent NIE JE NULL -- 1. zahodiť hodnoty NULL
Pretože Keďže sme nepoužili slová DISTINCT, potom sa započítajú opakované BonusPercents, ak existujú, pričom sa budú ignorovať BonusPercents rovné NULL. Napríklad porovnajme výsledok s a bez DISTINCT. Pre lepšiu prehľadnosť použijeme hodnoty poľa DepartmentID: SELECT COUNT(*), -- 6 COUNT (DISTINCT DepartmentID), -- 3 COUNT (DepartmentID) -- 5 FROM Zamestnanci
|
| MAX (bonusové percento)- vráti maximálnu hodnotu BonusPercent, opäť bez hodnôt NULL. Berieme hodnoty stĺpca BonusPercent a hľadáme medzi nimi maximálnu hodnotu, nevenujeme pozornosť hodnotám NULL:
VYBERTE 1 NAJLEPŠIE Percento bonusu OD zamestnancov, KDE Percento bonusu NIE JE NULL OBJEDNÁVKA PODĽA BonusPercent DESC -- zoradiť zostupne |
| MIN (bonusové percentá)– vráti minimálnu hodnotu BonusPercent, pričom opäť ignoruje hodnoty NULL. Rovnako ako v prípade MAX, hľadá sa iba minimálna hodnota, pričom sa ignoruje NULL:
Tie. dostaneme nasledujúcu hodnotu: VYBERTE 1 NAJLEPŠIE Percento bonusu OD zamestnancov, KDE Percento bonusu NIE JE NULL OBJEDNÁVKA PODĽA BonusPercent - zoradiť vzostupne Vizuálne znázornenie MIN (bonusových percent) a MAX (bonusových percent):
|
| SUM(Plat/100*bonusové percentá)– vráti súčet všetkých hodnôt, ktoré nie sú NULL. Analýza hodnôt výrazu (Plat/100*Bonus Percent):
Tie. sčítavajú sa nasledujúce hodnoty: SELECT Plat/100*Bonus Percent OD zamestnancov, KDE Mzda/100*Bonus Percent NIE JE NULL
|
| AVG (plat/100*bonusové percentá)- vráti priemer hodnôt. Neberú sa do úvahy výrazy NULL, t.j. toto zodpovedá druhému výrazu: SELECT AVG(Plat/100*Bonus Percent), -- 1108,333333333333 SUM(Plat/100*Bonus Percent)/POČET (Plat/100*BonusPercent), -- 1108,333333333333 SUM(6 centov*6SUM) 60 Sk*6SUM OD zamestnancov
Tie. opäť hodnoty NULL sa nezapočítavajú do počtu. Ak potrebujete vypočítať priemer všetkých zamestnancov, ako v treťom výraze, ktorý dáva 554,166666666667, potom použite predbežný prevod hodnôt NULL na nulu: SELECT AVG(ISNULL(Plat/100*Bonus Percent,0)), -- 554,166666666667 SUM(Plat/100*Bonus Percent)/POČET (*) -- 554,166666666667 OD zamestnancov |
| AVG (plat)– v skutočnosti je tu všetko rovnaké ako v predchádzajúcom prípade, t.j. ak je mzda zamestnanca NULL, potom sa nebude započítavať. Ak chcete vziať do úvahy všetkých zamestnancov, vykonajte predbežnú NULL transformáciu hodnôt AVG(ISNULL(Plat,0)) |
Zhrňme si nejaké výsledky:
- COUNT(*) – slúži na počítanie celkového počtu riadkov, ktoré sú prijaté príkazom „SELECT ... WHERE ...“
- vo všetkých ostatných agregovaných funkciách uvedených vyššie sa pri výpočte súčtu neberú do úvahy hodnoty NULL
- ak potrebujeme vziať do úvahy všetky riadky, je to relevantnejšie pre funkciu AVG, potom je najprv potrebné spracovať hodnoty NULL, napríklad ako je uvedené vyššie "AVG (ISNULL (Mzda, 0))"
Preto pri zadávaní dodatočnej podmienky s agregačnými funkciami v bloku WHERE sa vypočítajú len súčty pre riadky, ktoré spĺňajú podmienku. Tie. súhrnné hodnoty sa vypočítajú pre konečný súbor, ktorý sa získa pomocou konštruktu SELECT. Urobme napríklad to isté, ale iba v kontexte oddelenia IT:
SELECT COUNT(*) [Celkový počet zamestnancov], COUNT(DISTINCT DepartmentID) [Počet jedinečných oddelení], COUNT(DISTINCT PositionID) [Počet jedinečných pozícií], COUNT(BonusPercent) [Počet zamestnancov s bonusom %] , MAX (Bonus Percent) [Maximálne percento bonusu], MIN (Bonus Percent) [Minimálne percento bonusu], SUM (Plat/100*Bonus Percent) [Súčet všetkých bonusov], AVG (Plat/100*Bonus Percent) [Priemerná výška bonusu], AVG ( Plat) [Priemerný plat] OD zamestnancov, KDE DepartmentID=3 -- berte do úvahy iba IT oddelenie
Odporúčam vám, aby ste lepšie porozumeli práci agregovaných funkcií, nezávisle analyzovať každú prijatú hodnotu. Tu vykonávame výpočty podľa podrobných údajov prijatých na žiadosť:
SELECT DepartmentID, PositionID, BonusPercent, Plat/100*BonusPercent , Plat OD zamestnancov WHERE DepartmentID=3 -- zvážte iba IT oddelenie
| ID oddelenia | PositionID | Bonus Percent | Plat/100*Bonus Percent | Plat |
|---|---|---|---|---|
| 3 | 3 | 15 | 225 | 1500 |
| 3 | 4 | 30 | 600 | 2000 |
| 3 | 3 | NULOVÝ | NULOVÝ | 1500 |
Pohni sa. Ak agregačná funkcia vráti NULL (napríklad všetci zamestnanci nemajú hodnotu Mzda), alebo vo výbere nie sú žiadne záznamy a v zostave pre tento prípad musíme zobraziť 0, potom funkcia ISNULL môže zabaliť súhrnný výraz:
SELECT SUM(Plat), AVG(Plat), -- spracovať súčet pomocou ISNULL ISNULL(SUM(Plat),0), ISNULL(AVG(Plat),0) FROM Zamestnanci WHERE DepartmentID=10 -- tu zadané neexistujúce oddelenie takže dotaz nevracia žiadne záznamy
| (Bez názvu stĺpca) | (Bez názvu stĺpca) | (Bez názvu stĺpca) | (Bez názvu stĺpca) |
|---|---|---|---|
| NULOVÝ | NULOVÝ | 0 | 0 |
Myslím si, že je veľmi dôležité pochopiť účel každej agregovanej funkcie a spôsob výpočtu, pretože v SQL je to hlavný nástroj používaný na výpočet súčtov.
V tomto prípade sme zvažovali, ako sa každá agregačná funkcia správa nezávisle, t.j. aplikovalo sa na hodnoty celej sady záznamov vrátené príkazom SELECT. Ďalej sa pozrieme na to, ako sa tieto isté funkcie používajú na výpočet súčtu skupín pomocou klauzuly GROUP BY.
GROUP BY - zoskupenie údajov
Predtým sme už vypočítali súčty pre konkrétne oddelenie, asi takto:SELECT COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci WHERE DepartmentID=3 -- údaje len pre IT oddelenie
Teraz si predstavte, že sme boli požiadaní, aby sme získali rovnaké čísla v kontexte každého oddelenia. Samozrejme si môžeme vyhrnúť rukávy a zadať rovnakú požiadavku pre každé oddelenie. Takže, povedané a hotovo, napíšeme 4 otázky:
SELECT "Administration" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Palary) PlatAmount FROM Zamestnanci WHERE DepartmentID=1 -- Údaje pre správu SELECT "Účtovníctvo" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT( * ) Počet zamestnancov, SÚČET (Mzd) Suma platu OD zamestnancov WHERE DepartmentID=2 -- Účtovné údaje VYBERTE "IT" Info, COUNT(DISTINCT PositionID) Počet pozícií, COUNT(*) Počet zamestnancov, SUM(Mzda) Suma platu OD zamestnancov WHERE DepartmentID=3 -- Údaje IT oddelenia VYBERTE "Iné" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci, KDE JE ID oddelenia NULL -- a nezabudnite na údaje na voľnej nohe
V dôsledku toho získame 4 súbory údajov:
Upozorňujeme, že môžeme použiť polia špecifikované ako konštanty - "Administrácia", "Účtovníctvo", ...
Vo všeobecnosti sme získali všetky čísla, na ktoré sme boli požiadaní, skombinovali všetko v Exceli a dali to riaditeľovi.
Riaditeľovi sa správa páčila a hovorí: "A pridajte ďalšiu kolónku s údajmi o priemernej mzde." A ako vždy to treba urobiť veľmi urgentne.
Hmm, čo robiť?! Navyše si predstavte, že nemáme 3 oddelenia, ale 15.
Presne na to slúži konštrukcia GROUP BY pre takéto prípady:
SELECT DepartmentID, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount, AVG(Malary) SalaryAvg -- plus splniť želanie riaditeľa FROM Zamestnanci GROUP BY DepartmentID
| ID oddelenia | Počet pozícií | EmplCount | Výška platu | Priem. plat |
|---|---|---|---|---|
| NULOVÝ | 0 | 1 | 2000 | 2000 |
| 1 | 1 | 1 | 5000 | 5000 |
| 2 | 1 | 1 | 2500 | 2500 |
| 3 | 2 | 3 | 5000 | 1666.66666666667 |
Dostali sme všetky rovnaké údaje, ale teraz používame iba jednu požiadavku!
Zatiaľ nevenujte pozornosť tomu, že naše oddelenia sú zobrazené vo forme čísel, potom sa naučíme, ako všetko krásne zobraziť.
V klauzule GROUP BY môžete zadať niekoľko polí „GROUP BY pole1, pole2, ..., poleN“, v tomto prípade sa zoskupenie uskutoční v skupinách, ktoré tvoria hodnoty týchto polí „pole1, pole2, .. ., pole N".
Napríklad zoskupme údaje v kontexte oddelení a pozícií:
SELECT DepartmentID,PositionID, COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci GROUP BY DepartmentID,PositionID
Potom sa vykoná beh každej kombinácie a vypočítajú sa súhrnné funkcie:
SELECT COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci WHERE DepartmentID JE NULL A PositionID JE NULL SELECT COUNT(*) EmplCount, SUM(Malary) PlatAmount FROM Zamestnanci WHERE DepartmentID=1 AND PositionID=2 -- ... SELECT COUNT(*) EmplCount, SUM(Plat) MzdaSuma OD zamestnancov WHERE DepartmentID=3 AND PositionID=4
A potom sa všetky tieto výsledky skombinujú a dajú nám ako jeden súbor:
Z toho hlavného stojí za zmienku, že v prípade zoskupenia (GROUP BY) v zozname stĺpcov v bloku SELECT:
- Môžeme použiť iba stĺpce uvedené v bloku GROUP BY
- Môžete použiť výrazy s poľami z bloku GROUP BY
- Môžete použiť konštanty, pretože nemajú vplyv na výsledok zoskupenia
- Všetky ostatné polia (neuvedené v bloku GROUP BY) možno použiť iba s agregačnými funkciami (COUNT, SUM, MIN, MAX, ...)
- V zozname stĺpcov SELECT nie je potrebné uvádzať všetky stĺpce z bloku GROUP BY
A ukážka toho všetkého:
SELECT "Konštanta reťazca" Const1, -- konštanta ako reťazec 1 Const2, -- konštanta ako číslo -- výraz využívajúci polia zahrnuté v zoskupení CONCAT("Department № ",DepartmentID) ConstAndGroupField, CONCAT("Department № ", DepartmentID ,", Position № ",PositionID) ConstAndGroupFields, DepartmentID, -- pole zo zoznamu polí participujúcich na zoskupení -- PositionID, -- pole participujúce na zoskupení, tu nie je potrebné duplikovať COUNT(*) EmplCount , -- počet riadkov v skupine -- ostatné polia je možné použiť iba s agregačnými funkciami: COUNT, SUM, MIN, MAX, … SUM(Mzda) Suma mzdy, MIN(ID) MINID OD zamestnancov GROUP BY DepartmentID,PositionID -- zoskupenie podľa polí DepartmentID, PositionID
Za zmienku tiež stojí, že zoskupovanie možno vykonávať nielen podľa polí, ale aj podľa výrazov. Napríklad zoskupme údaje podľa zamestnancov podľa roku narodenia:
SELECT CONCAT("Rok narodenia - ",YEAR(Narodeniny)) YearOfBirthday, COUNT(*) EmplCount FROM Zamestnanci GROUP PODĽA ROKU (Narodeniny)
Pozrime sa na príklad so zložitejším výrazom. Napríklad dostaneme odstupňovanie zamestnancov podľa roku narodenia:
VYBERTE PRÍPAD KEDY ROK(Narodeniny)>=2000 THEN "od 2000" KEDY ROK (Narodeniny)>=1990 THEN "1999-1990" KEDY ROK(Narodeniny)>=1980 THEN "1989-1980" KEDY NARODENIE ROK>( 1970 POTOM "1979-1970" KEĎ NARODENIA NIE SÚ NULL POTOM "pred rokom 1970" ELSE "nešpecifikované" KONIEC Názov rozsahu, POČET(*) Počet zamestnancov SKUPINA PODĽA PRÍPADU KEĎ ROK(Narodeniny)>=2000 TENTO ROK"od 2000 ROK"OD 2000 (Narodeniny)>=1990 POTOM "1999-1990" KEĎ ROK (Narodeniny)>=1980 POTOM "1989-1980" KEĎ ROK (Narodeniny)>=1970 POTOM "1979-1970", KEĎ NARODENIA NIE SÚ 7 NULL ELSE "nešpecifikované" KONIEC
| RangeName | EmplCount |
|---|---|
| 1979-1970 | 1 |
| 1989-1980 | 2 |
| nešpecifikované | 2 |
| pred rokom 1970 | 1 |
Tie. v tomto prípade sa zoskupenie vykoná podľa výrazu CASE, ktorý bol predtým vypočítaný pre každého zamestnanca:
VYBERTE ID, PRÍPAD KEDY ROK(Narodeniny)>=2000 THEN "od 2000" KEDY ROK(Narodeniny)>=1990 THEN "1999-1990" KEDY ROK(Narodeniny)>=1980 POTOM "1989-1980" KEDY ROK >=1970 POTOM "1979-1970" KEĎ NARODENIA NIE SÚ NULL POTOM "pred rokom 1970" INAK "nešpecifikované" KONIEC OD zamestnancov
A samozrejme môžete kombinovať výrazy s poľami v bloku GROUP BY:
SELECT DepartmentID, CONCAT("Rok narodenia - ",YEAR(Birthday)) YearOfBirthday, COUNT(*) EmplCount FROM Zamestnanci GROUP BY YEAR(Birthday),DepartmentID -- objednávka sa nemusí zhodovať s objednávkou, v ktorej sú použité v SELECT ORDER BY Blok DepartmentID, YearOfBirthday -- konečne môžeme zoradiť výsledok
Vráťme sa k našej pôvodnej úlohe. Ako už vieme, riaditeľovi sa správa veľmi páčila a požiadal nás, aby sme ju robili týždenne, aby mohol sledovať zmeny vo firme. Aby sme zakaždým v Exceli neprerušili digitálnu hodnotu oddelenia s jeho názvom, využijeme znalosti, ktoré už máme, a vylepšíme náš dotaz:
SELECT CASE DepartmentID WHEN 1 THEN "Administration" WHEN 2 THEN "Accounting" WHEN 3 THEN "IT" ELSE "Iné" END Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount, AVG(Malary ) PlatAvg -- plus splníme želanie riaditeľa OD zamestnancov SKUPINA PODĽA ID oddelenia OBJEDNAŤ PODĽA Info -- pre väčšie pohodlie pridajte triedenie podľa stĺpca Info
Aj keď to zvonku môže vyzerať strašidelne, stále je to lepšie ako pôvodne. Nevýhodou je, že ak sa spustí nové oddelenie a jeho zamestnanci, budeme musieť pridať výraz CASE, aby zamestnanci nového oddelenia nespadali do skupiny „Iné“.
Ale nič, časom sa naučíme robiť všetko krásne, aby náš výber nezávisel od objavenia sa nových údajov v databáze, ale bol dynamický. Trochu preskočím, aby som ukázal, aké druhy dopytov sa snažíme dostať do písania:
SELECT ISNULL(dep.Name,"Others") DepName, COUNT(DISTINCT emp.PositionID) PositionCount, COUNT(*) EmplCount, SUM(emp.Salary) PlatAmount, AVG(emp.Mold) PlatAvg -- plus splniť želanie riaditeľa OD Zamestnanci zam. LEFT PRIPOJTE SA K oddeleniam dep ON emp.DepartmentID=dep.ID GROUP BY emp.DepartmentID,dep.Name ORDER BY DepName
Vo všeobecnosti sa nebojte - každý začal s jednoduchým. Zatiaľ vám stačí pochopiť podstatu konštrukcie GROUP BY.
Nakoniec sa pozrime, ako môžete zostaviť súhrnné zostavy pomocou GROUP BY.
Ukážme si napríklad kontingenčnú tabuľku rozdelenú podľa oddelení, aby sa vypočítali celkové mzdy zamestnancov podľa pozície:
SELECT DepartmentID, SUM(CASE WHEN PositionID=1 THEN Plat END) [Účtovníci], SUM(CASE WHEN PositionID=2 THEN Plat END) [Riaditelia], SUM(CASE WHEN PositionID=3 THEN Plat END) [Programátori], SUM( PRÍPAD, KEĎ ID pozície=4 POTOM KONIEC platu) [Senior programátori], SÚČET (plat) [Celkom oddelenia] OD zamestnancov SKUPINA PODĽA ID oddelenia
Tie. v rámci agregovaných funkcií môžeme voľne používať akékoľvek výrazy.
Samozrejme, môžete ho prepísať aj pomocou IIF:
VYBERTE ID oddelenia, SUM(IIF(ID pozície=1,Plat,NULL)) [účtovník], SUM(IIF(ID pozície=2,Plat,NULL)) [Riaditeľ], SUM(IIF(ID pozície=3,Plat,NULL)) [Programátori], SUM(IIF(ID pozície=4,Plat,NULL)) [Senior programátori], SUM(Plat) [Celkom oddelenia] OD zamestnancov SKUPINA PODĽA ID oddelenia
Ale v prípade IIF budeme musieť explicitne špecifikovať NULL, ktorá sa vráti, ak podmienka nie je splnená.
V podobných prípadoch radšej používam CASE bez bloku ELSE, ako znova písať NULL. Ale to je samozrejme vec vkusu, ktorá nie je spochybnená.
A pamätajme, že hodnoty NULL sa počas agregácie nezohľadňujú v agregovaných funkciách.
Ak chcete konsolidovať, vykonajte nezávislú analýzu prijatých údajov na podrobnú požiadavku:
SELECT DepartmentID, CASE WHEN PositionID=1 THEN Plat END [účtovník], CASE WHEN PositionID=2 THEN Plat END [Riaditelia], CASE WHEN PositionID=3 THEN Plat END [Programátori], CASE WHEN PositionID=4 THEN Plat END [Senior programátori ] ], Plat [Celkom podľa oddelení] OD zamestnancov
| ID oddelenia | účtovník | riaditeľov | programátorov | senior programátorov | Celkom podľa oddelení |
|---|---|---|---|---|---|
| 1 | NULOVÝ | 5000 | NULOVÝ | NULOVÝ | 5000 |
| 3 | NULOVÝ | NULOVÝ | 1500 | NULOVÝ | 1500 |
| 2 | 2500 | NULOVÝ | NULOVÝ | NULOVÝ | 2500 |
| 3 | NULOVÝ | NULOVÝ | NULOVÝ | 2000 | 2000 |
| 3 | NULOVÝ | NULOVÝ | 1500 | NULOVÝ | 1500 |
| NULOVÝ | NULOVÝ | NULOVÝ | NULOVÝ | NULOVÝ | 2000 |
A tiež si pamätajme, že ak namiesto NULL chceme vidieť nuly, tak vieme spracovať hodnotu vrátenú agregovanou funkciou. Napríklad:
SELECT DepartmentID, ISNULL(SUM(IIF(PozíciaID=1,Plat,NULL)),0) [Účtovník], ISNULL(SUM(IIF(PositionID=2,Plat,NULL)),0) [Riaditeľ], ISNULL(SUM (IIF(ID pozície=3,Plat,NULL)),0) [Programátori], ISNULL(SUM(IIF(ID pozície=4,Plat,NULL)),0) [Senior programátori], ISNULL(SUM(Plat),0 ) [Celkom podľa oddelenia] OD zamestnancov SKUPINA PODĽA ID oddelenia
Teraz môžete na precvičenie:
- názvy výstupných oddelení namiesto ich ID, napríklad pridaním príkazu CASE, ktorý spracováva ID oddelenia v bloku SELECT
- pridajte triedenie podľa názvu oddelenia pomocou ORDER BY
GROUP BY je v spojení s agregačnými funkciami jedným z hlavných nástrojov používaných na získanie súhrnných údajov z databázy, pretože údaje sa zvyčajne používajú v tejto forme, pretože. zvyčajne sme povinní poskytovať súhrnné správy, nie podrobné údaje (hárky). A samozrejme, všetko sa to točí okolo poznania základného dizajnu, pretože. predtým, ako niečo zhrniete (agregujete), musíte to najskôr správne vybrať pomocou „SELECT ... WHERE ...“.
Prax tu hrá dôležitú úlohu, takže ak si stanovíte cieľ porozumieť jazyku SQL, nie študovať, ale rozumieť – cvičiť, cvičiť a cvičiť, triediť cez tie najrôznejšie možnosti, aké vás napadnú.
V počiatočných fázach, ak si nie ste istí správnosťou získaných agregovaných údajov, vykonajte podrobný výber vrátane všetkých hodnôt, ktoré sa agregujú. A podľa týchto podrobných údajov ručne skontrolujte správnosť výpočtov. V tomto prípade môže byť použitie Excelu veľmi užitočné.
Povedzme, že ste dosiahli tento bod
Povedzme, že ste účtovník Sidorov S.S., ktorý sa rozhodol naučiť písať SELECT dopyty.Povedzme, že ste si tento návod už prečítali až sem a už s istotou používate všetky vyššie uvedené základné konštrukcie, t.j. môžeš:
- Vyberte podrobné údaje podľa klauzuly WHERE z jednej tabuľky
- Vedieť používať agregačné funkcie a zoskupovanie z jednej tabuľky
Áno, ale nebrali do úvahy, že aj tak nevieš zostaviť dotazy z viacerých tabuliek, ale len z jednej, t.j. nemôžete urobiť niečo také:
SELECT zam.*, -- vráťte všetky polia tabuľky Zamestnanci dep.Name DepartmentName, -- do týchto polí pridajte pole Názov z tabuľky Oddelenia poz.Name PozíciaName -- a pridajte pole Názov z tabuľky Pozície FROM Zamestnanci zam. LEFT JOIN Oddelenia dep ON emp.DepartmentID=dep.ID LEFT JOIN Pozícia ON emp.PositionID=poz.ID
Napriek tomu, že neviete, ako to urobiť, verte mi, urobili ste dobre a dosiahli ste už toľko.
Ako teda môžete využiť svoje súčasné znalosti a zároveň dosiahnuť ešte produktívnejšie výsledky?! Využime silu kolektívnej mysle – ideme za programátormi, ktorí pre vás pracujú, t.j. Andreevovi A.A., Petrovovi P.P. alebo Nikolaev N.N., a požiadajte jedného z nich, aby vám napísal pohľad (POHĽAD alebo len „Zobraziť“, takže vám dokonca, myslím, rýchlejšie porozumejú), ktorý vám okrem hlavných polí z tabuľky Zamestnanci vrátiť aj polia s „Názov oddelenia“ a „Názov pozície“, ktoré vám teraz tak chýbajú v týždennom prehľade, ktorý vám naložil Ivanov I.I.
Pretože všetko ste správne vysvetlili, potom IT špecialisti okamžite pochopili, čo od nich chcú a špeciálne pre vás vytvorili pohľad s názvom ViewEmployeesInfo.
Predstavujeme si, že nasledujúci príkaz nevidíte, pretože Ľudia z IT to robia:
CREATE VIEW ViewEmployeesInfo AS SELECT emp.*, -- vráti všetky polia tabuľky Zamestnanci dep.Name DepartmentName, -- do týchto polí pridajte pole Name z tabuľky Departments poz.Name PositionName -- a tiež pridajte pole Name z poľa Name Tabuľka pozícií OD Zamestnanci zam. LEFT JOIN Oddelenia dep ON emp.DepartmentID=dep.ID LEFT JOIN Pozícia ON emp.PositionID=poz.ID
Tie. pre vás celý tento, doteraz strašidelný a nezrozumiteľný text ostáva v zákulisí a ITčkári vám dajú len názov pohľadu ViewEmployeesInfo, ktorý vráti všetky vyššie uvedené údaje (teda to, čo ste od nich žiadali).
Teraz môžete s týmto zobrazením pracovať ako s bežnou tabuľkou:
SELECT * FROM ViewEmployeesInfo
Pretože teraz sú všetky údaje potrebné pre zostavu v jednej „tabuľke“ (a la zobrazenie), potom môžete jednoducho znova vykonať svoj týždenný prehľad:
SELECT DepartmentName, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Malary) PlatAmount, AVG(Malary) PlatAvg FROM ViewEmployeesInfo emp GROUP BY DepartmentID,DepartmentName ORDER BY DepartmentName
Teraz sú všetky názvy oddelení na mieste a dopyt sa stal dynamickým a zmení sa, keď pribudnú nové oddelenia a ich zamestnanci, t.j. teraz už nemusíte nič prerábať, ale stačí požiadavku vykonať raz týždenne a výsledok odovzdať riaditeľovi.
Tie. pre vás v tomto prípade, akoby sa nič nezmenilo, naďalej pracujete s jednou tabuľkou rovnakým spôsobom (len je to správnejšie povedať so zobrazením ViewEmployeesInfo), ktorá vráti všetky potrebné údaje. Vďaka pomoci IT ľudí zostali pre vás podrobnosti o získaní DepartmentName a PositionName v čiernej skrinke. Tie. pohľad vám vyzerá ako bežná tabuľka, predstavte si to ako rozšírenú verziu tabuľky Zamestnanci.
Vytvorte napríklad vyhlásenie, aby ste sa uistili, že všetko platí tak, ako som povedal (že celý výber pochádza z jedného pohľadu):
VYBERTE ID, Meno, Mzda OD ZobraziťInformácie o zamestnancoch, KDE Mzda NIE JE NULL A Mzda>0 OBJEDNAŤ PODĽA mena
Dúfam, že tejto žiadosti rozumiete.
Použitie pohľadov v niektorých prípadoch umožňuje výrazne rozšíriť hranice používateľov, ktorí vedia písať základné SELECT dotazy. V tomto prípade je pohľadom plochá tabuľka so všetkými údajmi, ktoré používateľ potrebuje (pre tých, ktorí rozumejú OLAP, to možno porovnať s približnou podobnosťou kocky OLAP s faktami a rozmermi).
Výstrižok z Wikipédie. Hoci SQL bol koncipovaný ako nástroj pre koncového používateľa, nakoniec sa stal natoľko zložitým, že sa stal nástrojom programátora.
Ako vidíte, milí používatelia, jazyk SQL bol pôvodne koncipovaný ako nástroj pre vás. Takže všetko je vo vašich rukách a túžbe, nenechajte sa ísť.
HAVING - použitie podmienky výberu na zoskupené údaje
V skutočnosti, ak rozumiete tomu, čo je zoskupovanie, potom na HAVING nie je nič zložité. HAVING - niečo podobné ako WHERE, iba ak sa podmienka WHERE aplikuje na podrobné dáta, potom sa podmienka HAVING aplikuje na už zoskupené dáta. Z tohto dôvodu môžeme v podmienkach bloku HAVING použiť buď výrazy s poľami zahrnutými v zoskupení, alebo výrazy uzavreté v agregačných funkciách.Zvážte príklad:
VYBERTE ID oddelenia, SÚČET (Plat) PlatSuma OD zamestnancov SKUPINA PODĽA ID oddelenia SÚČET (Mzda)>3000
| ID oddelenia | Výška platu |
|---|---|
| 1 | 5000 |
| 3 | 5000 |
Tie. tento dotaz nám vrátil zoskupené údaje len za tie oddelenia, ktorých celková mzda všetkých zamestnancov presahuje 3000, t.j. "SÚČET (Plat) > 3000".
Tie. tu najskôr dochádza k zoskupovaniu a vypočítavajú sa údaje pre všetky oddelenia:
VYBERTE ID oddelenia, SÚČET (Plat) Suma mzdy OD zamestnancov GROUP BY DepartmentID -- 1. získajte zoskupené údaje naprieč všetkými oddeleniami
A na tieto údaje sa už vzťahuje podmienka špecifikovaná v bloku HAVING:
SELECT DepartmentID, SUM(Mold) PlatAmount FROM Zamestnanci GROUP BY DepartmentID -- 1. získajte zoskupené údaje pre všetky oddelenia MAJÚCE SÚČET (plat)>3000 -- 2. podmienka pre filtrovanie zoskupených údajov
V podmienke HAVING môžete tiež vytvoriť zložité podmienky pomocou operátorov AND, OR a NOT:
VYBERTE ID oddelenia, SÚČET (Plat) Suma mzdy OD zamestnancov SKUPINA PODĽA ID oddelenia S SÚČETOU (Plat) > 3000 A POČET (*)<2 -- и число людей меньше 2-х
Ako môžete vidieť tu, agregačná funkcia (pozri "COUNT(*)") môže byť špecifikovaná iba v bloku HAVING.
Podľa toho môžeme zobraziť iba číslo oddelenia, ktoré spadá pod podmienku HAVING:
VYBERTE ID oddelenia OD zamestnancov SKUPINA PODĽA ID oddelenia S SÚČETOU (Plat) > 3000 A POČET (*)<2 -- и число людей меньше 2-х
Príklad použitia podmienky HAVING pre pole zahrnuté v GROUP BY:
SELECT DepartmentID, SUM(Mold) PlatAmount FROM Zamestnanci GROUP BY DepartmentID -- 1. vytvorte zoskupenie MAJÚCI ID oddelenia=3 -- 2. použite filter na výsledok zoskupenia
Toto je len príklad, pretože v tomto prípade by bolo logickejšie skontrolovať podmienku WHERE:
VYBRAŤ ID oddelenia, SUM(Mzda) MzdaSuma OD Zamestnancov KDE IDoddelu=3 -- 1. filtrovať podrobné údaje GROUP BY DepartmentID -- 2. skupina len podľa vybraných záznamov
Tie. najprv vyfiltrujte zamestnancov podľa oddelenia 3 a až potom vykonajte výpočet.
Poznámka. V skutočnosti, aj keď tieto dva dotazy vyzerajú odlišne, optimalizátor DBMS ich môže vykonať rovnakým spôsobom.
Myslím, že toto je koniec príbehu o MAJTE podmienky.
Zhrnutie
Zhrňme si údaje získané v druhej a tretej časti a zvážme konkrétne umiestnenie každej štruktúry, ktorú sme študovali, a uveďte poradie, v ktorom sa vykonávajú:| Štruktúra/blok | Poriadok vykonania | Spustiteľná funkcia |
|---|---|---|
| SELECT návratové výrazy | 4 | Vrátenie údajov prijatých žiadosťou |
| ZO zdroja | 0 | V našom prípade sú to zatiaľ všetky riadky tabuľky |
| WHERE podmienka pre načítanie zo zdroja | 1 | Vyberú sa iba riadky zodpovedajúce podmienke |
| GROUP BY zoskupovacie výrazy | 2 | Vytvorte skupiny na základe zadaného výrazu zoskupenia. Výpočet agregovaných hodnôt pre tieto skupiny používané v blokoch SELECT alebo HAVING |
| HAVING filtrovať podľa zoskupených údajov | 3 | Filtrovanie použité na zoskupené údaje |
| ORDER BY výraz triedenia výsledkov | 5 | Zoradiť údaje podľa zadaného výrazu |
Samozrejme, môžete tiež použiť klauzuly DISTINCT a TOP naučené v časti 2 na zoskupené údaje.
Tieto návrhy v tomto prípade budú platiť pre konečný výsledok:
VYBERTE TOP 1 -- 6. použite poslednú SÚČET (Plat) MzdaSuma OD zamestnancov ZOSKUPENIE PODĽA ID oddelenia S SÚČETOU (Plat)>3000 OBJEDNAŤ PODĽA ID oddelenia -- 5. Zoradiť výsledok
Ako ste sa k týmto výsledkom dostali, analyzujte sami.
Záver
Hlavným cieľom, ktorý som si v tejto časti stanovil, je odhaliť vám podstatu agregovaných funkcií a zoskupení.Ak nám základný návrh umožnil získať potrebné podrobné údaje, potom použitie agregovaných funkcií a zoskupení na týchto podrobných údajoch nám umožnilo získať o nich súhrnné údaje. Takže, ako vidíte, všetko je tu dôležité, pretože. jedno sa spolieha na druhé - bez znalosti základného dizajnu nebudeme vedieť napríklad správne vybrať údaje, pre ktoré potrebujeme vypočítať výsledky.
Tu sa zámerne snažím ukázať len základy, aby som začiatočníkov zameral na najdôležitejšie konštrukcie a nezahltil ich zbytočnými informáciami. Dôkladné pochopenie základných konštrukcií (o ktorých budem pokračovať v ďalších častiach) vám dá príležitosť vyriešiť takmer akýkoľvek problém získavania údajov z RDB. Základné konštrukcie príkazu SELECT sú v rovnakej podobe použiteľné takmer vo všetkých DBMS (rozdiely sú najmä v detailoch napr. v implementácii funkcií – pre prácu s reťazcami, časom a pod.).
Dobrá znalosť databázy vám v budúcnosti poskytne príležitosť ľahko sa naučiť rôzne rozšírenia jazyka SQL, ako napríklad:
- SKUPINA PODĽA ROLLUPU (…), SKUPINA PODĽA SKUPENIA SÚPRAV (…), …
- PIVOT, NEPIVOT
- atď.
Ak robíte prvé kroky v SQL, tak sa v prvom rade zamerajte na naučenie sa základných štruktúr, pretože. vlastniť základňu, všetko ostatné bude pre vás oveľa jednoduchšie pochopiť, a navyše, na vlastnú päsť. V prvom rade treba do hĺbky pochopiť možnosti jazyka SQL, t.j. aké operácie vo všeobecnosti umožňuje vykonávať s údajmi. Sprostredkovať informácie začiatočníkom v objemovej forme je ďalší dôvod, prečo ukážem len najdôležitejšie (železné) štruktúry.
Veľa šťastia pri učení a pochopení jazyka SQL.
Štvrtá časť -
tím CASE vám umožňuje vybrať pre jeden od viacero sekvencií príkazov. Táto konštrukcia je prítomná v štandarde SQL od roku 1992, hoci v Oracle SQL nebola podporovaná až do Oracle8i a PL/SQL až do vydania Oracle9i 1. Počnúc touto verziou sú podporované nasledujúce druhy príkazov CASE:
- jednoduchý príkaz CASE - Priraďuje jednu alebo viacero sekvencií príkazov PL/SQL k zodpovedajúcim hodnotám (sekvencia, ktorá sa má vykonať, sa vyberá na základe výsledku vyhodnotenia výrazu, ktorý vracia jednu z hodnôt).
- Vyhľadávací tím CASE - vyberie jednu alebo viac sekvencií príkazov, ktoré sa majú vykonať, v závislosti od výsledkov kontroly zoznamu boolovských hodnôt. Vykoná sa postupnosť príkazov spojených s prvou podmienkou, ktorá sa vyhodnotí ako TRUE.
NULL alebo NEZNÁMY?
V článku o príkaze IF ste sa možno dozvedeli, že výsledkom boolovského výrazu môže byť TRUE , FALSE alebo NULL .
V PL/SQL je toto tvrdenie pravdivé, ale v širšom kontexte relačnej teórie sa považuje za nesprávne hovoriť o vrátení NULL z boolovského výrazu. Relačná teória hovorí, že porovnanie s NULL má nasledujúcu formu:
2 < NULL
dáva logický výsledok UNKNOWN a UNKNOWN nie je ekvivalentom NULL. Netrápte sa však príliš tým, že PL/SQL používa NULL pre UNKNOWN . Mali by ste si však uvedomiť, že tretia hodnota v 3-hodnotovej logike je NEZNÁMA . A dúfam, že sa nikdy neposeriete (ako ja!) použitím nesprávneho termínu pri diskusii o trojhodnotovej logike s odborníkmi na vzťahy.
Okrem príkazov CASE podporuje PL/SQL aj príkazy CASE. Takýto výraz je veľmi podobný príkazu CASE, umožňuje vybrať jeden alebo viac výrazov na vyhodnotenie. Výsledkom výrazu CASE je jedna hodnota, zatiaľ čo výsledkom príkazu CASE je vykonanie sekvencie príkazov PL/SQL.
Jednoduché príkazy CASE
Jednoduchý príkaz CASE vám umožňuje vybrať jednu z niekoľkých sekvencií príkazov PL/SQL na vykonanie v závislosti od výsledku vyhodnotenia výrazu. Píše sa takto:
CASE výraz WHEN výsledok_1 THEN príkaz_1 KEĎ výsledok_2 THEN príkaz_2 ... ELSE príkaz_else END CASE;
Pobočka ELSE je tu voliteľná. Pri vykonávaní takéhoto príkazu PL/SQL najskôr vyhodnotí výraz a potom sa výsledok porovná s výsledkom_1 . Ak sa zhodujú, vykonajú sa príkazy_1. V opačnom prípade sa kontroluje výsledok_2 atď.
Tu je príklad jednoduchého príkazu CASE, v ktorom sa bonus vypočítava v závislosti od hodnoty premennej zamestnanec_typ:
PRÍPAD typ_zamestnanca KEĎ "S" THEN pridelenie_platu_bonus(id_zamestnanca); KEĎ "H" THEN prize_hourly_bonus(employee_id); KEĎ "C" THEN award_commissioned_bonus(employee_id); ELSE RAISE invalid_employee_type; KONCOVÝ PRÍPAD;
V tomto príklade je explicitná klauzula ELSE, ale vo všeobecnosti sa nevyžaduje. Bez sekcie ELSE kompilátor PL/SQL implicitne nahrádza kód takto:
ELSE RAISE CASE_NOT_FOUND;
Inými slovami, ak nešpecifikujete kľúčové slovo ELSE a ak sa žiadny z výsledkov v klauzulách WHEN nezhoduje s výsledkom výrazu v príkaze CASE, PL/SQL vyvolá výnimku CASE_NOT_FOUND. Toto je rozdiel medzi týmto príkazom a IF . Keď príkaz IF neobsahuje kľúčové slovo ELSE, pri nesplnení podmienky sa nič nedeje, zatiaľ čo v príkaze CASE podobná situácia vedie k chybe.
Je zaujímavé vidieť, ako implementovať bonusovú logiku popísanú na začiatku kapitoly pomocou jednoduchého príkazu CASE. Na prvý pohľad sa to zdá nemožné, ale kreatívnym prístupom k veci dospejeme k nasledovnému riešeniu:
PRÍPAD PRAVDIVÝ, KEĎ plat >= 10 000 A plat<=20000 THEN give_bonus(employee_id, 1500); WHEN salary >20 000 A plat<= 40000 THEN give_bonus(employee_id, 1000); WHEN salary >40 000 TAK daj_bonus(id_zamestnanca, 500); ELSE give_bonus(id_zamestnanca, 0); KONCOVÝ PRÍPAD;
Dôležité je, že prvky výrazu a výsledku môžu byť skalárne hodnoty alebo výrazy, ktoré sa vyhodnotia ako skalárne hodnoty.
Keď sa vrátime k príkazu IF...THEN...ELSIF, ktorý implementuje rovnakú logiku, uvidíte, že sekcia ELSE je definovaná v príkaze CASE, zatiaľ čo kľúčové slovo ELSE v príkaze IF-THEN-ELSIF chýba. Dôvod pridania ELSE je jednoduchý: ak nie je splnená žiadna z bonusových podmienok, pokyn IF nerobí nič a bonus je nulový. Príkaz CASE v tomto prípade vyvolá chybu, takže situácia s nulovou prémiou musí byť explicitne naprogramovaná.
Aby ste predišli chybám CASE_NOT_FOUND, uistite sa, že akákoľvek hodnota testovaného výrazu bude spĺňať aspoň jednu z podmienok.
Vyššie uvedený príkaz CASE TRUE môže niekomu znieť ako trik, ale v skutočnosti je to len implementácia príkazu CASE search, o ktorom si povieme v ďalšej časti.
tím vyhľadávania CASE
Príkaz CASE search skúma zoznam boolovských výrazov; pri stretnutí s výrazom rovným TRUE vykoná sekvenciu príkazov, ktoré sú s ním spojené. V podstate je príkaz CASE search ekvivalentom príkazu CASE TRUE, ktorého príklad bol uvedený v predchádzajúcej časti. Príkaz CASE search má nasledujúci zápis:
CASE WHEN výraz_1 THEN príkaz_1 KEĎ výraz_2 THEN príkaz_2 ... ELSE príkaz_else KONIEC PÍSMENO; Je ideálny na implementáciu bonusovej logiky: PRÍPAD, KEĎ plat >= 10 000 A plat<=20000 THEN give_bonus(employee_id, 1500); WHEN salary >20 000 A plat<= 40000 THEN give_bonus(employee_id, 1000); WHEN salary >40 000 TAK daj_bonus(id_zamestnanca, 500); ELSE give_bonus(id_zamestnanca, 0); KONCOVÝ PRÍPAD;
Vyhľadávací príkaz CASE, podobne ako jednoduchý príkaz, podlieha nasledujúcim pravidlám:
- Vykonanie príkazu sa skončí okamžite po vykonaní sekvencie spustiteľných príkazov spojených s výrazom true. Ak je viacero výrazov pravdivých, vykonajú sa príkazy spojené s prvým z nich.
- Kľúčové slovo ELSE je voliteľné. Ak nie je nastavený a žiadny z výrazov nie je TRUE , vyvolá sa výnimka CASE_NOT_FOUND.
- KEĎ sa podmienky kontrolujú v presne definovanom poradí, od začiatku do konca.
Zvážte inú implementáciu logiky výpočtu bonusu, ktorá využíva skutočnosť, že podmienky KEDY sa kontrolujú v poradí, v akom sú napísané. Jednotlivé výrazy sú jednoduchšie, ale dá sa povedať, že sa význam celého príkazu stal zrozumiteľnejším?
PRÍPAD, KEĎ plat > 40000 TAK daj_bonus(id_zamestnanca, 500); KEĎ plat > 20000 TAK daj_bonus(id_zamestnanca, 1000); KEĎ plat >= 10000 TAK daj_bonus(id_zamestnanca, 1500); ELSE give_bonus(id_zamestnanca, 0); KONCOVÝ PRÍPAD;
Ak je mzda zamestnanca 20 000 USD, potom prvé dve podmienky sú NEPRAVDIVÉ a tretia PRAVDA, takže zamestnanec dostane bonus vo výške 1 500 USD. Ak je plat 21 000 USD, potom bude výsledok druhej podmienky PRAVDA a bonus bude 1 000 USD. Vykonanie príkazu CASE sa skončí v druhej vetve WHEN a tretia podmienka nebude ani kontrolovaná. Je diskutabilné, či sa oplatí použiť tento prístup pri písaní príkazov CASE. Nech je to akokoľvek, majte na pamäti, že napísanie takéhoto príkazu je možné a pri ladení a úprave programov, v ktorých výsledok závisí od poradia výrazov, je potrebná osobitná pozornosť.
Logika, ktorá závisí od poradia homogénnych vetiev WHEN, je potenciálnym zdrojom chýb, ktoré sa vyskytujú pri ich preusporiadaní. Ako príklad si vezmite nasledujúci príkaz CASE search, v ktorom s hodnotou platu 20 000 sa podmienky v oboch vetvách WHEN vyhodnotia ako PRAVDA:
PRÍPAD, KEĎ plat MEDZI 10 000 AŽ 20 000 TAK daj_bonus(id_zamestnanca, 1500); KEĎ plat MEDZI 20000 AŽ 40000 TAK daj_bonus(id_zamestnanca, 1000); ...
Predstavte si, že správca tohto programu neopatrne preusporiada pobočky KEDY, aby ich zoradil v zostupnom platovom poradí. Neodmietajte túto príležitosť! Programátori majú často tendenciu „dokončiť“ perfektne fungujúci kód, riadený nejakou internou myšlienkou poriadku. Príkaz CASE s preusporiadanými sekciami WHEN vyzerá takto:
PRÍPAD, KEĎ plat MEDZI 20000 AŽ 40000 TAK daj_bonus(id_zamestnanca, 1000); KEĎ plat MEDZI 10000 AŽ 20000 TAK daj_bonus(id_zamestnanca, 1500); ...
Na prvý pohľad je všetko správne, však? Žiaľ, kvôli prekrývaniu dvoch vetiev WHEN sa v programe objavuje zákerná chyba. Teraz zamestnanec s platom 20 000 dostane bonus 1 000 namiesto očakávaných 1 500. V niektorých situáciách môže byť žiaduce prekrývať sa medzi pobočkami KEDY, ale ak je to možné, treba sa tomu vyhnúť. Vždy pamätajte, že poradie vetiev je dôležité a odolajte nutkaniu vylepšiť už fungujúci kód – „neopravujte, čo nie je pokazené“.
Pretože podmienky KEĎ sa vyhodnocujú v poradí, môžete svoj kód trochu zefektívniť umiestnením vetví s najpravdepodobnejšími podmienkami na začiatok zoznamu. Taktiež, ak máte pobočku s „drahými“ výrazmi (napríklad vyžadujúce značný čas CPU a pamäť), môžete ich umiestniť na koniec, aby ste minimalizovali možnosť ich kontroly. Podrobnosti nájdete v časti Vnorené príkazy IF.
Príkazy vyhľadávania CASE sa používajú, keď sú príkazy, ktoré sa majú vykonať, určené množinou logických výrazov. Jednoduchý príkaz CASE sa používa, keď sa rozhoduje na základe výsledku jedného výrazu.
Vnorené príkazy CASE
Príkazy CASE, podobne ako príkazy IF, môžu byť vnorené. Napríklad vnorený príkaz CASE je prítomný v nasledujúcej (skôr spletitej) implementácii bonusovej logiky:
PRÍPAD, KEĎ plat >= 10000 TAK PRÍPAD, KEĎ plat<= 20000 THEN give_bonus(employee_id, 1500); WHEN salary >40 000 TAK daj_bonus(id_zamestnanca, 500); KEĎ plat > 20000 TAK daj_bonus(id_zamestnanca, 1000); KONCOVÝ PRÍPAD; KEDY plat< 10000 THEN give_bonus(employee_id,0); END CASE;
V príkaze CASE možno použiť ľubovoľný príkaz, takže interný príkaz CASE možno ľahko nahradiť príkazom IF. Podobne môže byť každý príkaz vnorený do príkazu IF, vrátane CASE .
CASE výrazy
Výrazy CASE riešia rovnaký problém ako príkazy CASE, len nie pre spustiteľné príkazy, ale pre výrazy. Jednoduchý výraz CASE vyberie jeden z niekoľkých výrazov na vyhodnotenie na základe danej skalárnej hodnoty. Vyhľadávací výraz CASE postupne vyhodnocuje výrazy v zozname, až kým sa jeden z nich nevyhodnotí ako TRUE, a potom vráti výsledok priradeného výrazu.
Syntax týchto dvoch druhov výrazov CASE je:
Simple_Expression_Case:= CASE výraz WHEN vysledok_1 THEN vysledok_vyraz_1 KEDY vysledok_2 THEN vysledok_vyraz_2 ... ELSE vysledok_vyraz_else END; Search_expression_Case:= CASE KEDY vyraz_1 THEN vysledok_vyraz_1 KEDY vyraz_2 THEN vysledok_vyraz_2 ... ELSE vysledok_vyraz_else END;
Výraz CASE vráti jednu hodnotu, výsledok výrazu vybraného na vyhodnotenie. Každej vetve WHEN musí byť priradený jeden výsledný výraz (nie však príkaz). Bodkočiarka ani END CASE sa neumiestňujú na koniec výrazu CASE. Výraz CASE končí kľúčovým slovom END.
Nasleduje príklad jednoduchého výrazu CASE použitého v spojení s procedúrou PUT_LINE balíka DBMS_OUTPUT na zobrazenie hodnoty booleovskej premennej.
(Pripomeňme, že program PUT_LINE priamo nepodporuje boolovské typy.) V tomto príklade výraz CASE konvertuje boolovskú hodnotu na znakový reťazec, ktorý je potom výstupom procedúry PUT_LINE:
DECLARE boolean_true BOOLEAN:= TRUE; boolean_false BOOLEAN:= FALSE; boolean_null BOOLEAN; FUNKCIA boolean_to_varchar2 (príznak V BOOLEAN) RETURN VARCHAR2 JE BEGIN RETURN CASE príznak KEĎ TRUE, TAK „True“ WHEN FALSE THEN „False“ ELSE „NULL“ KONIEC; KONIEC; BEGIN DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_true)); DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_false)); DBMS_OUTPUT.PUT_LINE(boolean_to_varchar2(boolean_null)); KONIEC;
Na implementáciu logiky výpočtu bonusu môžete použiť vyhľadávací výraz CASE, ktorý vráti hodnotu bonusu pre daný plat:
PRIHLÁSIŤ ČÍSLO platu:= 20000; zamestnanec_id NUMBER:= 36325; PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE(emp_id); DBMS_OUTPUT.PUT_LINE(bonus_amt); KONIEC; BEGIN give_bonus(employee_id, PRÍPAD, KEĎ plat >= 10 000 A plat<= 20000 THEN 1500 WHEN salary >20 000 A plat<= 40000 THEN 1000 WHEN salary >40000 POTOM 500 INAK 0 KONIEC); KONIEC;
Výraz CASE možno použiť všade tam, kde sú povolené výrazy akéhokoľvek iného typu. V nasledujúcom príklade sa na výpočet sumy bonusu používa výraz CASE, ktorý sa vynásobí 10 a výsledok sa priradí premennej, ktorú zobrazí DBMS_OUTPUT:
PRIHLÁSIŤ ČÍSLO platu:= 20000; zamestnanec_id NUMBER:= 36325; bonus_suma NUMBER; BEGIN bonus_amount:= PRÍPAD, KEĎ plat >= 10 000 A plat<= 20000 THEN 1500 WHEN salary >20 000 A plat<= 40000 THEN 1000 WHEN salary >40000 POTOM 500 INAK 0 KONIEC * 10; DBMS_OUTPUT.PUT_LINE(suma_bonusu); KONIEC;
Na rozdiel od príkazu CASE, ak nie je splnená žiadna podmienka vetvenia WHEN, výraz CASE nevyvolá chybu, ale jednoducho vráti NULL .
Tento tutoriál Oracle vysvetľuje, ako používať Oracle/PLSQL vyhlásenie CASE so syntaxou a príkladmi.
Popis
Príkaz Oracle/PLSQL CASE má funkčnosť príkazu IF-THEN-ELSE. Od Oracle 9i môžete použiť príkaz CASE v rámci príkazu SQL.
Syntax
Syntax pre príkaz CASE v Oracle/PLSQL je:
CASE [ výraz ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END
Parametre alebo argumenty
výraz voliteľný. Je to hodnota, ktorú porovnávate so zoznamom podmienok. (tj: podmienka_1, podmienka_2, ... podmienka_n) podmienka_1, podmienka_2, ... podmienka_n Podmienky, ktoré musia mať rovnaký dátový typ. Podmienky sa vyhodnocujú v uvedenom poradí. Raz stav sa zistí, že je pravdivý, príkaz CASE vráti výsledok a nebude ďalej vyhodnocovať podmienky. vysledok_1, vysledok_2, ... vysledok_n Výsledky, ktoré musia mať rovnaký typ údajov. Toto je hodnota vrátená raz a stav sa zistilo, že je pravdivé.sa vracia
Príkaz CASE vráti akýkoľvek typ údajov, ako je reťazec, číslo, dátum atď. (ALE všetky výsledky musia mať v príkaze CASE rovnaký dátový typ.)
Ak všetky podmienky nie sú rovnakého typu údajov, vráti sa chyba ORA-00932.
Ak všetky výsledky nie sú rovnakého typu údajov, vráti sa chyba ORA-00932.
Ak nie stav sa zistí, že je pravdivý, potom príkaz CASE vráti hodnotu v klauzule ELSE.
Ak sa vynechá doložka ELSE a č stav sa zistí, že je pravdivý, potom príkaz CASE vráti hodnotu NULL.
Poznámka
- V príkaze CASE môžete mať až 255 porovnaní. Každá klauzula WHEN ... THEN sa považuje za 2 prirovnania.
Týka sa
Príkaz CASE je možné použiť v nasledujúcich verziách Oracle/PLSQL:
- Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i
príklad
Príkaz CASE je možné použiť v Oracle/PLSQL.
Príkaz CASE môžete použiť v príkaze SQL takto: (zahŕňa výraz doložka)
SELECT table_name, CASE owner WHEN "SYS" THEN "Vlastníkom je SYS" WHEN "SYSTEM" THEN "Vlastníkom je SYSTEM" ELSE "Vlastníkom je iná hodnota" END FROM all_tables;
Alebo môžete napísať príkaz SQL pomocou príkazu CASE takto: (vynecháva výraz doložka)
SELECT table_name, CASE WHEN owner="SYS" THEN "Vlastníkom je SYS" WHEN owner="SYSTEM" THEN "Vlastníkom je SYSTEM" ELSE "Vlastníkom je iná hodnota" END FROM all_tables;
Vyššie uvedené dva príkazy CASE sú ekvivalentné nasledujúcemu príkazu IF-THEN-ELSE:
AK vlastník = "SYS" THEN result:= "Vlastníkom je SYS"; ELSIF vlastník = "SYSTÉM" THEN výsledok:= "Vlastníkom je SYSTÉM""; ELSE výsledok:= "Vlastníkom je iná hodnota"; END IF;
Výpis CASE porovná každú hodnotu vlastníka, jednu po druhej.
Jedna vec, ktorú treba poznamenať, je, že klauzula ELSE v príkaze CASE je voliteľná. Mohol si to vynechať. Pozrime sa na vyššie uvedený príkaz SQL s vynechanou klauzulou ELSE.
Váš príkaz SQL bude vyzerať takto:
SELECT názov_tabulky, CASE vlastník KEĎ "SYS" THEN "Vlastníkom je SYS" KEĎ "SYSTEM" THEN "Vlastníkom je SYSTEM" END FROM all_tables;
S vynechanou klauzulou ELSE, ak sa nezistilo, že je pravdivá žiadna podmienka, príkaz CASE vráti hodnotu NULL.
Porovnanie 2 podmienok
Tu je príklad, ktorý ukazuje, ako použiť príkaz CASE na porovnanie rôznych podmienok:
VYBERTE PRÍPAD, KEĎ a< b THEN "hello" WHEN d < e THEN "goodbye" END FROM suppliers;
často kladené otázky
Otázka: Môžete vytvoriť príkaz CASE, ktorý vyhodnocuje dve rôzne polia? Chcem vrátiť hodnotu na základe kombinácií v dvoch rôznych poliach.
Odpoveď: Áno, nižšie je príklad výroku o prípade, ktorý hodnotí dve rôzne polia.
SELECT Supplier_id, CASE WHEN Supplier_name = "IBM" a Supplier_type = "Hardvér" THEN "North office" WHEN Supplier_name = "IBM" a Supplier_type = "Software" THEN "Južná kancelária" KONČÍ OD dodávateľov;
Ak je teda pole názov_dodávateľa IBM a pole Supplier_type je Hardvér, potom sa vráti príkaz CASE severná kancelária. Ak je pole názov_dodávateľa IBM a dodávateľ_typ je softvér, vráti sa príkaz CASE južná kancelária.