Šioje pamokoje sužinosite, kaip naudotis SUM funkcija SQL serveryje (Transact-SQL) su sintaksė ir pavyzdžiais.

apibūdinimas

SQL serveryje (Transact-SQL) SUM funkcija grąžina bendrą išraiškos reikšmę.

Sintaksė

SQL serverio (Transact-SQL) funkcijos SUM sintaksė yra tokia:

ARBA funkcijos SUM sintaksė grupuojant rezultatus pagal vieną ar daugiau stulpelių yra:

Parametrai arba argumentai

išraiška1 , išraiška2 , ... išraiška_n yra išraiškos, kurios nėra įtrauktos į funkciją SUM ir turi būti įtrauktos į GROUP BY sąlygą SQL sakinio pabaigoje.
aggregate_expression yra stulpelis arba išraiška, kuri bus apibendrinta.
lentelės – lentelės, iš kurių norima gauti įrašus. Turi būti bent viena lentelė, nurodyta iš FROM.
KUR sąlygos – neprivaloma. Tai sąlygos, kurias turi atitikti pasirinkti įrašai.

Taikymas

Funkciją SUM galima naudoti šiose SQL Server (Transact-SQL) versijose:
„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“

Pavyzdys su vienu lauku

Pažvelkime į kai kuriuos SQL Server SUM funkcijos pavyzdžius, kad suprastume, kaip naudoti SUM funkciją SQL serveryje (Transact-SQL).

Pavyzdžiui, galite sužinoti bendrą visų produktų, kurių kiekis yra didesnis nei 10, kiekį.

Šiame funkcijos SUM pavyzdyje išraišką SUM(kiekis) pavadinome „Bendras kiekis“. Grąžinant rezultatų rinkinį – kaip lauko pavadinimas bus rodomas „Bendras kiekis“.

DISTINCT naudojimo pavyzdys

Funkcijoje SUM galite naudoti operatorių DISTINCT. Pavyzdžiui, toliau pateiktame SQL sakinyje pateikiamas bendras atlyginimas su unikaliomis atlyginimų reikšmėmis, kai atlyginimas yra mažesnis nei 29 000 USD per metus.

Jei du atlyginimai būtų 24 000 USD per metus, tik viena iš šių verčių būtų naudojama SUM funkcijoje.

Formulės naudojimo pavyzdys

Funkcijoje SUM esanti išraiška nebūtinai turi būti vienas laukas. Taip pat galite naudoti formulę. Pavyzdžiui, galite apskaičiuoti visą komisinį mokestį.

Transact-SQL

PASIRINKITE SUMĄ(pardavimai * 0.03) AS "Visas komisinis" IŠ užsakymų;

PASIRINKITE SUMĄ (pardavimas * 0,03 ) AS "Visas komisinis mokestis" IŠ užsakymų;

GROUP BY naudojimo pavyzdys

Kai kuriais atvejais turėsite naudoti operatorių GROUP BY su funkcija SUM.

SKAIČIAVIMAS

Suvestinės funkcijos

SQL užklausos išraiškoms dažnai reikia išankstinio duomenų apdorojimo. Tam naudojamos specialios funkcijos ir išraiškos.

Gana dažnai reikia išsiaiškinti, kiek įrašų atitinka konkrečią užklausą,kokia yra tam tikro skaitinio stulpelio reikšmių suma, jo didžiausios, minimalios ir vidutinės reikšmės. Tam naudojamos vadinamosios galutinės (statistinės, agregacinės) funkcijos. Suvestinės funkcijos apdoroja įrašų rinkinius, nurodytus, pavyzdžiui, WHERE sąlyga. Jei įtrauksite juos į stulpelių sąrašą po sakinio SELECT, gautoje lentelėje bus ne tik duomenų bazės lentelės stulpeliai, bet ir šių funkcijų apskaičiuotos reikšmės. Toliau nurodytasuvestinių funkcijų sąrašas.

• COUNT (parametras ) grąžina parametre nurodytą įrašų skaičių. Jei norite gauti visų įrašų skaičių, kaip parametrą turėtumėte nurodyti žvaigždutės (*) simbolį. Jei kaip parametrą nurodysite stulpelio pavadinimą, funkcija pateiks įrašų, kuriuose šio stulpelio reikšmės yra kitos nei NULL, skaičių. Norėdami sužinoti, kiek skirtingų reikšmių yra stulpelyje, prieš stulpelio pavadinimą įveskite raktinį žodį DISTINCT. Pavyzdžiui:

SELECT COUNT(*) FROM Clients;

PASIRINKTI SKAIČIUS(Užsakymo_Suma) IŠ klientų;

PASIRINKTI SKAIČIUS(ATSKIRTI Užsakymo_Suma) IŠ klientų;

Bandant paleisti šią užklausą bus rodomas klaidos pranešimas:

PASIRINKTI regioną , COUNT(*) FROM Klientai ;

• SUM (parametras ) grąžina parametre nurodyto stulpelio reikšmių sumą. Parametras taip pat gali būti išraiška, kurioje yra stulpelio pavadinimas. Pavyzdžiui:

PASIRINKTI SUMĄ (Užsakymo_Suma) IŠ klientų;

Šis SQL sakinys pateikia vieno stulpelio vieno įrašo lentelę, kurioje yra visų nustatytų stulpelio Order_Amount verčių suma iš lentelės Klientai.

Tarkime, kad šaltinio lentelėje stulpelio Order_Amount reikšmės išreiškiamos rubliais, o visą sumą reikia apskaičiuoti doleriais. Jei dabartinis valiutos kursas yra, pavyzdžiui, 27,8, tada reikiamą rezultatą galite gauti naudodami išraišką:

PASIRINKTI SUMĄ (Užsakymo_suma*27,8) IŠ klientų;

• AVG (parametras ) pateikia visų parametre nurodytos stulpelio reikšmių aritmetinį vidurkį. Parametras gali būti išraiška su stulpelio pavadinimu. Pavyzdžiui:

SELECT AVG (Order_Amount) FROM Klientai;

PASIRINKITE AVG (užsakymo_suma*27.8) IŠ klientų

KUR Regionas<>"Šiaurės_3 vakarai";

• MAX (parametras ) grąžina didžiausią reikšmę parametre nurodytame stulpelyje. Parametras taip pat gali būti išraiška, kurioje yra stulpelio pavadinimas. Pavyzdžiui:

PASIRINKTI MAX(Užsakymo_Suma) IŠ klientų;

PASIRINKITE MAX(Užsakymo_Suma*27.8) IŠ klientų

KUR Regionas<>"Šiaurės_3 vakarai";

• MIN (parametras ) pateikia mažiausią reikšmę parametre nurodytame stulpelyje. Parametras gali būti išraiška, kurioje yra stulpelio pavadinimas. Pavyzdžiui:

PASIRINKITE MIN(Užsakymo_Suma) IŠ klientų;

PASIRINKTI MIN (Užsakymo suma*27 . 8) IŠ klientų

KUR Regionas<>"Šiaurės_3 vakarai";

Praktiškai dažnai reikia gauti galutinę lentelę, kurioje būtų suminė, vidutinė, maksimali ir mažiausia skaitinių stulpelių reikšmės. Norėdami tai padaryti, turėtumėte naudoti grupavimo (GROUP BY) ir suvestinės funkcijas.

PASIRINKITE regioną, SUM (Užsakymo_suma) IŠ klientų

GROUP BY Region;

Šios užklausos rezultatų lentelėje pateikiami regionų pavadinimai ir visų klientų iš atitinkamų regionų užsakymų bendra (bendra) suma (5 pav.).

Dabar apsvarstykite prašymą gauti visus suvestinius duomenis pagal regioną:

PASIRINKITE regioną, SUM (užsakymo_suma), AVG (užsakymo_suma), MAX(užsakymo_suma), MIN (užsakymo_ suma)

IŠ klientų

GROUP BY Region;

Originalios ir rezultatų lentelės parodytos fig. 8. Pavyzdyje tik šiaurės vakarų regionas šaltinio lentelėje atstovaujamas daugiau nei vienu įrašu. Todėl jo rezultatų lentelėje skirtingos suvestinės funkcijos pateikia skirtingas reikšmes.

Ryžiai. 8. Galutinė užsakymų sumų lentelė pagal regionus

Kai naudojate suvestinės funkcijas stulpelių sąraše SELECT sakinyje, atitinkamų stulpelių antraštės rezultatų lentelėje yra Expr1001, Expr1002 ir pan. (arba kažkas panašaus, priklausomai nuo SQL diegimo). Tačiau savo nuožiūra galite nustatyti suvestinių funkcijų ir kitų stulpelių verčių antraštes. Norėdami tai padaryti, iškart po stulpelio sakinyje SELECT nurodykite formos išraišką:

AS stulpelio_antraštė

Raktinis žodis AS (as) reiškia, kad rezultatų lentelėje atitinkamo stulpelio antraštė turi būti nurodyta po AS. Priskirtas pavadinimas taip pat vadinamas slapyvardžiu. Toliau pateiktame pavyzdyje (9 pav.) nustatyti visų apskaičiuotų stulpelių slapyvardžiai:

PASIRINKTI regioną,

SUMA (Order_Amount) AS [Bendra užsakymo suma],

AVG (Order_Amount) AS [Vidutinė užsakymo suma],

MAX(Order_Amount) AS maksimalus,

MIN (Order_amount) AS minimumas,

IŠ klientų

GROUP BY Region;

Ryžiai. 9. Galutinė užsakymų sumų lentelė pagal regionus, naudojant stulpelių slapyvardžius

Slapyvardžiai, sudaryti iš kelių žodžių, atskirtų tarpais, pateikiami laužtiniuose skliaustuose.

Suvestinės funkcijos gali būti naudojamos SELECT ir HAVING sakiniuose, tačiau jų negalima naudoti WHERE sakiniuose. Operatorius HAVING yra panašus į operatorių WHERE, tačiau skirtingai nei WHERE, jis pasirenka įrašus grupėmis.

Tarkime, kad norite nustatyti, kuriuose regionuose yra daugiau nei vienas klientas. Šiuo tikslu galite naudoti šią užklausą:

PASIRINKTI regioną, skaičius (*)

IŠ klientų

GRUPĖ PAGAL regioną, TURIANT SKAIČIŲ (*) > 1;

Vertės apdorojimo funkcijos

Dirbant su duomenimis dažnai tenka juos apdoroti (konvertuoti į norimą formą): eilutėje pasirinkti poeilelę, pašalinti priekinius ir galinius tarpus, apvalinti skaičių, apskaičiuoti kvadratinę šaknį, nustatyti esamą laiką ir tt SQL turi šių trijų tipų funkcijas:

• stygų funkcijos;
• skaitmeninės funkcijos;
• datos ir laiko funkcijos.

Styginių funkcijos

Styginių funkcijos paima eilutę kaip parametrą ir ją apdorojusios grąžina eilutę arba NULL.

• SUBSTRING (eilutė NUO pradžios)grąžina eilutę, gautą iš eilutės, nurodytos kaip parametras linija . Poeilutė prasideda simboliu, kurio serijos numeris nurodytas pradžios parametre, ir turi ilgį, nurodytą ilgio parametre. Simboliai eilutėje numeruojami iš kairės į dešinę, pradedant nuo 1. Čia esantys laužtiniai skliaustai tik rodo, kad juose esanti išraiška yra neprivaloma. Jei išraiška DĖL ilgio nenaudojamas, tada poeilutė iš Pradėti ir iki pradinės eilutės pabaigos. Parametrų reikšmės pradžia ir ilgis turi būti parinkti taip, kad ieškoma poeilutė iš tikrųjų būtų pradinėje eilutėje. Priešingu atveju funkcija SUBSTRING grąžins NULL.

Pavyzdžiui:

SUBSTRING („Miela Maša!“ NUO 9 FOR 4) grąžina „Maša“;

SUBSTRING („Miela Maša!“ IŠ 9) grąžina „Maša!“;

SUBSTRING („Brangioji Maša!“ NUO 15) grąžina NULL.

Šią funkciją galite naudoti SQL išraiškoje, pavyzdžiui, taip:

PASIRINKTI * IŠ klientų

WHERE SUBSTRING(Regionas FROM 1 FOR 5) = "Šiaurė";

• UPPER(eilutė ) konvertuoja visus parametre nurodytos eilutės simbolius į didžiąsias raides.
• LOWER(eilutė ) konvertuoja visus parametre nurodytos eilutės simbolius į mažąsias raides.
• TRIM (pirmaujantis | POŽIŪRIS | ABU ["simbolis"] FROM eilutės ) iš eilutės pašalina pirmuosius (LEADING), galinius (TRAILING) arba abu (BOTH) simbolius. Pagal numatytuosius nustatymus šalinamas simbolis yra tarpas (" "), todėl jo galima praleisti. Dažniausiai ši funkcija naudojama tarpams pašalinti.

Pavyzdžiui:

TRIM (VEDANTIS „“ IŠ „Sankt Peterburgo miesto“) sukasi „Sankt Peterburgo miestas“;

TRIM(TRALING " " FROM "miestas Sankt Peterburgas") grąžina "miestas Sankt Peterburgas";

TRIM (ABIE " " IŠ " miestas Sankt Peterburgas ") grąžina "miestas Sankt Peterburgas";

TRIM (ABIE IŠ "miestas Sankt Peterburgas") grąžina "miestas Sankt Peterburgas";

TRIM (abi "g" IŠ "miestas Sankt Peterburgas") grąžina "miestas Sankt Peterburgas".

Tarp šių funkcijų dažniausiai naudojamos SUBSTRING() IR TRIM().

Skaitmeninės funkcijos

Skaitinės funkcijos gali priimti ne tik skaitinio tipo duomenis kaip parametrą, bet visada grąžina skaičių arba NULL (neapibrėžta reikšmė).

• POZICIJA ( targetString IN eilutėje) ieško tikslinės eilutės įvykio nurodytoje eilutėje. Jei paieška sėkminga, grąžina pirmojo simbolio pozicijos numerį, kitu atveju 0. Jei tikslinės eilutės ilgis yra nulis (pvz., eilutė " "), tada funkcija grąžina 1. Jei bent vienas iš parametrų yra NULL , tada grąžinama NULL. Eilučių simboliai numeruojami iš kairės į dešinę, pradedant nuo 1.

Pavyzdžiui:

POSITION("e" IN "Sveiki visi") grąžina 5;

POSITION („visi“ „Sveiki visi“) grąžina 8;

POSITION(" " Sveiki visi") grąžina 1;

POSITION ("Sveiki!" IN "Sveiki visi") grąžina 0.

Lentelėje Klientai (žr. 1 pav.) stulpelyje Adresas, be miesto pavadinimo, yra pašto kodas, gatvės pavadinimas ir kiti duomenys. Gali reikėti pasirinkti įrašus apie klientus, gyvenančius konkrečiame mieste. Taigi, jei norite pasirinkti įrašus, susijusius su klientais, gyvenančiais Sankt Peterburge, galite naudoti šią SQL užklausos išraišką:

PASIRINKTI * IŠ klientų

KUR PADĖTIS (" Sankt Peterburgas " IN Adresas ) > 0;

Atminkite, kad ši paprasta duomenų gavimo užklausa gali būti suformuluota kitaip:

PASIRINKTI * IŠ klientų

KUR Adresas LIKE "%Petersburg%";

• EXTRACT (parametras ) išskiria elementą iš datos ir laiko reikšmės arba iš intervalo. Pavyzdžiui:

IŠRAŠAS (MĖNESIO NUO DATOS "2005-10-25") grąžina 10.

• CHARACTER_LENGTH(eilutė ) grąžina simbolių skaičių eilutėje.

Pavyzdžiui:

CHARACTER_LENGTH("Sveiki visi") grąžina 11.

• OCTET_LENGTH(eil ) grąžina oktetų (baitų) skaičių eilutėje. Kiekvienas lotyniškas arba kirilicos simbolis pavaizduotas vienu baitu, o kinų abėcėlės simbolis – dviem baitais.
• KARDINALUMAS (parametras ) ima elementų rinkinį kaip parametrą ir pateikia rinkinio elementų skaičių (pagrindinį skaičių). Kolekcija gali būti, pavyzdžiui, masyvas arba kelių rinkinys, kuriame yra įvairių tipų elementų.
• ABS (numeris ) grąžina absoliučią skaičiaus reikšmę. Pavyzdžiui:

ABS (-123) grąžina 123;

ABS (2–5) grąžina 3.

• MO D (numeris1, numeris2 ) grąžina pirmojo skaičiaus sveikojo skaičiaus dalybos iš antrojo likutį. Pavyzdžiui:

MOD(5, h) grąžina 2;

MOD(2, h) grąžina 0.

• LN (numeris ) grąžina natūralųjį skaičiaus logaritmą.
• EXP (skaičius) grąžina skaičių (natūralaus logaritmo bazė iki skaičiaus laipsnio).
• GALIA (skaičius1, skaičius2 ) grąžina skaičių1 numeris2 (skaičius1 iki skaičiaus2 laipsnio).
• SQRT (numeris ) grąžina skaičiaus kvadratinę šaknį.
• GRINDAS (numeris ) pateikia didžiausią sveikąjį skaičių, neviršijantį parametre nurodyto skaičiaus (apvalinimas žemyn). Pavyzdžiui:

FLORAS (5.123) grąžina 5.0.

• CEIL (skaičius) arba CEILING (numeris ) pateikia mažiausią sveikąjį skaičių, kuris yra ne mažesnis už reikšmę, nurodytą apvalinimo parametru). Pavyzdžiui:

CEIL(5.123) grąžina 6.0.

• WIDTH_BUCKET (skaičius1, skaičius2, skaičius3, skaičius4) grąžina sveikąjį skaičių intervale tarp 0 ir skaičius4 + 1. Parametrai skaičius2 ir skaičius3 nurodo skaitinį intervalą, padalintą į vienodus intervalus, kurių skaičių nurodo parametras number4 Funkcija nustato intervalo, kuriame reikšmė patenka, skaičius skaičius1. Jei skaičius1 yra už nurodyto diapazono ribų, funkcija grąžina 0 arba skaičių 4 + 1. Pavyzdžiui:

WIDTH_BUCKET(3.14; 0; 9; 5) grąžina 2.

Datos ir laiko funkcijos

SQL turi tris funkcijas, kurios grąžina dabartinę datą ir laiką.

• DABARTINĖ DATA grąžina dabartinę datą (įveskite DATE).

Pavyzdžiui: 2005-06-18.

• CURRENT_TIME (skaičius ) grąžina esamą laiką (TIME tipas). Parametras sveikasis skaičius nurodo sekundžių vaizdavimo tikslumą. Pavyzdžiui, 2 reikšmė reikš sekundes iki artimiausio šimtosios dalies (dviejų skaitmenų po kablelio):

12:39:45.27.

• CURRENT_TIMESTAMP (skaičius ) grąžina datą ir laiką (TIMESTAMP tipas). Pavyzdžiui, 2005-06-18 12:39:45.27. Parametras sveikasis skaičius nurodo sekundžių vaizdavimo tikslumą.

Atminkite, kad šių funkcijų grąžinama data ir laikas nėra simbolių tipas. Jei norite juos pavaizduoti kaip simbolių eilutes, tam turėtumėte naudoti CAST() tipo konvertavimo funkciją.

Datos ir laiko funkcijos dažniausiai naudojamos duomenims įterpti, atnaujinti ir ištrinti užklausose. Pavyzdžiui, registruojant pardavimo informaciją, tam skirtame stulpelyje įrašoma dabartinė data ir laikas. Susumavus mėnesio ar ketvirčio rezultatus, ataskaitinio laikotarpio pardavimų duomenys gali būti ištrinti.

Apskaičiuotos išraiškos

Apskaičiuotos išraiškos sudaromos iš konstantų (skaitinių, eilinių, loginių), funkcijų, laukų pavadinimų ir kitų tipų duomenų, sujungiant juos su aritmetiniais, eiliniais, loginiais ir kitais operatoriais. Savo ruožtu išraiškas galima sujungti naudojant operatorius į sudėtingesnes (sudėtines) išraiškas. Skliaustai naudojami posakių vertinimo tvarkai valdyti.

Loginiai operatoriai IR, ARBA ir NE ir funkcijos buvo aptartos anksčiau.

Aritmetiniai operatoriai:

• + papildymas;
• - atimtis;
• * daugyba;
• / padalijimas.

Styginių operatoriustik vienas konkatenacijos arba eilutės sujungimo operatorius (| |). Kai kuriuose SQL diegimuose (pvz., Microsoft Access) vietoj (| |) naudojamas simbolis (+). Sujungimo operatorius prideda antrąją eilutę prie pirmojo pavyzdžio pabaigos, išraiška:

"Saša" | | "myli" | | "Mojuoja"

dėl to grąžins stygą „Sasha loves Masha“.

Kurdami išraiškas turite įsitikinti, kad operatorių operandai yra tinkamo tipo. Pavyzdžiui, išraiška: 123 + "Sasha" negalioja, nes aritmetinis sudėjimo operatorius taikomas eilutės operandui.

Apskaičiuotos išraiškos gali būti rodomos po SELECT sakinio, taip pat WHERE ir HAVI teiginių sąlygų išraiškose N.G.

Pažvelkime į kelis pavyzdžius.

Tegul pardavimo lentelėje yra stulpeliai Produkto tipas, Kiekis ir Kaina, ir mes norime žinoti kiekvieno produkto tipo pajamas. Norėdami tai padaryti, tiesiog įtraukite išraišką Kiekis*Kaina į stulpelių sąrašą po sakinio SELECT:

PASIRINKITE Prekės_tipą, Kiekis, Kaina, Kiekis*Kaina AS

Iš viso IŠ pardavimų;

Tai naudoja AS (as) raktinį žodį, kad nurodytų apskaičiuotų duomenų stulpelio slapyvardį.

Fig. 10 paveiksle parodyta pradinė pardavimo lentelė ir užklausos rezultatų lentelė.

Ryžiai. 10. Užklausos su kiekvienos rūšies produkto pajamų apskaičiavimu rezultatas

Jei norite sužinoti visas pajamas iš visų prekių pardavimo, tiesiog naudokite šią užklausą:

PASIRINKTI SUMĄ (Kiekis*Kaina) IŠ pardavimo;

Šioje užklausoje yra apskaičiuotų išraiškų ir stulpelių sąraše, ir WHERE sąlygos sąlygoje. Jis iš pardavimo lentelės atrenka tuos produktus, kurių pardavimo pajamos yra didesnės nei 1000:

PASIRINKITE Prekės_tipą, Kiekis*Kaina AS Iš viso

IŠ Pardavimų

KUR Kiekis*Kaina > 1000;

Tarkime, kad norite gauti lentelę su dviem stulpeliais:

Produktas, kuriame nurodyta produkto rūšis ir kaina;

Iš viso su pajamomis.

Kadangi daroma prielaida, kad originalioje pardavimo lentelėje stulpelis Product_Type yra simbolis (CHAR tipas), o stulpelis Kaina yra skaitinis, sujungiant (klijuojant) duomenis iš šių stulpelių, reikia išvesti skaitinį tipą į simbolių tipą, naudojant CAST() funkcija. Užklausa, kuri atlieka šią užduotį, atrodo taip (11 pav.):

PASIRINKITE produkto_tipą | | " (Kaina: " | | CAST(Kaina AS CHAR(5)) | | ")" AS Produktas, Kiekis*Kaina AS Iš viso

IŠ Pardavimų;

Ryžiai. 11. Užklausos, sujungiančios skirtingų tipų duomenis viename stulpelyje, rezultatas

Pastaba. „Microsoft Access“ panaši užklausa atrodytų taip:

PASIRINKITE produkto_tipą + " (Kaina: " + C Str (Kaina) + ")" AS prekė,

Kiekis*Kaina AS Iš viso

IŠ Pardavimų;

Sąlyginės išraiškos su CASE pareiškimu

Įprastos programavimo kalbos turi sąlyginius šuolio operatorius, kurie leidžia valdyti skaičiavimo procesą, priklausomai nuo to, ar kuri nors sąlyga yra teisinga, ar ne. SQL šis operatorius yra CASE (atvejis, aplinkybė, atvejis). SQL:2003 šis operatorius grąžina reikšmę, todėl gali būti naudojamas išraiškose. Jis turi dvi pagrindines formas, kurias apžvelgsime šiame skyriuje.

CASE teiginys su reikšmėmis

CASE sakinys su reikšmėmis turi tokią sintaksę:

CASE checked_value

WHEN reikšmė1 THEN rezultatas1

KAI reikšmė2 TAI rezultatas2

. . .

KAI N reikšmė, tada N rezultatas

KITAS rezultatasX

Tuo atveju patikrinta_vertė lygi 1 vertei , CASE sakinys grąžina reikšmę rezultatas1 , nurodyta po raktinio žodžio THEN. Kitu atveju patikrinta_reikšmė lyginama su vertė2 , o jei jie lygūs, tada grąžinama reikšmė result2. Kitu atveju tikrinama reikšmė lyginama su kita reikšme, nurodyta po raktinio žodžio WHEN ir tt Jei tested_value nėra lygi nė vienai iš šių reikšmių, tada vertė grąžinama rezultatas X , nurodyta po ELSE (else) raktinio žodžio.

Raktažodis ELSE yra neprivalomas. Jei jo trūksta ir nė viena iš lyginamų reikšmių nėra lygi testuojamai reikšmei, CASE sakinys grąžina NULL.

Tarkime, remiantis Klientų lentele (žr. 1 pav.), norite gauti lentelę, kurioje regionų pavadinimai būtų pakeisti jų kodų numeriais. Jei šaltinio lentelėje nėra per daug skirtingų regionų, tada norint išspręsti šią problemą, patogu naudoti užklausą su CASE operatoriumi:

PASIRINKITE vardą, adresą,

CASE regionas

KADA "Maskva" TAI "77"

KAI „Tverės sritis“, TAI „69“

. . .

ELSE regionas

AS regiono kodas

IŠ klientų;

CASE pareiškimas su paieškos sąlygomis

Antroji CASE operatoriaus forma apima jo naudojimą ieškant lentelėje tų įrašų, kurie atitinka tam tikrą sąlygą:

CASE

KADA sąlyga1 TAI rezultatas1

KAI gaudymas2 TAI rezultatas2

. . .

KAI sąlyga N, tada rezultatas N

KITAS rezultatasX

CASE teiginys patikrina, ar sąlyga1 yra teisinga pirmajam įrašui aibėje, apibrėžtoje WHERE sąlyga, arba visai lentelei, jei WHERE nėra. Jei taip, tada CASE grąžina rezultatą1. Kitu atveju tikrinama šio įrašo sąlyga2. Jei tai tiesa, tada grąžinama reikšmė result2 ir tt Jei nė viena iš sąlygų nėra teisinga, grąžinama reikšmės rezultatas X , nurodyta po raktinio žodžio ELSE.

Raktažodis ELSE yra neprivalomas. Jei jo trūksta ir nė viena iš sąlygų nėra teisinga, CASE sakinys pasukamas NULL. Po to, kai pirmam įrašui įvykdomas sakinys, kuriame yra CASE, jis pereina prie kito įrašo. Tai tęsiasi tol, kol bus apdorotas visas įrašų rinkinys.

Tarkime, knygų lentelėje (pavadinimas, kaina) stulpelis yra NULL, jei atitinkamos knygos nėra sandėlyje. Ši užklausa pateikia lentelę, kurioje vietoj NULL rodoma „Išparduota“:

PASIRINKTI pavadinimą,

CASE

KAI Kaina NULINĖ, TAI „Išparduota“

ELSE CAST (kaina KAIP CHAR (8))

AS Kaina

IŠ knygų;

Visos to paties stulpelio reikšmės turi būti to paties tipo. Todėl ši užklausa naudoja CAST tipo konvertavimo funkciją, kad perduotų stulpelio Kaina skaitines reikšmes į simbolių tipą.

Atminkite, kad visada galite naudoti antrąją CASE teiginio formą, o ne pirmąją:

CASE

KAI išbandyta_vertė = vertė1 TAI rezultatas1

KAI išbandyta_vertė = vertė2 TAI rezultatas2

. . .

WHEN checked_value = vertė N TADA rezultatasN

KITAS rezultatas

NULLIF ir COALESCE funkcijos

Kai kuriais atvejais, ypač užklausose atnaujinti duomenis (operatorius UPDATE), patogu naudoti kompaktiškesnes funkcijas NULLIF() (NULL if) ir COALESCE() (kombinuoti), o ne sudėtingą CASE operatorių.

NULLIF funkcija ( vertė1, vertė2) grąžina NULL, jei pirmojo parametro reikšmė sutampa su antrojo parametro reikšme, nesutapimo atveju pirmojo parametro reikšmė grąžinama nepakitusi. Tai yra, jei lygybė value1 = value2 yra teisinga, tada funkcija grąžina NULL, kitu atveju reikšmę value1.

Ši funkcija yra lygiavertė CASE teiginiui šiomis dviem formomis:

• CASE reikšmė1

WHEN reikšmė2 THEN NULL

ELSE vertė1

• CASE

KAI reikšmė1 = reikšmė2 TAI NULL

ELSE vertė1

Funkcija COALESCE( vertė1, vertė2, ... , reikšmė N) priima reikšmių sąrašą, kuris gali būti NULL arba NULL. Funkcija grąžina nurodytą reikšmę iš sąrašo arba NULL, jei visos reikšmės neapibrėžtos.

Ši funkcija atitinka šį CASE teiginį:

CASE

KAI 1 reikšmė NĖRA NULIS, TAI reikšmė 1

KAI 2 reikšmė NĖRA NULIS, TAI vertė 2

. . .

KAI reikšmė N NĖRA NULL, TAI reikšmė N

KITAIP NULIS

Tarkime, kad lentelėje Knygos (pavadinimas, kaina) stulpelis Kaina yra NULL, jei atitinkamos knygos nėra sandėlyje. Ši užklausa grąžina lentelę, kurioje vietoj NULL Rodomas tekstas „Išparduota“:

PASIRINKITE Vardą, KOALESCIJĄ (CAST (Kaina kaip CHAR(8)),

"Išparduota") AS Kaina

IŠ knygų;

Tai dar viena įprasta užduotis. Pagrindinis principas yra kaupti vieno atributo (suvestinio elemento) reikšmes pagal kito atributo ar atributų (tvarkos elemento) išdėstymą, galbūt su eilučių dalimis, apibrėžtomis pagal dar vieną atributą ar atributus (skirstymo elementas). . Gyvenime yra daug sukauptų sumų skaičiavimo pavyzdžių, pavyzdžiui, banko sąskaitų likučių apskaičiavimas, prekių prieinamumo sandėlyje ar dabartinių pardavimų duomenų sekimas ir kt.

Prieš SQL Server 2012, rinkiniais pagrįsti sprendimai, naudojami skaičiuojant veikiančias sumas, buvo itin daug išteklių reikalaujantys. Taigi žmonės buvo linkę kreiptis į pasikartojančius sprendimus, kurie buvo lėti, bet kai kuriose situacijose vis tiek greitesni nei rinkiniais pagrįsti sprendimai. Išplėtus langų funkcijų palaikymą sistemoje SQL Server 2012, veikiančias sumas galima apskaičiuoti naudojant paprastą rinkiniu pagrįstą kodą, kuris veikia daug geriau nei senesni T-SQL pagrįsti sprendimai – tiek rinkiniais, tiek kartotiniais. Galėčiau parodyti naują sprendimą ir pereiti prie kito skyriaus; bet kad padėčiau iš tikrųjų suprasti pakeitimo apimtį, aprašysiu senus būdus ir palyginsiu jų našumą su nauju požiūriu. Žinoma, galite laisvai perskaityti tik pirmąją dalį, kurioje aprašomas naujas požiūris, o likusią straipsnio dalį praleisti.

Naudosiu sąskaitų likučius, kad parodyčiau įvairius sprendimus. Štai kodas, kuris sukuria ir užpildo lentelę Operacijos su nedideliu kiekiu bandomųjų duomenų:

NUSTATYTI NOCOUNT ON; NAUDOKITE TSQL2012; JEI OBJECT_ID("dbo.Transactions", "U") NĖRA NULL DROP LENTELĖ dbo.Transactions; KURTI LENTELĘ dbo.Transactions (actid INT NOT NULL, -- skirstymo stulpelis tranid INT NOT NULL, -- užsakymo stulpelis val MONEY NOT NULL, -- priemonė CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)); GO – mažas bandymo duomenų rinkinys INSERT INTO dbo.Transactions(actid, tranid, val) VALUES (1, 1, 4,00), (1, 2, -2,00), (1, 3, 5,00), (1, 4, 2,00), (1, 5, 1,00), (1, 6, 3,00), (1, 7, -4,00), (1, 8, -1,00), (1, 9, -2,00), (1, 10) , -3,00), (2, 1, 2,00), (2, 2, 1,00), (2, 3, 5,00), (2, 4, 1,00), (2, 5, -5,00), (2, 6) , 4,00), (2, 7, 2,00), (2, 8, -4,00), (2, 9, -5,00), (2, 10, 4,00), (3, 1, -3,00), (3, 2, 3,00), (3, 3, -2,00), (3, 4, 1,00), (3, 5, 4,00), (3, 6, -1,00), (3, 7, 5,00), (3, 8, 3,00), (3, 9, 5,00), (3, 10, -3,00);

Kiekviena lentelės eilutė nurodo banko operaciją sąskaitoje. Indėliai stulpelyje val žymimi kaip sandoriai su teigiama verte, o išėmimai – kaip neigiama operacijos vertė. Mūsų užduotis yra apskaičiuoti sąskaitos likutį kiekvienu laiko momentu, kaupiant operacijų sumas eilutėje val, surūšiuotas pagal stulpelį tranid, ir tai turi būti daroma kiekvienai sąskaitai atskirai. Norimas rezultatas turėtų atrodyti taip:

Norint išbandyti abu sprendimus, reikia daugiau duomenų. Tai galima padaryti naudojant tokią užklausą:

DEKLARUOTI @skaičius_skirstymus AS INT = 10, @eilutes_per_skirstymą AS INT = 10000; Sutrumpinti LENTELĘ dbo.Sandoriai; INSERT INTO dbo.Transactions WITH (TABLOKAS) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM() NAUJASIS ID())%5)) IŠ dbo.GetNums(1, @skyrių_skaičius) AS NP KRYPTINIS JOIN dbo.GetNums(1, @eilutės_per_skirstymą) AS RPP;

Galite nustatyti įvestis, kad pakeistumėte skilčių (paskyrų) ir eilučių (operacijų) skaičių.

Rinkiniu pagrįstas sprendimas naudojant langų funkcijas

Pradėsiu nuo rinkiniu pagrįsto sprendimo, kuris naudoja SUM langų agregavimo funkciją. Lango apibrėžimas čia yra gana aiškus: reikia skirstyti langą pagal actid, suskirstyti pagal tranidą ir filtro pagalba pasirinkti eilutes rėmelyje nuo apatinės (NEribota) iki dabartinės. Čia yra atitinkamas prašymas:

PASIRINKITE actid, tranid, val, SUM(val) OVER(SKYRIUS PAGAL actid UŽSAKYTI PAGAL tranido EILUTES TARP NEribotos ANKSTESNĖS IR DABARTINĖS EILTELĖS) KAIP balansą IŠ dbo.Transactions;

Šis kodas ne tik paprastas ir aiškus, bet ir greitas. Šios užklausos planas parodytas paveikslėlyje:

Lentelėje yra sugrupuotas indeksas, kuris atitinka POC reikalavimus ir yra naudojamas langų funkcijoms. Tiksliau, indekso raktų sąrašas yra pagrįstas skaidymo elementu (actid), po kurio seka eilės elementas (tranid), o indeksas taip pat apima visus kitus užklausos stulpelius (val), kad būtų užtikrinta aprėptis. Plane yra užsakytas nuskaitymas, po kurio apskaičiuojamas eilutės numeris vidiniams poreikiams, o tada langų agregatas. Kadangi yra POC indeksas, optimizuotojui nereikia į planą įtraukti rūšiavimo operatoriaus. Tai labai efektyvus planas. Be to, jis keičiasi tiesiškai. Vėliau, kai parodysiu našumo palyginimo rezultatus, pamatysite, kiek šis metodas yra efektyvesnis, palyginti su senesniais sprendimais.

Prieš SQL Server 2012 buvo naudojamos antrinės užklausos arba sujungimai. Kai naudojama antrinė užklausa, vykdomos sumos apskaičiuojamos filtruojant visas eilutes su tokia pačia actid reikšme kaip ir išorinėje eilutėje, o tranidine verte, kuri yra mažesnė arba lygi išorinės eilutės reikšmei. Tada apibendrinimas taikomas filtruotoms eilutėms. Čia yra atitinkamas prašymas:

Panašus metodas gali būti įgyvendintas naudojant ryšius. Naudojamas tas pats predikatas, kaip ir papildomos užklausos WHERE sakinyje sujungimo ON sakinyje. Tokiu atveju tos pačios sąskaitos A N-osios operacijos atveju T1 egzemplioriuje rasite N atitikmenų T2 egzemplioriuje, kurių operacijų numeriai bus nuo 1 iki N. Dėl atitikmenų T1 eilutės yra kartojasi, todėl jums reikia sugrupuoti eilutes visuose T1 elementuose, kad gautumėte informaciją apie dabartinę operaciją, ir taikyti agregaciją atributui val iš T2, kad apskaičiuotumėte einamąją sumą. Užpildytas prašymas atrodo maždaug taip:

PASIRINKTI T1.actid, T1.tranid, T1.val, SUM(T2.val) AS likutis IŠ dbo.Operacijos AS T1 PRISIJUNGTI dbo.Operacijos AS T2 ON T2.actid = T1.actid IR T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

Toliau pateiktame paveikslėlyje parodyti abiejų sprendimų planai:

Atminkite, kad abiem atvejais T1 egzemplioriuje atliekamas visas sugrupuoto indekso nuskaitymas. Tada kiekvienai plano eilutei atliekama paieškos operacija indekso pabaigos puslapio einamosios sąskaitos skyriaus pradžios rodyklėje, kuri nuskaito visas operacijas, kuriose T2.tranid yra mažesnis arba lygus T1. tranidas. Taškas, kuriame įvyksta eilučių sujungimas, planuose šiek tiek skiriasi, tačiau nuskaitytų eilučių skaičius yra toks pat.

Norėdami suprasti, kiek eilučių peržiūrima, turite atsižvelgti į duomenų elementų skaičių. Tegul p yra sekcijų (sąskaitų) skaičius, o r – skilties (sandorio) eilučių skaičius. Tada lentelės eilučių skaičius yra maždaug lygus p*r, jei darysime prielaidą, kad operacijos paskirstytos tolygiai. Taigi aukščiau pateiktas nuskaitymas apima p * r eilutes. Tačiau mus labiausiai domina tai, kas vyksta įdėtųjų kilpų iteratoriuje.

Kiekviename skyriuje plane numatyta perskaityti 1 + 2 + ... + r eilutes, kurios iš viso yra (r + r*2) / 2. Bendras planuose apdorojamų eilučių skaičius yra p*r + p* (r + r2) / 2. Tai reiškia, kad operacijų skaičius plane didėja kvadratu didėjant sekcijos dydžiui, tai yra, jei padidinsite pjūvio dydį f kartus, darbo kiekis padidės maždaug f 2 kartus. Tai yra blogai. Pavyzdžiui, 100 eilučių atitinka 10 tūkstančių eilučių, o tūkstantis eilučių – milijoną ir pan. Paprasčiau tariant, tai labai sulėtėja užklausos vykdymas esant gana dideliam sekcijos dydžiui, nes kvadratinė funkcija auga labai greitai. Tokie sprendimai patenkinamai veikia su keliomis dešimtimis eilučių vienoje sekcijoje, bet ne daugiau.

Kursoriaus sprendimai

Žymekliu pagrįsti sprendimai įgyvendinami tiesiai. Žymeklis deklaruojamas remiantis užklausa, kuri rūšiuoja duomenis pagal actid ir tranid. Po to atliekamas kartotinis perėjimas per žymeklio įrašus. Kai aptinkama nauja paskyra, kintamasis, kuriame yra suvestinė informacija, nustatomas iš naujo. Kiekvienos iteracijos metu prie kintamojo pridedama naujos operacijos suma, o po to eilutė išsaugoma lentelės kintamajame su informacija apie dabartinę operaciją ir dabartinę vykdomos sumos vertę. Po pakartotinio perdavimo grąžinamas lentelės kintamojo rezultatas. Čia yra užbaigto sprendimo kodas:

DEKLARUOTI @Rezultą KAIP LENTELĖ (actid INT, tranid INT, val MONEY, balansas MONEY); SKELBTI @actid AS INT, @prvactid AS INT, @tranid AS INT, @val KAIP PINIGAI, @balance KAIP PINIGAI; DECLARE C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo.Transactions ORDER BY actid, tranid; ATIDARYTI C ATVESTI KITAS IŠ C Į @actid, @tranid, @val; PASIRINKITE @prvactid = @actid, @balansas = 0; WHILE @@fetch_status = 0 BEGIN IF @actid<>@prvactid PASIRINKITE @prvactid = @actid, @balansas = 0; NUSTATYTI @balansas = @balansas + @val; INSERT INTO @Result VALUES(@actid, @tranid, @val, @balance); ATGAUTI KITAS IŠ C Į @actid, @tranid, @val; PABAIGA UŽDARYTI C; ATSKIRTI C; SELECT * FROM @Result;

Užklausos planas naudojant žymeklį parodytas paveikslėlyje:

Šis planas keičiamas tiesiškai, nes duomenys iš indekso nuskaitomi tik vieną kartą tam tikra tvarka. Be to, kiekviena eilutės iš žymeklio gavimo operacija kainuoja maždaug tiek pat. Jei apkrovą, sukurtą apdorojant vieną žymeklio eilutę, laikysime lygia g, šio sprendimo kaina gali būti įvertinta kaip p*r + p*r*g (kaip prisimenate, p yra sekcijų skaičius, o r yra eilučių skaičius skyriuje). Taigi, jei padidinsite eilučių skaičių vienoje sekcijoje f kartų, sistemos apkrova bus p*r*f + p*r*f*g, tai yra, ji augs tiesiškai. Apdorojimo kaina vienai eilutei yra didelė, tačiau dėl tiesinio mastelio keitimo pobūdžio šis sprendimas bus geriau keičiamas, nei įdėtos užklausos ir sujungimais pagrįsti sprendimai dėl šių sprendimų kvadratinio mastelio keitimo. Atlikti našumo matavimai rodo, kad greitesnis žymeklio sprendimas yra keli šimtai eilučių vienoje sekcijoje.

Nepaisant žymekliu pagrįstų sprendimų teikiamų pranašumų, jų apskritai reikėtų vengti, nes jie nėra susiję.

CLR pagrįsti sprendimai

Vienas galimas sprendimas, pagrįstas CLR (bendrosios kalbos vykdymo laikas) iš esmės yra sprendimo forma naudojant žymeklį. Skirtumas tas, kad užuot naudoję T-SQL žymeklį, kuris eikvoja daug išteklių, kad gautumėte kitą eilutę ir kartotųsi, naudojate .NET SQLDataReader ir .NET iteracijas, kurios yra daug greitesnės. Viena iš CLR ypatybių, kuri pagreitina šią parinktį, yra ta, kad gautos eilutės nereikia laikinojoje lentelėje – rezultatai siunčiami tiesiai į iškvietimo procesą. CLR pagrįsto sprendimo logika yra panaši į žymeklio ir T-SQL sprendimo logiką. Čia yra C# kodas, apibrėžiantis sprendimo saugomą procedūrą:

Sistemos naudojimas; naudojant System.Data; naudojant System.Data.SqlClient; naudojant System.Data.SqlTypes; naudojant Microsoft.SqlServer.Server; viešoji dalinė klasė StoredProcedures ( public static void AccountBalces() ( naudojant (SqlConnection conn = new SqlConnection("context connection=true;")) ( SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @" " + "SELECT actid, tranid, val " + "FROM dbo.Transactions " + "ORDER BY actid, tranid;"; SqlMetaData stulpeliai = nauji SqlMetaData; stulpeliai = nauji SqlMetaData("actid" , SqlDbType.Int); stulpeliai = nauji SqlMetaData("tranid" , SqlDbType.Int); stulpeliai .ExecuteReader(); prvactid = actid.SetSqlInt32(0, reader.GetSqlInt32(0)); įrašas.SetSqlInt32(1, skaitytuvas.GetSqlInt32(1)); įrašas.SetSqlMoney(2, val); įrašas.SetSqlMoney(3, likutis); SqlContext.Pipe.SendResultsRow(įrašas); ) SqlContext.Pipe.SendResultsEnd(); ) ) )

Kad galėtumėte vykdyti šią saugomą procedūrą SQL serveryje, pirmiausia turite sukurti sąranką, pavadintą AccountBalances, pagrįstą šiuo kodu ir įdiegti ją į TSQL2012 duomenų bazę. Jei nesate susipažinę su rinkinių diegimu SQL Server, galbūt norėsite perskaityti saugomų procedūrų ir CLR straipsnio skyrių „Saugomos procedūros“.

Jei surinkote pavadinimą „AccountBalances“, o kelias į surinkimo failą yra „C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll“, galite įkelti rinkinį į duomenų bazę ir užregistruoti saugomą procedūrą naudodami šį kodą:

KURTI MONTAVIMĄ AccountBalances IŠ „C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll“; EITI KURTI PROCEDŪRĄ dbo.AccountBalances KAIP IŠORINIS PAVADINIMAS AccountBalances.StoredProcedures.AccountBalances;

Įdiegę surinkimą ir užregistravę procedūrą, galite ją vykdyti naudodami šį kodą:

EXEC dbo.AccountBalances;

Kaip jau sakiau, SQLDataReader yra tik dar viena žymeklio forma, tačiau ši versija turi žymiai mažiau eilučių skaitymo išlaidų nei naudojant tradicinį žymeklį T-SQL. Iteracijos taip pat yra daug greitesnės .NET nei T-SQL. Taigi, CLR pagrįsti sprendimai taip pat keičiasi tiesiškai. Testavimas parodė, kad šio sprendimo našumas tampa didesnis nei sprendimų, naudojančių antrines užklausas ir sujungimus, kai sekcijoje yra daugiau nei 15 eilučių.

Baigę turite paleisti šį valymo kodą:

ATMESTI PROCEDŪRĄ dbo.AccountBalances; DROP ASSEMBLY Sąskaitos likučiai;

Įdėtos iteracijos

Iki šiol parodžiau iteracinius ir rinkiniais pagrįstus sprendimus. Kitas sprendimas yra pagrįstas įdėtomis iteracijomis, kurios yra kartotinių ir rinkiniu pagrįstų metodų hibridas. Idėja yra pirmiausia nukopijuoti eilutes iš šaltinio lentelės (mūsų atveju banko sąskaitų) į laikiną lentelę kartu su nauju atributu, vadinamu rownum, kuris apskaičiuojamas naudojant ROW_NUMBER funkciją. Eilučių numeriai skirstomi pagal actid ir išdėstomi pagal tranidą, todėl pirmajai operacijai kiekvienoje banko sąskaitoje priskiriamas numeris 1, antrajai operacijai – 2 ir t.t. Tada laikinojoje lentelėje sukuriamas sugrupuotas indeksas su raktų sąrašu (rownum, actid). Tada naudojama rekursinė CTE išraiška arba specialiai sukurta kilpa, kad apdorotų vieną eilutę per iteraciją visose paskyrose. Tada einamoji suma apskaičiuojama pridedant vertę, susietą su dabartine eilute, prie vertės, susietos su ankstesne eilute. Štai šios logikos įgyvendinimas naudojant rekursinį CTE:

PASIRINKITE actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; KURTI UNIKALŲ KLASTERINĘ RODEKĄ idx_rownum_actid ON #Transactions(rownum, actid); SU C AS (SELECT 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum = 1 UNION ALL SELECT PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val FROM C AS PRV JOIN #Operacijos AS CUR ON CUR.rownum = PRV.rownum + 1 AND CUR.actid = PRV.actid) PASIRINKITE actid, tranid, val, sumqty FROM C OPTION (MAXRECURSION 0); DROP TABLE #Sandoriai;

Ir tai yra įgyvendinimas naudojant aiškų kilpą:

PASIRINKITE ROW_NUMBER() OVER(SKYRIUS PAGAL actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST(val AS BIGINT) AS sumqty Į #Transactions FROM dbo.Transactions; KURTI UNIKALŲ KLASTERINĘ RODEKĄ idx_rownum_actid ON #Transactions(rownum, actid); SKELBTI @rownum AS INT; NUSTATYTI @rownum = 1; WHILE 1 = 1 PRADĖTI NUSTATYTI @rownum = @rownum + 1; ATNAUJINTI CUR SET sumqty = PRV.sumqty + CUR.val FROM #Transactions AS CUR JOIN #Transactions AS PRV ON CUR.rownum = @rownum IR PRV.rownum = @rownum - 1 IR CUR.actid = PRV.actid; JEI @@eilučių skaičius = 0 PERTRAUKA; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #Sandoriai;

Šis sprendimas užtikrina gerą našumą, kai yra daug skaidinių su nedideliu eilučių skaičiumi viename skaidinyje. Tada pakartojimų skaičius yra mažas, o didžiąją darbo dalį atlieka aibės pagrindu sukurta sprendimo dalis, kuri sujungia eilutes, susietas su vienu eilutės numeriu, su eilutėmis, susietomis su ankstesnės eilutės numeriu.

Kelių eilučių atnaujinimas su kintamaisiais

Sukauptų sumų apskaičiavimo metodai, nurodyti iki šio taško, garantuoja teisingą rezultatą. Šiame skyriuje aprašyta technika yra prieštaringa, nes ji pagrįsta stebima, o ne dokumentuota sistemos elgsena, be to, ji prieštarauja reliatyvumo principams. Didelį jo patrauklumą lemia didelis darbo greitis.

Šis metodas naudoja teiginį UPDATE su kintamaisiais. UPDATE sakinys gali priskirti išraiškas kintamiesiems pagal stulpelio reikšmę, taip pat gali priskirti reikšmes stulpeliuose išraiškai su kintamuoju. Sprendimas pradedamas sukuriant laikiną lentelę, pavadintą Transactions su atributais actid, tranid, val ir balance bei sugrupuotą indeksą su raktų sąrašu (actid, tranid). Tada laikinoji lentelė užpildoma visomis eilėmis iš šaltinio Transactions duomenų bazės, o į visų eilučių balanso stulpelį įrašoma reikšmė 0,00. Tada iškviečiamas UPDATE sakinys su kintamaisiais, susijusiais su laikinąja lentele, kad būtų galima apskaičiuoti einamąsias sumas ir įterpti apskaičiuotą vertę į balanso stulpelį.

Naudojami kintamieji @prevaccount ir @prevbalance, o vertė balanso stulpelyje apskaičiuojama naudojant šią išraišką:

NUSTATYTI @prevbalance = balansas = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END

CASE išraiška patikrina, ar dabartinis ir ankstesnis sąskaitų ID sutampa, ir, jei jie yra, grąžina ankstesnių ir dabartinių verčių sumą balanso stulpelyje. Jei sąskaitų ID skiriasi, grąžinama dabartinė operacijos suma. Tada CASE išraiškos rezultatas įterpiamas į balanso stulpelį ir priskiriamas @prevbalance kintamajam. Atskiroje išraiškoje kintamajam ©prevaccount priskiriamas einamosios sąskaitos ID.

Po UPDATE sakinio sprendimas pateikia laikinosios lentelės eilutes ir ištrina paskutinę. Čia yra užbaigto sprendimo kodas:

KURTI LENTELĘ #Sandoriai (actid INT, tranid INT, val MONEY, balansas MONEY); KURTI KLASTERINĘ INDEKSĄ idx_actid_tranid ĮJUNGTA #Transakcijos(actid, tranid); INSERT INTO #Transactions WITH (TABLOKAS) (actid, tranid, val, balance) PASIRINKTI actid, tranid, val, 0,00 FROM dbo.Transactions ORDER BY actid, tranid; DEKLARUOTI @prevaccount KAIP INT, @prevbalance KAIP PINIGAI; ATNAUJINTI #Operacijos NUSTATYTI @prevbalance = balansas = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH(INDEX(1), TABLOCKX) OPTION (MAXDOP 1); PASIRINKTI * IŠ #Operacijos; DROP TABLE #Sandoriai;

Šio sprendimo kontūrai parodyta toliau pateiktame paveikslėlyje. Pirmoji dalis yra pavaizduota INSERT sakiniu, antroji - UPDATE, o trečioji - SELECT:

Šiame sprendime daroma prielaida, kad UPDATE vykdymo optimizavimas visada atliks užsakytą grupinio indekso nuskaitymą, o sprendimas pateikia daugybę patarimų, kaip užkirsti kelią aplinkybėms, kurios gali to išvengti, pvz., lygiagretumo. Problema ta, kad nėra oficialios garantijos, kad optimizatorius visada žiūrės sugrupuoto indekso tvarka. Negalite pasikliauti fiziniu skaičiavimu, kad įsitikintumėte, jog kodas yra logiškai teisingas, nebent kode yra loginių elementų, kurie pagal apibrėžimą gali garantuoti tokį elgesį. Šiame kode nėra jokios loginės funkcijos, kuri galėtų garantuoti tokį elgesį. Žinoma, pasirinkimas, ar naudoti šį metodą, priklauso tik nuo jūsų sąžinės. Manau, kad jį naudoti yra neatsakinga, net jei tikrinote tūkstančius kartų ir „atrodo, kad viskas veikia taip, kaip turėtų“.

Laimei, dėl SQL Server 2012 šio pasirinkimo praktiškai nereikia. Kai turite itin efektyvų sprendimą, naudodami langų agregavimo funkcijas, jums nereikia galvoti apie kitus sprendimus.

našumo matavimas

Matavau ir lyginau įvairių technikų veikimą. Rezultatai parodyti toliau pateiktuose paveikslėliuose:

Rezultatus padalijau į du grafikus, nes antrinės užklausos / prisijungimo metodas yra daug lėtesnis nei kiti, todėl turėjau naudoti kitą skalę. Bet kuriuo atveju atminkite, kad dauguma sprendimų rodo tiesinį ryšį tarp darbo krūvio ir skaidinio dydžio, o tik antrinė užklausa arba sujungimo sprendimas rodo kvadratinį ryšį. Taip pat aiškiai matyti, kiek efektyvesnis yra naujasis sprendimas, pagrįstas langų agregavimo funkcija. UPDATE sprendimas su kintamaisiais taip pat yra labai greitas, tačiau dėl jau aprašytų priežasčių jo naudoti nerekomenduoju. CLR sprendimas taip pat yra gana greitas, bet jūs turite parašyti visą tą .NET kodą ir įdiegti surinkimą į duomenų bazę. Kad ir kaip žiūrėtumėte, rinkiniu pagrįstas sprendimas, naudojant langų blokus, išlieka pats tinkamiausias.

Funkcija SUM SQL kalboje, nepaisant jos paprastumo, gana dažnai naudojama dirbant su duomenų baze. Su jo pagalba patogu gauti kai kuriuos tarpinius ar galutinius rezultatus nesinaudojant pagalbiniais DBVS įrankiais.

Funkcijos sintaksė

Daugumoje SQL kalbų sumos sintaksė yra ta pati – kaip argumentas naudojamas tik lauko pavadinimas arba tam tikra aritmetinė kelių iš jų operacija, virš kurios reikalaujama suma.

Išimtiniais atvejais galima perduoti konkrečią reikšmę kaip skaičių ar kintamąjį, tačiau tokios „schemos“ praktiškai nenaudojamos, nes neturi didelės reikšmės. Žemiau yra funkcijos sintaksė SQL:

suma(a) – čia kaip parametras a naudojama kokia nors skaitinė reikšmė arba išraiška

Verta paminėti, kad prieš parametrą galite nustatyti raktinius žodžius, pavyzdžiui, DISTINCT arba ALL, kurie atitinkamai paims tik unikalias arba visas reikšmes.

SUM naudojimo SQL pavyzdys

Norint visiškai suprasti, kaip veikia funkcija, verta apsvarstyti keletą pavyzdžių. SQL sistemoje SUM galima naudoti ir kaip grįžtamąjį rezultatą, ir kaip tarpinę reikšmę, pavyzdžiui, norint patikrinti sąlygą.

Pirmuoju atveju apsvarstykite galimybę, kai reikia grąžinti kiekvienos prekės parduotą sumą, atsižvelgiant į tai, kad pirkimų skaičius gali būti daugiskaita. Norint gauti rezultatą, pakaks paleisti šią užklausą:

PASIRINKITE produktą, suma (Pirkimo suma) IŠ Pardavimų Grupės Pagal produktą;

Atsakymas į šią komandą bus unikalus produktų sąrašas su bendra kiekvieno iš jų pirkimo suma.

Antram pavyzdžiui reikia gauti sąrašą produktų, kurių pardavimų suma viršijo tam tikrą vertę, pavyzdžiui, 100. Šios užduoties rezultatą galite gauti keliais būdais, iš kurių optimaliausias – vykdyti vieną užklausą:

SELECT Product FROM (SELECT Product, sum (Pirkimo suma) kaip suma IŠ pardavimo) WHERE Sum > 100.

Kaip sužinoti konkretaus tiekėjo pagamintų kompiuterių modelių skaičių? Kaip nustatyti vidutinę vienodų techninių charakteristikų kompiuterių kainą? Į šiuos ir daugelį kitų klausimų, susijusių su tam tikra statistine informacija, galima atsakyti naudojant galutinės (suvestinės) funkcijos. Standarte pateikiamos šios suvestinės funkcijos:

Visos šios funkcijos grąžina vieną reikšmę. Tuo pačiu ir funkcijos COUNT, MIN Ir MAX taikoma bet kokiam duomenų tipui SUMA Ir AVG naudojami tik skaitiniams laukams. Skirtumas tarp funkcijų SKAIČIUS (*) Ir COUNT(<имя поля>) yra tai, kad antrasis skaičiuodamas neatsižvelgia į NULL reikšmes.

Pavyzdys. Raskite minimalią ir maksimalią asmeninių kompiuterių kainą:

Pavyzdys. Raskite galimą A gamintojo pagamintų kompiuterių skaičių:

Pavyzdys. Jeigu mus domina A gamintojo gaminamų skirtingų modelių skaičius, tai užklausą galima suformuluoti taip (naudojant tai, kad lentelėje Prekė kiekvienas modelis įrašomas vieną kartą):

Pavyzdys. Raskite galimų skirtingų modelių, kuriuos gamina gamintojas A, skaičių. Užklausa panaši į ankstesnę, kurioje reikėjo nustatyti bendrą gamintojo A pagamintų modelių skaičių. Čia taip pat reikia rasti skirtingų modelių skaičių kompiuterio stalą (t. y. tuos, kuriuos galima parduoti).

Siekiant užtikrinti, kad gaunant statistinius rodiklius būtų naudojamos tik unikalios reikšmės, kai suvestinių funkcijų argumentas Gali būti naudojamas DISTINCT parametras. Kitas parametras ALL yra numatytoji ir daroma prielaida, kad skaičiuojamos visos grąžintos reikšmės stulpelyje. Operatorius,

Jei mums reikia gauti pagamintų kompiuterių modelių skaičių Visi gamintojas, turėsite naudoti GROUP BY sąlyga, sintaksiškai seka po KUR punktai.

GROUP BY sąlyga

GROUP BY sąlyga naudojamas apibrėžti išvesties linijų grupes, kurioms galima taikyti apibendrintos funkcijos (COUNT, MIN, MAX, AVG ir SUM). Jei šios sąlygos nėra ir naudojamos suvestinės funkcijos, tada visi stulpeliai, kurių pavadinimai paminėti PASIRINKTI, turėtų būti įtraukta agregatinės funkcijos, ir šios funkcijos bus taikomos visam eilučių rinkiniui, atitinkančiam užklausos predikatą. Kitu atveju visi SELECT sąrašo stulpeliai neįtrauktos turi būti nurodytos suvestinėse funkcijose punkte GROUP BY. Dėl to visos išvesties užklausos eilutės yra suskirstytos į grupes, kurioms būdingi tie patys reikšmių deriniai šiuose stulpeliuose. Po to kiekvienai grupei bus taikomos suvestinės funkcijos. Atkreipkite dėmesį, kad GROUP BY visos NULL reikšmės laikomos lygiomis, t.y. grupuojant pagal lauką, kuriame yra NULL reikšmės, visos tokios eilutės pateks į vieną grupę.
Jeigu jei yra sąlyga GROUP BY, SELECT sąlygoje jokių agreguotų funkcijų, tada užklausa tiesiog pateiks vieną eilutę iš kiekvienos grupės. Ši funkcija kartu su raktiniu žodžiu DISTINCT gali būti naudojama norint pašalinti pasikartojančias eilutes rezultatų rinkinyje.
Pažvelkime į paprastą pavyzdį:

PASIRINKITE modelį, COUNT (modelis) AS Kiekis_modelis, AVG (kaina) AS Vid._kaina IŠ PC GROUP BY modelį;

Šiame prašyme kiekvienam kompiuterio modeliui nustatomas jų skaičius ir vidutinė kaina. Visos eilutės su ta pačia modelio verte sudaro grupę, o SELECT išvestis apskaičiuoja kiekvienos grupės verčių skaičių ir vidutines kainos vertes. Užklausos rezultatas bus tokia lentelė:

modelis	Kiekis_modelis	Vid._kaina
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Jei SELECT būtų datos stulpelis, tuomet būtų galima skaičiuoti šiuos rodiklius kiekvienai konkrečiai datai. Norėdami tai padaryti, turite pridėti datą kaip grupavimo stulpelį, o tada apibendrintos funkcijos bus apskaičiuojamos kiekvienam verčių deriniui (modelio data).

Yra keletas specifinių suvestinių funkcijų vykdymo taisyklės:

• Jeigu dėl prašymo negauta jokių eilučių(arba daugiau nei vieną eilutę tam tikrai grupei), tada nėra jokių pirminių duomenų, skirtų skaičiuoti bet kurią suminę funkciją. Šiuo atveju funkcijų COUNT rezultatas bus lygus nuliui, o visų kitų funkcijų rezultatas bus NULIS.
• Argumentas agregatinė funkcija pati negali turėti agreguotų funkcijų(funkcija iš funkcijos). Tie. vienoje užklausoje neįmanoma, tarkime, gauti maksimalių vidutinių reikšmių.
• Funkcijos COUNT vykdymo rezultatas yra sveikasis skaičius(SVEIKI SKAIČIUS). Kitos agregacinės funkcijos paveldi jų apdorojamų verčių duomenų tipus.
• Jei funkcija SUM duoda rezultatą, didesnį už didžiausią naudojamo duomenų tipo reikšmę, klaida.

Taigi, jei prašyme nėra GROUP BY sąlygos, Tai agregatinės funkcijosįtrauktas į SELECT sąlyga, vykdomi visose gautose užklausos eilutėse. Jei prašyme yra GROUP BY sąlyga, kiekvienas eilučių rinkinys, turintis tokias pačias stulpelio ar stulpelių grupės, nurodytos stulpelyje, reikšmes GROUP BY sąlyga, sudaro grupę ir agregatinės funkcijos atliekami kiekvienai grupei atskirai.

TURIMAS pasiūlymą

Jeigu WERE sąlyga apibrėžia predikatą eilučių filtravimui, tada TURIMAS pasiūlymą taikoma po grupavimo apibrėžti panašų predikatą, kuris filtruoja grupes pagal reikšmes agregatinės funkcijos. Ši sąlyga reikalinga norint patvirtinti reikšmes, gautas naudojant agregatinė funkcija ne iš atskirų įrašo šaltinio eilučių, apibrėžtų FROM sąlyga, ir iš tokių linijų grupės. Todėl toks čekis negali būti įtrauktas į WERE sąlyga.