SQL-vinduesfunktioner

Starter du her? Denne lektion er en del af en tutorial i fuld længde i brug af SQL til dataanalyse. Se begyndelsen.

I denne lektion dækker vi:

• Introduktion til vinduesfunktioner
• Grundlæggende syntaks for vinduesvindue
• De sædvanlige mistænkte: SUM, COUNT og AVG
• ROW_NUMBER ()
• RANK () og DENSE_RANK ()
• NTILE
• LAG og LEAD
• Definition af et vinduesalias
• Avancerede vindueteknikker

Denne lektion bruger data fra Washington DCs Capital Bikeshare Program, der udgiver detaljerede historiske data på tripniveau på deres hjemmeside. Dataene blev downloadet i februar 2014, men er begrænset til data indsamlet i første kvartal af 2012. Hver række repræsenterer en tur. De fleste felter er selvforklarende, undtagen rider_type: “Registreret” angiver et månedligt medlemskab af rideshare-programmet, “Casual” tilskynder til, at rytteren købte et 3-dages pas. start_time og end_time felterne blev renset fra deres originale formularer, så de passer til SQL-datoformatering – de er gemt i denne tabel som tidsstempler.

Introduktion til vinduesfunktioner

Dokumentationen fra PostgreSQL gør et fremragende stykke arbejde med at introducere konceptet med Window-funktioner:

En vinduesfunktion udfører en beregning på tværs af et sæt tabelrækker, der på en eller anden måde er relateret til den aktuelle række. Dette kan sammenlignes med den type beregning, der kan udføres med en samlet funktion. Men i modsætning til almindelige samlede funktioner, brug af et vindue funktion får ikke rækker til at blive grupperet i en enkelt outputrække – rækkerne bevarer deres separate identitet. Bag kulisserne er vinduesfunktionen i stand til at få adgang til mere end bare den aktuelle række i forespørgselsresultatet.

Det mest praktiske eksempel på dette er et løbende total:

Du kan se, at ovenstående forespørgsel skaber en sammenlægning (running_total) uden at bruge GROUP BY. Lad os nedbryde syntaksen og se, hvordan den fungerer.

Grundlæggende syntaks for vinduer

Den første del af ovenstående sammenlægning, SUM(duration_seconds) , ligner meget enhver anden sammenlægning. Tilføjelse af OVER betegner det som en vinduesfunktion. Du kan læse ovenstående sammenlægning som “tag summen af duration_seconds over hele resultatsættet, i rækkefølge efter start_time. “

Hvis du gerne vil indsnævre vinduet fra hele datasættet til individuelle grupper inden for datasættet kan du bruge PARTITION BY til at gøre det:

Ovenstående forespørgsel grupperer og ordrer forespørgslen ved start_terminal . Inden for hver værdi af start_terminal ordnes den efter start_time, og de løbende samlede summer på tværs af den aktuelle række og alle tidligere rækker af duration_seconds. Rul ned, indtil værdien start_terminal ændres, og du vil bemærke, at running_total starter forfra. Det er, hvad der sker, når du grupperer ved hjælp af PARTITION BY. I tilfælde af at du stadig er stumpet af ORDER BY, bestiller den simpelthen efter den udpegede kolonne (s) på samme måde som ORDER BY -sætningen ville, bortset fra at den behandler hver partition som separat. Det opretter også det samlede løb – uden ORDER BY, vil hver værdi simpelthen være en sum af alle duration_seconds -værdierne i den respektive start_terminal. Prøv at køre ovenstående forespørgsel uden ORDER BY for at få en idé:

ORDER og PARTITION definer det, der kaldes” vinduet “- den ordnede delmængde af data, som beregningerne foretages over.

Bemærk: Du kan ikke bruge vinduesfunktioner og standardaggregeringer i det samme Mere specifikt kan du ikke inkludere vinduesfunktioner i en GROUP BY -klausul.

De sædvanlige mistænkte: SUM, COUNT og AVG

Når du bruger vinduesfunktioner, kan du anvende de samme aggregater, som du ville gøre under normale omstændigheder— SUM, COUNT og AVG. Den nemmeste måde at forstå disse på er at genkøre det foregående eksempel med nogle ekstra funktioner. Lav

Alternativt fungerer de samme funktioner med ORDER BY:

Sørg for at sætte de to foregående forespørgsler i Mode og køre dem. Dette næste praksis-problem svarer meget til eksemplerne, så prøv at ændre ovenstående kode i stedet for at starte fra bunden.

ROW_NUMBER ()

ROW_NUMBER() gør, hvad det lyder – viser nummeret på en given række. Det starter er 1 og nummererer rækkerne i henhold til ORDER BY -delen af vindussætningen. ROW_NUMBER() kræver ikke, at du angiver en variabel inden for parenteserne:

Brug af PARTITION BY -klausulen giver dig mulighed for begynde at tælle 1 igen i hver partition. Følgende forespørgsel starter optællingen igen for hver terminal:

RANK () og DENSE_RANK ()

RANK() er lidt anderledes end ROW_NUMBER(). Hvis du f.eks. Bestiller med start_time, kan det være tilfældet, at nogle terminaler har forlystelser med to identiske starttider. I dette tilfælde får de samme rang, mens ROW_NUMBER() giver dem forskellige tal. I den følgende forespørgsel bemærker du 4. og 5. observation for start_terminal 31000 — de får begge en rang på 4, og følgende resultat får en rang på 6:

Du kan også bruge DENSE_RANK() i stedet for RANK() afhængigt af din applikation. Forestil dig en situation, hvor tre poster har samme værdi. Ved hjælp af begge kommandoer får de alle samme rang. Af hensyn til dette eksempel, lad “s sige det” s “2.” Her er hvordan de to kommandoer evaluerer de næste resultater forskelligt:

• RANK() giver de samme rækker en rang på 2, så spring ranger 3 over og 4, så det næste resultat ville være 5
• DENSE_RANK() ville stadig give alle de samme rækker en rang på 2, men den følgende række ville være 3 — nej rækker springes over.

NTILE

Du kan bruge vinduesfunktioner til at identificere, hvilken percentil (eller kvartil eller enhver anden underinddeling) en given række falder i. Syntaksen er NTILE(*# of buckets*) I dette tilfælde bestemmer ORDER BY hvilken kolonne, der skal bruges til at bestemme kvartilerne (eller hvilket antal af “fliser du angive). For eksempel:

Når du ser på resultaterne fra forespørgslen ovenfor, kan du se, at kolonnen percentile ikke beregner nøjagtigt som du kunne forvente. Hvis du kun havde to poster, og du målte percentiler, du ville forvente, at en post definerede 1. percentil, og den anden record definerede 100. percentil. Ved hjælp af NTILE -funktionen er det, du faktisk ser, en post i 1. percentilen og en i 2. percentilen. Du kan se dette i resultaterne for start_terminal 31000 — kolonnen percentile ligner bare en numerisk placering. Hvis du ruller ned til start_terminal 31007, kan se, at det korrekt beregner percentiler, fordi der er mere end 100 poster for det start_terminal. Hvis du arbejder med meget små vinduer, skal du huske dette og overveje at bruge kvartiler eller lignende små bånd.

LAG og LEAD

Det kan ofte være nyttigt at sammenligne rækker med foregående eller efterfølgende rækker, især hvis du har fået dataene i en rækkefølge, der giver mening. Du kan bruge LAG eller LEAD til at oprette kolonner, der trækker værdier fra andre rækker – alt hvad du skal gøre er at indtaste, hvilken kolonne du vil trække fra, og hvor mange rækker væk du gerne vil trække. LAG trækker fra tidligere rækker og LEAD trækker fra følgende rækker:

Dette er især nyttigt, hvis du vil beregne forskelle mellem rækker:

Den første række i difference kolonnen er nul, fordi der ikke er nogen tidligere række, hvorfra man kan trække. Tilsvarende vil brug af LEAD skabe nuller i slutningen af datasættet. Hvis du gerne vil gøre resultaterne lidt renere, kan du pakke det ind i en ydre forespørgsel for at fjerne nul:

Definere et vinduesalias

Hvis du planlægger at skrive flere vinduer fungerer i den samme forespørgsel ved hjælp af det samme vindue kan du oprette et alias.Tag NTILE eksemplet ovenfor:

Dette kan omskrives som:

WINDOW klausul, hvis den er inkluderet, skal altid komme efter WHERE klausulen.

Avancerede vindueteknikker

Du kan tjekke en komplet liste over vinduer funktioner i Postgres (syntaks-tilstand bruger) i Postgres-dokumentationen. Hvis du bruger vinduesfunktioner i en tilsluttet database, skal du se på den relevante syntaksvejledning til dit system.