Redgate Hub (Română)

am petrecut ultimii șase ani călătorind în jurul SUA spunând profesioniștilor bazei de date despre funcțiile ferestrei T-SQL la Sâmbăta SQL și alte evenimente. Sunt uimit de cât de puțini oameni au auzit despre aceste funcții și chiar mai puțini care le folosesc. La sfârșitul fiecărei prezentări, unul sau mai mulți oameni vin să spună că au dorit au învățat despre aceste funcții ani mai devreme, deoarece acestea ar fi putut fi benefice pentru atât de multe întrebări.,aceste funcții au fost promovate pentru a îmbunătăți performanța față de alte metode mai tradiționale. Sunt parțial de acord. Ele fac multe întrebări mai ușor de scris și, uneori, îmbunătățesc performanța.

nimic de-a face cu sistemul de operare Windows

aceste funcții fac parte din standardele ANSI SQL 2003 și, în cazul SQL Server, sunt funcții T-SQL utilizate pentru a scrie interogări. Ei nu au nimic de-a face cu sistemul de operare Windows sau orice apeluri API. Alte sisteme de baze de date, cum ar fi Oracle, le-au inclus, de asemenea, ca parte a propriului limbaj SQL.,

funcțiile Window (de asemenea, windowing sau windowed) efectuează un calcul pe un set de rânduri. Îmi place să mă gândesc la „privirea prin fereastră” la rândurile care sunt returnate și având o ultimă șansă de a efectua un calcul. Fereastra este definită de clauza OVER care determină dacă rândurile sunt împărțite în seturi mai mici și dacă sunt ordonate. De fapt, dacă utilizați o funcție de fereastră, veți folosi întotdeauna o clauză OVER. Clauza OVER este, de asemenea, parte a valorii următoare pentru sintaxa necesară pentru obiectul secvență, dar, în caz contrar este folosit cu funcții fereastră.,

clauza OVER poate conține o partiție după opțiune. Aceasta rupe rândurile în seturi mai mici. S-ar putea crede că acest lucru este la fel ca GROUP BY, dar nu este. La gruparea, un rând pentru fiecare grup unic este returnat. Când utilizați PARTITION BY, toate rândurile de detalii sunt returnate împreună cu calculele. Dacă aveți o fereastră în casa dvs. care este împărțită în panouri, fiecare panou este o fereastră. Când vă gândiți la funcțiile ferestrei, întregul set de rezultate este o partiție, dar când utilizați PARTITION BY, fiecare partiție poate fi considerată și o fereastră., PARTITION BY este acceptată-și opțională-pentru toate funcțiile de fereastră.

clauza OVER poate conține, de asemenea, o comandă după opțiune. Aceasta este independentă de clauza ORDER BY a interogării. Unele dintre funcțiile necesită ordine de, și nu este susținută de celelalte. Atunci când ordinea rândurilor este importantă atunci când se aplică calculul, este necesară ordinea.

funcțiile ferestrei pot fi utilizate numai în clauzele SELECT și ORDER BY ale unei interogări. Acestea sunt aplicate după orice îmbinare, filtrare sau grupare.,

funcții de clasare

cele mai utilizate funcții de fereastră, funcții de clasare, sunt disponibile din 2005. Atunci Microsoft a introdus ROW_NUMBER, RANK, DENSE_RANK și NTILE. ROW_NUMBER este folosit foarte frecvent, pentru a adăuga numere unice de rând la o partiție sau la întregul set de rezultate. Adăugarea unui număr de rând sau a uneia dintre celelalte funcții de clasare nu este de obicei Obiectivul, dar este un pas pe calea către soluție.

ORDER BY este necesar în clauza OVER atunci când se utilizează ROW_NUMBER și celelalte funcții din acest grup., Aceasta indică motorului bazei de date ordinea în care trebuie aplicate numerele. Dacă valorile coloanelor sau expresiilor utilizate în ORDER BY nu sunt unice, atunci RANK și DENSE_RANK se vor ocupa de legături, în timp ce ROW_NUMBER nu-i pasă de legături. NTILE este folosit pentru a împărți rândurile în găleți în funcție de ordinea După.un beneficiu al ROW_NUMBER este abilitatea de a transforma rânduri non-unice în rânduri unice. Acest lucru ar putea fi folosit pentru a elimina rândurile duplicate, de exemplu.

pentru a arăta cum funcționează, începeți cu un tabel temp care conține rânduri duplicate., Primul pas este crearea mesei și popularea acesteia.

adăugarea ROW_NUMBER și partiționarea de fiecare coloană va reporni numerele rândurilor pentru fiecare set unic de rânduri. Puteți identifica rândurile unice prin găsirea celor cu un număr de rând egal cu unul.,

Acum, tot ce trebuie să faceți este să ștergeți rânduri care au o linie număr mai mare decât unu., Problema este că nu puteți adăuga funcții de fereastră la clauza WHERE.,

You’ll see this error message:

The way around this problem is to separate the logic using a common table expression (CTE)., Puteți șterge apoi rândurile chiar de la CTE.

succes! Rândurile suplimentare au fost șterse și un set unic de rânduri rămâne.

Pentru a vedea diferența dintre ROW_NUMĂR, RANG, și DENSE_RANK, rula această întrebare:

COMANDA pentru fiecare PESTE clauză este OrderDate care nu este unic. Acest client a plasat două comenzi pe 2013-10-24. ROW_NUMBER a continuat să atribuie numere și nu a făcut nimic diferit, chiar dacă există o dată duplicată., Rang atribuit 6 la ambele rânduri și apoi prins până la ROW_NUMBER cu un 8 pe rândul următor. DENSE_RANK a atribuit, de asemenea, 6 celor două rânduri, dar a atribuit 7 rândului următor.doi explică diferența, gândiți-vă la ROW_NUMBER ca fiind pozițional. Rangul este atât pozițional, cât și logic. Aceste două rânduri sunt clasate logic la fel, dar următorul rând este clasat după poziția din set. DENSE_RANK le clasifică logic. Comanda 2013-11-04 este a 7-A dată unică.funcția finală din acest grup se numește NTILE. Se atribuie numere găleată la rânduri în loc de numere de rând sau rândurile., Aici este un exemplu:

NTILE are un parametru, în acest caz 4, care este numărul de compartimente care doriți să vedeți în rezultatele. Comanda se aplică la suma vânzărilor. Rândurile cu cel mai mic 25% sunt atribuite 1, rândurile cu cel mai mare 25% sunt atribuite 4. În cele din urmă, rezultatele NTILE sunt înmulțite cu 1000 pentru a veni cu suma bonusului. Deoarece 14 nu poate fi împărțit uniform la 4, un rând suplimentar intră în fiecare dintre primele două găleți.

agregatele de ferestre

agregatele de ferestre au fost, de asemenea, introduse cu SQL Server 2005., Acestea fac ca scrierea unor interogări dificile să fie ușoară, dar de multe ori va funcționa mai rău decât tehnicile mai vechi. Acestea vă permit să adăugați funcția agregată preferată la o interogare non-agregată. Spuneți, de exemplu, doriți să afișați toate comenzile clienților împreună cu subtotalul pentru fiecare client., Prin adăugarea unei SUME folosind PESTE clauză, puteți realiza acest lucru foarte usor:

Prin adăugarea PARTIȚIE de CĂTRE un subtotal se calculează pentru fiecare client. Orice funcție agregată poate fi utilizată, iar comanda în clauza OVER nu este acceptată.

îmbunătățiri ale agregatelor de ferestre în 2012

începând cu 2012, puteți adăuga o comandă la clauza OVER la agregatele de ferestre pentru a produce totaluri rulante și medii mobile, de exemplu. În același timp, Microsoft a introdus conceptul de încadrare. Adăugarea unei partiții prin este ca și cum ai împărți o fereastră în panouri., Adăugarea de încadrare este ca și cum ai crea o vitraliu. Fiecare rând are o fereastră individuală în care se va aplica expresia.cu această îmbunătățire, puteți crea totaluri care rulează chiar și fără a adăuga sintaxa de încadrare., Here is an example that returns a running total by customer:

implicit cadru, care este utilizat dacă un cadru nu este specificat, este cuprinsă ÎNTRE NEMĂRGINITĂ PRECEDENT ȘI CURENT RÂND. Din păcate, acest lucru nu va funcționa la fel de bine ca dacă specificați acest cadru în schimb: rânduri între rândul precedent și cel curent nelimitat. Diferența este cuvântul rânduri. Gama este implementată doar parțial în acest moment și este destinată lucrului cu perioade de timp, în timp ce rândurile sunt poziționale., Cadrul, rândurile dintre rândul precedent și cel curent, înseamnă că fereastra constă din primul rând al partiției și toate rândurile până la rândul curent. Fiecare calcul se face pe un set diferit de rânduri. De exemplu, atunci când efectuați calculul pentru rândul 4, se utilizează rândurile 1 până la 4.

când efectuați calculul pentru rândul 5, rândurile sunt de la 1 la 5. Fereastra crește mai mare pe măsură ce treceți de la un rând la altul.,

puteți utiliza, de asemenea, rândurile de sintaxă între N precedarea și rândul curent sau rândurile dintre rândul curent și N următoarele. Acest lucru ar putea fi util pentru calcularea unei medii mobile de trei luni, de exemplu. Figura de mai jos reprezintă rânduri între 2 rând precedent și curent.

când 5 este rândul curent, fereastra se mișcă; nu modifică dimensiunea.,

Aici este o listă de termeni ce trebuie să știți atunci când scrieți încadrarea opțiune:

recunosc că această sintaxă este un pic confuz, dar folosind SQL Prompt ajută face scris încadrarea opțiune mai ușor!de asemenea, incluse cu lansarea SQL Server 2012 sunt patru funcții care vă permit să includă valori din alte rânduri – fără a face o auto-join. Microsoft numește aceste „funcții analitice”, dar mă refer întotdeauna la ele ca „funcții offset” atunci când prezint pe acest subiect., Două dintre funcții vă permit să trageți coloane sau expresii dintr-un rând înainte (întârziere) sau după (plumb) rândul curent. Celelalte două funcții vă permit să returnați valori din primul rând al partiției (FIRST_VALUE) sau ultimul rând al partiției (LAST_VALUE). FIRST_VALUE și LAST_VALUE necesită, de asemenea, încadrare, deci asigurați-vă că includeți cadrul atunci când utilizați aceste funcții. Toate cele patru funcții necesită comanda prin opțiunea clauzei OVER. Acest lucru are sens, deoarece motorul bazei de date trebuie să cunoască ordinea rândurilor pentru a afla care rând conține valoarea de returnat.,unii oameni au o trupă preferată, alții au un film preferat. Am o funcție preferată-LAG. Este ușor de utilizat (fără cadru!) și efectuează mare., Here is an example:

LAG și DUCE necesita un argument – coloana sau expresia pe care doriți să se întoarcă. În mod implicit, LAG returnează valoarea din rândul anterior, iar LEAD returnează valoarea din rândul următor. Puteți modifica acest lucru furnizând o valoare pentru parametrul OFFSET, care este 1 în mod implicit. Observați că primul rând al partiției returnează NULL. Dacă doriți să suprascrieți NULLs, puteți furniza o valoare implicită., Here is a similar query that goes back two rows and has a default value:

FIRST_VALUE și LAST_VALUE poate fi folosit pentru a găsi o valoare din primul rând sau ultimul rând de partiție. Asigurați-vă că specificați cadrul, nu numai din motive de performanță, ci pentru că cadrul implicit nu funcționează așa cum v-ați aștepta cu LAST_VALUE. Cadrul implicit, intervalul dintre unbounded precedent și rândul curent, merge doar până la rândul curent. Ultimul rând al partiției nu este inclus., Pentru a obține rezultatele așteptate, asigurați-vă că specificați rânduri între rândul curent și următoarele nelimitate atunci când utilizați LAST_VALUE., Aici este un exemplu folosind FIRST_VALUE:

Funcții Statistice

Microsoft grupuri aceste patru funcții – PERCENT_RANK, CUME_DIST, PERCENTILE_DISC, PERCENTILE_CONT – împreună cu offset funcții de asteptare toate opt funcții analitice. Deoarece îmi place să le disting de funcțiile offset, le numesc statistice.

PERCENT_RANK și CUME_DIST oferă un clasament pentru fiecare rând pe o partiție. Ele diferă ușor. PERCENT_RANK returnează procentul de rânduri care rang mai mic decât rândul curent., „Scorul meu este mai mare de 90% din scoruri.”CUME_DIST, sau distribuția cumulativă, returnează rangul exact. „Scorul meu este la 90% din scoruri.”Aici este un exemplu folosind temperatura medie ridicată în St. Louis pentru fiecare lună. Rețineți că rândurile au fost determinate de temperatura Fahrenheit.

rândurile nu sunt determinate de valorile relative, ci de pozițiile rândurilor. Observați că martie și noiembrie au aceeași temperatură medie ridicată, deci au fost clasate la fel.

poate vă întrebați cum să calculați PROCENT_RANK și CUME_DIST., Aici sunt formule:

PERCENTILE_DISC și PERCENTILE_CONT funcționează și în sens opus. Având un rang procentual, găsiți valoarea la acel rang., Acestea diferă în care PERCENTILE_DISC va returna o valoare care există în set, în timp ce PERCENTILE_CONT va calcula o valoare exactă, dacă nici una dintre valorile din setul cade tocmai la acest rang. Puteți utiliza PERCENTILE_CONT pentru a calcula o mediană prin furnizarea de 0,5 ca rang la sută. De exemplu, ce temperatură se situează la 50% în St. Louis?

PERCENTILE_CONT funcția ia media celor două valori cea mai aproape de mijloc, 67 și 69, și mediile lor. PERCENTILE_DISC returnează o valoare exactă, 67., De asemenea, observați că aceste două funcții au o clauză suplimentară care nu este văzută în celelalte funcții, în cadrul grupului, care conține ordinea în loc de clauza OVER.

rezumat

Acest articol este o prezentare foarte rapidă a funcțiilor ferestrei T-SQL. Două tipuri de funcții au fost lansate cu SQL Server 2005, funcțiile de clasificare și agregatele de ferestre. Cu 2012, ați îmbunătățit agregatul ferestrei cu încadrarea și funcțiile analitice. Îmi place să separ funcțiile analitice în două grupuri, funcțiile offset și cele statistice., Funcțiile ferestrei fac mai multe interogări mai ușor de scris și cred că acesta este principalul beneficiu. În unele cazuri, interogările vor funcționa mai bine, dar aceasta este o discuție pentru o altă zi.sper că acest articol v-a inspirat să aflați mai multe despre aceste funcții fantastice!