SQL-vindusfunksjoner

Starter du her? Denne leksjonen er en del av en fullstendig opplæring i bruk av SQL til dataanalyse. Sjekk ut begynnelsen.

I denne leksjonen vil vi dekke:

• Introduksjon til vindusfunksjoner
• Grunnleggende syntaks for vindu
• De vanlige mistenkte: SUM, COUNT og AVG
• ROW_NUMBER ()
• RANK () og DENSE_RANK ()
• NTILE
• LAG og LEAD
• Definere et vindusalias
• Avanserte vindueteknikker

Denne leksjonen bruker data fra Washington DCs Capital Bikeshare Program, som publiserer detaljerte historiske data på turnivå på deres nettside. Dataene ble lastet ned i februar 2014, men er begrenset til data samlet inn i første kvartal 2012. Hver rad representerer en tur. De fleste felt er selvforklarende, bortsett fra rider_type: «Registrert» indikerer et månedlig medlemskap i rideshare-programmet, «Casual» tilskynder at rytteren kjøpte et 3-dagers pass. start_time og end_time -feltene ble ryddet fra de opprinnelige skjemaene for å passe SQL-datformatering – de er lagret i denne tabellen som tidsstempler.

Introduksjon til vindusfunksjoner

Dokumentasjonen til PostgreSQL gjør en utmerket jobb med å introdusere konseptet med Window Functions:

En vindusfunksjon utfører en beregning på tvers av et sett med tabellrader som på en eller annen måte er relatert til den gjeldende raden. Dette kan sammenlignes med typen beregning som kan gjøres med en samlet funksjon. funksjonen fører ikke til at radene blir gruppert i en enkelt utgangsrad – radene beholder hver sin identitet. Bak kulissene har vinduefunksjonen tilgang til mer enn bare den gjeldende raden i spørringsresultatet.

Det mest praktiske eksemplet på dette er en løpende total:

Du kan se at spørringen ovenfor skaper en aggregering (running_total) uten å bruke GROUP BY. La oss bryte ned syntaksen og se hvordan den fungerer.

Grunnleggende syntaks for vinduer

Den første delen av ovennevnte aggregering, SUM(duration_seconds) , ser ut som alle andre aggregeringer. Ved å legge til OVER betegnes det som en vindusfunksjon. Du kan lese ovennevnte aggregering som «ta summen av duration_seconds over hele resultatsettet, i rekkefølge etter start_time. «

Hvis du ønsker å begrense vinduet fra hele datasettet til individuelle grupper innen datasettet, kan du bruke PARTITION BY for å gjøre det:

Ovennevnte spørringer grupperer og bestiller spørringen av start_terminal . Innenfor hver verdi av start_terminal, er den ordnet etter start_time, og de løpende totalsummen over den nåværende raden og alle tidligere rader med duration_seconds. Rull ned til start_terminal verdien endres, og du vil legge merke til at running_total starter på nytt. Det er det som skjer når du grupperer ved hjelp av PARTITION BY. Hvis du fremdeles blir stumpet av ORDER BY, bestiller den ganske enkelt etter den angitte kolonnen (s) på samme måte som ORDER BY -satsen, bortsett fra at den behandler hver partisjon som separat. Det oppretter også den totale summen – uten ORDER BY, vil hver verdi ganske enkelt være en sum av alle duration_seconds verdiene i sin respektive start_terminal. Prøv å kjøre spørringen ovenfor uten ORDER BY for å få en idé:

ORDER og PARTITION definer det som blir referert til som» vinduet «- det ordnede delsettet med data som beregningene gjøres over.

Merk: Du kan ikke bruke vindusfunksjoner og standardaggregasjoner i samme Mer spesifikt kan du ikke inkludere vindusfunksjoner i en GROUP BY -klausul.

De vanlige mistenkte: SUM, COUNT og AVG

Når du bruker vindusfunksjoner, kan du bruke de samme aggregatene som du ville gjort under normale omstendigheter— SUM, COUNT, og AVG. Den enkleste måten å forstå disse på er å kjøre det forrige eksemplet på nytt med noen tilleggsfunksjoner. Lag

Alternativt, de samme funksjonene med ORDER BY:

Sørg for at du kobler de to foregående spørsmålene til Mode og kjører dem. Dette neste øvelsesproblemet er veldig likt eksemplene, så prøv å endre ovennevnte kode i stedet for å starte fra bunnen av.

ROW_NUMBER ()

ROW_NUMBER() gjør akkurat slik det høres ut – viser nummeret på en gitt rad. Det starter er 1 og nummererer radene i henhold til ORDER BY -delen av vindusuttrykket. ROW_NUMBER() krever ikke at du spesifiserer en variabel i parentes:

Ved å bruke PARTITION BY -klausulen kan du begynn å telle 1 igjen i hver partisjon. Følgende spørsmål starter tellingen igjen for hver terminal:

RANK () og DENSE_RANK ()

RANK() er litt annerledes enn ROW_NUMBER(). Hvis du for eksempel bestiller etter start_time, kan det være slik at noen terminaler har turer med to identiske starttider. I dette tilfellet får de samme rang, mens ROW_NUMBER() gir dem forskjellige tall. I det følgende spørsmålet legger du merke til 4. og 5. observasjon for start_terminal 31000 — begge får rangering 4, og følgende resultat får rang 6:

Du kan også bruke DENSE_RANK() i stedet for RANK() avhengig av applikasjonen din. Tenk deg en situasjon der tre oppføringer har samme verdi. Ved å bruke en av kommandoene får de alle samme rang. Av hensyn til dette eksemplet, la «s si det» s «2.» Her er hvordan de to kommandoene vil evaluere de neste resultatene annerledes:

• RANK() vil gi de samme radene en rangering på 2 og deretter hoppe over rang 3 og 4, så neste resultat vil være 5
• DENSE_RANK() vil fremdeles gi alle de identiske radene en rangering på 2, men den neste raden vil være 3 — nei rangeringer vil bli hoppet over.

NTILE

Du kan bruke vindusfunksjoner til å identifisere hvilken prosentil (eller kvartil, eller hvilken som helst annen underavdeling) en gitt rad faller inn i. Syntaksen er NTILE(*# of buckets*) I dette tilfellet bestemmer ORDER BY hvilken kolonne som skal brukes til å bestemme kvartilene (eller hvilket som helst antall «fliser du spesifisere). For eksempel:

Når du ser på resultatene fra spørringen ovenfor, kan du se at percentile -kolonnen ikke beregner nøyaktig slik du kunne forvente. Hvis du bare hadde to poster, og du målte persentiler, du ville forvente at en post skulle definere den første persentilen, og den andre posten å definere den 100. persentilen. Ved å bruke NTILE -funksjonen, er det du faktisk ser en post i 1. persentilen og en i den andre persentilen. Du kan se dette i resultatene for start_terminal 31000 — percentile -kolonnen ser bare ut som en numerisk rangering. Hvis du blar ned til start_terminal 31007, vil du kan se at den beregner riktig persentiler på riktig måte fordi det er mer enn 100 poster for den start_terminal. Hvis du jobber med veldig små vinduer, må du huske på dette og vurdere å bruke kvartiler eller lignende små bånd.

LAG og LEAD

Det kan ofte være nyttig å sammenligne rader med forrige eller påfølgende rader, spesielt hvis du har fått dataene i en rekkefølge som gir mening. Du kan bruke LAG eller LEAD til å lage kolonner som trekker verdier fra andre rader – alt du trenger å gjøre er å angi hvilken kolonne du vil trekke fra og hvor mange rader du vil gjøre. LAG trekker fra forrige rader og LEAD trekker fra følgende rader:

Dette er spesielt nyttig hvis du vil beregne forskjeller mellom rader:

Den første raden i difference -kolonnen er null fordi det ikke er noen tidligere rad å trekke fra. På samme måte vil bruk av LEAD opprette null på slutten av datasettet. Hvis du vil gjøre resultatene litt renere, kan du pakke det inn i en ytre forespørsel for å fjerne null:

Definere et vindusalias

Hvis du planlegger å skrive flere vindusfunksjoner i den samme spørringen, ved å bruke det samme vinduet, kan du opprette et alias.Ta NTILE eksemplet ovenfor:

Dette kan skrives om som:

WINDOW klausul, hvis den er inkludert, bør alltid komme etter WHERE klausulen.

Avanserte vindueteknikker

Du kan sjekke ut en fullstendig liste over vinduer funksjoner i Postgres (syntaksmodus bruker) i Postgres-dokumentasjonen. Hvis du bruker vindusfunksjoner i en tilkoblet database, bør du se på riktig syntaksveiledning for systemet ditt.