INFORMACIONI

Funksionet përmbledhëse

Shprehjet e pyetjeve SQL shpesh kërkojnë parapërpunim të të dhënave. Për këtë qëllim përdoren funksione të veçanta dhe shprehjet.

Shumë shpesh ju duhet të zbuloni se sa regjistrime përputhen me një pyetje të veçantë,sa është shuma e vlerave të një kolone të caktuar numerike, vlerat maksimale, minimale dhe mesatare të saj. Për këtë qëllim përdoren të ashtuquajturat funksione përfundimtare (statistikore, agregate). Funksionet përmbledhëse përpunojnë grupe të dhënash të specifikuara, për shembull, nga një klauzolë WHERE. Nëse i përfshini ato në listën e kolonave pas një deklarate SELECT, tabela që rezulton do të përmbajë jo vetëm kolonat e tabelës së bazës së të dhënave, por edhe vlerat e llogaritura nga këto funksione. Më poshtë ështëlista e funksioneve përmbledhëse.

• COUNT (parametër ) kthen numrin e regjistrimeve të specifikuara në parametër. Nëse dëshironi të merrni numrin e të gjitha rekordeve, duhet të specifikoni simbolin yll (*) si parametër. Nëse specifikoni një emër kolone si parametër, funksioni do të kthejë numrin e regjistrimeve në të cilat kjo kolonë ka vlera të tjera nga NULL. Për të zbuluar se sa vlera të ndryshme përmban një kolonë, paraprini emrin e kolonës me fjalën kyçe DISTINCT. Për shembull:

SELECT COUNT(*) FROM Klientët;

SELECT COUNT(Shumë_porosie) FROM Konsumatorët;

ZGJIDH NUMËRIN (Shumë_porosie të dallueshme) NGA Klientët;

Përpjekja për të ekzekutuar pyetjen e mëposhtme do të rezultojë në një mesazh gabimi:

SELECT Rajoni , COUNT(*) FROM Klientët ;

• SUM (parametri ) kthen shumën e vlerave të kolonës së specifikuar në parametër. Parametri mund të jetë gjithashtu një shprehje që përmban emrin e kolonës. Për shembull:

ZGJIDH SHUMEN (Porosi_Shuma) NGA Klientët;

Kjo deklaratë SQL kthen një tabelë me një kolonë, një rekord që përmban shumën e të gjitha vlerave të përcaktuara për kolonën Order_Amount nga tabela e Konsumatorëve.

Le të themi se në tabelën burimore vlerat e kolonës Order_Amount shprehen në rubla, dhe ne duhet të llogarisim shumën totale në dollarë. Nëse kursi aktual i këmbimit është, për shembull, 27.8, atëherë mund të merrni rezultatin e kërkuar duke përdorur shprehjen:

ZGJIDH SHUMEN (Shuma_porosit*27.8) NGA Klientët;

• AVG (parametri ) kthen mesataren aritmetike të të gjitha vlerave të kolonës të specifikuara në parametër. Parametri mund të jetë një shprehje që përmban emrin e kolonës. Për shembull:

SELECT AVG (Shumë_porosie) FROM Customers;

SELECT AVG (Order_Amount*27.8) FROM Clients

KU Rajoni<>"Veriu_3perëndim";

• MAX (parametër ) kthen vlerën maksimale në kolonën e specifikuar në parametër. Parametri mund të jetë gjithashtu një shprehje që përmban emrin e kolonës. Për shembull:

SELECT MAX (Shumë_porosie) NGA Klientët;

ZGJIDH MAX (Shumë_porosie*27.8) NGA Klientët

KU Rajon<>"Veriu_3perëndim";

• MIN (parametër ) kthen vlerën minimale në kolonën e specifikuar në parametër. Parametri mund të jetë një shprehje që përmban emrin e kolonës. Për shembull:

SELECT MIN (Shumë_porosie) NGA Klientët;

SELECT MIN (Shuma e porosisë*27,8) NGA Klientët

KU Rajon<>"Veriu_3perëndim";

Në praktikë, shpesh është e nevojshme të merret një tabelë përfundimtare që përmban vlerat totale, mesatare, maksimale dhe minimale të kolonave numerike. Për ta bërë këtë, duhet të përdorni funksionet e grupimit (GROUP BY) dhe përmbledhjes.

SELECT Rajoni, SUM (Sasia_porosi) NGA Klientët

GRUPI SIPAS Rajoni;

Tabela e rezultateve për këtë pyetje përmban emrat e rajoneve dhe shumat totale (totali) të porosive nga të gjithë klientët nga rajonet përkatëse (Fig. 5).

Tani merrni parasysh një kërkesë për të marrë të gjitha të dhënat përmbledhëse sipas rajonit:

SELECT Rajoni, SUM (Order_Amount), AVG (Shumë_porosie), MAX (Shumë_porosie), MIN (porosia_samount)

NGA Klientët

GRUPI SIPAS Rajoni;

Tabelat origjinale dhe të rezultateve janë paraqitur në Fig. 8. Në shembull, vetëm rajoni Veriperëndimor përfaqësohet në tabelën burimore me më shumë se një regjistrim. Prandaj, në tabelën e rezultateve për të, funksione të ndryshme përmbledhëse japin vlera të ndryshme.

Oriz. 8. Tabela përfundimtare e shumave të porosive sipas rajonit

Kur përdorni funksione përmbledhëse në një listë kolonash në një deklaratë SELECT, titujt e kolonave të tyre përkatëse në tabelën e rezultateve janë Expr1001, Expr1002, e kështu me radhë. (ose diçka e ngjashme, në varësi të zbatimit të SQL). Sidoqoftë, mund të vendosni titujt për vlerat e funksioneve përmbledhëse dhe kolonave të tjera sipas gjykimit tuaj. Për ta bërë këtë, vetëm pas kolonës në deklaratën SELECT, specifikoni një shprehje të formës:

AS kollona_titulli

Fjala kyçe AS (as) do të thotë që në tabelën e rezultateve, kolona përkatëse duhet të ketë një titull të specifikuar pas AS. Titulli i caktuar quhet gjithashtu një pseudonim. Shembulli i mëposhtëm (Figura 9) vendos pseudonimet për të gjitha kolonat e llogaritura:

ZGJIDH rajonin,

SHUMË (Shumë_porosie) AS [Shuma totale e porosisë],

AVG (Sasia_porosi) AS [Shuma mesatare e porosisë],

MAX(Order_Amount) AS Maksimumi,

MIN (porosia_samount) SI Minimum,

NGA Klientët

GRUPI SIPAS Rajoni;

Oriz. 9. Tabela përfundimtare e shumave të porosive sipas rajonit duke përdorur pseudonimet e kolonave

Pseudonimet që përbëhen nga disa fjalë të ndara me hapësira janë të mbyllura në kllapa katrore.

Funksionet përmbledhëse mund të përdoren në klauzolat SELECT dhe HAVING, por ato nuk mund të përdoren në klauzolat WHERE. Operatori HAVING është i ngjashëm me operatorin WHERE, por ndryshe nga WHERE zgjedh regjistrimet në grupe.

Le të themi se dëshironi të përcaktoni se cilat rajone kanë më shumë se një klient. Për këtë qëllim, mund të përdorni pyetjen e mëposhtme:

ZGJIDH rajonin, numërimin (*)

NGA Klientët

GRUPI SIPAS Rajoni QË KA NUMËR (*) > 1;

Funksionet e përpunimit të vlerës

Kur punoni me të dhëna, shpesh duhet t'i përpunoni ato (konvertojeni në lloji i duhur): zgjidhni një nënvarg në një varg, hiqni hapësirat kryesore dhe pasuese, rrumbullakoni një numër, llogaritni rrënjën katrore, përcaktoni koha aktuale etj. Ekzistojnë tre lloje funksionesh në SQL:

• funksionet e vargut;
• funksionet numerike;
• funksionet datë-kohë.

Funksionet e vargut

Funksionet e vargut marrin një varg si parametër dhe kthejnë një varg ose NULL pas përpunimit të tij.

• SUBSTRING (rreshti FROM fillimi)kthen një nënvarg që rezulton nga vargu i specifikuar si parametër linjë . Nënstring fillon me karakterin, numri serial i të cilit është specifikuar në parametrin startues, dhe ka gjatësinë e specifikuar në parametrin gjatësi. Karakteret në varg numërohen nga e majta në të djathtë, duke filluar nga 1. Kllapat katrore këtu tregojnë vetëm se shprehja e mbyllur në to është fakultative. Nëse shprehja PËR gjatësinë nuk përdoret, atëherë një nënvarg nga Filloni dhe deri në fund të rreshtit origjinal. Vlerat e parametrave fillimi dhe gjatësia duhet të zgjidhet në mënyrë që nënvargu i kërkuar të jetë në të vërtetë brenda vargut origjinal. Përndryshe, funksioni SUBSTRING do të kthejë NULL.

Për shembull:

SUBSTRING ("E dashur Masha!" NGA 9 PËR 4) kthen "Masha";

SUBSTRING ("E dashur Masha!" NGA 9) kthen "Masha!";

SUBSTRING ("E dashur Masha!" NGA 15) kthen NULL.

Ju mund ta përdorni këtë funksion në një shprehje SQL, për shembull, si kjo:

SELECT * NGA Klientët

WHERE SUBSTRING (Rajoni NGA 1 PËR 5) = "North";

• UPPER(varg ) konverton të gjitha karakteret e vargut të specifikuar në parametër në shkronja të mëdha.
• LOWER(varg ) konverton të gjitha karakteret e vargut të specifikuar në parametër në shkronja të vogla.
• TRIM (PREJTORËT | TRAILING | TË DYJA ["karakter"] NGA vargu ) heq karakteret kryesore (LEADING), pasuese (TRAILING) ose të dy (DY) nga një varg. Si parazgjedhje, karakteri që do të hiqet është një hapësirë ​​(" "), kështu që mund të hiqet. Më shpesh, ky funksion përdoret për të hequr hapësirat.

Për shembull:

TRIM (DREJTUES " " NGA "qyteti i Shën Petersburgut") rrotullohet "qyteti i Shën Petersburgut";

TRIM(TRALING " " NGA "qyteti i Shën Petersburgut") kthen "qyteti i Shën Petersburgut";

TRIM (DY " " NGA " qyteti Shën Petersburg ") kthen "qytet Shën Petersburg";

TRIM (TË DY NGA "qyteti i Shën Petersburgut") kthen "qytetin e Shën Petersburgut";

TRIM (TË DY "g" NGA "qyteti i Shën Petersburgut") kthen "qytetin e Shën Petersburgut".

Ndër këto funksione, më të përdorurat janë SUBSTRING() AND TRIM().

Funksionet numerike

Funksionet numerike mund të pranojnë të dhëna jo vetëm të tipit numerik si parametër, por gjithmonë të kthejnë një numër ose NULL (vlerë e pacaktuar).

• POZICIONI ( targetString NË varg) kërkon për një ndodhje të vargut të synuar në vargun e specifikuar. Nëse kërkimi është i suksesshëm, kthen numrin e pozicionit të karakterit të tij të parë, përndryshe 0. Nëse vargu i synuar ka gjatësi zero (për shembull, vargu " "), atëherë funksioni kthen 1. Nëse të paktën një nga parametrat është NULL , pastaj kthehet NULL. Karakteret e rreshtit numërohen nga e majta në të djathtë, duke filluar nga 1.

Për shembull:

POSITION("e" IN "Përshëndetje të gjithëve") kthen 5;

POZICIONI ("të gjithë" NË "Përshëndetje të gjithëve") kthen 8;

POSITION(" "Përshëndetje të gjithëve") kthen 1;

POSITION ("Përshëndetje!" NË "Përshëndetje të gjithëve") kthen 0.

Në tabelën Klientët (shih Fig. 1), kolona Adresa përmban, përveç emrit të qytetit, kodin postar, emrin e rrugës dhe të dhëna të tjera. Mund t'ju duhet të zgjidhni të dhënat për klientët që jetojnë në një qytet të caktuar. Pra, nëse doni të zgjidhni të dhënat që lidhen me klientët që jetojnë në Shën Petersburg, mund të përdorni shprehjen e mëposhtme të pyetjes SQL:

SELECT * NGA Klientët

KU POZICIONI (" Shën Petersburg " IN Adresa ) > 0;

Vini re se kjo kërkesë e thjeshtë për marrjen e të dhënave mund të formulohet ndryshe:

SELECT * NGA Klientët

KU Adresa LIKE "%Petersburg%";

• EKSTRAKT (parametër ) nxjerr një element nga një vlerë datë-kohë ose nga një interval. Për shembull:

EKSTRAKT (MUAJ NGA DATA "2005-10-25") kthen 10.

• CHARACTER_LENGTH(varg ) kthen numrin e karaktereve në varg.

Për shembull:

CHARACTER_LENGTH ("Përshëndetje të gjithëve") kthen 11.

• OCTET_LENGTH(varg ) kthen numrin e okteteve (bajt) në varg. Çdo karakter latin ose cirilik përfaqësohet nga një bajt, dhe karakteri i alfabetit kinez përfaqësohet nga dy bajt.
• KARDINALITETI (parametër ) merr një koleksion elementesh si parametër dhe kthen numrin e elementeve në koleksion (numrin kardinal). Një koleksion mund të jetë, për shembull, një grup ose një grup i shumëfishtë që përmban elementë të llojeve të ndryshme.
• ABS (numri ) kthen vlerën absolute të një numri. Për shembull:

ABS (-123) kthen 123;

ABS (2 - 5) kthen 3.

• MO D (numri 1, numri 2 ) kthen pjesën e mbetur të një pjesëtimi të plotë të numrit të parë me të dytin. Për shembull:

MOD(5, h) kthen 2;

MOD(2, h) kthen 0.

• LN (numri ) kthen logaritmin natyror të një numri.
• EXP (numri) kthen numrin (baza e logaritmit natyror në fuqinë e numrit).
• POWER (numri 1, numri 2 ) kthen numrin 1 numri 2 (numri 1 në fuqinë e numrit 2).
• SQRT (numri ) kthen rrënjën katrore të një numri.
• KATI (numri ) kthen numrin më të madh të plotë që nuk e kalon atë të specifikuar nga parametri (rrumbullakimi poshtë). Për shembull:

KATI (5.123) kthen 5.0.

• TAVANI (numri) ose TAVANI (numri ) kthen numrin më të vogël të plotë që nuk është më i vogël se vlera e specifikuar nga parametri i rrumbullakosura lart). Për shembull:

CEIL(5.123) kthen 6.0.

• WIDTH_KOVË (numri 1, numri 2, numri 3, numri 4) kthen një numër të plotë në diapazonin ndërmjet 0 dhe numri 4 + 1. Parametrat numër 2 dhe numri 3 përcaktojnë një interval numerik të ndarë në intervale të barabarta, numri i të cilit përcaktohet nga parametri numër 4. Funksioni përcakton numri i intervalit në të cilin vlera bie numri 1. Nëse numri 1 është jashtë intervalit të specifikuar, atëherë funksioni kthen 0 ose numrin 4 + 1. Për shembull:

WIDTH_BUCKET(3.14, 0, 9, 5) kthen 2.

Funksionet datë-kohë

NË Gjuha SQL Janë tre funksione që kthejnë datën dhe kohën aktuale.

• CURRENT_DATA kthen datën aktuale (lloji DATE).

Për shembull: 2005-06-18.

• CURRENT_TIME (numri ) kthen kohën aktuale (lloji TIME). Parametri i numrit të plotë specifikon saktësinë e paraqitjes së sekondave. Për shembull, një vlerë prej 2 do të përfaqësojë sekonda në të qindtën më të afërt (dy shifra dhjetore):

12:39:45.27.

• CURRENT_TIMESTAMP (numri ) kthen datën dhe kohën (lloji TIMESTAMP). Për shembull, 2005-06-18 12:39:45.27. Parametri i numrit të plotë specifikon saktësinë e paraqitjes së sekondave.

Vini re se data dhe ora e kthyer nga këto funksione nuk është një lloj karakteri. Nëse dëshironi t'i përfaqësoni ato si vargje karakteresh, atëherë duhet të përdorni funksionin e konvertimit të tipit CAST() për ta bërë këtë.

Funksionet e datës së kohës përdoren zakonisht në pyetje për të futur, përditësuar dhe fshirë të dhënat. Për shembull, kur regjistroni informacionin e shitjeve, ju futni data aktuale dhe koha. Pas përmbledhjes së rezultateve për një muaj ose tremujor, të dhënat e shitjeve për periudhën raportuese mund të fshihen.

Shprehjet e llogaritura

Shprehjet e llogaritura ndërtohen nga konstante (numerike, vargje, logjike), funksione, emra fushash dhe lloje të tjera të dhënash duke i lidhur ato me aritmetikë, vargje, logjikë dhe operatorë të tjerë. Nga ana tjetër, shprehjet mund të kombinohen duke përdorur operatorë në shprehje më komplekse (të përbëra). Kllapat përdoren për të kontrolluar rendin në të cilin vlerësohen shprehjet.

Operatorët logjikë DHE, OSE dhe JO dhe funksionet janë diskutuar më parë.

Operatorët aritmetikë:

• + shtim;
• - zbritja;
• * shumëzim;
• / ndarje.

Operatori i vargutvetëm një operator i bashkimit ose i lidhjes së vargut (| |). Në disa implementime SQL (për shembull, Microsoft Access) në vend të (| |) përdoret simboli (+). Operatori i lidhjes shton vargun e dytë në fund të shembullit të parë, shprehjen:

"Sasha" | | "dashuron" | | "Duke tundur"

si rezultat do të kthejë vargun "Sasha e do Masha".

Kur kompozoni shprehje, duhet të siguroheni që operandët e operatorëve të jenë të llojeve të vlefshme. Për shembull, shprehja: 123 + "Sasha" nuk është e vlefshme sepse operatori i mbledhjes aritmetike zbatohet në një operand vargu.

Shprehjet e llogaritura mund të shfaqen më pas ZGJIDH deklaratën, si dhe shprehjet në gjendje të deklaratave WHERE dhe HAVI N.G.

Le të shohim disa shembuj.

Lëreni që tabela e Shitjeve të përmbajë kolonat Lloji i produktit, Sasia dhe Çmimi, dhe ne duam të dimë të ardhurat për çdo lloj produkti. Për ta bërë këtë, thjesht përfshini shprehjen Sasia*Çmimi në listën e kolonave pas deklaratës SELECT:

SELECT_Lloji i produktit, Sasia, Çmimi, Sasia*Çmimi AS

Totali NGA Shitjet;

Kjo përdor fjalën kyçe AS (as) për të specifikuar një pseudonim për kolonën e të dhënave të llogaritura.

Në Fig. Figura 10 tregon tabelën origjinale të Shitjeve dhe tabelën e rezultateve të pyetjes.

Oriz. 10. Rezultati i pyetjes me llogaritjen e të ardhurave për çdo lloj produkti

Nëse dëshironi të zbuloni të ardhurat totale nga shitja e të gjitha mallrave, atëherë thjesht përdorni pyetjen e mëposhtme:

ZGJIDH SHUMEN (Sasia*Çmimi) NGA Shitjet;

Pyetja e mëposhtme përmban shprehje të llogaritura si në listën e kolonave ashtu edhe në gjendjen e klauzolës WHERE. Ai zgjedh nga tabela e shitjeve ato produkte, të ardhurat nga shitjet e të cilave janë më shumë se 1000:

SELECT Lloji_Produkti, Sasia*Çmimi AS Total

NGA Shitjet

KU Sasia*Çmimi > 1000;

Le të supozojmë se dëshironi të merrni një tabelë që ka dy kolona:

Produkti që përmban llojin dhe çmimin e produktit;

Të ardhurat që përmbajnë total.

Meqenëse në tabelën origjinale të shitjeve supozohet se kolona Product_Type është karakter (lloji CHAR) dhe kolona Çmimi është numerike, kur bashkohen (ngjiten) të dhënat nga këto kolona, ​​është e nevojshme të hidhet lloji numerik në një lloj karakteri duke përdorur Funksioni CAST(). Pyetja që kryen këtë detyrë duket kështu (Fig. 11):

ZGJIDH Lloji_Produkti | | " (Çmimi: " | | CAST(Çmimi AS CHAR(5)) | | ")" AS Produkt, Sasia*Çmimi AS Total

NGA Shitjet;

Oriz. 11. Rezultati i një pyetësori që kombinon lloje të ndryshme të dhënash në një kolonë

Shënim. Në Microsoft Access, një pyetje e ngjashme do të duket si kjo:

SELECT Lloji_Produkti + " (Çmimi: " + C Rr (Çmimi) + ")" AS Produkt,

Sasia*Çmimi AS Total

NGA Shitjet;

Shprehjet e kushtëzuara me Deklaratë RAST

Gjuhët konvencionale të programimit kanë operatorë kërcimi të kushtëzuar që ju lejojnë të kontrolloni procesin llogaritës në varësi të faktit nëse një kusht është i vërtetë apo jo. Në SQL, ky operator është CASE (rast, rrethanë, shembull). Në SQL:2003, ky operator kthen një vlerë dhe për këtë arsye mund të përdoret në shprehje. Ai ka dy forma kryesore, të cilat do t'i shikojmë në këtë seksion.

Deklarata CASE me vlera

Deklarata CASE me vlera ka sintaksën e mëposhtme:

CASE checked_value

KUR vlera 1 ATHE rezulton1

KUR vlera 2 PASTAJ rezulton2

. . .

KUR vlera e N-së, atëherë rezultati i N-së

TJETER rezultatiX

Në rast se kontrolluar_vleraështë e barabartë me vlerën 1 , deklarata CASE kthen vlerën rezultat 1 , treguar më pas fjalë kyçe PASTAJ (ajo). Përndryshe, vlera e kontrolluar krahasohet me vlera 2 , dhe nëse ato janë të barabarta, atëherë vlera rezultat2 kthehet. Përndryshe, vlera që testohet krahasohet me vlerën tjetër të specifikuar pas fjalës kyçe WHEN, etj. Nëse tested_value nuk është e barabartë me asnjërën nga këto vlera, atëherë vlera kthehet rezultati X , e specifikuar pas fjalës kyçe ELSE (tjetër).

Fjala kyçe ELSE është fakultative. Nëse mungon dhe asnjë nga vlerat që krahasohen nuk është e barabartë me vlerën që testohet, atëherë deklarata CASE kthen NULL.

Le të themi, bazuar në tabelën Klientët (shih Fig. 1), ju dëshironi të merrni një tabelë në të cilën emrat e rajoneve zëvendësohen me numrat e tyre të kodit. Nëse nuk ka shumë rajone të ndryshme në tabelën burimore, atëherë për të zgjidhur këtë problem është i përshtatshëm të përdorni një pyetje me operatorin CASE:

ZGJIDHni emrin, adresën,

Rajoni i RASTIT

KUR "Moska" APO "77"

KUR "Rajoni i Tverit" APO "69"

. . .

Rajoni TJETËR

Kodi i Rajonit AS

NGA Klientët;

Deklarata CASE me kushtet e kërkimit

Forma e dytë e operatorit CASE përfshin përdorimin e tij kur kërkoni një tabelë për ato regjistrime që plotësojnë një kusht të caktuar:

RAST

KUR kushti 1 PASTAJ rezulton 1

KUR kap2 ATHE rezultojnë2

. . .

KUR kushti N ATHE rezultoi N

TJETER rezultatiX

Deklarata CASE teston nëse kushti 1 është i vërtetë për rekordin e parë në grupin e përcaktuar nga klauzola WHERE, ose të gjithë tabelën nëse WHERE nuk është e pranishme. Nëse po, atëherë CASE kthen rezultatin1. Përndryshe, gjendja 2 kontrollohet për këtë regjistrim. Nëse është e vërtetë, atëherë kthehet vlera rezultati2, etj. Nëse asnjë nga kushtet nuk është e vërtetë, atëherë rezultati i vlerës kthehet X , e specifikuar pas fjalës kyçe ELSE.

Fjala kyçe ELSE është fakultative. Nëse mungon dhe asnjë nga kushtet nuk është e vërtetë, deklarata CASE rrotullohet NULL. Pasi deklarata që përmban CASE ekzekutohet për regjistrimin e parë, ai kalon në regjistrimin tjetër. Kjo vazhdon derisa të përpunohet i gjithë grupi i regjistrimeve.

Supozoni se në një tabelë libri (Titulli, Çmimi), një kolonë është NULL nëse libri përkatës nuk ka magazinë. Kërkesa e mëposhtme kthen një tabelë që shfaq "Jo i disponueshëm" në vend të NULL:

ZGJIDH Titullin,

RAST

KUR QMIMI ËSHTË NULL atherë "Jashtë stok"

TJETËR cast (çmimi SI CHAR(8))

SI Çmimi

NGA Librat;

Të gjitha vlerat në të njëjtën kolonë duhet të jenë të të njëjtit lloj. Prandaj, ky pyetje përdor funksionin e konvertimit të tipit CAST për të transmetuar vlerat numerike kolona Çmimi për llojin e karakterit.

Vini re se gjithmonë mund të përdorni formën e dytë të deklaratës CASE në vend të të parës:

RAST

KUR testuar_value = vlera1 PASTAJ rezultati1

KUR testuar_value = vlera2 PASTAJ rezultat2

. . .

KUR checked_value = vlerë N PASTAJ rezultatN

rezultat TJETER

Funksionet NULLIF dhe COALESCE

Në disa raste, veçanërisht në kërkesat për përditësimin e të dhënave (operatori UPDATE), është i përshtatshëm përdorimi i funksioneve më kompakte NULLIF() (NULL nëse) dhe COALESCE() (kombinoje) në vend të operatorit të rëndë CASE.

Funksioni NULLIF ( vlera 1, vlera 2) kthen NULL nëse vlera e parametrit të parë përputhet me vlerën e parametrit të dytë; në rast mospërputhjeje, vlera e parametrit të parë kthehet e pandryshuar. Kjo do të thotë, nëse vlera e barazisë1 = vlera2 është e vërtetë, atëherë funksioni kthen NULL, përndryshe vlerën vlerën1.

Ky funksionështë ekuivalente me deklaratën CASE në dy format e mëposhtme:

• Vlera CASE1

KUR vlera 2 PASTAJ NULL

TJETER vlera 1

• RAST

KUR vlera 1 = vlera 2 PASTAJ NULL

TJETER vlera 1

Funksioni COALESCE( vlera 1, vlera 2, ..., vlera N) pranon një listë vlerash, të cilat mund të jenë NULL ose NULL. Funksioni kthen një vlerë të caktuar nga një listë ose NULL nëse të gjitha vlerat janë të papërcaktuara.

Ky funksion është ekuivalent me deklaratën e mëposhtme CASE:

RAST

KUR vlera 1 NUK ËSHTË NULL ATËHERË vlera 1

KUR vlera 2 NUK ËSHTË NULL ATËHERË vlera 2

. . .

KUR vlera N NUK ËSHTË NULL ATËHERË vlera N

TJETER NULL

Supozoni se në tabelën Librat (Titulli, Çmimi), kolona Çmimi është NULL nëse libri përkatës nuk ka magazinë. Pyetja e mëposhtme kthen një tabelë ku në vend të I PAVLEFSHËM Shfaqet teksti "Jo i disponueshëm":

SELECT Emrin, COALESCE (CAST(Çmimi AS CHAR(8)),

"Jashtë stok") AS Çmimi

NGA Librat;

Në këtë tutorial do të mësoni se si ta përdorni Funksioni SUM V SQL Server(Transact-SQL) me sintaksë dhe shembuj.

Përshkrim

Në SQL Server (Transact-SQL) Funksioni SUM kthen vlerën totale të një shprehjeje.

Sintaksë

Sintaksë Funksionet SUM në SQL Server (Transact-SQL):

OSE sintaksa për funksionin SUM kur grupohen rezultatet sipas një ose më shumë kolonave është:

Parametrat ose Argumentet

shprehja1, shprehja2, ... shprehja_n janë shprehje që nuk përfshihen në funksionin SUM dhe duhet të përfshihen në klauzolën GROUP BY në fund të deklaratës SQL.
aggregate_expression është kolona ose shprehja që do të grumbullohet.
tabela - tabela nga të cilat dëshironi të merrni shënime. Duhet të ketë të paktën një tabelë të listuar në klauzolën FROM.
KU kushtet - opsionale. Këto janë kushtet që duhet të plotësohen për të dhënat e përzgjedhura.

Aplikacion

Funksioni SUM mund të përdoret në versionet e mëposhtme të SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Shembull me një fushë

Le të shohim disa shembuj të funksionit SUM Server SQL për të kuptuar se si të përdorim funksionin SUM në SQL Server (Transact-SQL).

Për shembull, mund të zbuloni sasinë totale të të gjitha produkteve, sasia e të cilave është më e madhe se 10.

Në këtë shembull të funksionit SUM, ne kemi emërtuar shprehjen SUM(sasi) "Sasia totale". Kur ktheni një grup rezultatesh - "Sasia totale" do të shfaqet si emri i fushës.

Shembull i përdorimit të DISTINCT

Mund të përdorni operatorin DISTINCT në funksionin SUM. Për shembull, deklarata SQL më poshtë kthen pagën totale me vlera unike pagash ku paga është nën 29,000 dollarë në vit.

Nëse dy pagat do të ishin 24,000 dollarë në vit, vetëm një nga ato vlera do të përdorej në funksionin SUM.

Shembull i përdorimit të formulës

Shprehja e përfshirë në funksionin SUM nuk duhet të jetë një fushë e vetme. Ju gjithashtu mund të përdorni formulën. Për shembull, ju mund të llogarisni komisionin total.

Transact-SQL

SELECT SUM(shitje * 0.03) SI "Komisioni Total" NGA porositë;

SELECT SUM (shitjet * 0.03 ) AS "Komisioni Total" NGA porositë;

Shembull i përdorimit të GROUP BY

Në disa raste, do t'ju duhet të përdorni operatorin GROUP BY me funksionin SUM.

SQL - Mësimi 11. Funksionet totale, kolonat e llogaritura dhe pamjet

Funksionet totale quhen gjithashtu funksione statistikore, agregate ose shuma. Këto funksione përpunojnë një grup vargjesh për të numëruar dhe kthyer një vlerë të vetme. Ekzistojnë vetëm pesë funksione të tilla:

• Funksioni AVG() kthen vlerën mesatare të një kolone.


• Funksioni COUNT() kthen numrin e rreshtave në një kolonë.


• Funksioni MAX() kthen vlerën më të madhe në një kolonë.


• Funksioni MIN() kthen vlerën më të vogël në kolonë.


• SUM() Funksioni kthen shumën e vlerave të kolonës.

Tashmë takuam njërin prej tyre - COUNT() - në mësimin 8. Tani le të takojmë të tjerët. Le të themi se donim të dinim çmimin minimal, maksimal dhe mesatar të librave në dyqanin tonë. Pastaj nga tabela e çmimeve ju duhet të merrni vlerat minimale, maksimale dhe mesatare për kolonën e çmimeve. Kërkesa është e thjeshtë:

SELECT MIN(çmimi), MAX(çmimi), AVG(çmimi) FROM çmimet;

Tani, duam të zbulojmë se sa mallra na janë sjellë nga furnizuesi "Shtypshkronja" (id=2). Bërja e një kërkese të tillë nuk është aq e lehtë. Le të mendojmë se si ta kompozojmë atë:

1. Fillimisht, nga tabela e Furnizimeve (në hyrje), zgjidhni identifikuesit (id_incoming) të atyre dërgesave që janë kryer nga furnizuesi "Print House" (id=2):

2. Tani nga tabela e Ditarit të Furnizimit (revista_arritëse) duhet të zgjidhni mallrat (id_produkti) dhe sasitë e tyre (sasia), të cilat janë kryer në dërgesat e gjetura në pikën 1. Kjo do të thotë, pyetja nga pika 1 bëhet e ndërthurur:

3. Tani duhet të shtojmë në tabelën që rezulton çmimet për produktet e gjetura, të cilat ruhen në tabelën Çmimet. Kjo do të thotë, do të na duhet të bashkohemi me tabelat e Revistës Furnizimi (magazine_incoming) dhe Çmimet duke përdorur kolonën id_product:

4. Tabela që rezulton i mungon qartë kolona Sasia, d.m.th kolona e llogaritur. Mundësia për të krijuar kolona të tilla ofrohet në MySQL. Për ta bërë këtë, thjesht duhet të specifikoni në pyetje emrin e kolonës së llogaritur dhe çfarë duhet të llogarisë. Në shembullin tonë, një kolonë e tillë do të quhet përmbledhje dhe do të llogarisë produktin e kolonave të sasisë dhe çmimit. Emri i kolonës së re ndahet me fjalën AS:

SELECT magazine_incoming.id_product, magazine_incoming.sasia, Prices.price, magazine_incoming.sasia*prices.price AS përmbledhje FROM magazine_incoming, çmimet WHERE magazine_incoming.id_product= Prices.id_produkti=coming.

5. E shkëlqyeshme, gjithçka që duhet të bëjmë është të mbledhim kolonën përmbledhëse dhe më në fund të zbulojmë se për sa na i ka sjellë furnizuesi "Shtëpia e Shtypshkronjës". Sintaksa për përdorimin e funksionit SUM() është si më poshtë:

SELECT SUM(emri_kolona) FROM emri_tabeles;

Ne e dimë emrin e kolonës - summa, por nuk e kemi emrin e tabelës, pasi është rezultat i një pyetjeje. Çfarë duhet bërë? Për raste të tilla, MySQL ka Views. Një pamje është një pyetje përzgjedhjeje që i jepet një emër unik dhe mund të ruhet në një bazë të dhënash për përdorim të mëvonshëm.

Sintaksa për krijimin e një pamjeje është si më poshtë:

KRIJO VIEW view_name AS kërkesë;

Le ta ruajmë kërkesën tonë si një pamje me emrin report_vendor:

KRIJO PAMJE report_vendor SI ZGJEDHJE magazine_incoming.id_product, magazine_incoming.sasia, Prices.price, Magazine_incoming.sasia*prices.çmimi SI përmbledhje NGA revista_arritëse, çmimet WHERE magazine_incoming.id_produkt FHERE magazine_incoming.id_produkti në SELECT_incoming. KETU id_shitësi=2 );

6. Tani mund të përdorni funksionin përfundimtar SUM():

ZGJIDH SHUMËN(përmbledhjen) NGA raporti_shitësi;

Pra, arritëm rezultatin, megjithëse për këtë duhej të përdornim pyetje të ndërthurura, bashkime, kolona të llogaritura dhe pamje. Po, ndonjëherë duhet të mendosh për të marrë një rezultat, pa këtë nuk mund të arrish askund. Por ne prekëm dy tema shumë të rëndësishme - kolonat e llogaritura dhe pamjet. Le të flasim për to në më shumë detaje.

Fushat e llogaritura (kolonat)

Duke përdorur një shembull, ne shikuam një fushë të llogaritur matematikore sot. Këtu do të doja të shtoja se mund të përdorni jo vetëm operacionin e shumëzimit (*), por edhe zbritjen (-), mbledhjen (+) dhe ndarjen (/). Sintaksa është si më poshtë:

SELECT emri i kolonës 1, emri i kolonës 2, emri i kolonës 1 * emri i kolonës 2 SI i llogaritur_emri i kolonës NGA emri i tabelës;

Nuanca e dytë është fjala kyçe AS, e kemi përdorur për të vendosur emrin e kolonës së llogaritur. Në fakt, kjo fjalë kyçe përdoret për të vendosur pseudonime për çdo kolonë. Pse është e nevojshme kjo? Për reduktimin dhe lexueshmërinë e kodit. Për shembull, pamja jonë mund të duket si kjo:

KRIJO PAMJE report_vendor SI ZGJIDH A.id_product, A.sasi, B.price, A.sasi*B.price AS përmbledhje NGA revista_incoming AS A, çmimet AS B WHERE A.id_product= B.id_product AND id_incoming= (SELECT id_incoming FROM WHERE id_shitësi=2);

Pajtohuni që kjo është shumë më e shkurtër dhe më e qartë.

Përfaqësimi

Ne kemi parë tashmë sintaksën për krijimin e pamjeve. Pasi të krijohen pamjet, ato mund të përdoren në të njëjtën mënyrë si tabelat. Kjo do të thotë, ekzekutoni pyetje kundër tyre, filtroni dhe renditni të dhënat dhe kombinoni disa pamje me të tjera. Nga njëra anë, kjo është një mënyrë shumë e përshtatshme për të ruajtur që përdoret shpesh pyetje komplekse(si në shembullin tonë).

Por mbani mend se pamjet nuk janë tabela, domethënë, ato nuk ruajnë të dhëna, por vetëm i marrin ato nga tabelat e tjera. Prandaj, së pari, kur të dhënat në tabela ndryshojnë, do të ndryshojnë edhe rezultatet e prezantimit. Dhe së dyti, kur i bëhet një kërkesë një pamjeje, kërkohen të dhënat e kërkuara, domethënë reduktohet performanca e DBMS. Prandaj, nuk duhet të abuzoni me to.

Le të mësojmë të përmbledhim. Jo, këto nuk janë rezultatet e studimit të SQL, por rezultatet e vlerave të kolonave të tabelave të bazës së të dhënave. Funksionet e agregatit SQL funksionojnë në vlerat e një kolone për të prodhuar një vlerë të vetme rezultuese. Funksionet agregate SQL më të përdorura janë SUM, MIN, MAX, AVG dhe COUNT. Është e nevojshme të bëhet dallimi midis dy rasteve të përdorimit të funksioneve agregate. Së pari, funksionet agregate përdoren më vete dhe kthejnë një vlerë të vetme që rezulton. Së dyti, funksionet agregate përdoren me klauzolën SQL GROUP BY, domethënë grupimi sipas fushave (kolonave) për të marrë vlerat që rezultojnë në secilin grup. Le të shqyrtojmë fillimisht rastet e përdorimit të funksioneve agregate pa grupim.

Funksioni SQL SUM

Funksioni SQL SUM kthen shumën e vlerave në kolonën e tabelës së bazës së të dhënave. Mund të aplikohet vetëm për kolonat vlerat e të cilave janë numra. Pyetjet SQL për të marrë shumën që rezulton fillojnë kështu:

ZGJIDH SHUMËN (COLUMN_NAME) ...

Kjo shprehje ndiqet nga FROM (TABLE_NAME), dhe më pas një kusht mund të specifikohet duke përdorur klauzolën WHERE. Për më tepër, emri i kolonës mund të paraprihet nga DISTINCT, që do të thotë se do të numërohen vetëm vlerat unike. Si parazgjedhje, të gjitha vlerat merren parasysh (për këtë ju mund të specifikoni në mënyrë specifike jo DISTINCT, por ALL, por fjala ALL nuk kërkohet).

Nëse dëshironi të ekzekutoni pyetjet e bazës së të dhënave nga ky mësim në MS SQL Server, por kjo DBMS nuk është e instaluar në kompjuterin tuaj, atëherë mund ta instaloni duke përdorur udhëzimet në këtë lidhje .

Së pari do të punojmë me bazën e të dhënave të kompanisë - Company1. Skripti për krijimin e kësaj baze të dhënash, tabelat e saj dhe plotësimin e tabelave me të dhëna është në skedarin në këtë lidhje .

Shembulli 1. Ekziston një bazë e të dhënave e kompanisë me të dhëna për divizionet dhe punonjësit e saj. Tabela e Stafit ka edhe një kolonë me të dhëna për pagat e punonjësve. Zgjedhja nga tabela duket si kjo (për të zmadhuar foton, klikoni mbi të me butonin e majtë të miut):

Për të marrë shumën e të gjitha pagave, ne përdorim pyetjen e mëposhtme (në MS SQL Server - me ndërtimin e mëparshëm USE company1;):

SELECT SUM (Paga) NGA Stafi

Ky pyetje do të kthejë vlerën 287664.63.

Dhe tani . Në ushtrime tashmë kemi filluar të ndërlikojmë detyrat, duke i afruar ato me ato që hasen në praktikë.

Funksioni SQL MIN

Funksioni SQL MIN funksionon gjithashtu në kolona, ​​vlerat e të cilave janë numra dhe kthen minimumin e të gjitha vlerave në kolonë. Ky funksion ka një sintaksë të ngjashme me atë të funksionit SUM.

Shembulli 3. Baza e të dhënave dhe tabela janë të njëjta si në shembullin 1.

Duhet të zbulojmë pagën minimale për punonjësit e departamentit numër 42. Për ta bërë këtë, shkruani pyetjen e mëposhtme (në MS SQL Server - me prefiksin USE company1;):

Kërkesa do të kthejë vlerën 10505.90.

Dhe perseri ushtrim për vendim i pavarur . Në këtë dhe disa ushtrime të tjera, do t'ju duhet jo vetëm tabela e Stafit, por edhe tabela Org, që përmban të dhëna për divizionet e kompanisë:

Shembulli 4. Tabela Org i shtohet tabelës Staff, që përmban të dhëna për departamentet e kompanisë. Shtypni numrin minimal të viteve të punuara nga një punonjës në një departament të vendosur në Boston.

Funksioni SQL MAX

Funksioni SQL MAX funksionon në mënyrë të ngjashme dhe ka një sintaksë të ngjashme, e cila përdoret kur duhet të përcaktoni vlerën maksimale midis të gjitha vlerave në një kolonë.

Shembulli 5.

Duhet të zbulojmë pagën maksimale të punonjësve në departamentin numër 42. Për ta bërë këtë, shkruani pyetjen e mëposhtme (në MS SQL Server - me prefiksin USE company1;):

Kërkesa do të kthejë vlerën 18352.80

Eshte koha ushtrime për zgjidhje të pavarur.

Shembulli 6. Ne punojmë përsëri me dy tavolina - Stafi dhe Org. Shfaq emrin e departamentit dhe vlerën maksimale të komisionit të marrë nga një punonjës në departamentin që i përket grupit të departamenteve (Divizioni) Lindor. Përdorni JOIN (bashkimi i tabelave) .

Funksioni SQL AVG

Ajo që thuhet në lidhje me sintaksën për funksionet e mëparshme të përshkruara është gjithashtu e vërtetë për funksionin SQL AVG. Ky funksion kthen mesataren e të gjitha vlerave në një kolonë.

Shembulli 7. Baza e të dhënave dhe tabela janë të njëjta si në shembujt e mëparshëm.

Le të themi se dëshironi të zbuloni kohëzgjatjen mesatare të shërbimit të punonjësve në departamentin numër 42. Për ta bërë këtë, shkruani pyetjen e mëposhtme (në MS SQL Server - me ndërtimin e mëparshëm USE company1;):

Rezultati do të jetë 6.33

Shembulli 8. Ne punojmë me një tavolinë - Stafi. Tregoni pagën mesatare të punonjësve me 4 deri në 6 vjet përvojë.

Funksioni SQL COUNT

Funksioni SQL COUNT kthen numrin e regjistrimeve në një tabelë të bazës së të dhënave. Nëse specifikoni SELECT COUNT(COLUMN_NAME) ... në pyetje, rezultati do të jetë numri i regjistrimeve pa marrë parasysh ato regjistrime në të cilat vlera e kolonës është NULL (e papërcaktuar). Nëse përdorni një yll si argument dhe filloni një pyetje SELECT COUNT(*) ..., rezultati do të jetë numri i të gjitha rekordeve (rreshtave) të tabelës.

Shembulli 9. Baza e të dhënave dhe tabela janë të njëjta si në shembujt e mëparshëm.

Ju dëshironi të dini numrin e të gjithë punonjësve që marrin komisione. Numri i punonjësve, vlerat e kolonës Comm të të cilëve nuk janë NULL do të kthehen nga pyetja e mëposhtme (në MS SQL Server - me prefiksin USE company1;):

ZGJIDH NUMRIN (Kom) NGA Stafi

Rezultati do të jetë 11.

Shembulli 10. Baza e të dhënave dhe tabela janë të njëjta si në shembujt e mëparshëm.

Nëse dëshironi të zbuloni numrin total të regjistrimeve në tabelë, atëherë përdorni një pyetje me një yll si argument për funksionin COUNT (në MS SQL Server - me ndërtimin e mëparshëm USE company1;):

ZGJIDH NUMRIN (*) NGA Stafi

Rezultati do të jetë 17.

Në tjetrën ushtrim për zgjidhje të pavarur do t'ju duhet të përdorni një nënpyetje.

Shembulli 11. Ne punojmë me një tavolinë - Stafi. Paraqitni numrin e punonjësve në departamentin e planifikimit (Plains).

Funksionet Agregate me SQL GROUP BY

Tani le të shohim përdorimin e funksioneve agregate së bashku me deklaratën SQL GROUP BY. Deklarata SQL GROUP BY përdoret për të grupuar vlerat e rezultateve sipas kolonave në një tabelë të bazës së të dhënave. Faqja e internetit ka një mësim kushtuar veçmas këtij operatori .

Ne do të punojmë me bazën e të dhënave "Ads Portal 1". Skripti për krijimin e kësaj baze të dhënash, tabelën e saj dhe plotësimin e tabelës së të dhënave është në skedarin në këtë lidhje .

Shembulli 12. Pra, ekziston një bazë të dhënash e portalit të reklamave. Ka një tabelë reklamash që përmban të dhëna për reklamat e paraqitura për javën. Kolona Kategoria përmban të dhëna për kategoritë e mëdha të reklamave (për shembull, pasuritë e paluajtshme) dhe kolona Pjesë përmban të dhëna për pjesët më të vogla të përfshira në kategori (për shembull, pjesët e apartamenteve dhe shtëpive verore janë pjesë e kategorisë së pasurive të paluajtshme). Kolona Njësitë përmban të dhëna për numrin e reklamave të dorëzuara, dhe kolona Paratë përmban të dhëna për shumën e parave të marra për paraqitjen e reklamave.

Kategoria	Pjesë	Njësitë	Paratë
Transporti	Makina	110	17600
Pasuri të paluajtshme	Apartamente	89	18690
Pasuri të paluajtshme	Dachas	57	11970
Transporti	Motoçikleta	131	20960
Materiale Ndertimi	Bordet	68	7140
inxhinieri elektrike	TV	127	8255
inxhinieri elektrike	Frigoriferë	137	8905
Materiale Ndertimi	Regjistrat	112	11760
Koha e lirë	librat	96	6240
Pasuri të paluajtshme	Në shtëpi	47	9870
Koha e lirë	Muzikë	117	7605
Koha e lirë	Lojëra	41	2665

Duke përdorur deklaratën SQL GROUP BY, gjeni shumën e parave të fituara duke postuar reklama në secilën kategori. Ne shkruajmë pyetjen e mëposhtme (në MS SQL Server - me ndërtimin e mëparshëm USE adportal1;):

ZGJIDH Kategorinë, SHUMA (Paratë) SI PARA NGA REKLAMAT GRUP SIPAS Kategorisë

Shembulli 13. Baza e të dhënave dhe tabela janë të njëjta si në shembullin e mëparshëm.

Duke përdorur deklaratën SQL GROUP BY, zbuloni se cila pjesë e secilës kategori kishte më shumë listime. Ne shkruajmë pyetjen e mëposhtme (në MS SQL Server - me ndërtimin e mëparshëm USE adportal1;):

ZGJIDH Kategorinë, Pjesë, MAX (njësi) AS Maksimumi NGA REKLAMAT GRUPI SIPAS Kategorisë

Rezultati do të jetë tabela e mëposhtme:

Vlerat totale dhe individuale mund të merren në një tabelë duke kombinuar rezultatet e pyetjeve duke përdorur operatorin UNION .

Bazat e të dhënave relacionale dhe gjuha SQL