Uvjetni CASE izrazi. Uslovni izrazi CASE HAVING – nametanje uslova selekcije na grupisane podatke
- sa naredbom CASE, koja vam omogućava da uključite uslovne izraze u upit;
- sa agregatnim funkcijama koje vam omogućavaju da dobijete različite vrste ukupnih (agregiranih vrijednosti) izračunatih na osnovu detaljnih podataka dobivenih operatorom “SELECT ... WHERE ...”;
- sa klauzulom GROUP BY, koja, zajedno sa agregatnim funkcijama, omogućava da dobijete rezultate za detaljne podatke raščlanjene po grupama;
- sa klauzulom HAVING, koja vam omogućava filtriranje prema grupisanim podacima.
CASE izraz – SQL uvjetni izraz
Ovaj operator vam omogućava da provjerite uslove i vratite određeni rezultat u zavisnosti od ispunjenja određenog uslova.CASE izraz ima 2 oblika:
Izrazi također mogu poslužiti kao vrijednosti ovdje.
Pogledajmo prvi obrazac CASE kao primjer:
ODABERITE ID,IME,PLATU, SLUČAJ KADA Plata>=3000 PA "Plata >= 3000" KADA Plata>=2000 ONDA "2000<= ЗП < 3000"
ELSE "ЗП < 2000"
END SalaryTypeWithELSE,
CASE
WHEN Salary>=3000 ONDA "Plata >= 3000" KAD Plata>=2000 ONDA "2000<= ЗП < 3000"
END SalaryTypeWithoutELSE
FROM Employees
KADA se uvjeti provjeravaju uzastopno, od vrha do dna. Kada se dostigne prvi zadovoljavajući uslov, dalje testiranje se prekida i vraća se vrijednost specificirana nakon riječi THEN pridružene ovom bloku WHEN.
Ako nijedan od uslova WHEN nije ispunjen, tada se vraća vrijednost navedena nakon riječi ELSE (što je u u ovom slučaju znači “DRUGO VRAĆANJE...”).
Ako blok ELSE nije specificiran i nijedan WHEN uvjet nije ispunjen, tada se vraća NULL.
I u prvom i u drugom obliku, blok ELSE dolazi na samom kraju CASE konstrukcije, tj. nakon svih uslova KADA.
Pogledajmo drugi obrazac CASE kao primjer:
Recimo da su za Novu godinu odlučili dati bonuse svim zaposlenicima i zatražili da izračunaju iznos bonusa prema sljedećoj šemi:
- Zaposleni u IT odjelu će dobiti 15% plate;
- Zaposleni u računovodstvu primaju 10% svoje plate;
- Svi ostali primaju 5% svoje plate.
Za ovaj zadatak koristimo upit s CASE izrazom:
SELECT ID,Name,Salary,DepartmentID, -- radi jasnoće, prikazaćemo procenat kao niz CASE DepartmentID -- vrijednost koja se provjerava KADA 2 ONDA "10%" -- 10% plate će biti izdato računovođama KADA 3 ONDA "15%" -- 15% od plate, izdajte IT ljudima OSTALO "5%" -- svi ostali 5% END NewYearBonusPercent, -- napravite izraz koristeći CASE da vidite iznos bonusa Plaća/100* CASE DepartmentID KADA 2 ONDA 10 -- 10% od plate za izdavanje računovođe KADA 3 ONDA 15 -- dajte 15% plate IT radnicima OSTALO 5 -- svi ostali 5% KRAJ Bonus Iznos OD Zaposlenih
Ovdje se vrši sekvencijalna provjera vrijednosti DepartmentID sa WHEN vrijednostima. Kada je prvi DepartmentID jednak WHEN vrijednosti, provjera se prekida i vrijednost specificirana nakon riječi THEN pridružene ovom bloku WHEN se vraća.
Prema tome, vrijednost bloka ELSE se vraća ako DepartmentID ne odgovara nijednoj vrijednosti WHEN.
Ako ne postoji blok ELSE, onda ako DepartmentID ne odgovara nijednoj vrijednosti WHEN, NULL će biti vraćeno.
Drugi oblik CASE se lako može predstaviti pomoću prvog oblika:
ODABIR ID,Naziv,Plata,ID odjeljenja, SLUČAJ KADA ID odjeljenja=2 ONDA "10%" -- 10% plate će biti dato računovođama KADA ID odjela=3 ONDA "15%" -- 15% plate će se dati IT ljudima OSTALO "5%" -- svi ostali dobijaju 5% END NewYearBonusPercent, -- konstruišite izraz koristeći CASE da vidite iznos bonusa Plata/100* SLUČAJ KADA DepartmentID=2 ONDA 10 -- dajte 10% plate za Računovođe KADA ID odjela=3 ONDA 15 -- Dajte 15% plate IT zaposlenima OSTALI 5 -- 5% svima ostalima KRAJ Bonus Iznos OD Zaposlenih
Dakle, drugi oblik je samo pojednostavljena notacija za one slučajeve kada trebamo napraviti poređenje jednakosti iste testirane vrijednosti sa svakom WHEN vrijednošću/izrazom.
Bilješka. Prvi i drugi oblik CASE su uključeni u standard SQL jezika, tako da bi najvjerovatnije trebali biti primjenjivi u mnogim DBMS-ovima.
Sa MS SQL verzijom 2012, pojavio se pojednostavljeni IIF obrazac za snimanje. Može se koristiti za pojednostavljenje konstrukcije CASE kada se vrate samo 2 vrijednosti. IIF dizajn je sljedeći:
IIF(uvjet, istinita_vrijednost, lažna_vrijednost)
One. Ovo je u suštini omot za sljedeću CASE konstrukciju:
CASE WHEN uslov THEN true_value ELSE false_value END
Pogledajmo primjer:
SELECT ID,Ime,Plata, IIF(Plata>=2500,"ZP >= 2500","ZP< 2500") DemoIIF, CASE WHEN Salary>=2500 ONDA "ZP >= 2500" OSTALO "ZP< 2500" END DemoCASE FROM Employees
CASE i IIF konstrukcije mogu biti ugniježđene jedna u drugu. Pogledajmo jedan apstraktni primjer:
ODABIR ID, Ime, Plata, SLUČAJ KADA ID odjela IN(1,2) ONDA "A" KADA DepartmentID=3 ONDA SLUČAJ PositionID -- ugniježđeni SLUČAJ KADA 3 ONDA "B-1" KADA 4 ONDA "B-2" END ELSE " C" END Demo1, IIF(ID odjela IN(1,2),"A", IIF(ID odjela=3,CASE PositionID KADA 3 ONDA "B-1" KADA 4 ONDA "B-2" KRAJ,"C")) Demo2 OD zaposlenih
Pošto su CASE i IIF konstrukcije izrazi koji vraćaju rezultat, možemo ih koristiti ne samo u bloku SELECT, već iu drugim blokovima koji dozvoljavaju upotrebu izraza, na primjer, u blokovima WHERE ili ORDER BY.
Na primjer, neka nam se da zadatak da napravimo listu za izdavanje plaće na ruke, kako slijedi:
- Prije svega, platu treba da primaju zaposleni čija je plata manja od 2500
- Oni zaposleni čija je plata veća ili jednaka 2500 primaju platu na drugom mjestu
- Unutar ove dvije grupe, potrebno je sortirati redove po punom imenu (polje Ime)
Pokušajmo riješiti ovaj problem dodavanjem CASE izraza u blok ORDER BY:
ODABERITE ID,IME,PLATU OD Zaposlenih REDAJTE PO SLUČAJU KADA plata>=2500 ONDA 1 OSTALO 0 KRAJ, -- prvo izdajte platu onima koji je imaju ispod 2500 Ime -- zatim naručite listu po punom imenu
Kao što vidimo, Ivanov i Sidorov će poslednji napustiti posao.
I apstraktni primjer korištenja CASE u bloku WHERE:
ODABIR ID,IME,PLATA OD Zaposlenih GDJE SLUČAJ KADA Plata>=2500 PA 1 OSTALO 0 KRAJ=1 -- svi zapisi čiji je izraz jednak 1
Možete pokušati sami preraditi zadnja 2 primjera sa IIF funkcijom.
I na kraju, sjetimo se još jednom NULL vrijednosti:
ODABIR ID,Naziv,Plata,ID odjeljenja, SLUČAJ KADA ID odjeljenja=2 ONDA "10%" -- 10% plate će biti dato računovođama KADA ID odjela=3 ONDA "15%" -- 15% plate će se dati IT ljudima KADA JE DepartmentID NULL ONDA "-" -- ne dajemo bonuse slobodnjacima (koristimo IS NULL) OSTALO "5%" -- svi ostali dobijaju 5% END NewYearBonusPercent1 -- a vi ne možete provjeriti NULL, zapamtite šta je rečeno o NULL u drugom dijelu CASE DepartmentID - - vrijednost se provjerava KADA 2 ONDA "10%" KADA 3 ONDA "15%" KADA NULL ONDA "-" -- !!! u ovom slučaju, upotreba drugog CASE obrasca nije prikladna OSTALI "5%" KRAJ NewYearBonusPercent2 FROM Employees
Naravno, možete to prepisati otprilike ovako:
SELECT ID,Name,Plata,DepartmentID, CASE ISNULL(DepartmentID,-1) -- koristite zamjenu u slučaju NULL za -1 KAD 2 ONDA "10%" KAD 3 ONDA "15%" KADA -1 ONDA "-" - - ako smo sigurni da ne postoji odjel sa ID-om jednakim (-1) i da neće biti DRUGO "5%" KRAJ NewYearBonusPercent3 OD Zaposlenih
Općenito, let mašte u ovom slučaju nije ograničen.
Kao primjer, pogledajmo kako se funkcija ISNULL može modelirati korištenjem CASE i IIF:
SELECT ID,Ime,Prezime, ISNULL(Prezime,"Nije navedeno") DemoISNULL, SLUČAJ KADA JE Prezime NULL ONDA "Nije navedeno" OSTALO Prezime END DemoCASE, IIF(Prezime JE NULL,"Nije navedeno",Prezime) Employees
CASE konstrukcija je vrlo moćna značajka SQL jezika koja vam omogućava primjenu dodatne logike za izračunavanje vrijednosti skupa rezultata. U ovom dijelu će nam biti od koristi poznavanje CASE dizajna, pa mu je u ovom dijelu prvenstveno posvećena pažnja.
Agregatne funkcije
Ovdje ćemo razmotriti samo osnovne i najčešće korištene agregatne funkcije:| Ime | Opis |
|---|---|
| COUNT(*) | Vraća broj redova dobivenih naredbom "SELECT ... WHERE ...". Ako nedostaje WHERE, broj svih zapisa tabele. |
| COUNT(kolona/izraz) | Vraća broj vrijednosti koje nisu nulte u navedenoj koloni/izrazu |
| COUNT(DISTINCT stupac/izraz) | Vraća broj jedinstvenih vrijednosti koje nisu nulte u navedenoj koloni/izrazu |
| SUM(kolona/izraz) | Vraća zbir vrijednosti stupca/izraza |
| AVG(kolona/izraz) | Vraća prosjek vrijednosti stupca/izraza. NULL vrijednosti nisu uključene u brojanje. |
| MIN (kolona/izraz) | Vraća minimalnu vrijednost na osnovu vrijednosti stupca/izraza |
| MAX(kolona/izraz) | Povratak maksimalna vrijednost po vrijednostima stupca/izraza |
Agregatne funkcije nam omogućavaju da izračunamo ukupnu vrijednost za skup redova dobivenih korištenjem SELECT izraza.
Pogledajmo svaku funkciju koristeći primjer:
SELECT COUNT(*) [Ukupni broj zaposlenih], COUNT(DISTINCT DepartmentID) [Broj jedinstvenih odjela], COUNT(DISTINCT PositionID) [Broj jedinstvenih pozicija], COUNT(BonusPercent) [Broj zaposlenih sa navedenim % bonusa] , MAX(BonusPercent) [Maksimalni procenat bonusa], MIN(BonusPercent) [Minimalni procenat bonusa], SUM(Plata/100*BonusPercent) [Zbroj svih bonusa], AVG(Plata/100*BonusPercent) [Prosječna veličina bonusa], AVG (Plata) [Prosječna plata] OD Zaposlenih
Radi veće jasnoće, odlučio sam da napravim izuzetak ovdje i koristio sam sintaksu […] da postavim pseudonime stupaca.
Pogledajmo kako je dobijena svaka vraćena vrijednost i prvo se prisjetimo konstrukcije osnovne sintakse naredbe SELECT.
Prvo, jer S obzirom da u zahtjevu nismo precizirali GDJE uslove, rezultati će biti izračunati za detaljne podatke dobijene zahtjevom:
SELECT * FROM Employees
One. za sve redove tabele Zaposleni.
Radi jasnoće, izabrat ćemo samo polja i izraze koji se koriste u agregatnim funkcijama:
SELECT DepartmentID, PositionID, BonusPercent, Salary/100*BonusPercent , Plata od zaposlenih
| DepartmentID | PositionID | BonusPercent | Plata/100*BonusPercent | Plata |
|---|---|---|---|---|
| 1 | 2 | 50 | 2500 | 5000 |
| 3 | 3 | 15 | 225 | 1500 |
| 2 | 1 | NULL | NULL | 2500 |
| 3 | 4 | 30 | 600 | 2000 |
| 3 | 3 | NULL | NULL | 1500 |
| NULL | NULL | NULL | NULL | 2000 |
Ovo su izvorni podaci (detaljni redovi) koji će se koristiti za izračunavanje rezultata zbirnog upita.
Sada pogledajmo svaku agregiranu vrijednost:
| COUNT(*)– jer nismo specificirali uslove filtriranja u bloku WHERE u upitu, tada nam je COUNT(*) dao ukupan broj zapisa u tabeli, tj. ovo je broj redova koje upit vraća: SELECT * FROM Employees |
| COUNT(DISTINCT DepartmentID)– vratila nam je vrijednost 3, tj. ovaj broj odgovara broju jedinstvenih vrijednosti odjela navedenih u stupcu DepartmentID, isključujući NULL vrijednosti. Prođimo kroz vrijednosti stupca DepartmentID i obojimo iste vrijednosti istom bojom (slobodno, sve metode su dobre za učenje): Odbacujemo NULL, nakon čega dobijamo 3 jedinstvene vrijednosti (1, 2 i 3). One. vrijednost dobijena pomoću COUNT(DISTINCT DepartmentID), u proširenom obliku, može se predstaviti sljedećim odabirom: ODABIR DISTINCT DepartmentID -- 2. uzmi samo jedinstvene vrijednosti OD Zaposlenih GDJE DepartmentID NIJE NULL -- 1. odbaci NULL vrijednosti
|
| COUNT(DISTINCT PositionID)– isto što je rečeno za COUNT(DISTINCT DepartmentID), samo za polje PositionID. Pogledajmo vrijednosti stupca PositionID i ne štedimo na bojama:
|
| COUNT(BonusPercent)– vraća broj redova koji imaju specificiranu vrijednost BonusPercent, tj. Računa se broj zapisa za koje BonusPercent NIJE NULL. Ovdje će nam biti lakše, jer... nema potrebe za brojanjem jedinstvenih vrijednosti, samo odbacite zapise sa NULL vrijednostima. Uzmite vrijednosti stupca BonusPercent i precrtajte sve NULL vrijednosti: Ostale su 3 vrijednosti. One. U proširenom obliku, uzorak se može predstaviti na sljedeći način: ODABIR BonusPercent -- 2. preuzmi sve vrijednosti OD Zaposlenih GDJE BonusPercent NIJE NULL -- 1. odbaci NULL vrijednosti
Jer Nismo koristili riječi DISTINCT, tada će se ponavljajući BonusPercents računati ako postoje, bez uzimanja u obzir BonusPercents jednak NULL. Kao primjer, uporedimo rezultat koristeći DISTINCT i bez njega. Radi veće jasnoće, koristimo vrijednosti polja DepartmentID: SELECT COUNT(*), -- 6 COUNT(DISTINCT DepartmentID), -- 3 COUNT(DepartmentID) -- 5 FROM Employees
|
| MAX (procenat bonusa)– vraća maksimalnu vrijednost BonusPercent, opet isključujući NULL vrijednosti. Uzimamo vrijednosti stupca BonusPercent i tražimo maksimalnu vrijednost među njima; ne obraćamo pažnju na NULL vrijednosti:
ODABERITE NAJBOLJIH 1 BonusPercent OD Zaposlenih GDJE BonusPercent NIJE NULL RED PO DESC BonusPercent -- sortirajte u silaznom redoslijedu |
| MIN (procenat bonusa)– vraća minimalnu vrijednost BonusPercent, opet isključujući NULL vrijednosti. Kao iu slučaju MAX, samo tražimo minimalnu vrijednost, zanemarujući NULL:
One. dobijamo sljedeću vrijednost: ODABERITE NAJBOLJIH 1 BonusPercent OD Zaposlenih GDJE BonusPercent NIJE NULL RED PO BonusPercentu -- sortirajte uzlaznim redoslijedom Vizuelni prikaz MIN(BonusPercent) i MAX(BonusPercent):
|
| SUM(Plata/100*Bonus posto)– vraća zbroj svih vrijednosti koje nisu NULL. Hajde da analiziramo vrijednosti izraza (Plata/100*BonusPercent):
One. Sljedeće vrijednosti se sumiraju: ODABERITE Platu/100*BonusPercent OD Zaposlenih GDJE Plaća/100*BonusPercent NIJE NULL
|
| AVG(Plata/100*Bonus posto)– vraća prosjek vrijednosti. NULL izrazi se ne uzimaju u obzir, tj. ovo odgovara drugom izrazu: SELECT AVG(Plata/100*BonusPercent), -- 1108,333333333333 SUM(Plata/100*BonusPercent)/COUNT(Plata/100*BonusPercent), -- 1108,333333333333 SUM(Plata/100*BonusPercent)/COUNT(Plata/100*BonusPercent), -- 1108,333333333333 SUMPerCOUNT(SUMPerSent) 54.166666666667 OD Employees
One. opet, NULL vrijednosti se ne uzimaju u obzir prilikom izračunavanja količina. Ako trebate izračunati prosjek za sve zaposlene, kao u trećem izrazu, koji daje 554.166666666667, onda koristite preliminarnu konverziju NULL vrijednosti u nulu: ODABIR PROCJENA (ISNULL(Plata/100*Procenat bonusa,0)), -- 554,166666666667 SUM(Plata/100*Procenat bonusa)/BROJ(*) -- 554,166666666667 OD Zaposlenih |
| AVG (plata)– zapravo, ovdje je sve isto kao u prethodnom slučaju, tj. ako je plata zaposlenog NULL, onda se neće uzeti u obzir. Da biste uzeli u obzir sve zaposlene, izvršite preliminarnu konverziju NULL vrijednosti AVG(ISNULL(Plata,0)) |
Sumiramo neke rezultate:
- COUNT(*) – služi za prebrojavanje ukupnog broja redova koji su dobijeni naredbom “SELECT... WHERE…”
- u svim ostalim gore navedenim agregatnim funkcijama, prilikom izračunavanja ukupnog iznosa, NULL vrijednosti se ne uzimaju u obzir
- ako trebamo uzeti u obzir sve redove, ovo je relevantnije za AVG funkciju, tada prvo moramo obraditi NULL vrijednosti, na primjer, kao što je prikazano iznad "AVG(ISNULL(Plata,0))"
Prema tome, kada se specificira dodatni uvjet u bloku WHERE sa agregatnim funkcijama, izračunat će se samo zbrojevi za redove koji zadovoljavaju uvjet. One. izračunavanje agregatnih vrijednosti se dešava za konačni skup, koji se dobija pomoću SELECT konstrukcije. Na primjer, uradimo istu stvar, ali samo iz perspektive IT odjela:
SELECT COUNT(*) [Ukupni broj zaposlenih], COUNT(DISTINCT DepartmentID) [Broj jedinstvenih odjela], COUNT(DISTINCT PositionID) [Broj jedinstvenih pozicija], COUNT(BonusPercent) [Broj zaposlenih sa navedenim % bonusa] , MAX(BonusPercent) [Maksimalni procenat bonusa], MIN(BonusPercent) [Minimalni procenat bonusa], SUM(Plata/100*BonusPercent) [Zbroj svih bonusa], AVG(Plata/100*BonusPercent) [Prosječna veličina bonusa], AVG (Plata) [Prosječna veličina plate] FROM Employees WHERE DepartmentID=3 -- uzeti u obzir samo IT odjel
Predlažem vam da, radi boljeg razumijevanja rada agregatnih funkcija, samostalno analizirate svaku dobivenu vrijednost. Ovdje vršimo kalkulacije, odnosno na osnovu detaljnih podataka dobijenih na zahtjev:
SELECT DepartmentID, PositionID, BonusPercent, Salary/100*BonusPercent , Salary FROM Employees WHERE DepartmentID=3 -- uzmite u obzir samo IT odjel
| DepartmentID | PositionID | BonusPercent | Plata/100*BonusPercent | Plata |
|---|---|---|---|---|
| 3 | 3 | 15 | 225 | 1500 |
| 3 | 4 | 30 | 600 | 2000 |
| 3 | 3 | NULL | NULL | 1500 |
Nastavi. Ako agregatna funkcija vrati NULL (na primjer, svi zaposleni nemaju navedenu vrijednost plaće), ili odabir ne uključuje jedan zapis, a u izvještaju za ovaj slučaj trebamo prikazati 0, tada funkcija ISNULL može koristiti za omotavanje agregatnog izraza:
SELECT SUM(Plata), AVG(Plata), -- obradite ukupan iznos koristeći ISNULL ISNULL(SUM(Plata),0), ISNULL(AVG(Plata),0) FROM Employees WHERE DepartmentID=10 -- nepostojeće odjeljenje je ovdje posebno specificirano tako da upit ne vraća nikakve zapise
| (bez naziva kolone) | (bez naziva kolone) | (bez naziva kolone) | (bez naziva kolone) |
|---|---|---|---|
| NULL | NULL | 0 | 0 |
Vjerujem da je vrlo važno razumjeti svrhu svake agregatne funkcije i način na koji izračunavaju, jer... u SQL-u je glavni alat koji se koristi za izračunavanje zbroja.
U ovom slučaju smo pogledali kako se svaka agregatna funkcija ponaša nezavisno, tj. primijenjen je na vrijednosti cijelog skupa zapisa dobijenih naredbom SELECT. Zatim ćemo pogledati kako se te iste funkcije koriste za izračunavanje ukupnih grupa koristeći konstrukciju GROUP BY.
GROUP BY – grupiranje podataka
Prije toga smo već izračunali ukupne vrijednosti za određeni odjel, otprilike na sljedeći način:SELECT COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Plata) SalaryAmount FROM Employees WHERE DepartmentID=3 -- podaci samo za IT odjel
Zamislite sada da je od nas traženo da dobijemo iste brojeve za svaki odjel. Naravno, možemo zasukati rukave i ispuniti isti zahtjev za svaki odjel. Dakle, rečeno i urađeno, pišemo 4 upita:
SELECT "Administration" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=1 -- podaci o administraciji SELECT "Accounting" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT( *) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=2 -- Računovodstveni podaci SELECT "IT" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees WHERE DepartmentID=3 - - podaci o IT odjelu SELECT "Other" Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Plata) SalaryAmount FROM Employees GDJE DepartmentID JE NULL -- i ne zaboravite podatke o freelancerima
Kao rezultat, dobićemo 4 seta podataka:
Imajte na umu da možemo koristiti polja navedena kao konstante - "Administracija", "Računovodstvo", ...
Generalno, dobili smo sve brojeve koje smo tražili, sve kombinujemo u Excel-u i dajemo direktoru.
Direktoru se svidio izvještaj, pa je rekao: „dodajte još jednu kolonu sa podacima o prosječnoj plati“. I kao i uvijek, to treba uraditi vrlo hitno.
Hmmm, šta da radim?! Uz to, zamislimo da imamo ne 3, već 15 odjela.
Upravo za to se koristi konstrukcija GROUP BY u takvim slučajevima:
SELECT DepartmentID, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Plata) SalaryAmount, AVG(Plata) SalaryAvg -- plus ispunjavamo želje direktora FROM Employees GRUPA PO DepartmentID
| DepartmentID | PositionCount | EmplCount | SalaryAmount | SalaryAvg |
|---|---|---|---|---|
| NULL | 0 | 1 | 2000 | 2000 |
| 1 | 1 | 1 | 5000 | 5000 |
| 2 | 1 | 1 | 2500 | 2500 |
| 3 | 2 | 3 | 5000 | 1666.66666666667 |
Dobili smo sve iste podatke, ali sada koristimo samo jedan upit!
Za sada ne obraćajte pažnju na to da smo odjeljenja prikazali u obliku brojeva, onda ćemo naučiti kako sve lijepo prikazati.
U klauzuli GROUP BY možete odrediti nekoliko polja “GROUP BY polje1, polje2, ..., poljeN”, u ovom slučaju će se grupisati po grupama koje formiraju vrijednosti ovih polja “polje1, polje2, .. ., polje N”.
Na primjer, grupišimo podatke po odjelima i pozicijama:
SELECT DepartmentID, PositionID, COUNT(*) EmplCount, SUM(Plata) SalaryAmount FROM Employees GROUP BY DepartmentID, PositionID
Nakon toga se vrši prolazak kroz svaku kombinaciju i kalkulacije agregatnih funkcija:
SELECT COUNT(*) EmplCount, SUM(Salary) SalaryAmount FROM Employees GDJE DepartmentID JE NULL A PositionID JE NULL SELECT COUNT(*) EmplCount, SUM(Plata) SalaryAmount FROM Employees GDJE DepartmentID=1 I PositionID=2 -- ... SELECT COUNT(*) EmplCount, SUM(Plata) SalaryAmount FROM Employees WHERE DepartmentID=3 I PositionID=4
A onda se svi ovi rezultati kombinuju zajedno i daju nam u obliku jednog seta:
Iz glavnog razloga, vrijedi napomenuti da u slučaju grupiranja (GROUP BY), na listi stupaca u bloku SELECT:
- Možemo koristiti samo kolone navedene u bloku GROUP BY
- Možete koristiti izraze s poljima iz bloka GROUP BY
- Možete koristiti konstante, jer ne utiču na rezultat grupisanja
- Sva ostala polja (nisu navedena u bloku GROUP BY) mogu se koristiti samo sa agregatnim funkcijama (COUNT, SUM, MIN, MAX, ...)
- Nije potrebno navesti sve stupce iz bloka GROUP BY na listi stupaca SELECT
I demonstracija svega što je rečeno:
SELECT "String konstanta" Const1, -- konstanta u obliku niza 1 Const2, -- konstanta u obliku broja -- izraz koristeći polja koja učestvuju u grupisanju CONCAT("Department No",DepartmentID) ConstAndGroupField, CONCAT( "Department No",DepartmentID ,", Position No. ",PositionID) ConstAndGroupFields, DepartmentID, -- polje sa liste polja koja učestvuju u grupisanju -- PositionID, -- polje koje učestvuje u grupisanju, ne mora se duplirati ovdje COUNT(*) EmplCount, -- broj redova u svakoj grupi -- preostala polja se mogu koristiti samo sa agregatnim funkcijama: COUNT, SUM, MIN, MAX, ... SUM(Plata) Iznos plaće, MIN(ID) MinID FROM Employees GROUP BY DepartmentID, PositionID -- grupiranje po poljima DepartmentID, PositionID
Također je vrijedno napomenuti da se grupisanje može izvršiti ne samo po poljima, već i po izrazima. Na primjer, grupirajmo podatke po zaposlenima, prema godini rođenja:
SELECT CONCAT("Godina rođenja - ",YEAR(Dan rođenja)) YearOfBirthday, COUNT(*) EmplCount FROM Employees GRUPA PO GODINA (Rođendan)
Pogledajmo primjer sa složenijim izrazom. Na primjer, uzmimo gradaciju zaposlenih prema godini rođenja:
ODABIRAJTE SLUČAJ KADA GODINA(rođendan)>=2000 ONDA "od 2000" KADA GODINA(rođendan)>=1990 ONDA "1999-1990" KADA GODINA(rođendan)>=1980 ONDA "1989-1980"=dan GODINA> 1970 ONDA "1979-1970" KADA Rođendan NIJE NULL ONDA "prije 1970" OSTALO "nije navedeno" KRAJ Naziv opsega, COUNT(*) EmplCount OD GRUPE zaposlenih PO SLUČAJU KADA GODINA(Dan rođenja)>=2000 GODINA THG (Rođendan)>=1990 ONDA "1999-1990" KADA GODINA(Rođendan)>=1980 ONDA "1989-1980" KADA GODINA(Rođendan)>=1970 ONDA "1979-1970" KADA ROĐENDAN NIJE "Prethodno"190 ELSE "nije specificirano" KRAJ
| RangeName | EmplCount |
|---|---|
| 1979-1970 | 1 |
| 1989-1980 | 2 |
| nije naznačeno | 2 |
| ranije 1970 | 1 |
One. u ovom slučaju, grupisanje se vrši prema CASE izrazu koji je prethodno izračunat za svakog zaposlenog:
ODABIR ID, SLUČAJ KADA GODINA(rođendan)>=2000 ONDA "od 2000" KADA GODINA(rođendan)>=1990 ONDA "1999-1990" KADA GODINA(rođendan)>=1980 ONDA "1989-1980" GODINA KADA >=1970 ONDA "1979-1970" KADA rođendan NIJE NULL ONDA "prethodno 1970" OSTALO "nije navedeno" KRAJ OD Zaposlenih
I naravno, možete kombinirati izraze s poljima u bloku GROUP BY:
SELECT DepartmentID, CONCAT("Godina rođenja - ",YEAR(Dan rođenja)) YearOfBirthday, COUNT(*) EmplCount FROM Employees GROUP BY (Dan rođenja),DepartmentID -- redoslijed se možda ne podudara s redoslijedom kojim se koriste u blok SELECT ORDER BY DepartmentID, YearOfBirthday -- konačno možemo primijeniti sortiranje na rezultat
Vratimo se našem prvobitnom zadatku. Kao što već znamo, direktoru se izvještaj jako svidio, pa je zamolio da ga radimo svake sedmice kako bi mogao pratiti promjene u kompaniji. Kako ne bismo svaki put u Excelu prekidali digitalnu vrijednost odjela njegovim imenom, koristit ćemo znanje koje već imamo i poboljšati naš upit:
ODABIR SLUČAJ ID odjela KADA 1 ONDA "Administracija" KADA 2 ONDA "Računovodstvo" KADA 3 ONDA "TO" OSTALO "Drugo" KRAJ Info, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Plata) Iznos plaće, AVG(Plaća ) SalaryAvg - plus ispunjavamo želje direktora FROM Employees GROUP BY DepartmentID ORDER BY Info - dodajmo sortiranje po koloni Info radi veće pogodnosti
Iako može izgledati zastrašujuće izvana, ipak je bolje nego što je prvobitno bilo. Nedostatak je što ako kreiraju novo odjeljenje i njegove zaposlenike, onda ćemo morati dodati izraz CASE kako zaposlenici novog odjela ne bi završili u grupi „Ostalo“.
Ali bez obzira na to, vremenom ćemo naučiti sve raditi lijepo, tako da naš odabir ne ovisi o pojavljivanju novih podataka u bazi, već je dinamičan. Dozvolite mi da skočim malo naprijed da pokažem kakve upite pokušavamo napisati:
SELECT ISNULL(dep.Name,"Other") DepName, COUNT(DISTINCT emp.PositionID) PositionCount, COUNT(*) EmplCount, SUM(emp.Salary) SalaryAmount, AVG(emp.Salary) SalaryAvg -- plus ispunite želje direktora FROM Employees emp LEFT JOIN Odjeli dep ON emp.DepartmentID=dep.ID GROUP BY emp.DepartmentID,dep.Name ORDER BY DepName
Generalno, ne brinite - svi su počeli jednostavno. Za sada, samo trebate razumjeti konstrukciju GROUP BY.
Na kraju, pogledajmo kako možete napraviti sažete izvještaje koristeći GROUP BY.
Na primjer, prikažimo zbirnu tabelu raščlanjenu po odjelima, tako da se izračunaju ukupne plaće koje primaju zaposleni raščlanjene po pozicijama:
ODABIR ID odjela, SUM(SLUČAJ KADA ID pozicije=1 ONDA KRAJ PLAĆE) [Računovodje], SUM(SLUČAJ KADA ID pozicije=2 ONDA KRAJ PLAĆE) [Direktori], SUM(SLUČAJ KADA ID pozicije=3 ONDA KRAJ plaće) [Programeri], SUM( CASE WHEN PositionID=4 THEN Plata END) [Viši programeri], SUM(Plata) [Ukupno odjeljenje] FROM Employees GROUP BY DepartmentID
One. slobodni smo koristiti sve izraze unutar agregatnih funkcija.
Naravno, možete ga prepisati koristeći IIF:
ODABIR ID odjela, SUM(IIF(ID=1,Plata,NULL)) [Računovodje], ZBIR(IIF(ID=2,Plata,NULL)) [Direktori], ZBIR(IIF(ID=3,Plata,NULL)) [Programeri], SUM(IIF(PositionID=4,Plata,NULL)) [Viši programeri], SUM(Plata) [Ukupno odjeljenje] FROM Employees GROUP BY DepartmentID
Ali u slučaju IIF-a, moraćemo eksplicitno navesti NULL, koji se vraća ako uslov nije ispunjen.
U sličnim slučajevima, radije koristim CASE bez ELSE bloka umjesto da ponovo pišem NULL. Ali ovo je naravno stvar ukusa, o čemu nema rasprave.
I zapamtimo da se u agregatnim funkcijama NULL vrijednosti ne uzimaju u obzir tokom agregacije.
Za konsolidaciju izvršite nezavisnu analizu primljenih podataka koristeći prošireni zahtjev:
SELECT DepartmentID, CASE WHEN PositionID=1 THEN Pay END [Računovodi], CASE WHEN PositionID=2 THEN Pay END [Directors], CASE WHEN PositionID=3 THEN Plata END [Programeri], CASE WHEN PositionID=4 ONDA ili Plata Programmer END [Seni ], Plata [Ukupno odjeljenje] OD Zaposlenih
| DepartmentID | Računovođa | Direktori | Programeri | Stariji programeri | Ukupno po odjelima |
|---|---|---|---|---|---|
| 1 | NULL | 5000 | NULL | NULL | 5000 |
| 3 | NULL | NULL | 1500 | NULL | 1500 |
| 2 | 2500 | NULL | NULL | NULL | 2500 |
| 3 | NULL | NULL | NULL | 2000 | 2000 |
| 3 | NULL | NULL | 1500 | NULL | 1500 |
| NULL | NULL | NULL | NULL | NULL | 2000 |
I zapamtimo da ako želimo vidjeti nule umjesto NULL, onda možemo obraditi vrijednost koju vraća agregatna funkcija. Na primjer:
ODABIR ID odjela, ISNULL(SUM(IIF(ID=1,Plata,NULL)),0) [Računovodje], ISNULL(SUM(IIF(ID=2,Plata,NULL)),0) [Direktori], ISNULL(SUM (IIF(PositionID=3,Plata,NULL)),0) [Programeri], ISNULL(SUM(IIF(PositionID=4,Plata,NULL)),0) [Viši programeri], ISNULL(SUM(Plata),0 ) [Ukupno odjeljenje] FROM Employees GROUP BY DepartmentID
Sada u svrhu prakse, možete:
- prikazati imena odjela umjesto njihovih identifikatora, na primjer, dodavanjem CASE izraza za obradu DepartmentID u blok SELECT
- dodajte sortiranje po nazivu odjela koristeći ORDER BY
GROUP BY je, zajedno sa agregatnim funkcijama, jedan od glavnih alata koji se koriste za dobijanje zbirnih podataka iz baze podataka, jer se podaci obično koriste u ovom obliku, jer Od nas se obično traži da dostavimo sažete izvještaje, a ne detaljne podatke (listove). I naravno, sve se to vrti oko poznavanja osnovnog dizajna, jer... Prije nego što nešto sažmite (agregirate), prvo morate to ispravno odabrati koristeći “SELECT ... WHERE ...”.
Vježba je ovdje ključna, pa ako vam je cilj razumjeti SQL jezik, ne proučavati ga, već razumjeti - vježbajte, vježbajte, vježbajte, isprobavajući najrazličitije opcije koje možete smisliti.
U početnim fazama, ako niste sigurni u ispravnost dobijenih agregiranih podataka, napravite detaljan odabir koji uključuje sve vrijednosti koje se koriste za agregaciju. I provjerite točnost proračuna ručno koristeći ove detaljne podatke. U ovom slučaju korištenje Excela može biti od velike pomoći.
Recimo da ste došli do ove tačke
Recimo da ste računovođa S.S. Sidorov, koji je odlučio naučiti kako pisati SELECT upite.Pretpostavimo da ste već pročitali ovaj udžbenik do sada i da već sigurni u korištenje svih navedenih osnovnih struktura, tj. možeš:
- Odaberite detaljne podatke koristeći klauzulu WHERE iz jedne tabele
- Znati kako koristiti agregatne funkcije i grupiranje iz jedne tablice
Da, ali nisu uzeli u obzir da još ne znate da gradite upite iz više tabela, već samo iz jedne, tj. ne znaš kako da uradiš nešto ovako:
SELECT emp.*, -- vratite sva polja tabele Employees dep.Name DepartmentName, -- u ova polja dodajte polje Name iz tabele Departments pos.Name PositionName -- i takođe dodajte polje Name iz tabele Positions FROM Employees emp LEFT JOIN Odjeli dep ON emp.DepartmentID=dep.ID LEFT JOIN Pozicije pos ON emp.PositionID=pos.ID
Uprkos činjenici da ne znate kako se to radi, vjerujte mi, sjajni ste i već ste postigli toliko toga.
Dakle, kako možete iskoristiti svoje trenutno znanje i postići još produktivnije rezultate?! Iskoristimo moć kolektivne inteligencije - idite kod programera koji rade za vas, tj. Andreevu A.A., Petrovu P.P. ili Nikolaev N.N., i zamolite nekog od njih da vam napiše pogled (VIEW ili samo „Pregled“, pa mislim da će vas čak i brže razumeti), koji će, pored glavnih polja iz tabele Zaposleni, takođe vratiti polja sa “Naziv odeljenja” i “Naziv pozicije”, koja vam sada toliko nedostaju za nedeljni izveštaj kojim vas je natovario Ivanov I.I.
Jer Sve ste tačno objasnili, onda su IT ljudi odmah shvatili šta žele od njih i kreirali, posebno za vas, pogled pod nazivom ViewEmployeesInfo.
Pretpostavljamo da ne vidite sljedeću naredbu, jer... IT ljudi rade ovo:
CREATE VIEW ViewEmployeesInfo KAO SELECT emp.*, -- vratite sva polja tabele Employees dep.Name DepartmentName, -- dodajte polje Name iz tabele Departments pos.Name PositionName u ova polja -- i takođe dodajte polje Name iz Tabela pozicija FROM Employees emp LEFT JOIN Odjeli dep ON emp.DepartmentID=dep.ID LEFT JOIN Pozicije pos ON emp.PositionID=pos.ID
One. za vas, sve ovo, iako zastrašujuće i nerazumljivo, tekst ostaje iza kulisa, a informatičari vam daju samo naziv pogleda “ViewEmployeesInfo”, koji vraća sve gore navedene podatke (tj. ono što ste tražili od njih).
Sada možete raditi s ovim prikazom kao sa običnom tablicom:
SELECT * IZ ViewEmployeesInfo
Jer Sada su svi podaci potrebni za izvještaj u jednoj „tablici“ (a la view), možete jednostavno ponoviti svoj sedmični izvještaj:
SELECT DepartmentName, COUNT(DISTINCT PositionID) PositionCount, COUNT(*) EmplCount, SUM(Plata) SalaryAmount, AVG(Plata) SalaryAvg FROM ViewEmployeesInfo emp GROUP BY DepartmentID,DepartmentName ORDER BY DepartmentName
Sada su svi nazivi odjela na mjestu, plus zahtjev je postao dinamičan i mijenjat će se kada se dodaju novi odjeli i njihovi zaposlenici, tj. Sada ne morate ništa ponavljati, već samo jednom sedmično pokrenite zahtjev i pošaljite njegov rezultat direktoru.
One. za vas u ovom slučaju, kao da se ništa nije promenilo, nastavljate da radite na isti način sa jednom tabelom (samo bi bilo ispravnije reći sa ViewEmployeesInfo pogledom), koja vraća sve podatke koji su vam potrebni. Zahvaljujući pomoći IT ljudi, detalji o dobijanju DepartmentName i PositionName ostali su za vas u crnoj kutiji. One. Pogled vam izgleda kao obična tabela, zamislite ga kao poboljšanu verziju tabele Zaposleni.
Kreirajmo izjavu kao primjer kako biste se uvjerili da je sve zaista kako sam rekao (da cijeli uzorak dolazi iz jednog pogleda):
ODABERITE ID, Ime, Platu IZ ViewEmployeesInfo GDJE Plata NIJE NULL I Plata>0 RED PO Imenu
Nadam se da vam je ovaj zahtjev jasan.
Upotreba pogleda u nekim slučajevima omogućava značajno proširenje granica korisnika koji mogu pisati osnovne SELECT upite. U ovom slučaju, pogled je ravna tabela sa svim podacima koji su potrebni korisniku (za one koji razumiju OLAP, ovo se može usporediti s približnom sličnošću OLAP kocke sa činjenicama i dimenzijama).
Izvod iz Wikipedije. Iako je SQL trebao biti alat za krajnjeg korisnika, na kraju je postao toliko složen da je postao programerski alat.
Kao što vidite, dragi korisnici, SQL jezik je prvobitno zamišljen kao alat za vas. Dakle, sve je u vašim rukama i željama, ne puštajte.
IMATI – nametanje uslova uzorkovanja na grupisane podatke
Zapravo, ako razumete šta je grupisanje, onda nema ništa komplikovano sa IMATI. HAVING je donekle sličan WHERE, samo ako se uvjet WHERE primjenjuje na detaljne podatke, tada se uvjet HAVING primjenjuje na već grupisane podatke. Iz tog razloga, u uslovima bloka HAVING, možemo koristiti ili izraze sa poljima uključenim u grupisanje, ili izraze zatvorene u agregatne funkcije.Pogledajmo primjer:
ODABERITE ID odjela, SUM(Plata) Iznos plaće IZ GRUPE zaposlenih PO ID odjela KOJI IMA SUMU(Platu)>3000
| DepartmentID | SalaryAmount |
|---|---|
| 1 | 5000 |
| 3 | 5000 |
One. Ovaj zahtjev nam je vratio grupisane podatke samo za one odjele u kojima ukupna plata svih zaposlenih prelazi 3000, tj. "SUM(Plata)>3000".
One. ovdje prije svega dolazi do grupiranja i izračunavaju se podaci za sva odjeljenja:
SELECT DepartmentID, SUM(Plata) SalaryAmount FROM Employees GROUP BY DepartmentID -- 1. dobiti grupisane podatke za sve odjele
I uvjet specificiran u bloku HAVING primjenjuje se na ove podatke:
ODABERITE ID odjela, SUM(Plata) Iznos plate IZ GRUPE zaposlenih PO ID odjela -- 1. dobijete grupisane podatke za sve odjele KOJI IMAJU SUM(platu)>3000 -- 2. uvjet za filtriranje grupisanih podataka
U uslovu HAVING, takođe možete izgraditi složene uslove koristeći AND, OR i NOT operatore:
ODABERITE ID odjela, SUM(Plata) Iznos plaće IZ GRUPE zaposlenih PO ID odjela KOJI IMA ZVONU (Platu)>3000 I COUNT(*)<2 -- и число людей меньше 2-х
Kao što možete vidjeti ovdje, agregatna funkcija (pogledajte “COUNT(*)”) može biti specificirana samo u bloku HAVING.
Shodno tome, možemo prikazati samo broj odjela koji potpada pod uslov IMATI:
ODABERITE ID odjela IZ GRUPE zaposlenika PO ID odjela KOJI IMA SUMU (Platu)>3000 I BROJ(*)<2 -- и число людей меньше 2-х
Primjer korištenja uslova HAVING na polju uključenom u GROUP BY:
SELECT DepartmentID, SUM(Plata) SalaryAmount OD Zaposlenih GRUPA PO DepartmentID -- 1. napravite grupisanje IMAJUĆI DepartmentID=3 -- 2. primijeniti filter na rezultat grupisanja
Ovo je samo primjer, jer... u ovom slučaju, bilo bi logičnije provjeriti kroz uvjet WHERE:
SELECT DepartmentID, SUM(Plata) SalaryAmount FROM Employees WHERE DepartmentID=3 -- 1. filtrirajte detaljne podatke GROUP BY DepartmentID -- 2. grupirajte samo po odabranim zapisima
One. prvo filtrirajte zaposlene po odjeljenju 3, pa tek onda napravite kalkulaciju.
Bilješka. U stvari, iako ova dva upita izgledaju različito, DBMS optimizator ih može izvršiti identično.
Mislim da se tu priča o IMATI uslove može završiti.
Hajde da sumiramo
Sumirajmo podatke dobijene u drugom i trećem dijelu i razmotrimo konkretnu lokaciju svake strukture koju smo proučavali i naznačimo redoslijed njihove implementacije:| Struktura/Blok | Nalog za izvršenje | Funkcija izvršena |
|---|---|---|
| SELECT povratne izraze | 4 | Vraćanje podataka primljenih na zahtjev |
| IZ izvora | 0 | U našem slučaju, ovo su svi redovi tabele |
| WHERE uvjet za preuzimanje iz izvora | 1 | Odabrani su samo redovi koji odgovaraju uslovu |
| GROUP BY grupiranje izraza | 2 | Kreirajte grupe na osnovu specificiranog izraza za grupisanje. Izračunavanje agregiranih vrijednosti za ove grupe koje se koriste u blokovima SELECT ili HAVING |
| IMAJUĆI filter na grupisanim podacima | 3 | Filtriranje je primijenjeno na grupisane podatke |
| ORDER BY izraz sortiranja rezultata | 5 | Sortiraj podatke prema navedenom izrazu |
Naravno, takođe možete primijeniti DISTINCT i TOP klauzule koje ste naučili u 2. dijelu na grupisane podatke.
Ovi prijedlozi u ovom slučaju će se primijeniti na konačni rezultat:
ODABIR NA VRH 1 -- 6. primijenjen zadnji SUM(Plata) Iznos plate OD Zaposlenih GRUPA PO ID-u odjela IMAJUĆI SUMU(Platu)>3000 RED PO ID-u odjela -- 5. sortirajte rezultat
Sami analizirajte kako su se ovi rezultati pokazali.
Zaključak
Glavni cilj koji sam postavio u ovom dijelu je da vam otkrijem suštinu agregatnih funkcija i grupisanja.Ako nam je osnovni dizajn omogućio da dobijemo potrebne detaljne podatke, onda nam je primjena agregatnih funkcija i grupiranja na te detaljne podatke dala mogućnost da dobijemo zbirne podatke o njima. Dakle, kao što vidite, ovde je sve bitno, jer... jedno se oslanja na drugo - bez poznavanja osnovnog dizajna nećemo moći, na primjer, da pravilno odaberemo podatke iz kojih trebamo izračunati rezultate.
Ovdje namjerno pokušavam prikazati samo osnove kako bih usmjerio pažnju početnika na najvažnije strukture i ne bih ih preopteretio nepotrebnim informacijama. Dobro razumevanje osnovnih konstrukcija (o kojima ću nastaviti da pričam u narednim delovima) daće vam mogućnost da rešite skoro svaki problem koji uključuje preuzimanje podataka iz RDB-a. Osnovne konstrukcije naredbe SELECT primjenjive su u istom obliku u gotovo svim DBMS-ovima (razlike su uglavnom u detaljima, na primjer, u implementaciji funkcija - za rad sa stringovima, vremenom itd.).
Nakon toga, solidno poznavanje baze će vam dati priliku da sami lako naučite različite ekstenzije SQL jezika, kao što su:
- GRUPA PREMA GRUPANJU (…), GRUPA PO GRUPOVIM SETOVIMA(…), …
- PIVOT, UNPIVOT
- i tako dalje.
Ako radite prve korake u SQL-u, onda se prije svega fokusirajte na učenje osnovnih konstrukcija, jer Kada budete imali bazu, sve ostalo će vam biti mnogo lakše razumjeti, i to sami. Prije svega, potrebno je temeljito razumjeti mogućnosti SQL jezika, tj. koje vrste operacija generalno dozvoljava izvođenje na podacima. Prenošenje informacija početnicima u obimnom obliku je još jedan razlog zašto ću prikazati samo najvažnije (gvozdene) strukture.
Sretno u učenju i razumijevanju SQL jezika.
četvrti dio -
Tim CASE vam omogućava da odaberete za izvesti jedan od nekoliko komandnih sekvenci. Ova konstrukcija je prisutna u SQL standardu od 1992. godine, iako nije bila podržana u Oracle SQL-u do Oracle8i, iu PL/SQL-u do Oracle9i Release 1. Počevši od ove verzije, podržani su sljedeći tipovi CASE naredbi:
- Jednostavna komanda CASE - povezuje jednu ili više sekvenci PL/SQL naredbi s odgovarajućim vrijednostima (sekvenca koja će se izvršiti bira se na osnovu rezultata evaluacije izraza koji vraća jednu od vrijednosti).
- Traži tim SLUČAJ - odabire jednu ili više sekvenci naredbi koje će se izvršiti u zavisnosti od rezultata testiranja liste Bulovih vrijednosti. Izvršava se niz naredbi povezanih s prvim uvjetom čiji je rezultat testa TRUE.
NULL ili NEPOZNATO?
U članku o naredbi IF, možda ste naučili da rezultat Booleovog izraza može biti TRUE, FALSE ili NULL.
U PL/SQL ova izjava je tačna, ali u širem kontekstu teorije relacija smatra se netačnim govoriti o vraćanju NULL-a iz Booleovog izraza. Relaciona teorija kaže da je poređenje sa NULL u sljedećem obliku:
2 < NULL
proizvodi logički rezultat UNKNOWN, a UNKNOWN nije ekvivalent NULL. Međutim, ne morate previše brinuti o PL/SQL NULL notaciji za UNKNOWN. Međutim, trebali biste znati da je treća vrijednost u trovrijednoj logici NEPOZNATO. I nadam se da nikada nećete upasti u nevolje (kao što sam ja!) koristeći pogrešan termin kada razgovarate o trovrednosnoj logici sa relacionim stručnjacima.
Pored CASE naredbi, PL/SQL također podržava CASE izraze. Ovaj izraz je vrlo sličan naredbi CASE; omogućava vam da odaberete jedan ili više izraza za procjenu. Rezultat CASE izraza je jedna vrijednost, dok je rezultat naredbe CASE izvršenje niza PL/SQL naredbi.
Jednostavne CASE komande
Jednostavna naredba CASE vam omogućava da odaberete između nekoliko sekvenci PL/SQL naredbi koje ćete izvršiti, ovisno o rezultatu evaluacije izraza. Napisano je kako slijedi:
CASE izraz WHEN rezultat_1 THEN commands_1 WHEN rezultat_2 THEN commands_2 ... ELSE commands_else END CASE;
Grana ELSE je ovdje opciona. Prilikom izvršavanja takve naredbe, PL/SQL prvo procjenjuje izraz, a zatim uspoređuje rezultat s rezultatom_1. Ako se podudaraju, onda se izvršavaju naredbe_1. U suprotnom se provjerava vrijednost result_2, itd.
Evo primjera jednostavne naredbe CASE, u kojoj se bonus izračunava ovisno o vrijednosti varijable employee_type:
CASE employee_type WHEN "S" THEN award_salary_bonus(employee_id); WHEN "H" THEN award_hourly_bonus(employee_id); WHEN "C" THEN award_commissioned_bonus(employee_id); ELSE RAISE invalid_employee_type; END CASE;
Ovaj primjer ima eksplicitni odjeljak ELSE, ali općenito nije potreban. Bez sekcije ELSE, PL/SQL kompajler implicitno zamjenjuje sljedeći kod:
ELSE RAISE CASE_NOT_FOUND;
Drugim riječima, ako ne navedete ključnu riječ ELSE i ako nijedan od rezultata u klauzulama WHEN ne odgovara rezultatu izraza u naredbi CASE, PL/SQL izbacuje izuzetak CASE_NOT_FOUND. Ovo je razlika između ove naredbe i IF. Kada ključna riječ ELSE nedostaje u naredbi IF, ništa se ne događa ako uvjet nije ispunjen, dok u naredbi CASE ista situacija rezultira greškom.
Bit će zanimljivo vidjeti kako implementirati logiku obračuna bonusa opisanu na početku poglavlja koristeći jednostavnu naredbu CASE. Na prvi pogled to izgleda nemoguće, ali kreativnim pristupom stvari dolazimo do sljedećeg rješenja:
SLUČAJ TAČNO KADA plata >= 10000 I plata<=20000 THEN give_bonus(employee_id, 1500); WHEN salary >20000 I plata<= 40000 THEN give_bonus(employee_id, 1000); WHEN salary >40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END CASE;
Ovdje je važno da elementi izraz i rezultat mogu biti ili skalarne vrijednosti ili izrazi čiji su rezultati skalarne vrijednosti.
Vraćajući se na naredbu IF...THEN...ELSIF, koja implementira istu logiku, vidjet ćete da komanda CASE ima klauzulu ELSE, dok naredba IF–THEN–ELSIF nema ključnu riječ ELSE. Razlog za dodavanje ELSE je jednostavan: ako nijedan od uslova bonusa nije ispunjen, naredba IF ne radi ništa i bonus je nula. U ovom slučaju, naredba CASE generiše grešku, tako da situacija sa nultom premiju mora biti eksplicitno programirana.
Da biste spriječili greške CASE_NOT_FOUND, osigurajte da je barem jedan od uvjeta istinit za bilo koju vrijednost izraza koji se testira.
Naredba CASE TRUE iznad može nekima izgledati kao fensi trik, ali ona zapravo samo implementira naredbu CASE search, o kojoj ćemo govoriti u sljedećem odjeljku.
CASE naredba pretraživanja
CASE naredba pretraživanja provjerava listu Bulovih izraza; Kada pronađe izraz koji je TRUE, on izvršava niz naredbi povezanih s njim. U suštini, naredba CASE search je analogna naredbi CASE TRUE, čiji je primjer dat u prethodnom dijelu. Naredba pretraživanja CASE ima sljedeću notaciju:
CASE WHEN izraz_1 THEN commands_1 WHEN izraz_2 THEN komanda_2 ... ELSE commands_else END CASE; Idealan je za implementaciju logike obračuna bonusa: SLUČAJ KADA plata >= 10000 I plata<=20000 THEN give_bonus(employee_id, 1500); WHEN salary >20000 I plata<= 40000 THEN give_bonus(employee_id, 1000); WHEN salary >40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END CASE;
Naredba pretraživanja CASE, kao i jednostavna naredba, slijedi sljedeća pravila:
- Izvršenje naredbe završava se odmah nakon što se dovrši niz izvršnih naredbi povezanih s pravim izrazom. Ako je više od jednog izraza istinito, izvršavaju se naredbe povezane s prvim.
- Ključna riječ ELSE nije obavezna. Ako nije specificiran i nijedan izraz nije TRUE, izbacuje se izuzetak CASE_NOT_FOUND.
- KADA se uvjeti provjeravaju po strogo definisanom redoslijedu, od početka do kraja.
Pogledajmo još jednu implementaciju premium logike koja koristi činjenicu da se uslovi WHEN provjeravaju redoslijedom kojim su napisani. Neki izrazi su jednostavniji, ali možemo li reći da je značenje cijele naredbe postalo jasnije?
SLUČAJ KADA plata > 40000 ONDA give_bonus(employee_id, 500); KADA plata > 20000 ONDA give_bonus(employee_id, 1000); KADA plata >= 10000 ONDA give_bonus(employee_id, 1500); ELSE give_bonus(employee_id, 0); END CASE;
Ako je plata određenog zaposlenog 20.000, tada su prva dva uslova LAŽNO, a treći je TAČNO, pa će zaposleni dobiti bonus od 1.500 dolara. Ako je plata 21.000, onda će rezultat drugog uslova biti TRUE i bonus će biti 1.000 dolara. Naredba CASE će završiti na drugoj grani WHEN, a treći uvjet neće biti ni provjeren. Da li ovaj pristup treba koristiti prilikom pisanja CASE komandi je diskutabilno. Bilo kako bilo, imajte na umu da je moguće napisati takvu naredbu, ali je potrebna posebna pažnja prilikom otklanjanja grešaka i uređivanja programa u kojima rezultat ovisi o redoslijedu izraza.
Logika koja zavisi od redosleda homogenih grana WHEN potencijalni je izvor grešaka koje nastaju kada se preurede. Kao primjer, uzmite u obzir sljedeću naredbu CASE pretraživanja, u kojoj, sa vrijednošću plaće od 20.000, testiranje uslova u obje grane WHEN vraća TRUE:
SLUČAJ KADA plata IZMEĐU 10000 I 20000 ONDA give_bonus(employee_id, 1500); KADA plata IZMEĐU 20000 I 40000 ONDA give_bonus(employee_id, 1000); ...
Zamislite da je održavalac ovog programa nepromišljeno preuredio grane WHEN kako bi ih poređao po opadajućem redoslijedu plaće. Ne odbijajte ovu priliku! Programeri često imaju tendenciju da "završe" savršeno funkcionalan kod, vođeni nekim internim idejama o redu. Naredba CASE sa preuređenim klauzulama WHEN izgleda ovako:
SLUČAJ KADA plata IZMEĐU 20000 I 40000 ONDA give_bonus(employee_id, 1000); KADA plata IZMEĐU 10000 I 20000 ONDA give_bonus(employee_id, 1500); ...
Na prvi pogled sve je tačno, zar ne? Nažalost, zbog preklapanja dvije grane WHEN, pojavljuje se podmukla greška u programu. Sada će zaposlenik sa platom od 20 000 dobiti bonus od 1 000 umjesto potrebnih 1 500. Iako je preklapanje između KADA filijala možda poželjno u nekim situacijama, ipak ga treba izbjegavati ako je moguće. Uvijek zapamtite da je redoslijed grananja važan i oduprite se porivu za izmjenom koda koji već radi – „ne popravljajte ono što nije pokvareno.”
Zato što KADA se uslovi testiraju po redu, možete učiniti svoj kod malo efikasnijim postavljanjem grana sa najverovatnijim uslovima na početak liste. Dodatno, ako imate granu sa "skupim" izrazima (na primjer, onima koji zahtijevaju značajno CPU vrijeme i memoriju), oni se mogu postaviti na kraj kako bi se smanjila vjerovatnoća da će biti pregledani. Za detalje pogledajte odjeljak Ugniježđene IF komande.
CASE naredbe za pretraživanje se koriste kada su naredbe koje treba izvršiti definirane skupom Bulovih izraza. Jednostavna naredba CASE se koristi kada se odluka donosi na osnovu rezultata jednog izraza.
Ugniježđene CASE komande
CASE komande, kao i IF komande, mogu biti ugniježđene. Na primjer, ugniježđena naredba CASE pojavljuje se u sljedećoj (prilično zbunjujućoj) implementaciji bonus logike:
SLUČAJ KADA plata >= 10000 ONDA SLUČAJ KADA plata<= 20000 THEN give_bonus(employee_id, 1500); WHEN salary >40000 THEN give_bonus(employee_id, 500); KADA plata > 20000 ONDA give_bonus(employee_id, 1000); END CASE; KADA plata< 10000 THEN give_bonus(employee_id,0); END CASE;
Naredba CASE može koristiti bilo koju naredbu, tako da se interna naredba CASE može lako zamijeniti naredbom IF. Isto tako, bilo koja naredba može biti ugniježđena unutar IF naredbe, uključujući CASE.
CASE izrazi
CASE izrazi obavljaju isti zadatak kao i CASE naredbe, ali ne za izvršne naredbe, već za izraze. Jednostavan izraz CASE odabire jedan od nekoliko izraza za procjenu na osnovu date skalarne vrijednosti. Izraz pretraživanja CASE sekvencijalno procjenjuje izraze na listi sve dok jedan ne dobije vrijednost TRUE, a zatim vraća rezultat svog pridruženog izraza.
Sintaksa za ove dvije vrste CASE izraza je:
Simple_expression_Case:= CASE izraz KADA rezultat_1 ONDA rezultat_izraz_1 KADA rezultat_2 THEN rezultat_izraz_2 ... ELSE rezultat_izraz_else END; Search_expression_Case:= SLUČAJ KADA izraz_1 ONDA rezultat_izraz_1 KADA rezultat_izraz_2 ONDA rezultat_izraz_2 ... ELSE rezultat_izraz_drugo END;
Izraz CASE vraća jednu vrijednost - rezultat izraza odabranog za evaluaciju. Svaka grana WHEN mora biti povezana s jednim rezultirajućim izrazom (ne naredbom). Ne postoji tačka-zarez ili END CASE na kraju izraza CASE. Izraz CASE završava ključnom riječi END.
Sljedeći je primjer jednostavnog izraza CASE koji se koristi u sprezi sa procedurom PUT_LINE paketa DBMS_OUTPUT za prikaz vrijednosti Booleove varijable.
(Podsjetite se da program PUT_LINE ne podržava direktno Boolean tipove.) U ovom primjeru, izraz CASE konvertuje Booleovu vrijednost u niz znakova, koji se zatim ispisuje procedurom PUT_LINE:
DECLARE boolean_true BOOLEAN:= TRUE; boolean_false BOOLEAN:= FALSE; boolean_null BOOLEAN; FUNCTION boolean_to_varchar2 (zastavica U LOGIČKOM) POVRATAK VARCHAR2 JE POČETAK VRAĆA SLUČAJ ZAstavicu KADA TRUE ONDA "Tačno" KADA LAŽ ONDA "Netačno" ELSE "NULL" KRAJ; END; 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)); END;
Za implementaciju logike izračuna bonusa, možete koristiti izraz za pretragu CASE, koji vraća vrijednost bonusa za datu platu:
OBJAVI BROJ plate:= 20000; Employee_id BROJ:= 36325; PROCEDURE give_bonus (emp_id U BROJU, bonus_amt U BROJU) JE BEGIN DBMS_OUTPUT.PUT_LINE(emp_id); DBMS_OUTPUT.PUT_LINE(bonus_amt); END; POČNI dati_bonus(employee_id, SLUČAJ KADA plata >= 10000 I plata<= 20000 THEN 1500 WHEN salary >20000 I plata<= 40000 THEN 1000 WHEN salary >40000 ONDA 500 OSTALO 0 KRAJ); END;
Izraz CASE se može koristiti gdje god je dozvoljena bilo koja druga vrsta izraza. Sljedeći primjer koristi izraz CASE da izračuna premiju, pomnoži je sa 10 i dodijeli rezultat varijabli koju prikazuje DBMS_OUTPUT:
OBJAVI BROJ plate:= 20000; Employee_id BROJ:= 36325; bonus_amount NUMBER; BEGIN bonus_amount:= SLUČAJ KADA plata >= 10000 I plata<= 20000 THEN 1500 WHEN salary >20000 I plata<= 40000 THEN 1000 WHEN salary >40000 ONDA 500 OSTALO 0 KRAJ * 10; DBMS_OUTPUT.PUT_LINE(iznos_bonusa); END;
Za razliku od naredbe CASE, ako nijedna od klauzula WHEN nije istinita, izraz CASE ne daje grešku, već jednostavno vraća NULL.
U ovoj sintaksi, Oracle upoređuje ulazni izraz (e) sa svakim izrazom za poređenje e1, e2, …, en.
Ako je ulazni izraz jednak bilo kojem izrazu za poređenje, izraz CASE vraća odgovarajući izraz rezultata (r).
Ako se ulazni izraz e ne podudara ni sa jednim izrazom za usporedbu, izraz CASE vraća izraz u ELSE klauzuli ako ELSE klauzula postoji, u suprotnom vraća null vrijednost.
Oracle koristi evaluaciju kratkog spoja za jednostavan CASE izraz. To znači da Oracle procjenjuje svaki izraz za poređenje (e1, e2, .. en) samo prije nego uporedi jedan od njih sa ulaznim izrazom (e). Oracle ne procjenjuje sve izraze za poređenje prije nego uporedi bilo koji od njih s izrazom (e). Kao rezultat toga, Oracle nikada ne procjenjuje izraz za poređenje ako je prethodni jednak ulaznom izrazu (e).
Jednostavan primjer CASE izraza
Za demonstraciju ćemo koristiti tabelu proizvoda.
Sljedeći upit koristi izraz CASE za izračunavanje popusta za svaku kategoriju proizvoda, tj. CPU 5%, video kartica 10% i ostale kategorije proizvoda 8%
SELECT CASE kategorija_id KADA 1 ONDA OKRUGLI (list_cijena*0,05,2)-- CPU KADA 2 ONDA OKRUGLI (List_price*0.1,2)-- Video kartica ELSE ROUND (list_price*0.08,2)-- ostale kategorije KRAJ popust OD POREDAK PO |
Imajte na umu da smo koristili funkciju ROUND() da zaokružimo popust na dvije decimale.
Traženi CASE izraz
CASE izraz koji je pretraživao Oracle procjenjuje listu Booleovih izraza kako bi odredio rezultat.
Traženi izraz CASE ima sljedeću sintaksu:
CASE KADA e1ONDA r1 KADA uvjet_1 ONDA rezultat_1 KADA uvjet_2 ONDA rezultat_2 ... KADA uvjet_n ONDA rezultat_n ELSE rezultat KRAJ Parametri ili argumentiizraz Opciono. To je vrijednost koja ti si u poređenju sa listom uslova. (tj.: stanje_1, stanje_2, ... stanje_n) stanje_1, stanje_2, ... stanje_n Uslovi koji moraju biti istog tipa podataka. Uslovi se vrednuju navedenim redosledom. Jednom a stanje ako se utvrdi da je istina, naredba CASE će vratiti rezultat i više neće procjenjivati uslove. rezultat_1, rezultat_2, ... rezultat_n Rezultati koji moraju biti istog tipa podataka. Ovo je vrijednost vraćena jednom a stanje se utvrdi da je istina.PovratakNaredba CASE vraća bilo koji tip podataka kao što je niz, broj, datum, itd. (ALI svi rezultati moraju biti istog tipa podataka u naredbi CASE.) Bilješka
Primjenjuje se naNaredba CASE se može koristiti u sljedećim verzijama Oracle/PLSQL:
PrimjerNaredba CASE se može koristiti u Oracle/PLSQL. Možete koristiti naredbu CASE u SQL izrazu na sljedeći način: (uključuje izraz klauzula) SELECT table_name, CASE owner WHEN "SYS" THEN "Vlasnik je SYS" WHEN "SYSTEM" THEN "Vlasnik je SYSTEM" ELSE "Vlasnik je druga vrijednost" END FROM all_tables; Ili možete napisati SQL naredbu koristeći CASE izraz ovako: (izostavlja izraz klauzula) SELECT table_name, CASE WHEN owner="SYS" THEN "Vlasnik je SYS" WHEN owner="SYSTEM" THEN "Vlasnik je SYSTEM" ELSE "Vlasnik je druga vrijednost" END FROM all_tables; Gornje dvije CASE izjave su ekvivalentne sljedećem IF-THEN-ELSE izrazu: IF owner = "SYS" THEN result:= "Vlasnik je SYS"; ELSIF vlasnik = "SISTEM" THEN rezultat:= "Vlasnik je SISTEM""; ELSE rezultat:= "Vlasnik je druga vrijednost"; END IF; Izraz CASE će usporediti svaku vrijednost vlasnika, jednu po jednu. Jedna stvar koju treba napomenuti je da je klauzula ELSE u naredbi CASE opciona. Mogao si to izostaviti. Pogledajmo gornju SQL naredbu sa izostavljenom klauzulom ELSE. Vaš SQL izraz bi izgledao ovako: SELECT table_name, CASE owner WHEN "SYS" THEN "Vlasnik je SYS" WHEN "SYSTEM" THEN "Vlasnik je SYSTEM" END FROM all_tables; Sa izostavljenom klauzulom ELSE, ako nije utvrđeno da je nijedan uslov istinit, naredba CASE bi vratila NULL. Poređenje 2 uslovaEvo primjera koji pokazuje kako koristiti naredbu CASE za poređenje različitih uslova: ODABERITE SLUČAJ KADA a< b THEN "hello" WHEN d < e THEN "goodbye" END FROM suppliers; Često Postavljena PitanjaPitanje: Možete li kreirati naredbu CASE koja procjenjuje dva različita polja? Želim vratiti vrijednost na osnovu kombinacija u dva različita polja. Odgovor: Da, ispod je primjer iskaza slučaja koji procjenjuje dva različita polja. SELECT supplier_id, CASE WHEN supplier_name = "IBM" i supplier_type = "Hardver" ONDA "Sjeverna kancelarija" KADA supplier_name = "IBM" i supplier_type = "Softver" ONDA "Južna kancelarija" KRAJ OD dobavljača; Dakle, ako je polje Supplier_name IBM a polje supplier_type je Hardver, onda će se naredba CASE vratiti Sjeverna kancelarija. Ako je polje Supplier_name IBM a Supplier_type je Softver, naredba CASE će se vratiti Južna kancelarija.  Najnoviji članci
|
