Kako sumirati posjete u sql. SQL agregatne funkcije - SUM, MIN, MAX, AVG, COUNT

U ovom ćete vodiču naučiti kako koristiti funkcija SUM u SQL Serveru (Transact-SQL) sa sintaksom i primjerima.

Opis

U SQL Serveru (Transact-SQL) funkcija SUM vraća ukupnu vrijednost izraza.

Sintaksa

Sintaksa za funkciju SUM u SQL Serveru (Transact-SQL) je:

ILI sintaksa za funkciju SUM pri grupiranju rezultata prema jednom ili više stupaca je:

Parametri ili argumenti

izraz1, izraz2, ... izraz_n su izrazi koji nisu uključeni u funkciju SUM i moraju biti uključeni u klauzulu GROUP BY na kraju SQL naredbe.
aggregate_expression je stupac ili izraz koji će biti agregiran.
tablice - tablice iz kojih želite dobiti zapise. Mora postojati najmanje jedna tablica navedena u klauzuli FROM.
WHERE uvjeti - izborno. Ovo su uvjeti koji moraju biti ispunjeni za odabrane zapise.

Primjena

Funkcija SUM može se koristiti u sljedećim verzijama SQL Servera (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

Primjer s jednim poljem

Pogledajmo neke primjere funkcije SUM SQL Servera da bismo razumjeli kako koristiti funkciju SUM u SQL Serveru (Transact-SQL).

Na primjer, možete saznati ukupnu količinu svih proizvoda čija je količina veća od 10.

U ovom primjeru funkcije SUM dodali smo alias izrazu SUM(količina) "Ukupna količina". Prilikom vraćanja skupa rezultata - "Ukupna količina" pojavit će se kao naziv polja.

Primjer korištenja DISTINCT

U funkciji SUM možete koristiti operator DISTINCT. Na primjer, SQL izjava u nastavku vraća ukupnu plaću s jedinstvenim vrijednostima plaće gdje je plaća ispod 29 000 USD godišnje.

Ako su dvije plaće bile 24.000 USD godišnje, samo bi se jedna od tih vrijednosti koristila u funkciji SUM.

Primjer korištenja formule

Izraz sadržan u funkciji SUM ne mora biti jedno polje. Također možete koristiti formulu. Na primjer, možete izračunati ukupnu proviziju.

Transact-SQL

SELECT SUM (prodaja * 0,03) KAO "Ukupna provizija" IZ narudžbi;

ODABERITE SUM (prodaja * 0,03 ) KAO "Ukupna provizija"

IZ naloga;

Primjer korištenja GROUP BY

U nekim slučajevima morat ćete koristiti operator GROUP BY s funkcijom SUM.

RAČUNALSTVO

Funkcije sažetka

Izrazi SQL upita često zahtijevaju pretprocesiranje podataka. U tu svrhu koriste se posebne funkcije i izrazi.

Vrlo često morate saznati koliko zapisa odgovara određenom upitu,koliki je zbroj vrijednosti određenog numeričkog stupca, njegove maksimalne, minimalne i prosječne vrijednosti. U tu svrhu koriste se tzv. konačne (statističke, agregatne) funkcije. Funkcije sažetka obrađuju skupove zapisa specificiranih, na primjer, klauzulom WHERE. Ako ih uključite u popis stupaca nakon naredbe SELECT, rezultirajuća tablica sadržavat će ne samo stupce tablice baze podataka, već i vrijednosti izračunate ovim funkcijama. Sljedeće jepopis funkcija sažetka.

  • COUNT (parametar ) vraća broj zapisa navedenih u parametru. Ako želite dobiti broj svih zapisa, trebate navesti simbol zvjezdice (*) kao parametar. Ako navedete naziv stupca kao parametar, funkcija će vratiti broj zapisa u kojima ovaj stupac ima vrijednosti različite od NULL. Da biste saznali koliko različitih vrijednosti sadrži stupac, ispred naziva stupca stavite ključnu riječ DISTINCT. Na primjer:

SELECT COUNT(*) FROM Clients;

SELECT COUNT(Order_Amount) FROM kupaca;

SELECT COUNT(DISTINCT Order_Amount) FROM Customers;

Pokušaj pokretanja sljedećeg upita rezultirat će porukom pogreške:

SELECT Region , COUNT(*) FROM Clients ;

  • SUM (parametar ) vraća zbroj vrijednosti stupca navedenog u parametru. Parametar također može biti izraz koji sadrži naziv stupca. Na primjer:

ODABERI ZBIR (Iznos_narudžbe) OD kupaca;

Ova SQL izjava vraća tablicu s jednim stupcem i jednim zapisom koja sadrži zbroj svih definiranih vrijednosti za stupac Order_Amount iz tablice Customers.

Recimo da su u izvornoj tablici vrijednosti stupca Order_Amount izražene u rubljima, a mi moramo izračunati ukupni iznos u dolarima. Ako je trenutni tečaj, na primjer, 27,8, tada možete dobiti traženi rezultat pomoću izraza:

ODABERI ZBIR (Iznos_narudžbe*27.8) OD klijenata;

  • AVG (parametar ) vraća aritmetičku sredinu svih vrijednosti stupca navedenog u parametru. Parametar može biti izraz koji sadrži naziv stupca. Na primjer:

SELECT PROSJ. (Iznos_narudžbe) FROM kupaca;

ODABERITE PROSJEČNI (Iznos_narudžbe*27,8) OD klijenata

GDJE Regija<>"Sjever_3zapad";

  • MAX (parametar ) vraća maksimalnu vrijednost u stupcu navedenom u parametru. Parametar također može biti izraz koji sadrži naziv stupca. Na primjer:

SELECT MAX(Order_Amount) FROM Clients;

SELECT MAX(Order_Amount*27.8) FROM Clients

GDJE Regija<>"Sjever_3zapad";

  • MIN (parametar ) vraća minimalnu vrijednost u stupcu navedenom u parametru. Parametar može biti izraz koji sadrži naziv stupca. Na primjer:

SELECT MIN(Iznos_narudžbe) FROM kupaca;

ODABERITE MIN (Iznos narudžbe*27 . 8) OD klijenata

GDJE Regija<>"Sjever_3zapad";

U praksi je često potrebno dobiti konačnu tablicu koja sadrži ukupne, prosječne, maksimalne i minimalne vrijednosti numeričkih stupaca. Da biste to učinili, trebali biste koristiti funkcije grupiranja (GROUP BY) i funkcije sažetka.

SELECT Region, SUM (Iznos_narudžbe) OD kupaca

GRUPIRAJ PO Regiji;

Tablica rezultata za ovaj upit sadrži nazive regija i ukupne (ukupne) iznose narudžbi svih kupaca iz pripadajućih regija (slika 5).

Sada razmotrite zahtjev za dobivanje svih sažetih podataka po regijama:

ODABERITE Regiju, SUM (Iznos_narudžbe), PROSJ (Iznos_narudžbe), MAX(Iznos_narudžbe), MIN (iznos_narudžbe)

OD klijenata

GRUPIRAJ PO Regiji;

Izvorna tablica i tablica rezultata prikazane su na sl. 8. U primjeru je samo sjeverozapadna regija predstavljena u izvornoj tablici s više od jednog zapisa. Stoga, u tablici rezultata za njega, različite funkcije sažetka daju različite vrijednosti.

Riža. 8. Konačna tablica iznosa narudžbi po regijama

Kada koristite funkcije sažetka na popisu stupaca u izjavi SELECT, zaglavlja njihovih odgovarajućih stupaca u tablici rezultata su Expr1001, Expr1002 i tako dalje. (ili nešto slično, ovisno o SQL implementaciji). Međutim, možete postaviti zaglavlja za vrijednosti funkcija sažetka i druge stupce prema vlastitom nahođenju. Da biste to učinili, odmah nakon stupca u izjavi SELECT navedite izraz u obliku:

AS zaglavlje_stupca

Ključna riječ AS (kao) znači da u tablici rezultata odgovarajući stupac mora imati naslov naveden nakon AS. Dodijeljeni naslov naziva se i alias. Sljedeći primjer (slika 9) postavlja pseudonime za sve izračunate stupce:

ODABIR regije,

IZNOS (Iznos_narudžbe) AS [Ukupni iznos narudžbe],

PROSJ (Iznos_narudžbe) KAO [Prosječni iznos narudžbe],

MAX(iznos_narudžbe) KAO maksimum,

MIN (Iznos_narudžbe) AS Minimum,

OD klijenata

GRUPIRAJ PO Regiji;

Riža. 9. Konačna tablica iznosa narudžbi po regijama pomoću aliasa stupaca

Nadimci koji se sastoje od nekoliko riječi odvojenih razmakom stavljaju se u uglate zagrade.

Funkcije sažetka mogu se koristiti u klauzulama SELECT i HAVING, ali se ne mogu koristiti u klauzulama WHERE. Operator HAVING sličan je operatoru WHERE, ali za razliku od WHERE odabire zapise u grupama.

Recimo da želite odrediti koje regije imaju više od jednog klijenta. U tu svrhu možete koristiti sljedeći upit:

ODABERITE Regiju, Broj(*)

OD klijenata

GROUP BY Region HAVING COUNT(*) > 1;

Funkcije obrade vrijednosti

Kada radite s podacima, često ih morate obraditi (pretvoriti u željeni oblik): odabrati podniz u nizu, ukloniti razmake na početku i na kraju, zaokružiti broj, izračunati kvadratni korijen, odrediti trenutno vrijeme itd. SQL ima sljedeće tri vrste funkcija:

  • funkcije niza;
  • numeričke funkcije;
  • funkcije datum-vrijeme.

String funkcije

String funkcije uzimaju niz kao parametar i nakon obrade vraćaju niz ili NULL.

  • PODNIZ (linija OD početka)vraća podniz koji proizlazi iz niza navedenog kao parametar crta . Podniz počinje znakom čiji je redni broj naveden u početnom parametru i ima duljinu specificiranu u parametru duljine. Znakovi u nizu su numerirani slijeva na desno, počevši od 1. Uglate zagrade ovdje samo označavaju da je izraz u njima opcionalan. Ako izraz ZA duljinu se ne koristi, tada podniz iz Početak i do kraja izvorne linije. Vrijednosti parametara početak i dužina mora biti odabran tako da je traženi podniz zapravo unutar izvornog niza. Inače će funkcija SUBSTRING vratiti NULL.

Na primjer:

PODNIZ ("Draga Maša!" OD 9 ZA 4) vraća "Maša";

PODNIZ ("Draga Maša!" OD 9) vraća "Maša!";

SUBSTRING("Draga Maša!" FROM 15) vraća NULL.

Ovu funkciju možete koristiti u SQL izrazu, na primjer, ovako:

ODABIR * OD klijenata

WHERE SUBSTRING(Region FROM 1 FOR 5) = "Sjever";

  • GORNJI(niz ) pretvara sve znakove niza navedenog u parametru u velika slova.
  • LOWER(niz ) pretvara sve znakove niza navedenog u parametru u mala slova.
  • SKRIVANJE (NA VODU | ZADNJE | OBA ["znak"] OD niza ) uklanja znak na početku (LEADING), kraj (TRAILING) ili oba (BOTH) znaka iz niza. Prema zadanim postavkama, znak koji se uklanja je razmak (" "), tako da se može izostaviti. Najčešće se ova funkcija koristi za uklanjanje razmaka.

Na primjer:

TRIM (VODI " " OD "grad St. Petersburg") rotira "grad St. Petersburg";

TRIM(TRALING " " FROM "grad St. Petersburg") vraća "grad St. Petersburg";

TRIM (BOTH " " FROM " grad St. Petersburg ") vraća "grad St. Petersburg";

TRIM(BOTH FROM " city of St. Petersburg ") vraća "city of St. Petersburg";

TRIM(BOTH "g" FROM "city of St. Petersburg") vraća "city of St. Petersburg".

Među tim funkcijama najčešće korištene su SUBSTRING() I TRIM().

Numeričke funkcije

Numeričke funkcije mogu prihvatiti podatke ne samo numeričkog tipa kao parametar, već uvijek vraćaju broj ili NULL (nedefinirana vrijednost).

  • POZICIJA ( targetString IN niz) traži pojavljivanje ciljnog niza u navedenom nizu. Ako je pretraživanje uspješno, vraća broj pozicije svog prvog znaka, inače 0. Ako ciljni niz ima nultu duljinu (na primjer, niz " "), tada funkcija vraća 1. Ako je barem jedan od parametara NULL , tada se vraća NULL. Znakovi u retku su numerirani s lijeva na desno, počevši od 1.

Na primjer:

POSITION("e" IN "Pozdrav svima") vraća 5;

POSITION ("svi" U "Pozdrav svima") vraća 8;

POSITION(" " Pozdrav svima") vraća 1;

POSITION("Pozdrav!" IN "Pozdrav svima") vraća 0.

U tablici Klijenti (vidi sliku 1) stupac Adresa sadrži, osim naziva grada, poštanski broj, naziv ulice i druge podatke. Možda ćete morati odabrati zapise za kupce koji žive u određenom gradu. Dakle, ako želite odabrati zapise koji se odnose na klijente koji žive u St. Petersburgu, možete koristiti sljedeći izraz SQL upita:

ODABIR * OD klijenata

WHERE POSITION (" St. Petersburg " IN Adresa ) > 0;

Imajte na umu da se ovaj jednostavan zahtjev za dohvaćanje podataka može drugačije formulirati:

ODABIR * OD klijenata

WHERE Adresa LIKE "%Petersburg%";

  • EKSTRAKT (parametar ) izvlači element iz vrijednosti datum-vrijeme ili iz intervala. Na primjer:

IZVOD (MJESEC OD DATUMA "2005-10-25") vraća 10.

  • CHARACTER_LENGTH(niz ) vraća broj znakova u nizu.

Na primjer:

CHARACTER_LENGTH("Pozdrav svima") vraća 11.

  • OCTET_LENGTH(niz ) vraća broj okteta (bajtova) u nizu. Svaki latinični ili ćirilični znak predstavljen je jednim bajtom, a znak kineske abecede predstavljen je s dva bajta.
  • KARDINALNOST (parametar ) uzima kolekciju elemenata kao parametar i vraća broj elemenata u kolekciji (kardinalni broj). Zbirka može biti, na primjer, niz ili višeskup koji sadrži elemente različitih tipova.
  • ABS (broj ) vraća apsolutnu vrijednost broja. Na primjer:

ABS (-123) vraća 123;

ABS (2 - 5) vraća 3.

  • MO D (broj1, broj2 ) vraća ostatak cjelobrojnog dijeljenja prvog broja s drugim. Na primjer:

MOD(5, h) vraća 2;

MOD(2, h) vraća 0.

  • LN (broj ) vraća prirodni logaritam broja.
  • EXP (broj) vraća broj (baza prirodnog logaritma na potenciju broja).
  • SNAGA (broj1, broj2 ) vraća broj1 broj2 (broj1 na potenciju broja2).
  • SQRT (broj ) vraća kvadratni korijen broja.
  • KAT (broj ) vraća najveći cijeli broj koji ne prelazi onaj naveden parametrom (zaokruživanje prema dolje). Na primjer:

FLOOR (5.123) vraća 5.0.

  • CEIL (broj) ili CEILING (broj ) vraća najmanji cijeli broj koji nije manji od vrijednosti navedene parametrom zaokruživanja). Na primjer:

CEIL(5.123) vraća 6.0.

  • WIDTH_BUCKET (broj1, broj2, broj3, broj4) vraća cijeli broj u rasponu između 0 i broj4 + 1. Parametri broj2 i broj3 određuju numerički interval podijeljen na jednake intervale, čiji je broj određen parametrom broj4 broj intervala u koji pada vrijednost broj1. Ako je broj1 izvan navedenog raspona, tada funkcija vraća 0 ili broj 4 + 1. Na primjer:

WIDTH_BUCKET(3.14, 0, 9, 5) vraća 2.

Datum-vrijeme funkcije

SQL ima tri funkcije koje vraćaju trenutni datum i vrijeme.

  • TRENUTNI DATUM vraća trenutni datum (tip DATE).

Na primjer: 2005-06-18.

  • CURRENT_TIME (broj ) vraća trenutno vrijeme (VRIJEME tipa). Integer parametar specificira preciznost predstavljanja sekundi. Na primjer, vrijednost 2 predstavljat će sekunde do najbliže stotinke (dva decimalna mjesta):

12:39:45.27.

  • CURRENT_TIMESTAMP (broj ) vraća datum i vrijeme (tip TIMESTAMP). Na primjer, 2005-06-18 12:39:45.27. Integer parametar specificira preciznost predstavljanja sekundi.

Imajte na umu da datum i vrijeme koje ove funkcije vraćaju nisu karakterni tip. Ako ih želite predstaviti kao nizove znakova, tada biste trebali koristiti funkciju pretvorbe tipa CAST().

Funkcije datuma i vremena obično se koriste u upitima za umetanje, ažuriranje i brisanje podataka. Na primjer, kada se bilježe podaci o prodaji, trenutni datum i vrijeme upisuju se u za to predviđeni stupac. Nakon zbrajanja rezultata za mjesec ili tromjesečje, podaci o prodaji za izvještajno razdoblje mogu se izbrisati.

Izračunati izrazi

Izračunati izrazi izgrađeni su od konstanti (numeričkih, nizovnih, logičkih), funkcija, naziva polja i drugih vrsta podataka njihovim povezivanjem s aritmetičkim, nizovnim, logičkim i drugim operatorima. S druge strane, izrazi se mogu kombinirati pomoću operatora u složenije (složene) izraze. Zagrade se koriste za kontrolu redoslijeda kojim se izrazi procjenjuju.

Logički operatori I, ILI i NE i funkcije o kojima se ranije raspravljalo.

Aritmetički operatori:

  • + zbrajanje;
  • - oduzimanje;
  • * množenje;
  • / podjela.

String operatorsamo jedan operator ulančavanja ili ulančavanja niza (| |). Neke implementacije SQL-a (kao što je Microsoft Access) koriste znak (+) umjesto (| |). Operator ulančavanja dodaje drugi niz na kraj prvog primjera, izraza:

"Saša" | | "voli" | | "mahanje"

vratit će niz "Sasha voli Mashu" kao rezultat.

Prilikom sastavljanja izraza, morate osigurati da su operandi operatora važećeg tipa. Na primjer, izraz: 123 + "Sasha" nije valjan jer se operator aritmetičkog zbrajanja primjenjuje na operand niza.

Izračunati izrazi mogu se pojaviti nakon naredbe SELECT, kao i u izrazima uvjeta naredbi WHERE i HAVI N.G.

Pogledajmo nekoliko primjera.

Neka tablica Sales sadrži stupce ProductType, Quantity i Price, a mi želimo znati prihod za svaku vrstu proizvoda. Da biste to učinili, samo uključite izraz Količina*Cijena u popis stupaca iza izjave SELECT:

ODABERITE Product_type, Quantity, Price, Quantity*Price KAO

Ukupno OD prodaje;

Ovo koristi ključnu riječ AS (as) za određivanje pseudonima za stupac izračunatih podataka.

Na sl. Slika 10 prikazuje originalnu tablicu prodaje i tablicu rezultata upita.

Riža. 10. Rezultat upita s izračunom prihoda za svaku vrstu proizvoda

Ako želite saznati ukupan prihod od prodaje sve robe, onda samo koristite sljedeći upit:

ODABERI ZBIR (Količina*Cijena) OD prodaje;

Sljedeći upit sadrži izračunate izraze u popisu stupaca i uvjetu WHERE klauzule. Iz tablice prodaje odabire one proizvode čiji je prihod od prodaje veći od 1000:

ODABERITE Product_type, Quantity*Price AS Total

OD prodaje

WHERE Količina*Cijena > 1000;

Pretpostavimo da želite dobiti tablicu koja ima dva stupca:

Proizvod koji sadrži vrstu i cijenu proizvoda;

Ukupni prihod koji sadrži.

Budući da se pretpostavlja da je u originalnoj prodajnoj tablici stupac Product_Type znakovni (CHAR tip), a stupac Price numerički, prilikom spajanja (lijepljenja) podataka iz ovih stupaca potrebno je brojčani tip pretvoriti u znakovni tip pomoću funkcija CAST(). Upit koji izvršava ovaj zadatak izgleda ovako (slika 11):

SELECT Product_Type | | " (Cijena: " | | CAST(cijena AS CHAR(5)) | | ")" KAO proizvod, količina*cijena kao ukupno

OD prodaje;

Riža. 11. Rezultat upita koji kombinira različite vrste podataka u jednom stupcu

Bilješka. U Microsoft Accessu bi sličan upit izgledao ovako:

ODABERITE Product_type + " (Cijena: " + C Str (Cijena) + ")" KAO stavka,

Količina*Cijena KAO Ukupno

OD prodaje;

Uvjetni izrazi s CASE naredbom

Konvencionalni programski jezici imaju operatore uvjetnog skoka koji vam omogućuju kontrolu procesa izračunavanja ovisno o tome je li neki uvjet istinit ili ne. U SQL-u ovaj operator je CASE (slučaj, okolnost, primjer). U SQL:2003 ovaj operator vraća vrijednost i stoga se može koristiti u izrazima. Ima dva glavna oblika, koje ćemo pogledati u ovom odjeljku.

CASE izjava s vrijednostima

Izjava CASE s vrijednostima ima sljedeću sintaksu:

CASE provjerena_vrijednost

WHEN vrijednost1 THEN rezultat1

WHEN vrijednost2 THEN rezultat2

. . .

WHEN vrijednost N THEN rezultat N

INAČE rezultatX

U slučaju provjerena_vrijednost jednako vrijednost1 , naredba CASE vraća vrijednost rezultat1 , naveden nakon ključne riječi THEN. Inače se checked_value uspoređuje s vrijednost2 , a ako su jednaki, tada se vraća vrijednost result2. U suprotnom, vrijednost koja se testira uspoređuje se sa sljedećom vrijednošću navedenom nakon ključne riječi WHEN, itd. Ako tested_value nije jednak nijednoj od ovih vrijednosti, vrijednost se vraća rezultat X , naveden nakon ključne riječi ELSE (else).

Ključna riječ ELSE nije obavezna. Ako nedostaje i nijedna od vrijednosti koje se uspoređuju nije jednaka vrijednosti koja se testira, tada izjava CASE vraća NULL.

Recimo, na temelju tablice Klijenti (vidi sl. 1) želite dobiti tablicu u kojoj su nazivi regija zamijenjeni njihovim kodnim brojevima. Ako u izvornoj tablici nema previše različitih regija, tada je za rješavanje ovog problema prikladno koristiti upit s CASE operatorom:

ODABERITE ime, adresu,

CASE regija

KAD "Moskva" ONDA "77"

KADA "Tver regija" ONDA "69"

. . .

DRUGO Regija

AS kod regije

OD klijenata;

CASE iskaz s uvjetima pretraživanja

Drugi oblik CASE operatora uključuje njegovu upotrebu kada se u tablici traže oni zapisi koji zadovoljavaju određeni uvjet:

SLUČAJ

WHEN uvjet1 THEN rezultat1

WHEN catch2 THEN result2

. . .

WHEN uvjet N THEN rezultat N

INAČE rezultatX

Izjava CASE testira je li uvjet1 istinit za prvi zapis u skupu definiranom klauzulom WHERE ili cijelu tablicu ako WHERE nije prisutan. Ako da, tada CASE vraća rezultat1. U suprotnom, uvjet2 se provjerava za ovaj zapis. Ako je istina, tada se vraća vrijednost rezultat2, itd. Ako nijedan od uvjeta nije istinit, vraća se vrijednost rezultat x , navedeno nakon ključne riječi ELSE.

Ključna riječ ELSE nije obavezna. Ako nedostaje i niti jedan od uvjeta nije istinit, naredba CASE rotira NULL. Nakon što se naredba koja sadrži CASE izvrši za prvi zapis, prelazi se na sljedeći zapis. To se nastavlja sve dok se cijeli skup zapisa ne obradi.

Pretpostavimo da je u tablici knjiga (naslov, cijena) stupac NULL ako odgovarajuće knjige nema na zalihama. Sljedeći upit vraća tablicu koja prikazuje "Nema na zalihama" umjesto NULL:

ODABERI naslov,

SLUČAJ

KADA JE cijena NULL, ONDA "Nema na skladištu"

ELSE CAST(cijena KAO CHAR(8))

AS Cijena

IZ knjiga;

Sve vrijednosti u istom stupcu moraju biti iste vrste. Stoga ovaj upit koristi funkciju pretvorbe tipa CAST za pretvaranje numeričkih vrijednosti stupca Cijena u vrstu znaka.

Imajte na umu da uvijek možete koristiti drugi oblik naredbe CASE umjesto prvog:

SLUČAJ

WHEN testirana_vrijednost = vrijednost1 ONDA rezultat1

WHEN testirana_vrijednost = vrijednost2 ONDA rezultat2

. . .

KADA je provjerena_vrijednost = vrijednost N ONDA rezultatN

ILSE rezultat

Funkcije NULLIF i COALESCE

U nekim slučajevima, posebno u zahtjevima za ažuriranje podataka (operator UPDATE), prikladno je koristiti kompaktnije funkcije NULLIF() (NULL if) i COALESCE() (kombinirati) umjesto glomaznog CASE operatora.

NULLIF funkcija ( vrijednost1, vrijednost2) vraća NULL ako se vrijednost prvog parametra podudara s vrijednošću drugog parametra, u slučaju neslaganja, vrijednost prvog parametra vraća se nepromijenjena. To jest, ako je jednakost vrijednost1 = vrijednost2 istinita, tada funkcija vraća NULL, inače vrijednost vrijednost1.

Ova funkcija je ekvivalentna naredbi CASE u sljedeća dva oblika:

  • CASE vrijednost1

WHEN vrijednost2 THEN NULL

ELSE vrijednost1

  • SLUČAJ

WHEN vrijednost1 = vrijednost2 THEN NULL

ELSE vrijednost1

Funkcija COALESCE( vrijednost1, vrijednost2, ... , vrijednost N) prihvaća popis vrijednosti, koje mogu biti NULL ili NULL. Funkcija vraća određenu vrijednost s popisa ili NULL ako su sve vrijednosti nedefinirane.

Ova funkcija je ekvivalentna sljedećoj CASE izjavi:

SLUČAJ

KADA vrijednost 1 NIJE NULL, ONDA vrijednost 1

KADA vrijednost 2 NIJE NULL, ONDA vrijednost 2

. . .

KADA vrijednost N NIJE NULL, ONDA vrijednost N

INAČE NULL

Pretpostavimo da je u tablici Knjige (naslov, cijena) stupac Cijena NULL ako odgovarajuće knjige nema na zalihama. Sljedeći upit vraća tablicu gdje umjesto NULL Prikazuje se tekst "Nema na skladištu":

ODABERI naziv, SPOJ (CAST(cijena AS CHAR(8)),

"Nema na skladištu") AS Cijena

IZ knjiga;

Ovo je još jedan uobičajeni zadatak. Osnovno je načelo akumulirati vrijednosti jednog atributa (element agregata) na temelju poretka drugog atributa ili atributa (element poretka), po mogućnosti s odjeljcima reda definiranim na temelju još jednog atributa ili atributa (particioni element) . U životu postoji mnogo primjera izračunavanja kumulativnih ukupnih iznosa, kao što je izračunavanje stanja bankovnog računa, praćenje raspoloživosti robe u skladištu ili trenutne prodajne brojke, itd.

Prije SQL Servera 2012, rješenja temeljena na skupovima koja su se koristila za izračun tekućih ukupnih iznosa bila su iznimno zahtjevna za resursima. Stoga su se ljudi skloniji okrenuti iterativnim rješenjima, koja su bila spora, ali ipak brža od rješenja temeljenih na skupu u nekim situacijama. S proširenom podrškom za funkcije prozora u SQL Serveru 2012, tekuće ukupne vrijednosti mogu se izračunati korištenjem jednostavnog koda temeljenog na skupu koji ima mnogo bolju izvedbu od starijih rješenja temeljenih na T-SQL-u—i temeljenih na skupu i iterativnih. Mogao bih pokazati novo rješenje i prijeći na sljedeći odjeljak; ali kako bih vam pomogao da uistinu shvatite opseg promjene, opisat ću stare načine i usporediti njihovu izvedbu s novim pristupom. Naravno, slobodno možete pročitati samo prvi dio koji opisuje novi pristup, a ostatak članka preskočiti.

Upotrijebit ću stanje računa da pokažem različita rješenja. Evo koda koji stvara i popunjava tablicu Transakcije malom količinom testnih podataka:

POSTAVITE NO COUNT ON; KORISTI TSQL2012; IF OBJECT_ID("dbo.Transactions", "U") NIJE NULL DROP TABLE dbo.Transactions; CREATE TABLE dbo.Transactions (actid INT NOT NULL, -- stupac particioniranja tranid INT NOT NULL, -- stupac naručivanja val MONEY NOT NULL, -- mjera CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)); KRENI -- mali testni skup podataka 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);

Svaki redak tablice predstavlja bankovnu transakciju na računu. Depoziti su označeni kao transakcije s pozitivnom vrijednošću u stupcu val, a isplate su označene kao negativna vrijednost transakcije. Naš zadatak je izračunati stanje na računu u svakom trenutku tako što ćemo akumulirati iznose transakcija u retku val, poredane po stupcu tranid, a to se mora učiniti za svaki račun zasebno. Željeni rezultat trebao bi izgledati ovako:

Za testiranje oba rješenja potrebno je više podataka. To se može učiniti s ovakvim upitom:

DEKLARIRAJ @num_partitions AS INT = 10, @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.Transakcije; INSERT INTO dbo.Transakcije SA (TABLOK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM( NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;

Možete postaviti svoje unose za promjenu broja odjeljaka (računa) i redaka (transakcija) u odjeljku.

Rješenje temeljeno na skupu korištenjem prozorskih funkcija

Počet ću s rješenjem temeljenim na skupu koje koristi funkciju agregacije prozora SUM. Definicija prozora ovdje je prilično jasna: trebate podijeliti prozor prema actidu, poredati prema tranidu i upotrijebiti filtar za odabir linija u okviru od najnižeg (NEOGRANIČENI PRETHODNI) do trenutnog. Evo odgovarajućeg zahtjeva:

SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWS) AS saldo FROM dbo.Transactions;

Ne samo da je ovaj kôd jednostavan i jasan, već je i brz. Plan za ovaj upit prikazan je na slici:

Tablica ima klasterirani indeks koji zadovoljava POC zahtjeve i mogu ga koristiti funkcije prozora. Konkretno, popis ključeva indeksa temelji se na particionom elementu (actid) nakon kojeg slijedi element redoslijeda (tranid), a indeks također uključuje sve ostale stupce u upitu (val) kako bi se osigurala pokrivenost. Plan sadrži naručeno skeniranje, zatim izračun broja linija za interne potrebe, a zatim agregat prozora. Budući da postoji POC indeks, optimizator ne treba dodati operator sortiranja u plan. Ovo je vrlo učinkovit plan. Osim toga, skalira se linearno. Kasnije, kada pokažem rezultate usporedbe performansi, vidjet ćete koliko je ova metoda učinkovitija u usporedbi sa starijim rješenjima.

Prije SQL Servera 2012 korišteni su podupiti ili spojevi. Kada koristite podupit, tekući ukupni iznosi izračunavaju se filtriranjem svih redaka s istom actid vrijednošću kao vanjski red i tranid vrijednošću koja je manja ili jednaka vrijednosti u vanjskom retku. Agregacija se zatim primjenjuje na filtrirane retke. Evo odgovarajućeg zahtjeva:

Sličan pristup može se implementirati korištenjem veza. Koristi se isti predikat kao u klauzuli WHERE podupita u klauzuli ON spoja. U ovom slučaju, za N-tu transakciju istog računa A u instanci označenoj T1, pronaći ćete N podudaranja u instanci T2, s brojevima transakcija od 1 do N. Kao rezultat podudaranja, retci u T1 su ponovljeno, tako da trebate grupirati retke po svim elementima iz T1 da biste dobili informacije o trenutnoj transakciji i primijeniti agregaciju na atribut val iz T2 za izračunavanje tekućeg ukupnog iznosa. Ispunjeni zahtjev izgleda otprilike ovako:

SELECT T1.actid, T1.tranid, T1.val, SUM(T2.val) AS saldo FROM dbo.Transakcije AS T1 JOIN dbo.Transakcije AS T2 ON T2.actid = T1.actid I ​​T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

Donja slika prikazuje planove za oba rješenja:

Imajte na umu da se u oba slučaja potpuno skeniranje klasteriranog indeksa izvodi na instanci T1. Potom se za svaki red u planu vrši operacija pretraživanja u indeksu početka rubrike tekućeg računa na završnoj stranici indeksa, koja očitava sve transakcije u kojima je T2.tranid manji ili jednak T1. tranid. Točka u kojoj dolazi do agregacije redaka malo je drugačija u planovima, ali je broj pročitanih redaka isti.

Da biste razumjeli koliko se redaka pregledava, trebate uzeti u obzir broj podatkovnih elemenata. Neka je p broj odjeljaka (računa), a r broj redaka u odjeljku (transakcija). Tada je broj redaka u tablici približno jednak p*r, ako pretpostavimo da su transakcije ravnomjerno raspoređene po računima. Dakle, gornji pregled pokriva p*r redaka. Ali ono što nas najviše zanima je ono što se događa u iteratoru ugniježđenih petlji.

U svakom odjeljku plan predviđa čitanje 1 + 2 + ... + r redaka, što ukupno iznosi (r + r*2) / 2. Ukupan broj obrađenih redaka u planovima je p*r + p* (r + r2) / 2. To znači da se broj operacija u planu povećava na kvadrat s povećanjem veličine odjeljka, odnosno ako povećate veličinu odjeljka za f puta, količina rada će se povećati za približno f 2 puta. To je loše. Na primjer, 100 redaka odgovara 10 tisuća redaka, a tisuću redaka odgovara milijunu, itd. Jednostavno rečeno, to dovodi do značajnog usporavanja u izvršavanju upita s prilično velikom veličinom odjeljka, jer kvadratna funkcija raste vrlo brzo. Takva rješenja rade zadovoljavajuće s nekoliko desetaka redaka po odjeljku, ali ne više.

Rješenja pokazivača

Rješenja koja se temelje na kursoru implementirana su direktno. Kursor se deklarira na temelju upita koji razvrstava podatke prema actid i tranid. Nakon toga se izvodi iterativni prolaz kroz zapise kursora. Kada se otkrije novi račun, varijabla koja sadrži agregat se poništava. U svakoj iteraciji, iznos nove transakcije dodaje se varijabli, nakon čega se red pohranjuje u varijablu tablice s informacijama o trenutnoj transakciji plus trenutna vrijednost tekućeg ukupnog iznosa. Nakon iterativnog prolaza vraća se rezultat iz varijable tablice. Evo koda za dovršeno rješenje:

DECLARE @Result AS TABLE (actid INT, tranid INT, val MONEY, saldo MONEY); DEKLARIRAJ @actid KAO INT, @prvactid KAO INT, @tranid KAO INT, @val KAO NOVAC, @balance KAO NOVAC; DECLARE C CURSOR FAST_FORWARD FOR SELECT actid, tranid, val FROM dbo.Transakcije ORDER BY actid, tranid; OTVORI C FETCH NEXT FROM C INTO @actid, @tranid, @val; SELECT @prvactid = @actid, @balance = 0; WHILE @@fetch_status = 0 BEGIN IF @actid<>@prvactid SELECT @prvactid = @actid, @balance = 0; SET @balance = @balance + @val; INSERT INTO @Result VALUES(@actid, @tranid, @val, @balance); FETCH NEXT FROM C INTO @actid, @tranid, @val; KRAJ ZATVORI C; DEALOKACIJA C; SELECT * FROM @Result;

Plan upita pomoću kursora prikazan je na slici:

Ovaj se plan skalira linearno jer se podaci iz indeksa skeniraju samo jednom određenim redoslijedom. Također, svaka operacija za dohvaćanje retka iz kursora ima približno istu cijenu po retku. Ako uzmemo da je opterećenje stvoreno obradom jedne linije kursora jednako g, trošak ovog rješenja može se procijeniti kao p*r + p*r*g (kao što se sjećate, p je broj odjeljaka, a r je broj redaka u odjeljku). Dakle, ako povećate broj redaka po odjeljku za f puta, opterećenje sustava će biti p*r*f + p*r*f*g, odnosno rasti će linearno. Trošak obrade po retku je visok, ali zbog linearne prirode skaliranja, od određene veličine particije ovo rješenje će pokazati bolju skalabilnost od rješenja koja se temelje na ugniježđenim upitima i spajanjima zbog kvadratnog skaliranja ovih rješenja. Mjerenja performansi koja sam proveo pokazuju da je broj u kojem je rješenje kursora brže nekoliko stotina redaka po odjeljku.

Unatoč prednostima izvedbe koje pružaju rješenja temeljena na kursoru, općenito ih treba izbjegavati jer nisu relacijski.

Rješenja temeljena na CLR-u

Jedno od mogućih rješenja na temelju CLR (Common Language Runtime) je u biti oblik rješenja pomoću kursora. Razlika je u tome što umjesto korištenja T-SQL pokazivača, koji troši mnogo resursa da bi se dobio sljedeći redak i iterirao, koristite .NET SQLDataReader i .NET iteracije, koje su mnogo brže. Jedna od značajki CLR-a koja ovu opciju čini bržom je ta da rezultirajući redak nije potreban u privremenoj tablici - rezultati se šalju izravno pozivnom procesu. Logika rješenja temeljenog na CLR-u slična je logici pokazivača i T-SQL rješenja. Ovdje je C# kod koji definira pohranjenu proceduru rješavanja:

Korištenje sustava; koristeći System.Data; koristeći System.Data.SqlClient; koristeći System.Data.SqlTypes; koristeći Microsoft.SqlServer.Server; javna djelomična klasa StoredProcedures ( public static void AccountBalances() ( using (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 stupci = novi SqlMetaData; stupci = novi SqlMetaData("actid" , SqlDbType.Int); stupci = novi SqlMetaData("tranid", SqlDbType.Int); stupci = novi SqlMetaData("val", SqlDbType.Money); stupci = novi SqlDataRecord(kolumne); Pipe.SendResultsStart(record); .ExecuteReader(); SqlInt32 prvactid = 0; while (reader.Read()) ; SqlMoney val = reader.GetSqlMoney(2); if (actid == val; ) else ( balance = val; ) prvactid = actid; zapis.SetSqlInt32(0, čitač.GetSqlInt32(0)); zapis.SetSqlInt32(1, čitač.GetSqlInt32(1)); zapis.SetSqlMoney(2, val); record.SetSqlMoney(3, stanje); SqlContext.Pipe.SendResultsRow(record); ) SqlContext.Pipe.SendResultsEnd(); ) ) )

Da biste mogli izvršiti ovu pohranjenu proceduru u SQL Serveru, prvo trebate izgraditi sklop pod nazivom AccountBalances na temelju ovog koda i implementirati ga u bazu podataka TSQL2012. Ako niste upoznati s implementacijom sklopova u SQL Serveru, možda biste trebali pročitati odjeljak Pohranjene procedure i CLR u članku Pohranjene procedure.

Ako ste sklop nazvali AccountBalances i put do datoteke sklopa je "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll", možete učitati sklop u bazu podataka i registrirati pohranjenu proceduru sa sljedećim kodom:

CREATE ASSEMBLY AccountBalances FROM "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll"; KRENITE STVARATI POSTUPAK dbo.AccountBalances KAO VANJSKI IME AccountBalances.StoredProcedures.AccountBalances;

Nakon postavljanja sklopa i registracije procedure, možete je izvršiti sa sljedećim kodom:

EXEC dbo.AccountBalances;

Kao što sam rekao, SQLDataReader je samo još jedan oblik pokazivača, ali ova verzija ima znatno manje opterećenja za čitanje reda nego korištenje tradicionalnog pokazivača u T-SQL-u. Iteracije su također mnogo brže u .NET nego u T-SQL. Stoga se rješenja temeljena na CLR-u također linearno skaliraju. Testiranje je pokazalo da izvedba ovog rješenja postaje veća od izvedbe rješenja koja koriste podupit i spojeve kada broj redaka u odjeljku premaši 15.

Kada završite, trebate pokrenuti sljedeći kod za čišćenje:

DROP PROCEDURE dbo.AccountBalances; DROP ASSEMBLY AccountBalances;

Ugniježđene iteracije

Do ove točke pokazao sam iterativna rješenja i rješenja koja se temelje na skupovima. Sljedeće rješenje temelji se na ugniježđenim iteracijama, što je hibrid iterativnog pristupa i pristupa temeljenog na skupu. Ideja je prvo kopirati retke iz izvorne tablice (u našem slučaju bankovni računi) u privremenu tablicu zajedno s novim atributom zvanim rownum, koji se izračunava pomoću funkcije ROW_NUMBER. Brojevi redaka podijeljeni su prema actid-u i raspoređeni prema tranidu, tako da je prvoj transakciji na svakom bankovnom računu dodijeljen broj 1, drugoj transakciji dodijeljen je broj 2, i tako dalje. Zatim se na privremenoj tablici kreira klasterirani indeks s popisom ključeva (rownum, actid). Zatim se koristi rekurzivni CTE izraz ili posebno izrađena petlja za obradu jednog retka po iteraciji na svim računima. Tekući ukupni zbroj se tada izračunava dodavanjem vrijednosti pridružene trenutnom retku s vrijednošću pridruženom prethodnom retku. Ovdje je implementacija ove logike pomoću rekurzivnog CTE-a:

SELECT actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON #Transactions(rownum, actid); WITH 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 #Transactions AS CUR ON CUR.rownum = PRV.rownum + 1 AND CUR.actid = PRV.actid) SELECT actid, tranid, val, sumqty FROM C OPTION (MAXRECURSION 0); DROP TABLE #Transakcije;

A ovo je implementacija koja koristi eksplicitnu petlju:

SELECT ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum, actid, tranid, val, CAST(val AS BIGINT) AS sumqty INTO #Transactions FROM dbo.Transactions; CREATE UNIQUE CLUSTERED INDEX idx_rownum_actid ON #Transactions(rownum, actid); DEKLARIRAJ @rownum KAO INT; SET @rownum = 1; WHILE 1 = 1 BEGIN SET @rownum = @rownum + 1; AŽURIRAJ CUR SET sumqty = PRV.sumqty + CUR.val FROM #Transactions AS CUR JOIN #Transactions AS PRV ON CUR.rownum = @rownum AND PRV.rownum = @rownum - 1 AND CUR.actid = PRV.actid; IF @@rowcount = 0 BREAK; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #Transakcije;

Ovo rješenje pruža dobre performanse kada postoji veliki broj particija s malim brojem redaka po particiji. Tada je broj ponavljanja mali, a glavninu posla obavlja dio rješenja koji se temelji na skupu, koji povezuje retke povezane s jednim brojem retka s redovima povezanim s prethodnim brojem retka.

Ažuriranje s više redaka s varijablama

Metode za izračunavanje kumulativnih ukupnih iznosa prikazane do ove točke zajamčeno daju točan rezultat. Tehnika opisana u ovom odjeljku je kontroverzna jer se temelji na promatranom, a ne dokumentiranom ponašanju sustava, a također je u suprotnosti s načelima relativnosti. Visoku atraktivnost duguje velikoj brzini rada.

Ova metoda koristi naredbu UPDATE s varijablama. Naredba UPDATE može dodijeliti izraze varijablama na temelju vrijednosti stupca, a također može dodijeliti vrijednosti u stupcima izrazu s varijablom. Rješenje počinje stvaranjem privremene tablice pod nazivom Transactions s atributima actid, tranid, val i balance i klasteriranim indeksom s popisom ključeva (actid, tranid). Tada se privremena tablica popunjava svim redovima iz izvorne baze podataka Transakcije, au stupac stanja svih redaka upisuje se vrijednost 0,00. Zatim se poziva izjava UPDATE s varijablama pridruženim privremenoj tablici za izračunavanje tekućih ukupnih iznosa i umetanje izračunate vrijednosti u stupac stanja.

Koriste se varijable @prevaccount i @prevbalance, a vrijednost u stupcu stanja izračunava se pomoću sljedećeg izraza:

SET @prevbalance = ravnoteža = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END

CASE izraz provjerava jesu li trenutni i prethodni ID-ovi računa isti i, ako jesu, vraća zbroj prethodnih i trenutnih vrijednosti u stupcu stanja. Ako su ID-ovi računa različiti, vraća se trenutni iznos transakcije. Zatim se rezultat izraza CASE umeće u stupac stanja i dodjeljuje varijabli @prevbalance. U zasebnom izrazu, varijabli ©prevaccount dodijeljen je ID tekućeg računa.

Nakon naredbe UPDATE, rješenje predstavlja retke iz privremene tablice i briše zadnji. Evo koda za dovršeno rješenje:

CREATE TABLE #Transactions (actid INT, tranid INT, val MONEY, saldo MONEY); CREATE CLUSTERED INDEX idx_actid_tranid ON #Transactions(actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, balance) SELECT actid, tranid, val, 0.00 FROM dbo.Transakcije ORDER BY actid, tranid; DEKLARIRAJ @prevaccount KAO INT, @prevbalance KAO NOVAC; AŽURIRANJE #Transakcije SET @prevbalance = stanje = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid FROM #Transactions WITH(INDEX(1), TABLOCKX) OPTION (MAXDOP 1); SELECT * FROM #Transakcije; DROP TABLE #Transakcije;

Nacrt ovog rješenja prikazan je na sljedećoj slici. Prvi dio predstavlja naredba INSERT, drugi naredba UPDATE, a treći naredba SELECT:

Ovo rješenje pretpostavlja da će optimizacija izvršenja UPDATE uvijek izvoditi uređeno skeniranje klasteriranog indeksa, a rješenje pruža niz savjeta za sprječavanje okolnosti koje bi to mogle spriječiti, kao što je istovremenost. Problem je u tome što ne postoji službeno jamstvo da će optimizator uvijek tražiti prema redoslijedu grupiranog indeksa. Ne možete se osloniti na fizičko računanje kako biste osigurali da je kod logički ispravan osim ako u kodu nema logičkih elemenata koji, po definiciji, mogu jamčiti takvo ponašanje. Ne postoji logična značajka u ovom kodu koja bi mogla jamčiti ovakvo ponašanje. Naravno, odluka o tome hoćete li koristiti ovu metodu u potpunosti je na vašoj savjesti. Mislim da je neodgovorno koristiti ga, čak i ako ste ga provjerili tisuće puta i "čini se da sve radi kako treba".

Srećom, SQL Server 2012 čini ovaj izbor gotovo nepotrebnim. Kada imate iznimno učinkovito rješenje koje koristi prozorske funkcije agregacije, ne morate razmišljati o drugim rješenjima.

mjerenje učinkovitosti

Mjerila sam i uspoređivala performanse raznih tehnika. Rezultati su prikazani na slikama u nastavku:

Podijelio sam rezultate u dva grafikona jer je metoda podupita/pridruživanja toliko sporija od ostalih da sam za nju morao koristiti drugu ljestvicu. U svakom slučaju, imajte na umu da većina rješenja pokazuje linearni odnos između radnog opterećenja i veličine particije, a samo podupit ili rješenje spajanja pokazuje kvadratni odnos. Također je jasno vidjeti koliko je učinkovitije novo rješenje temeljeno na prozorskoj funkciji agregacije. Rješenje UPDATE s varijablama je također vrlo brzo, ali iz već opisanih razloga ne preporučam njegovu upotrebu. CLR rješenje također je prilično brzo, ali morate napisati sav taj .NET kod i implementirati sklop u bazu podataka. Bez obzira kako na to gledate, rješenje temeljeno na kompletu s prozorskim jedinicama ostaje najpoželjnije.

Funkcija SUM u SQL jeziku, unatoč svojoj jednostavnosti, često se koristi pri radu s bazom podataka. Uz njegovu pomoć prikladno je dobiti neke srednje ili konačne rezultate bez pribjegavanja pomoći pomoćnih DBMS alata.

Sintaksa funkcije

U većini SQL jezika sintaksa zbroja je ista - kao argument se koristi samo naziv polja ili neka aritmetička operacija više njih, preko kojih se traži zbroj.

U iznimnim slučajevima moguće je prenijeti određenu vrijednost kao broj ili varijablu, ali se takve "sheme" praktički ne koriste, jer nemaju veliku vrijednost. Ispod je sintaksa funkcije u SQL-u:

sum(a) - ovdje se kao parametar a koristi neka numerička vrijednost ili izraz

Važno je napomenuti da prije parametra možete postaviti ključne riječi, na primjer, DISTINCT ili ALL, koje će uzeti samo jedinstvene ili sve vrijednosti.

Primjer korištenja SUM-a u SQL-u

Da biste u potpunosti razumjeli kako funkcija radi, vrijedi razmotriti nekoliko primjera. U SQL-u, SUM se može koristiti i kao povratni rezultat i kao međuvrijednost, na primjer, za testiranje uvjeta.

Za prvi slučaj razmotrite opciju kada trebate vratiti iznos prodaje za svaki proizvod, uzimajući u obzir da broj obavljenih kupnji može biti u množini. Da biste dobili rezultat, bit će dovoljno pokrenuti sljedeći upit:

SELECT Product, sum(PurchaseAmount) FROM Sales GroupBy Product;

Odgovor na ovu naredbu bit će jedinstveni popis proizvoda s ukupnim iznosom kupovine za svaki od njih.

Za drugi primjer, trebate dobiti popis proizvoda čiji je iznos prodaje premašio određenu vrijednost, na primjer, 100. Rezultat za ovaj zadatak možete dobiti na nekoliko načina, od kojih je najoptimalniji izvršiti jedan zahtjev:

SELECT Product FROM (SELECT Product, sum (Purchase Amount) as Amount FROM Sales) WHERE Sum > 100.

Kako mogu saznati broj modela računala koje je proizveo određeni dobavljač? Kako odrediti prosječnu cijenu računala s istim tehničkim karakteristikama? Na ova i mnoga druga pitanja vezana uz neke statističke informacije može se odgovoriti pomoću finalne (agregatne) funkcije. Norma pruža sljedeće agregatne funkcije:

Sve te funkcije vraćaju jednu vrijednost. Istodobno, funkcije BROJ, MIN I MAKS primjenjiv na bilo koju vrstu podataka, dok IZNOS I PROSJ koriste se samo za numerička polja. Razlika između funkcije RAČUNATI(*) I RAČUNATI(<имя поля>) je da drugi ne uzima u obzir NULL vrijednosti prilikom izračuna.

Primjer. Pronađite minimalnu i maksimalnu cijenu za osobna računala:

Primjer. Pronađite raspoloživi broj računala proizvođača A:

Primjer. Ako nas zanima broj različitih modela koje proizvodi proizvođač A, tada se upit može formulirati na sljedeći način (koristeći činjenicu da se u tablici proizvoda svaki model bilježi jednom):

Primjer. Pronađite broj dostupnih različitih modela koje proizvodi proizvođač A. Upit je sličan prethodnom, u kojem je trebalo odrediti ukupan broj modela koje proizvodi proizvođač A. Ovdje također trebate pronaći broj različitih modela u PC stol (tj. oni dostupni za prodaju).

Kako bi se osiguralo da se koriste samo jedinstvene vrijednosti prilikom dobivanja statističkih pokazatelja, kada argument agregatnih funkcija može se koristiti DISTINCT parametar. Još parametar SVE je zadana vrijednost i pretpostavlja da su sve vraćene vrijednosti u stupcu prebrojane. Operater,

Ako trebamo dobiti broj proizvedenih PC modela svatko proizvođača, morat ćete koristiti GROUP BY klauzula, sintaktički nakon WHERE odredbe.

GROUP BY klauzula

GROUP BY klauzula koristi se za definiranje grupa izlaznih linija na koje se može primijeniti agregatne funkcije (COUNT, MIN, MAX, AVG i SUM). Ako ova klauzula nedostaje, a koriste se agregatne funkcije, tada će svi stupci s nazivima navedenim u IZABERI, treba uključiti u agregatne funkcije, a te će se funkcije primijeniti na cijeli skup redaka koji zadovoljavaju predikat upita. Inače, svi stupci liste SELECT nije uključeno u agregatnim funkcijama moraju biti navedene u klauzuli GROUP BY. Kao rezultat toga, svi retci izlaznih upita podijeljeni su u grupe koje karakteriziraju iste kombinacije vrijednosti u tim stupcima. Nakon toga, agregatne funkcije će se primijeniti na svaku grupu. Imajte na umu da se za GROUP BY sve NULL vrijednosti tretiraju kao jednake, tj. kod grupiranja prema polju koje sadrži NULL vrijednosti, svi takvi redovi će pasti u jednu grupu.
Ako ako postoji klauzula GROUP BY, u klauzuli SELECT nema agregatnih funkcija, onda će upit jednostavno vratiti jedan redak iz svake grupe. Ova značajka, zajedno s ključnom riječi DISTINCT, može se koristiti za uklanjanje duplikata redaka u skupu rezultata.
Pogledajmo jednostavan primjer:
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price
S PC-ja
GROUP BY model;

U ovom zahtjevu za svaki model računala utvrđuje se njihov broj i prosječna cijena. Svi redovi s istom vrijednošću modela čine grupu, a izlaz SELECT-a izračunava broj vrijednosti i prosječne vrijednosti cijene za svaku grupu. Rezultat upita bit će sljedeća tablica:
model Kol_model Prosječna_cijena
1121 3 850.0
1232 4 425.0
1233 3 843.33333333333337
1260 1 350.0

Kad bi SELECT imao stupac datuma, tada bi bilo moguće izračunati ove pokazatelje za svaki određeni datum. Da biste to učinili, trebate dodati datum kao stupac grupiranja, a zatim bi se agregatne funkcije izračunale za svaku kombinaciju vrijednosti (model-datum).

Postoji nekoliko specifičnih pravila za izvođenje agregatnih funkcija:

  • Ako kao rezultat zahtjeva nema primljenih redaka(ili više od jednog redaka za danu grupu), tada nema izvornih podataka za izračun bilo koje agregatne funkcije. U tom će slučaju rezultat funkcija COUNT biti nula, a rezultat svih ostalih funkcija bit će NULL.
  • Argument funkcija agregata ne može sama sadržavati agregatne funkcije(funkcija iz funkcije). Oni. u jednom upitu nemoguće je, recimo, dobiti maksimalnu prosječnu vrijednost.
  • Rezultat izvršavanja funkcije COUNT je cijeli broj(CIJELI BROJ). Druge agregatne funkcije nasljeđuju tipove podataka vrijednosti koje obrađuju.
  • Ako funkcija SUM proizvede rezultat koji je veći od maksimalne vrijednosti korištene vrste podataka, greška.

Dakle, ako zahtjev ne sadrži GROUP BY klauzule, To agregatne funkcije uključen u SELECT klauzula, izvršavaju se na svim rezultirajućim recima upita. Ako zahtjev sadrži GROUP BY klauzula, svaki skup redaka koji ima iste vrijednosti stupca ili grupe stupaca navedenih u GROUP BY klauzula, čini grupu, i agregatne funkcije izvode se za svaku skupinu posebno.

IMAJU ponudu

Ako WHERE klauzula definira predikat za filtriranje nizova, zatim IMAJU ponudu primjenjuje se nakon grupiranja za definiranje sličnog predikata koji filtrira grupe prema vrijednostima agregatne funkcije. Ova klauzula je potrebna za provjeru vrijednosti koje su dobivene korištenjem funkcija agregata ne iz pojedinačnih redaka izvora zapisa definiranog u FROM klauzula, i od grupe takvih linija. Stoga takav ček ne može biti sadržan u WHERE klauzula.