Cum se însumează vizitele în sql. Funcții agregate SQL - SUM, MIN, MAX, AVG, COUNT

În acest tutorial, veți învăța cum să utilizați Funcția SUMîn SQL Server (Transact-SQL) cu sintaxă și exemple.

Descriere

În SQL Server (Transact-SQL) Funcția SUM returnează valoarea totală a expresiei.

Sintaxă

Sintaxa pentru funcția SUM în SQL Server (Transact-SQL) este:

SAU sintaxa funcției SUM la gruparea rezultatelor după una sau mai multe coloane:

Opțiuni sau Argumente

expresie1 , expresie2 , ... expresie_n sunt expresii care nu sunt incluse în funcția SUM și trebuie incluse în clauza GROUP BY la sfârșitul instrucțiunii SQL.
expresie_agregată este coloana sau expresia care va fi însumată.
tabele - tabele din care doriți să obțineți înregistrări. Trebuie să existe cel puțin un tabel listat în clauza FROM.
Condiții WHERE - opțional. Acestea sunt condițiile care trebuie îndeplinite pentru înregistrările selectate.

Aplicație

Funcția SUM poate fi utilizată în următoarele versiuni de SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Exemplu cu un singur câmp

Să ne uităm la câteva exemple de funcții SUM SQL Server pentru a înțelege cum să utilizați funcția SUM în SQL Server (Transact-SQL).

De exemplu, puteți afla cum este numărul total al tuturor produselor al căror număr este mai mare de 10.

În acest exemplu de funcție SUM, am alias expresia SUM(cantitate ) „Cantitate totală”. La returnarea unui set de rezultate - „Cantitatea totală” va fi afișată ca nume de câmp.

Exemplu DISTINCT

Puteți utiliza instrucțiunea DISTINCT în funcția SUM. De exemplu, instrucțiunea SQL de mai jos returnează salariul total cu valori unice ale salariului, unde salariul este mai mic de 29.000 USD pe an.

Dacă două salarii ar fi de 24.000 USD pe an, doar una dintre aceste valori ar fi utilizată în funcția SUM.

Exemplu de formulă

Expresia conținută în funcția SUM nu trebuie să fie un singur câmp. Puteți folosi și o formulă. De exemplu, puteți calcula comisionul total.

Transact SQL

SELECTAȚI SUMA (vânzări * 0,03) CA „Comision total” FROM comenzi;

SELECTAȚI SUMA (vânzări * 0,03) CA „Comision total”

DIN comenzi ;

GROUP BY exemplu

În unele cazuri, va trebui să utilizați clauza GROUP BY cu funcția SUM.

TEHNICA DE CALCUL

Funcții finale

Expresiile de interogare SQL necesită adesea preprocesarea datelor. În acest scop, se folosesc funcții și expresii speciale.

Destul de des, doriți să știți câte înregistrări corespund unei anumite interogări,care este suma valorilor unei coloane numerice, valorile maxime, minime și medii ale acesteia. Pentru aceasta se folosesc așa-numitele funcții finale (statistice, agregate). Funcțiile de rezumat procesează seturi de înregistrări specificate, de exemplu, de o clauză WHERE. Dacă sunt incluse în lista de coloane după instrucțiunea SELECT, tabelul rezultat va conține nu numai coloanele tabelului bazei de date, ci și valorile calculate folosind aceste funcții. Următorul estelista de funcții rezumative.

  • COUNT (parametru ) returnează numărul de înregistrări specificat în parametru. Dacă doriți să obțineți numărul tuturor înregistrărilor, atunci trebuie să specificați caracterul asterisc (*) ca parametru. Dacă specificați un nume de coloană ca parametru, funcția va returna numărul de înregistrări în care această coloană are valori non-NULL. Pentru a afla câte valori diferite conține o coloană, precedați numele cu cuvântul cheie DISTINCT. De exemplu:

SELECTARE COUNT(*) FROM Clienti;

SELECTAȚI COUNT(Suma_Comandă) FROM Clienți;

SELECTAȚI COUNT(DISTINCT Order_Sum) FROM Clienți;

Încercarea de a executa următoarea interogare va avea ca rezultat un mesaj de eroare:

SELECTARE Regiunea , COUNT(*) FROM Clienti ;

  • SUM (parametru ) returnează suma valorilor coloanei specificate în parametru. Parametrul poate fi și o expresie care conține numele coloanei. De exemplu:

SELECTAȚI SUMA (Suma_Comandă) DE LA Clienți;

Această instrucțiune SQL returnează un tabel cu o singură coloană, o înregistrare, care conține suma tuturor valorilor definite în coloana Order_Amount din tabelul Clienți.

Să presupunem că în tabelul sursă, valorile coloanei Order_Amount sunt exprimate în ruble și trebuie să calculăm suma totală în dolari. Dacă cursul de schimb curent este, de exemplu, 27,8, atunci puteți obține rezultatul dorit folosind expresia:

SELECTAȚI SUMA (Suma_Comandă*27,8) DE LA Clienți;

  • AVG (parametru ) returnează media aritmetică a tuturor valorilor coloanei specificate în parametru. Parametrul poate fi o expresie care conține numele coloanei. De exemplu:

SELECTAȚI AVG (Order_Amount) FROM Customers;

SELECTAȚI AVG (Suma_Comandă*27,8) FROM Clienți

UNDE Regiunea<>"Nord Vest";

  • MAX (parametru ) returnează valoarea maximă în coloana specificată în parametru. Parametrul poate fi și o expresie care conține numele coloanei. De exemplu:

SELECTAȚI MAX (Suma_Comandă) FROM Clienți;

SELECTAȚI MAX (Suma_Comandă*27,8) FROM Clienți

UNDE Regiune<>"Nord Vest";

  • MIN (parametrul ) returnează valoarea minimă în coloana specificată în parametru. Parametrul poate fi o expresie care conține numele coloanei. De exemplu:

SELECTAȚI MIN(Suma_Comandă) FROM Clienți;

SELECTAȚI MIN (Suma_Comandă*27 . 8) FROM Clienți

UNDE Regiune<>"Nord Vest";

În practică, este adesea necesar să se obțină un tabel rezumat care să conțină valorile totale, medii, maxime și minime ale coloanelor numerice. Pentru a face acest lucru, utilizați funcțiile de grupare (GROUP BY) și de rezumat.

SELECTARE Regiunea, SUM (Order_Amount) DE LA Clienți

GRUPĂ PE Regiune;

Tabelul cu rezultate pentru această interogare conține numele regiunilor și sumele totale (totale) de comenzi ale tuturor clienților din regiunile corespunzătoare (Fig. 5).

Acum luați în considerare o interogare pentru a obține toate datele rezumate pe regiune:

SELECTARE Regiunea, SUM (Suma_Comandă), AVG (Suma_Comandă), MAX(Suma_Comandă), MIN (Suma_Comandă)

DE LA Clienți

GRUPĂ PE Regiune;

Tabelele inițiale și de rezultate sunt prezentate în fig. 8. În exemplu, numai regiunea Nord-Vest este reprezentată în tabelul sursă de mai multe înregistrări. Prin urmare, în tabelul de rezultate pentru acesta, diferite funcții finale dau valori diferite.

Orez. 8. Tabel rezumativ al sumelor comenzilor pe regiune

Când funcțiile de rezumat sunt utilizate într-o listă de coloane într-o instrucțiune SELECT, titlurile de coloană corespunzătoare din tabelul de rezultate sunt Expr1001, Expr1002 și așa mai departe. (sau ceva similar, în funcție de implementarea SQL). Cu toate acestea, puteți seta anteturile pentru valorile funcțiilor de rezumat și alte coloane la discreția dvs. Pentru a face acest lucru, este suficient să specificați o expresie a formei după coloana din instrucțiunea SELECT:

AS column_header

Cuvântul cheie AS (as) înseamnă că în tabelul cu rezultate coloana corespunzătoare ar trebui să aibă un titlu specificat după AS. Antetul atribuit se mai numește și alias. Următorul exemplu (Figura 9) setează aliasuri pentru toate coloanele calculate:

SELECTARE Regiunea,

SUMĂ (Order_Amount) AS [Total Order Value],

AVG (Order_Amount) AS [Average Order Value],

MAX(Suma_Comandă) AS Maxim,

MIN (Order_Amount) AS Minimum,

DE LA Clienți

GRUPĂ PE Regiune;

Orez. 9. Tabel rezumat al sumelor comenzilor pe regiune folosind aliasuri de coloană

Aliasurile care constau din mai multe cuvinte separate prin spații sunt incluse între paranteze drepte.

Funcțiile de rezumat pot fi folosite în clauzele SELECT și HAVING, dar nu pot fi folosite într-o clauză WHERE. Oneratorul HAVING este similar cu clauza WHERE, dar spre deosebire de WHERE, selectează înregistrările în grupuri.

Să presupunem că doriți să determinați ce regiuni au mai mulți clienți. În acest scop, puteți utiliza următoarea interogare:

SELECTARE Regiunea, Număr (*)

DE LA Clienți

GRUPĂ DUPĂ Regiune AVÂND NUMĂRARE(*) > 1;

Funcții de manipulare a valorilor

Când lucrați cu date, de multe ori trebuie să le procesați (le transformați în forma dorită): selectați un anumit subșir dintr-un șir, eliminați spațiile de început și de final, rotunjiți un număr, calculați rădăcina pătrată, determinați ora curentă etc. SQL are următoarele trei tipuri de funcții:

  • funcții șir;
  • funcții numerice;
  • funcții dată-oră.

Funcții șiruri

Funcțiile șir iau un șir ca parametru și returnează un șir sau NULL după procesarea acestuia.

  • SUBSTRING (linia de la început)returnează un subșir rezultat din șirul dat ca parametru linie . Subșir începe cu caracterul al cărui număr de secvență este specificat în parametrul start și are lungimea specificată în parametrul lungime. Caracterele șirului sunt numerotate de la stânga la dreapta, începând de la 1. Parantezele pătrate de aici indică doar faptul că expresia inclusă în ele este opțională. Dacă expresia PENTRU lungime neutilizat, apoi un subșir de start și până la sfârșitul liniei originale. Valorile parametrilorînceput și lungime trebuie ales astfel încât subșirul căutat să fie de fapt în interiorul șirului original. În caz contrar, funcția SUBSTRING va returna NULL.

De exemplu:

SUBSTRING („Dragă Masha!” DE LA 9 PENTRU 4) returnează „Masha”;

SUBSTRING („Dragă Masha!” DIN 9) returnează „Masha!”;

SUBSTRING(„Dragă Masha!” FROM 15) returnează NULL.

Puteți utiliza această funcție într-o instrucțiune SQL, de exemplu, astfel:

SELECTAȚI * FROM Clienți

WHERE SUBSTRING(Regiune FROM 1 FOR 5) = „Nord”;

  • SUS (șir ) convertește toate caracterele șirului specificat în parametru în majuscule.
  • LOWER(șir ) convertește toate caracterele șirului specificat în parametru în litere mici.
  • TRIM (LEADING | TRAILING | BOTH [„caracter”] FROM șir ) elimină caracterele de început (LEADING), de final (TRAILING) sau ambele (BOTH) dintr-un șir. În mod implicit, caracterul care trebuie eliminat este un spațiu (""), deci poate fi omis. Cel mai adesea, această funcție este folosită pentru a elimina spații.

De exemplu:

TRIM (LEADING "" FROM "city of St. Petersburg") rotește „orașul St. Petersburg";

TRIM(TRALING " " FROM "city of St. Petersburg") returnează "orașul St. Petersburg";

TRIM (AMBELE „ „ DIN „ orașul Sankt Petersburg „) returnează „orașul Sankt Petersburg”;

TRIM(BOTH FROM "city of St. Petersburg") returnează "orașul St. Petersburg";

TRIM(BOTH "g" FROM "city of St. Petersburg") returnează "orașul St. Petersburg".

Dintre aceste funcții, cele mai frecvent utilizate sunt SUBSTRING() și TRIM().

Funcții numerice

Funcțiile numerice pot lua date nu numai de tip numeric ca parametru, dar returnează întotdeauna un număr sau NULL (valoare nedefinită).

  • POZIŢIE( targetString șir IN) caută o apariție a șirului țintă în șirul specificat. Dacă căutarea are succes, returnează numărul de poziție al primului său caracter, în caz contrar 0. Dacă șirul țintă are lungime zero (de exemplu, șirul " "), atunci funcția returnează 1. Dacă cel puțin unul dintre parametri este NULL, apoi este returnat NULL. Caracterele șirului sunt numerotate de la stânga la dreapta, începând de la 1.

De exemplu:

POZIȚIA („e” ÎN „Bună ziua tuturor”) returnează 5;

POZIȚIA („toată lumea” ÎN „bună ziua tuturor”) returnează 8;

POZIȚIA ("Bună ziua tuturor") returnează 1;

POZIȚIE(„Bună ziua!” ÎN „Bună ziua tuturor”) returnează 0.

În tabelul Clienți (vezi Fig. 1), coloana Adresă conține, pe lângă numele orașului, codul poștal, numele străzii și alte date. Este posibil să fie necesar să selectați înregistrările clienților dintr-un anumit oraș. Deci, dacă doriți să selectați înregistrări referitoare la clienții care locuiesc în Sankt Petersburg, puteți utiliza următoarea expresie de interogare SQL:

SELECTAȚI * FROM Clienți

UNDE POZIȚIA (" Adresa St. Petersburg " IN ) > 0;

Rețineți că această interogare simplă de preluare a datelor poate fi reformulată astfel:

SELECTAȚI * FROM Clienți

WHERE Adresa LIKE „%Petersburg%”;

  • EXTRAS (opțiune ) extrage un element dintr-o valoare dată-oră sau dintr-un interval. De exemplu:

EXTRAS (LUNA DE LA DATA „2005-10-25”)întoarce 10.

  • CHARACTER_LENGTH (șir ) returnează numărul de caractere din șir.

De exemplu:

CHARACTER_LENGTH(„Bună ziua tuturor”) returnează 11.

  • OCTET_LENGTH (șir ) returnează numărul de octeți (octeți) dintr-un șir. Fiecare caracter al alfabetului latin sau chirilic este reprezentat de un octet, iar caracterul alfabetului chinezesc este reprezentat de doi octeți.
  • CARDINALITATE (parametru ) ia o colecție de elemente ca parametru și returnează numărul de elemente din colecție (număr cardinal). O colecție poate fi, de exemplu, o matrice sau un multiset care conține elemente de diferite tipuri.
  • ABS (număr ) returnează valoarea absolută a unui număr. De exemplu:

ABS (-123) returnează 123;

ABS (2 - 5) revine 3.

  • MO D(număr1, număr2 ) returnează restul împărțirii întregi a primului număr cu al doilea. De exemplu:

MOD (5, h) returnează 2;

MOD (2, h) returnează 0.

  • LN (număr ) returnează logaritmul natural al unui număr.
  • EXP(număr) returnează numărul e (baza logaritmului natural la puterea numărului).
  • PUTEREA (numărul 1, numărul 2 ) returnează numărul 1 numarul 2 (numărul 1 la puterea numărului 2).
  • SQRT (număr ) returnează rădăcina pătrată a unui număr.
  • ETAJ (număr ) returnează cel mai mare număr întreg care nu depășește parametrul dat (rotunjire în jos). De exemplu:

FLOOR (5.123) returnează 5,0.

  • CEL (număr) sau CEILING (număr ) returnează cel mai mic număr întreg care nu este mai mic decât parametrul de rotunjire în sus specificat). De exemplu:

CEIL (5.123) returnează 6.0.

  • WIDTH_BUCKET (număr1, număr2, număr3, număr4) returnează un număr întreg în intervalul dintre 0 și număr4 + 1. Parametrii număr2 și număr3 definesc un segment numeric împărțit în intervale egale, al cărui număr este specificat de parametrul numărul 4. Funcția determină numărul intervalului în care se încadrează valoarea număr1. Dacă numărul1 se află în afara intervalului specificat, atunci funcția returnează 0 sau numărul 4 + 1. De exemplu:

WIDTH_BUCKET(3.14, 0, 9, 5) returnează 2.

Funcții dată-oră

Există trei funcții în SQL care returnează data și ora curente.

  • DATA CURENTA returnează data curentă (tip DATE).

De exemplu: 2005-06-18.

  • CURRENT_TIME (număr ) returnează ora curentă (de tip TIME). Argumentul întreg specifică precizia celei de-a doua reprezentări. De exemplu, cu o valoare de 2 secunde va fi prezentată cu o precizie de sutimi (două cifre în partea fracționată):

12:39:45.27.

  • CURRENT_TIMESTAMP (număr ) returnează data și ora (tip TIMESTAMP). De exemplu, 2005-06-18 12:39:45.27. Argumentul întreg specifică precizia celei de-a doua reprezentări.

Rețineți că data și ora returnate de aceste funcții sunt de tip non-caracter. Dacă doriți să le reprezentați ca șiruri de caractere, atunci ar trebui să utilizați funcția de conversie de tip CAST ().

Funcțiile datetime sunt utilizate în mod obișnuit în interogări pentru a insera, actualiza și șterge date. De exemplu, la înregistrarea informațiilor despre vânzări, data și ora curente sunt introduse într-o coloană special prevăzută în acest scop. După însumarea rezultatelor pentru lună sau trimestru, datele de vânzări pentru perioada de raportare pot fi șterse.

Expresii calculate

Expresiile calculate sunt construite din constante (numerice, șir, logice), funcții, nume de câmpuri și date de alte tipuri, conectându-le cu operatori aritmetici, șir, logici și alți operatori. La rândul lor, expresiile pot fi combinate folosind operatori în expresii mai complexe (compuse). Parantezele sunt folosite pentru a controla ordinea în care sunt evaluate expresiile.

Operatori logiciȘI, SAU și NU și funcții au fost revizuite mai devreme.

Operatori aritmetici:

  • + adaos;
  • - scăderea;
  • * înmulțire;
  • / Divizia.

Operator șirdoar un singur operator de concatenare de șir sau de concatenare (| |). Unele implementări ale SQL (cum ar fi Microsoft Access) folosesc caracterul (+) în loc de (| |). Operatorul de concatenare adaugă al doilea șir la sfârșitul primului exemplu, expresia:

„Sasha” | | "iubește" | | "Masha"

ca rezultat, va returna șirul „Sasha loves Masha”.

Când compuneți expresii, trebuie să vă asigurați că operanzii operatorilor au tipuri valide. De exemplu, expresia: 123 + „Sasha” este nevalidă deoarece operatorul aritmetic de adunare este aplicat unui operand șir.

Expresiile evaluate pot apărea după instrucțiunea SELECT, precum și în expresiile de condiție ale instrucțiunilor WHERE și HAVI NG.

Să ne uităm la câteva exemple.

Să presupunem că tabelul Vânzări conține coloanele Item_Type, Cantitate și Preț și dorim să cunoaștem venitul pentru fiecare tip de articol. Pentru a face acest lucru, este suficient să includeți expresia Cantitate*Preț în lista de coloane după instrucțiunea SELECT:

SELECTează Item_Type, Cantitate, Preț, Cantitate*Preț LA FEL DE

Total din vânzări;

Aici, cuvântul cheie AS (as) este folosit pentru a alias coloana de date calculate.

Pe fig. 10 arată tabelul de vânzări inițial și tabelul de interogări rezultat.

Orez. 10. Rezultatul interogării cu calculul veniturilor pentru fiecare tip de produs

Dacă doriți să aflați veniturile totale din vânzarea tuturor produselor, atunci aplicați următoarea interogare:

SELECTAȚI SUMA (Cantitate*Pret) DIN Vânzări;

Următoarea interogare conține expresii calculate atât în ​​lista de coloane, cât și în condiția clauzei WHERE. El selectează din tabelul de vânzări acele produse al căror venit din vânzări este mai mare de 1000:

SELECTează Tip_Produs, Cantitate*Preț AS Total

DIN Vânzări

UNDE Cantitate*Pret > 1000;

Să presupunem că doriți să obțineți un tabel cu două coloane:

Produs care conține tipul și prețul produsului;

Total care conține venituri.

Deoarece se presupune că în tabelul de vânzări sursă, coloana Product_Type este tipul de caractere (tip CHAR), iar coloana Price este numerică, atunci când combinați (lipiți) datele din aceste coloane, trebuie să transformați tipul numeric în caracter folosind CAST. () funcția. Interogarea care realizează această sarcină arată astfel (Figura 11):

SELECT Tip_produs | | " (Preț: " | | CAST(Preț AS CHAR(5)) | | ")" AS Mărfuri, Cantitate*Preț AS Total

DIN Vânzări;

Orez. 11. Rezultatul interogării cu combinarea datelor eterogene într-o coloană

Notă. În Microsoft Access, o interogare similară ar arăta astfel:

SELECT Product_type + " (Preț: " + C str (Preț) + ")" AS Articol,

Cantitate*AS Preț Total

DIN Vânzări;

Expresii condiționate cu instrucțiunea CASE

În limbajele de programare convenționale, există operatori de salt condiționat care vă permit să controlați procesul de calcul în funcție de îndeplinirea sau nu a unei anumite condiții. În SQL, acest operator este CASE (caz, împrejurare, caz). În SQL:2003, acest operator returnează o valoare și, prin urmare, poate fi folosit în expresii. Are două forme principale, pe care le vom discuta în această secțiune.

Declarație CASE cu valori

Instrucțiunea CASE cu valori are următoarea sintaxă:

CASE value_tested

CÂND valoarea1 ATUNCI rezultat1

CÂND valoarea2 ATUNCI rezultat2

. . .

CÂND valoarea N, rezultatul N

ELSE rezultatX

În cazul când valoare_testată este egal cu valoarea 1 , instrucțiunea CASE returnează o valoare rezultat1 Specificul după cuvântul cheie THEN. În caz contrar, test_value este comparat cu valoarea2 , iar dacă sunt egale, atunci rezultat2 este returnat. În caz contrar, valoarea verificată este comparată cu următoarea valoare specificată după cuvântul cheie WHEN și așa mai departe. Dacă valoarea verificată nu este egală cu niciuna dintre aceste valori, atunci valoarea rezultatul X Specifica după cuvântul cheie ELSE (altfel).

Cuvântul cheie ELSE este opțional. Dacă este absent și niciuna dintre valorile de comparat nu este egală cu valoarea testată, atunci instrucțiunea CASE returnează NULL.

Să presupunem că, pe baza tabelului Clienți (vezi Fig. 1), doriți să obțineți un tabel în care numele regiunilor sunt înlocuite cu numerele lor de cod. Dacă nu există prea multe regiuni diferite în tabelul sursă, atunci este convenabil să utilizați o interogare cu operatorul CASE pentru a rezolva această problemă:

SELECTează Nume , Adresă ,

Regiunea CASE

CÂND „Moscova” ATUNCI „77”

CÂND „regiunea Tver” APOI „69”

. . .

ELSE Regiunea

Codul regiunii AS

DE LA Clienți;

Declarație CASE cu termeni de căutare

A doua formă a instrucțiunii CASE implică utilizarea acesteia pentru a căuta într-un tabel înregistrări care îndeplinesc o anumită condiție:

CAZ

CÂND condiția1 ATUNCI rezultat1

CÂND condiția2 ATUNCI rezultatul2

. . .

CÂND condiția N, rezultatul N

ELSE rezultatX

Declarația CASE testează dacă condiția1 este adevărată pentru prima înregistrare din setul definit de clauza WHERE sau în întregul tabel dacă nu există WHERE. Dacă da, atunci CASE returnează rezultat1. În caz contrar, condiția2 este verificată pentru această intrare. Dacă este adevărat, atunci rezultat2 este returnat și așa mai departe. Dacă niciuna dintre condiții nu este adevărată, atunci rezultat este returnat. X Este specificat după cuvântul cheie ELSE.

Cuvântul cheie ELSE este opțional. Dacă este absent și nici una dintre condiții nu este îndeplinită, instrucțiunea CASE se rotește NULL. După ce instrucțiunea care conține CASE este executată pentru prima înregistrare, aceasta trece la următoarea înregistrare. Aceasta continuă până când întregul set de înregistrări a fost procesat.

Să presupunem că în tabelul cărții (Titlu, Preț) coloana este NULL dacă cartea corespunzătoare este epuizată. Următoarea interogare returnează un tabel care afișează „Stoc epuizat” în loc de NULL:

SELECTează numele,

CAZ

CÂND PREȚUL ESTE NUL, atunci „Stoc epuizat”

ELSE CAST(Preț ca CHAR(8))

AS Preț

DIN Cărți;

Toate valorile aceleiași coloane trebuie să aibă aceleași tipuri. Prin urmare, această interogare folosește funcția de conversie a tipului CAST pentru a transforma valorile numerice ale coloanei Preț într-un tip de caracter.

Rețineți că puteți utiliza întotdeauna a doua formă a instrucțiunii CASE în loc de prima formă:

CAZ

WHEN value_tested = value1 THEN result1

WHEN value_tested = value2 THEN result2

. . .

WHEN value_tested = valoare N ATUNCI rezultat

ELSE rezultat

Funcțiile NULLIF și COALESCE

În unele cazuri, în special în interogările de actualizare a datelor (instrucțiunea UPDATE), este convenabil să folosiți funcțiile mai compacte NULLIF () (NULL if) și COALESCE () (combine) în locul instrucțiunii CASE greoaie.

Funcția NULLIF( valoare1, valoare2) returnează NULL dacă valoarea primului parametru se potrivește cu valoarea celui de-al doilea parametru, în caz contrar, valoarea primului parametru este returnată neschimbată. Adică, dacă egalitatea value1 = value2 este adevărată, atunci funcția returnează NULL, în caz contrar valoarea value1.

Această funcție este echivalentă cu instrucțiunea CASE în următoarele două forme:

  • CAZ valoarea1

WHEN value2 THEN NULL

ELSE valoarea1

  • CAZ

WHEN valoare1 = valoare2 THEN NULL

ELSE valoarea1

funcția COALESCE( valoare1, valoare2, ... , valoarea N) acceptă o listă de valori care pot fi fie definite, fie nule (NULL). Funcția returnează o anumită valoare din listă sau NULL dacă toate valorile sunt nedefinite.

Această funcție este echivalentă cu următoarea instrucțiune CASE:

CAZ

CÂND valoarea 1 NU ESTE NULĂ, atunci valoarea 1

CÂND valoarea 2 NU ESTE NULĂ, atunci valoarea 2

. . .

CÂND valoarea N NU ESTE NULĂ, atunci valoarea N

ELSE NULL

Să presupunem că în tabelul Cărți (Titlu, Preț), coloana Preț este NULL dacă cartea corespunzătoare este epuizată. Următoarea interogare returnează un tabel unde în loc de NUL este afișat textul „Stoc epuizat”:

SELECT Titlu, COALESCE (CAST(Price AS CHAR(8)),

„Stoc epuizat”) AS Preț

DIN Cărți;

Aceasta este o altă problemă comună. Principiul de bază este de a acumula valorile unui atribut (element agregat) pe baza ordonării după un alt atribut sau atribute (element de ordonare), eventual cu secțiuni de rând definite pe baza încă un alt atribut sau atribute (element de partiționare). Există multe exemple în viața reală de calculare a totalurilor curente, cum ar fi calcularea soldurilor bancare, urmărirea dacă articolele sunt în stoc sau cifrele curente de vânzări și așa mai departe.

Înainte de SQL Server 2012, soluțiile bazate pe set utilizate pentru a calcula totalurile curente consumau extrem de mult resursele. Prin urmare, oamenii au apelat de obicei la soluții iterative, care erau lente, dar în unele situații încă mai rapide decât soluțiile bazate pe set. Odată cu îmbunătățirea suportului pentru funcțiile ferestre din SQL Server 2012, totalurile pot fi calculate utilizând cod simplu bazat pe set, care funcționează mult mai bine decât soluțiile mai vechi bazate pe T-SQL, atât bazate pe set, cât și iterative. Aș putea arăta o nouă soluție și aș trece la secțiunea următoare; dar pentru a înțelege cu adevărat domeniul de aplicare al schimbării, voi descrie vechile moduri și voi compara performanța lor cu noua abordare. Desigur, sunteți liber să citiți doar prima parte, care descrie noua abordare, și să săriți peste restul articolului.

Pentru a demonstra diferite soluții, voi folosi soldurile conturilor. Iată codul care creează și completează tabelul Tranzacții cu o cantitate mică de date de testare:

SETARE NOCOUNT ON; UTILIZAȚI TSQL2012; IF OBJECT_ID("dbo.Transactions", "U") NU ESTE NULL DROP TABLE dbo.Transactions; CREATE TABLE dbo.Tranzacții (actid INT NOT NULL, -- coloana de partiționare tranid INT NOT NULL, -- coloana de ordonare val MONEY NOT NULL, -- măsura CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)); GO -- caz de test mic 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);

Fiecare rând al tabelului reprezintă o tranzacție bancară în cont. Depozitele sunt marcate ca tranzacții cu o valoare pozitivă în coloana val, iar retragerile sunt marcate ca valoare negativă a tranzacției. Sarcina noastră este să calculăm soldul din cont în fiecare moment de timp prin acumularea sumelor tranzacțiilor din rândul val, sortate după coloana tranid, iar acest lucru trebuie făcut pentru fiecare cont separat. Rezultatul dorit ar trebui să arate astfel:

Ambele soluții necesită mai multe date pentru testare. Acest lucru se poate face cu o interogare ca aceasta:

DECLARE @num_partitions AS INT = 10, @rows_per_partition AS INT = 10000; TRUNCATE TABLE dbo.Tranzacții; INSERT INTO dbo.Transactions WITH (TABLOCK) (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;

Puteți seta intrările pentru a modifica numărul de partiții (conturi) și rânduri (tranzacții) dintr-o partiție.

Soluție bazată pe set folosind funcții de fereastră

Voi începe cu o soluție bazată pe set care utilizează funcția de fereastră de agregare SUM. Definiția ferestrei aici este destul de clară: trebuie să împărțiți fereastra după actid, să sortați după tranid și să filtrați liniile din cadru de la cel mai mic (UNBOUNDED PRECEDING) la cel curent. Iată cererea relevantă:

SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid RÂNDURI ÎNTRE RÂNDUL PRECEDENT ȘI CURRENT NEBORNAT) AS sold FROM dbo.Transactions;

Acest cod nu numai că este simplu și simplu, dar rulează și rapid. Planul pentru această interogare este prezentat în figură:

Tabelul are un index grupat care este compatibil POC și poate fi utilizat de funcțiile ferestrei. Mai exact, lista de chei de index se bazează pe un element de partiționare (actid) urmat de un element de ordonare (tranid), iar pentru a oferi acoperire, indexul include toate celelalte coloane din interogare (val). Planul conține o căutare ordonată urmată de un calcul intern al numărului de rând și apoi un agregat de fereastră. Deoarece există un index POC, optimizatorul nu trebuie să adauge un operator de sortare la plan. Acesta este un plan foarte eficient. În plus, se scalează liniar. Ulterior, când voi arăta rezultatele comparației de performanță, veți vedea cât de mai eficientă este această metodă în comparație cu soluțiile vechi.

Înainte de SQL Server 2012, erau folosite fie subinterogări, fie alăturari. Când se utilizează o interogare imbricată, totalurile curente sunt calculate prin filtrarea tuturor rândurilor cu aceeași valoare actid ca și rândul exterior și o valoare tranid mai mică sau egală cu valoarea din rândul exterior. Apoi, agregarea este aplicată rândurilor filtrate. Iată cererea relevantă:

O abordare similară poate fi implementată folosind conexiuni. Este folosit același predicat ca și în clauza WHERE a subinterogării din clauza ON a îmbinării. În acest caz, pentru cea de-a N-a tranzacție a aceluiași cont A în instanța desemnată ca T1, veți găsi N potriviri în instanța T2, cu numerele de tranzacție mergând de la 1 la N. Ca urmare a potrivirii, rândurile din T1 se repetă, așa că aveți nevoie de rânduri grupate după toate elementele din T1 pentru a obține informații despre tranzacția curentă și pentru a aplica agregarea atributului val din T2 pentru a calcula totalul curent. Solicitarea finalizată arată astfel:

SELECT T1.actid, T1.tranid, T1.val, SUM(T2.val) AS sold FROM dbo.Transactions AS T1 JOIN dbo.Transactions AS T2 ON T2.actid = T1.actid AND T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

Figura de mai jos prezintă planurile pentru ambele soluții:

Rețineți că, în ambele cazuri, o scanare completă a indexului grupat este efectuată pe instanța T1. Apoi, pentru fiecare rând din plan, există o operațiune de căutare în indexul de la începutul secțiunii de cont curent de pe pagina de sfârșit a indexului, citind toate tranzacțiile în care T2.tranid este mai mic sau egal cu T1.tranid. . Punctul în care are loc agregarea rândurilor este ușor diferit în planuri, dar numărul de rânduri citite este același.

Pentru a înțelege câte rânduri sunt vizualizate, trebuie luat în considerare numărul de elemente de date. Fie p numărul de partiții (conturi) și r numărul de rânduri dintr-o partiție (tranzacție). Atunci numărul de rânduri din tabel este aproximativ egal cu p * r, dacă presupunem că tranzacțiile sunt distribuite uniform între conturi. Astfel, scanarea din partea de sus se întinde pe p*r rânduri. Dar, mai ales, suntem interesați de ceea ce se întâmplă în iteratorul Nested Loops.

În fiecare secțiune, planul prevede citirea a 1 + 2 + ... + r linii, care în total este (r + r*2) / 2. Numărul total de linii procesate în planuri este p*r + p* (r + r2) / 2. Aceasta înseamnă că numărul de operații din plan crește la pătrat cu dimensiunea secțiunii, adică dacă dimensiunea secțiunii este mărită de f ori, volumul de lucru va crește cu aproximativ f de 2 ori. Asta e rău. De exemplu, 100 de linii corespund a 10 mii de linii, iar o mie de linii corespunde unui milion și așa mai departe. Pur și simplu, acest lucru duce la o încetinire puternică a execuției interogării cu o dimensiune a secțiunii destul de mare, deoarece funcția pătratică crește foarte repede. Astfel de soluții funcționează satisfăcător pentru câteva zeci de rânduri pe secțiune, dar nu mai mult.

Soluții de cursor

Soluțiile bazate pe cursor sunt implementate frontal. Un cursor este declarat pe baza unei interogări care ordonează datele după actid și tranid. După aceea, se efectuează o trecere iterativă a înregistrărilor cursorului. Când se găsește o nouă contorizare, variabila care conține agregatul este resetată. În fiecare iterație, valoarea noii tranzacții este adăugată la variabilă, după care rândul este stocat într-o variabilă de tabel cu informații despre tranzacția curentă plus valoarea curentă a totalului curent. După trecerea iterației, rezultatul din variabila de tabel este returnat. Iată codul pentru soluția finalizată:

DECLARE @Result AS TABLE (actid INT, tranid INT, val MONEY, sold MONEY); DECLARE @actid CA INT, @prvactid CA INT, @tranid CA INT, @val CA BANI, @balance CA BANI; DECLARE C CURSOR FAST_FORWARD PENTRU SELECT actid, tranid, val FROM dbo.Tranzacții ORDER BY actid, tranid; OPEN 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; SFÂRȘIT ÎNCHIS C; DEALOCATE C; SELECTAȚI * FROM @Result;

Planul de interogare folosind cursorul este prezentat în figură:

Acest plan se scalează liniar deoarece datele din index sunt scanate o singură dată într-o anumită ordine. De asemenea, fiecare operație de obținere a unui rând de pe cursor are aproximativ același cost pe rând. Dacă luăm sarcina creată prin procesarea unui rând al cursorului egal cu g, costul acestei soluții poate fi estimat ca p*r + p*r*g (rețineți, p este numărul de secțiuni, iar r este numărul de rânduri din secțiune). Deci, dacă creșteți numărul de rânduri pe partiție de f ori, sarcina sistemului va fi p*r*f + p*r*f*g, adică va crește liniar. Costul de procesare pe rând este mare, dar datorită naturii liniare a scalării, de la o anumită dimensiune a partiției, această soluție se va scala mai bine decât soluțiile bazate pe interogări imbricate și îmbinări datorită scalării pătratice a acestor soluții. Măsurarea performanței mele a arătat că de câte ori soluția cursorului este mai rapidă este de câteva sute de rânduri pe partiție.

În ciuda avantajelor de performanță oferite de soluțiile bazate pe cursor, acestea ar trebui în general evitate deoarece nu sunt relaționale.

Soluții bazate pe CLR

O soluție posibilă bazată pe CLR (Common Language Runtime) este în esență o formă de soluție bazată pe cursor. Diferența este că, în loc să folosiți un cursor T-SQL, care necesită o mulțime de resurse pentru a obține următorul rând și pentru a repeta, sunt folosite iterațiile .NET SQLDataReader și .NET, care sunt mult mai rapide. O caracteristică a CLR care face această opțiune mai rapidă este că rândul rezultat nu este necesar într-un tabel temporar - rezultatele sunt trimise direct procesului de apelare. Logica soluției bazată pe CLR este similară cu logica soluției bazată pe cursor și T-SQL. Iată codul C# care definește procedura de stocare a deciziei:

Utilizarea sistemului; folosind System.Data; folosind System.Data.SqlClient; folosind System.Data.SqlTypes; folosind Microsoft.SqlServer.Server; clasă parțială publică StoredProcedures ( public static void AccountBalances() (folosind (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;"; coloane SqlMetaData = nou SqlMetaData; coloane = nou SqlMetaData("actid" , SqlDbType.Int); coloane = nou SqlMetaData("tranid" , SqlDbType.Int); coloane = new SqlMetaData("val" , SqlDbType.Money); coloane = new SqlMetaData("balance", SqlDbType.Money); SqlDataRecord record = new SqlDbType.Money); Pipe.SendResultsStart(record); conn.Open(); SqlDataReader reader = comm.ExecuteReader(); SqlInt32 prvactid = 0; SqlMoney sold = 0; while (reader.Read()) ( SqlInt32 actid = reader.GetSql()Int32Sql() ;SqlMoney val = reader.GetSqlMoney(2);if (actid == prvactid) ( sold += val; ) else ( sold = val; ) prvactid = actid;rec ord.SetSqlInt32(0, reader.GetSqlInt32(0)); record.SetSqlInt32(1, reader.GetSqlInt32(1)); record.SetSqlMoney(2, val); record.SetSqlMoney(3, sold); SqlContext.Pipe.SendResultsRow(înregistrare); ) SqlContext.Pipe.SendResultsEnd(); ) ) )

Pentru a putea executa această procedură stocată în SQL Server, mai întâi trebuie să construiți un ansamblu numit AccountBalances pe baza acestui cod și să îl implementați într-o bază de date TSQL2012. Dacă nu sunteți familiarizat cu implementarea ansamblurilor în SQL Server, puteți citi secțiunea Stored Procedures și Common Language Runtime din articolul Stored Procedures.

Dacă ați denumit ansamblul AccountBalances și calea către fișierul de asamblare este „C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll”, puteți încărca ansamblul în baza de date și puteți înregistra procedura stocată cu următorul cod:

CREAȚI ASSEMBLARE AccountBalances DIN „C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll”; GO CREAȚI PROCEDURA dbo.AccountBlances CA NUME EXTERN AccountBalances.StoredProcedures.AccountBalances;

După ce ansamblul este implementat și procedura este înregistrată, o puteți executa cu următorul cod:

EXEC dbo.AccountBalances;

După cum am spus, SQLDataReader este doar o altă formă de cursor, dar în această versiune, suprasarcina de citire a rândurilor este mult mai mică decât utilizarea unui cursor tradițional în T-SQL. De asemenea, în .NET, iterațiile sunt mult mai rapide decât în ​​T-SQL. Astfel, soluțiile bazate pe CLR se scalează, de asemenea, liniar. Testele au arătat că performanța acestei soluții devine mai mare decât performanța soluțiilor care utilizează subinterogări și uniuni atunci când numărul de rânduri din secțiune depășește 15.

Când ați terminat, rulați următorul cod de curățare:

PROCEDURA DROP dbo.AccountBalances; DROP ASSEMBLY Solduri de cont;

Iterații imbricate

Până în acest moment, am arătat soluții iterative și bazate pe set. Următoarea soluție se bazează pe iterații imbricate, care este un hibrid al abordărilor iterative și bazate pe set. Ideea este să copiați mai întâi rândurile din tabelul sursă (conturile bancare în cazul nostru) într-un tabel temporar, împreună cu un nou atribut numit rownum, care este calculat folosind funcția ROW_NUMBER. Numerele rândurilor sunt împărțite după actid și ordonate după tranid, astfel încât primei tranzacții din fiecare cont bancar i se atribuie numărul 1, celei de-a doua tranzacții îi este atribuit numărul 2 și așa mai departe. Apoi, pe tabelul temporar este creat un index grupat cu o listă de chei (rownum, actid). Apoi utilizează un CTE recursiv sau o buclă special concepută pentru a procesa un rând pe iterație în toate contorizările. Totalul cumulat este apoi calculat prin adăugarea valorii corespunzătoare rândului curent cu valoarea asociată rândului anterior. Iată o implementare a acestei logici folosind un CTE recursiv:

SELECT actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY actid ORDER BY tranid) AS rownum INTO #Transactions FROM dbo.Transactions; CREAȚI INDEXUL UNIC CLUSTERED idx_rownum_actid ON #Transactions(rownum, actid); CU 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 #Tranzacții;

Și aceasta este implementarea folosind o buclă explicită:

SELECTARE 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; CREAȚI INDEXUL UNIC CLUSTERED idx_rownum_actid ON #Transactions(rownum, actid); DECLARE @rownum CA INT; SET @rownum = 1; WHILE 1 = 1 BEGIN SET @rownum = @rownum + 1; UPDATE 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; DACA @@rowcount = 0 BREAK; END SELECT actid, tranid, val, sumqty FROM #Transactions; DROP TABLE #Tranzacții;

Această soluție oferă performanțe bune atunci când există un număr mare de partiții cu un număr mic de rânduri în partiții. Apoi, numărul de iterații este mic, iar munca principală este realizată de partea bazată pe set a soluției, care conectează rândurile asociate cu un număr de rând cu rândurile asociate cu numărul de rând anterior.

Actualizare pe mai multe linii cu variabile

Trucurile pentru calcularea totalurilor curente prezentate până în acest moment sunt garantate pentru a da rezultatul corect. Metodologia descrisă în această secțiune este ambiguă, deoarece se bazează pe comportamentul observat mai degrabă decât pe comportamentul documentat al sistemului și, de asemenea, contrazice principiile relativității. Atractivitatea sa ridicată se datorează vitezei mari de lucru.

Această metodă utilizează o instrucțiune UPDATE cu variabile. Instrucțiunea UPDATE poate atribui expresii variabilelor pe baza valorii unei coloane și, de asemenea, poate atribui valori în coloane unei expresii cu o variabilă. Soluția începe prin crearea unui tabel temporar numit Tranzacții cu atributele actid, tranid, val și balance și un index grupat cu o listă de chei (actid, tranid). Tabelul temporar este apoi populat cu toate rândurile din baza de date originală Tranzacții, cu valoarea 0,00 introdusă în coloana soldului tuturor rândurilor. Apoi este apelată o instrucțiune UPDATE cu variabilele asociate cu tabelul temporar pentru a calcula totalul curent și a insera valoarea calculată în coloana soldului.

Sunt utilizate variabilele @prevaccount și @prevbalance, iar valoarea din coloana soldului este calculată folosind următoarea expresie:

SET @prevbalance = sold = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END

Expresia CASE verifică dacă ID-urile contului curent și precedent sunt aceleași și, dacă sunt egale, returnează suma valorilor anterioare și curente în coloana soldului. Dacă ID-urile contului sunt diferite, se returnează suma tranzacției curente. Apoi, rezultatul expresiei CASE este inserat în coloana sold și atribuit variabilei @prevbalance. Într-o declarație separată, variabilei ©prevaccount i se atribuie ID-ul contului curent.

După instrucțiunea UPDATE, soluția prezintă rândurile din tabelul temporar și le șterge pe acestea din urmă. Iată codul pentru soluția finalizată:

CREATE TABLE #Tranzacții (actid INT, tranid INT, val MONEY, sold MONEY); CREATE CLUSTERED INDEX idx_actid_tranid ON #Transactions(actid, tranid); INSERT INTO #Transactions WITH (TABLOCK) (actid, tranid, val, sold) SELECT actid, tranid, val, 0.00 FROM dbo.Tranzacții ORDER BY actid, tranid; DECLARE @prevaccount CA INT, @prevbalance CA BANI; UPDATE #Transactions SET @prevbalance = sold = CASE WHEN actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid FROM #Tranzacții WITH(INDEX(1), TABLOCKX) OPȚIUNEA (MAXDOP 1); SELECTAȚI * DIN #Tranzacții; DROP TABLE #Tranzacții;

Planul pentru această soluție este prezentat în figura următoare. Prima parte este o instrucțiune INSERT, a doua parte este o instrucțiune UPDATE și a treia parte este o instrucțiune SELECT:

Această soluție presupune că, atunci când se optimizează execuția UPDATE, se va efectua întotdeauna o scanare ordonată a indexului grupat, iar soluția include o serie de indicii pentru a preveni circumstanțele care ar putea interfera cu acest lucru, cum ar fi concurența. Problema este că nu există nicio garanție oficială că optimizatorul va arăta întotdeauna în ordinea indexului grupat. Nu te poți baza pe caracteristicile computerului fizic pentru a asigura corectitudinea logică a codului, decât dacă există elemente logice în cod care, prin definiție, pot garanta un astfel de comportament. Nu există caracteristici logice în acest cod care ar putea garanta exact acest comportament. Desigur, alegerea de a folosi sau nu această metodă depinde în totalitate de conștiința ta. Cred că este iresponsabil să-l folosești, chiar dacă l-ai verificat de mii de ori și „totul pare să funcționeze așa cum ar trebui”.

Din fericire, în SQL Server 2012 această alegere devine aproape inutilă. Cu o soluție extrem de eficientă folosind funcții de agregare ferestre, nu trebuie să te gândești la alte soluții.

măsurarea performanței

Am măsurat și comparat performanța diferitelor tehnici. Rezultatele sunt prezentate în figurile de mai jos:

Am împărțit rezultatele în două diagrame, deoarece metoda de interogare imbricată sau de îmbinare este atât de lentă încât a trebuit să folosesc o scară diferită pentru aceasta. În orice caz, rețineți că cele mai multe soluții arată o relație liniară între cantitatea de lucru și dimensiunea partiției și numai soluția bazată pe o interogare imbricată sau unire arată o relație pătratică. De asemenea, este clar cât de eficientă este noua soluție bazată pe funcția de agregare fereastră. Soluția bazată pe UPDATE cu variabile este și ea foarte rapidă, dar din motivele deja descrise, nu recomand să o folosești. Soluția CLR este, de asemenea, destul de rapidă, dar necesită să scrieți tot acest cod .NET și să implementați ansamblul în baza de date. Indiferent cum o priviți, soluția bazată pe set care utilizează agregate de ferestre rămâne soluția preferată.

Funcția SUM din SQL, în ciuda simplității sale, este folosită destul de des atunci când se lucrează cu o bază de date. Cu ajutorul acestuia, este convenabil să obții niște rezultate intermediare sau finale fără a recurge la instrumente auxiliare DBMS.

Sintaxa funcției

În majoritatea limbajelor SQL, sintaxa pentru suma este aceeași - doar numele câmpului sau o operație aritmetică a mai multor dintre ele este folosită ca argument, peste care doriți să faceți suma.

În cazuri excepționale, puteți transmite o anumită valoare sub forma unui număr sau a unei variabile, dar astfel de „scheme” practic nu sunt utilizate, deoarece nu au prea multă valoare. Următoarea este sintaxa funcției în limbajul SQL:

sum(a) - aici o valoare numerică sau expresie este folosită ca parametru a

Este de remarcat faptul că puteți seta cuvinte cheie înaintea parametrului, de exemplu, DISTINCT sau ALL, care vor lua numai valori unice sau, respectiv, toate.

Exemplu de utilizare a SUM în SQL

Pentru o înțelegere finală a modului în care funcționează funcția, merită să luăm în considerare câteva exemple. În SQL, SUM poate fi folosit atât ca rezultat returnat, cât și ca valoare intermediară, de exemplu, pentru a testa o condiție.

Pentru primul caz, ia in calcul varianta cand vrei sa returnezi suma vanzarilor pentru fiecare produs, in conditiile in care numarul de cumparaturi efectuate poate fi la plural. Pentru a obține rezultatul, va fi suficient să executați următoarea interogare:

SELECT Item, sum(Purchase Value) FROM Sales GroupBy Item;

Răspunsul la această comandă va fi o listă unică de produse cu suma totală de achiziții pentru fiecare dintre ele.

Pentru al doilea exemplu, trebuie să obțineți o listă de produse, valoarea vânzărilor pentru care a depășit o anumită valoare, de exemplu, 100. Există mai multe modalități de a obține rezultatul pentru această sarcină, dintre care cea mai optimă este de a executa o singură interogare:

SELECTARE Articol FROM (SELECTARE Articol, sumă (Suma achiziției) ca Sumă DIN Vânzări) WHERE Sumă > 100.

Cum pot afla numărul de modele de PC produse de un anumit furnizor? Cum se determină valoarea medie a prețului computerelor cu aceleași caracteristici tehnice? La aceste și multe alte întrebări legate de unele informații statistice se poate răspunde cu ajutorul lui funcții rezumative (agregate).. Standardul oferă următoarele funcții agregate:

Toate aceste funcții returnează o singură valoare. În același timp, funcțiile COUNT, MINși MAX se aplică oricărui tip de date, în timp ce SUMĂși AVG folosit numai pentru câmpurile numerice. Diferența între funcție NUMARA(*)și NUMARA(<имя поля>) este că al doilea nu ia în considerare valorile NULL la calcul.

Exemplu. Găsiți prețul minim și maxim pentru computerele personale:

Exemplu. Găsiți numărul disponibil de computere produse de producătorul A:

Exemplu. Dacă ne interesează numărul de modele diferite produse de producătorul A, atunci interogarea poate fi formulată după cum urmează (folosind faptul că fiecare model este înregistrat o dată în tabelul Produs):

Exemplu. Găsiți numărul de modele diferite disponibile produse de producătorul A. Interogarea este similară cu cea anterioară, în care a fost necesar să se determine numărul total de modele produse de producătorul A. Aici este necesar să se găsească numărul de modele diferite. în tabelul PC (adică, disponibil pentru vânzare).

Pentru a ne asigura că la obținerea indicatorilor statistici se utilizează numai valori unice, când argumentul funcției agregate poate fi utilizat parametru DISTINCT. O alta parametrul ALL este implicit și se așteaptă să numere toate valorile returnate în coloană. Operator,

Dacă trebuie să obținem numărul de modele de PC produse de fiecare producător, va trebui să utilizați GROUP BY oferta, urmând sintactic după clauze WHERE.

GROUP BY oferta

GROUP BY oferta este folosit pentru a defini grupuri de linii de ieșire cărora li se pot aplica funcții agregate (COUNT, MIN, MAX, AVG și SUM). Dacă această clauză lipsește și sunt utilizate funcții de agregare, atunci toate coloanele cu numele menționate în SELECTAȚI, ar trebui incluse în funcții agregate, iar aceste funcții se vor aplica întregului set de rânduri care satisfac predicatul de interogare. În caz contrar, toate coloanele din lista SELECT, nu este inclusîn funcții agregate, trebuie specificate în clauza GROUP BY. Ca urmare, toate rândurile de ieșire ale interogării sunt împărțite în grupuri caracterizate prin aceleași combinații de valori în aceste coloane. După aceea, se vor aplica funcții agregate fiecărui grup. Rețineți că pentru GROUP BY, toate valorile NULL sunt tratate ca egale, adică. la gruparea după un câmp care conține valori NULL, toate aceste rânduri vor intra într-un singur grup.
Dacă dacă există o clauză GROUP BY, în clauza SELECT fără funcții agregate, atunci interogarea va returna pur și simplu un rând din fiecare grup. Această caracteristică, împreună cu cuvântul cheie DISTINCT, poate fi utilizată pentru a elimina rândurile duplicate dintr-un set de rezultate.
Luați în considerare un exemplu simplu:
SELECT model, COUNT(model) AS Cantitate_model, AVG(preț) AS Avg_price
DE LA PC
GROUP BY model;

În această interogare, pentru fiecare model de PC, sunt determinate numărul și costul mediu al acestora. Toate rândurile cu aceeași valoare de model (număr de model) formează un grup, iar ieșirea SELECT calculează numărul de valori și valorile medii ale prețului pentru fiecare grup. Rezultatul interogării va fi următorul tabel:
model Cantitate_model Avg_price
1121 3 850.0
1232 4 425.0
1233 3 843.33333333333337
1260 1 350.0

Dacă ar exista o coloană cu o dată în SELECT, atunci ar fi posibil să se calculeze acești indicatori pentru fiecare dată specifică. Pentru a face acest lucru, trebuie să adăugați data ca coloană de grupare, iar apoi funcțiile agregate vor fi calculate pentru fiecare combinație de valori (model-data).

Sunt mai multe specifice reguli de executare a funcţiilor agregate:

  • Dacă în urma cererii nici un rând primit(sau mai mult de un rând pentru acest grup), atunci nu există date inițiale pentru calcularea vreuneia dintre funcțiile agregate. În acest caz, rezultatul execuției funcțiilor COUNT va fi zero, iar rezultatul tuturor celorlalte funcții va fi NULL.
  • Argument functie de agregat nu poate conține în sine funcții agregate(funcție din funcție). Acestea. într-o singură solicitare este imposibil, să zicem, să primim un maxim de valori medii.
  • Rezultatul executării funcției COUNT este întreg(ÎNTREG). Alte funcții agregate moștenesc tipurile de date ale valorilor procesate.
  • Dacă la executarea funcției SUM s-a obținut un rezultat care a depășit valoarea maximă a tipului de date utilizat, a greşeală.

Deci, dacă cererea nu conține Oferte GROUP BY, atunci funcții agregate inclus în clauza SELECT, sunt executate pe toate șirurile de interogare rezultate. Dacă cererea conţine GROUP BY oferta, fiecare set de rânduri care are aceleași valori ale coloanei sau grupului de coloane specificat în oferta GROUP BY, constituie un grup, și funcții agregate efectuate pentru fiecare grupă separat.

AVÂND oferta

Dacă clauza WHERE definește un predicat pentru filtrarea șirurilor, atunci clauza HAVING aplicat după grupare pentru a defini un predicat similar filtrarea grupurilor după valori funcții agregate. Această clauză este necesară pentru a valida valorile cu care se obțin functie de agregat nu din rânduri separate ale sursei de înregistrare definită în clauza FROM, și de la grupuri de astfel de linii. Prin urmare, un astfel de control nu poate fi inclus în clauza WHERE.