Funcțiile ferestrei SQL

Începând de aici? Această lecție face parte dintr-un tutorial complet privind utilizarea SQL pentru analiza datelor. Verificați începutul.

În această lecție vom acoperi:

• Introducere în funcțiile ferestrei
• Sintaxa de bază a ferestrelor
• Suspecții obișnuiți: SUM, COUNT și AVG
• ROW_NUMBER ()
• RANK () și DENSE_RANK ()
• NTILE
• LAG și LEAD
• Definirea unui alias de fereastră
• Tehnici avansate de fereastrare

Această lecție folosește date din Programul Capital Bikeshare din Washington DC, care publică date istorice detaliate la nivel de excursie pe site-ul lor web. Datele au fost descărcate în februarie 2014, dar sunt limitate la datele colectate în primul trimestru al anului 2012. Fiecare rând reprezintă o călătorie. Majoritatea câmpurilor se explică de la sine, cu excepția rider_type: „Înregistrat” indică un abonament lunar la programul de rideshare, „Casual” înseamnă că riderul a cumpărat un abonament de 3 zile. Câmpurile start_time și end_time au fost curățate din formularele lor originale pentru a se potrivi formatării datei SQL – sunt stocate în acest tabel ca timestamp-uri.

Introducere în funcțiile de fereastră

Documentația PostgreSQL face o treabă excelentă de a introduce conceptul de funcții de fereastră:

O funcție de fereastră efectuează un calcul pe un set de rânduri de tabele care sunt cumva legate de rândul curent. Acest lucru este comparabil cu tipul de calcul care se poate face cu o funcție agregată. Dar, spre deosebire de funcțiile agregate obișnuite, utilizarea unei ferestre funcția nu determină gruparea rândurilor într-un singur rând de ieșire – rândurile își păstrează identitățile separate. În culise, funcția fereastră poate accesa mai mult decât rândul curent al rezultatului interogării.

Cel mai practic exemplu în acest sens este un total care rulează:

Puteți vedea că interogarea de mai sus creează o agregare (running_total) fără a utiliza GROUP BY. Să „descompunem sintaxa și să vedem cum funcționează.

Sintaxa de fereastră de bază

Prima parte a agregării de mai sus, SUM(duration_seconds) , seamănă foarte mult cu orice altă agregare. Adăugarea OVER îl desemnează ca o funcție de fereastră. Puteți citi agregarea de mai sus ca „luați suma duration_seconds pentru întregul set de rezultate, în ordine de start_time. „

Dacă doriți să restrângeți fereastra din întregul set de date la grupuri individuale din cadrul setul de date, puteți utiliza PARTITION BY pentru a face acest lucru:

Grupurile de interogări de mai sus și comandă interogarea prin start_terminal . În cadrul fiecărei valori start_terminal, este ordonată de start_time și sumele totale care rulează pe rândul curent și pe toate rândurile anterioare din duration_seconds. Derulați în jos până când valoarea start_terminal se schimbă și veți observa că running_total începe din nou. Asta se întâmplă atunci când grupați utilizând PARTITION BY. În cazul în care sunteți încă stumped de ORDER BY, pur și simplu ordonează după coloana desemnată (s) în același mod în care ar face clauza ORDER BY, cu excepția faptului că tratează fiecare partiție ca separată. De asemenea, creează totalul care rulează – fără ORDER BY, fiecare valoare va fi pur și simplu o sumă a tuturor valorilor duration_seconds din start_terminal. Încercați să rulați interogarea de mai sus fără ORDER BY pentru a vă face o idee:

ORDER și definiți ceea ce se numește„ fereastră ”- subsetul ordonat de date pe care se fac calculele.

Notă: Nu puteți utiliza funcțiile ferestrei și agregările standard în același interogare. Mai exact, nu puteți „include funcțiile ferestrei într-o clauză GROUP BY.

Suspecții obișnuiți: SUM, COUNT și AVG

Când utilizați funcțiile de fereastră, puteți aplica aceleași agregate pe care le-ați face în circumstanțe normale – SUM, COUNT și AVG. Cel mai simplu mod de a le înțelege este să rulați exemplul anterior cu câteva funcții suplimentare. Asigurați

Alternativ, aceleași funcții cu ORDER BY:

Asigurați-vă că conectați cele două interogări anterioare în Mode și le executați. Această următoare problemă de practică este foarte asemănătoare cu exemplele, așa că încercați să modificați codul de mai sus decât să începeți de la zero.

ROW_NUMBER ()

ROW_NUMBER() face exact cum sună – afișează numărul unui rând dat. Începe este 1 și numerotează rândurile în conformitate cu partea ORDER BY din instrucțiunea ferestrei. ROW_NUMBER() nu necesită specificarea unei variabile între paranteze:

Utilizarea clauzei PARTITION BY vă va permite să începeți să numărați din nou 1 în fiecare partiție. Următoarea interogare începe din nou contorizarea pentru fiecare terminal:

RANK () și DENSE_RANK ()

RANK() este ușor diferit de ROW_NUMBER(). Dacă comandați de start_time, de exemplu, ar putea fi cazul ca unele terminale să aibă plimbări cu două ore de pornire identice. În acest caz, li se acordă același rang, în timp ce ROW_NUMBER() le oferă numere diferite. În următoarea interogare, observați observațiile a 4-a și a 5-a pentru start_terminal 31000 – ambele au un rang 4 și următorul rezultat primește un rang 6:

De asemenea, puteți utiliza DENSE_RANK() în loc de RANK() în funcție de aplicația dvs. Imaginați-vă o situație în care trei intrări au aceeași valoare. Folosind oricare comandă, toți vor obține același rang. De dragul acestui exemplu, să spunem „s” 2. „ Iată cum cele două comenzi ar evalua următoarele rezultate în mod diferit:

• RANK() ar da rândurilor identice un rang de 2, apoi sări peste rândurile 3 și 4, deci următorul rezultat ar fi 5
• DENSE_RANK() ar da în continuare tuturor rândurilor identice un rang de 2, dar următorul rând ar fi 3 – nu rândurile ar fi omise.

NTILE

Puteți utiliza funcțiile ferestrei pentru a identifica în ce percentilă (sau quartile sau orice altă subdiviziune) intră un rând dat. Sintaxa este NTILE(*# of buckets*). În acest caz, ORDER BY determină ce coloană să utilizați pentru a determina quartile (sau orice număr de „dale vă specifica). De exemplu:

Privind rezultatele din interogarea de mai sus, puteți vedea că coloana percentile nu calculează exact așa cum v-ați putea aștepta. Dacă numai avea două înregistrări și măsurați percentile, vă așteptați ca o înregistrare să definească prima percentilă, iar cealaltă înregistrare să definească percentila 100. Folosind funcția NTILE, ceea ce ați vedea de fapt este o înregistrare în prima percentilă și una în a doua percentilă. Puteți vedea acest lucru în rezultatele pentru start_terminal 31000 — coloana percentile arată doar ca o clasare numerică. Dacă derulați în jos la start_terminal 31007, poate vedea că calculează în mod corespunzător percentilele, deoarece există mai mult de 100 de înregistrări pentru acel start_terminal. Dacă lucrați cu ferestre foarte mici, țineți cont de acest lucru și luați în considerare utilizarea quartilelor sau la fel de mici benzi.

LAG și LEAD

Deseori poate fi util să comparați rândurile cu rândurile precedente sau următoare, în special dacă „aveți datele într-o ordine care are sens. Puteți utiliza LAG sau LEAD pentru a crea coloane care extrag valori din alte rânduri – tot ce trebuie să faceți este să introduceți din ce coloană să trageți și câte rânduri distanță doriți să faceți. LAG extrage din rândurile anterioare și LEAD extrage din rândurile următoare:

Acest lucru este util mai ales dacă doriți să calculați diferențe între rânduri:

Primul rând al coloanei difference este nul deoarece nu există niciun rând anterior din care să trageți. În mod similar, utilizarea LEAD va crea valori nule la sfârșitul setului de date. Dacă doriți să faceți rezultatele puțin mai curate, le puteți înfășura într-o interogare externă pentru a elimina nulele:

Definirea unui alias de fereastră

Dacă intenționați să scrie mai multe funcții de fereastră în aceeași interogare, folosind aceeași fereastră, puteți crea un alias.Luați exemplul NTILE de mai sus:

Acesta poate fi rescris ca:

WINDOW clauza, dacă este inclusă, ar trebui să apară întotdeauna după clauza WHERE.

Tehnici avansate de fereastrare

Puteți consulta o listă completă funcții în Postgres (se folosește modul de sintaxă) în documentația Postgres. Dacă utilizați funcțiile de fereastră într-o bază de date conectată, ar trebui să consultați ghidul de sintaxă adecvat pentru sistemul dvs.