SQL-fönsterfunktioner

Börjar du här? Den här lektionen är en del av en fullständig handledning i att använda SQL för dataanalys. Kolla in början.

I den här lektionen täcker vi:

• Introduktion till fönsterfunktioner
• Grundläggande fönstersyntax
• Vanliga misstänkta: SUM, COUNT och AVG
• ROW_NUMBER ()
• RANK () och DENSE_RANK ()
• NTILE
• LAG och LEAD
• Definiera ett fönsteralias
• Avancerade fönstertekniker

Denna lektion använder data från Washington DC: s Capital Bikeshare Program, som publicerar detaljerad historisk data på resanivå på deras webbplats. Uppgifterna laddades ner i februari 2014, men är begränsade till data som samlats in under första kvartalet 2012. Varje rad representerar en åktur. De flesta fält är självförklarande, förutom rider_type: ”Registrerad” anger ett månatligt medlemskap i rideshare-programmet, ”Casual” leder till att föraren köpte ett 3-dagarspass. Fälten start_time och end_time rensades från sina ursprungliga former för att passa SQL-datumformatering – de lagras i denna tabell som tidsstämplar.

Introduktion till fönsterfunktioner

PostgreSQLs dokumentation gör ett utmärkt jobb med att introducera konceptet med fönsterfunktioner:

En fönsterfunktion utför en beräkning över en uppsättning tabellrader som på något sätt är relaterade till den aktuella raden. Detta kan jämföras med den typ av beräkning som kan göras med en aggregerad funktion. funktionen orsakar inte att rader grupperas i en enda utgångsrad – raderna behåller sina separata identiteter. Bakom kulisserna kan fönsterfunktionen komma åt mer än bara den aktuella raden i frågeresultatet.

Det mest praktiska exemplet på detta är en löpande summa:

Du kan se att ovanstående fråga skapar en aggregering (running_total) utan att använda GROUP BY. Låt oss bryta ner syntaxen och se hur den fungerar.

Grundläggande fönstersyntax

Den första delen av ovanstående aggregering, SUM(duration_seconds) , ser ut som alla andra aggregeringar. Att lägga till OVER anger det som en fönsterfunktion. Du kan läsa ovanstående aggregation som ”ta summan av duration_seconds över hela resultatuppsättningen, i ordning efter start_time. ”

Om du vill begränsa fönstret från hela datasetet till enskilda grupper inom datauppsättningen kan du använda PARTITION BY för att göra det:

Ovanstående frågegrupper och beställer frågan med start_terminal . Inom varje värde av start_terminal, ordnas det av start_time, och de löpande summan över den aktuella raden och alla tidigare rader av duration_seconds. Rulla ner tills start_terminal -värdet ändras och du kommer att märka att running_total börjar om. Det är vad som händer när du grupperar med PARTITION BY. Om du fortfarande ”stubbar” av ORDER BY, beställer den helt enkelt efter den angivna kolumnen (s) på samma sätt som ORDER BY -satsen skulle göra, förutom att den behandlar varje partition som separat. Det skapar också den totala summan – utan ORDER BY blir varje värde helt enkelt en summa av alla duration_seconds värden i respektive start_terminal. Försök att köra ovanstående fråga utan ORDER BY för att få en idé:

ORDER och PARTITION definiera vad som kallas” fönster ”- den ordnade delmängden av data över vilka beräkningar görs.

Obs! Du kan inte använda fönsterfunktioner och standardaggregeringar i samma Mer specifikt kan du inte inkludera fönsterfunktioner i en GROUP BY -sats.

De vanliga misstänkta: SUM, COUNT och AVG

När du använder fönsterfunktioner kan du använda samma aggregat som du skulle göra under normala omständigheter— SUM, COUNT och AVG. Det enklaste sättet att förstå dessa är att köra om föregående exempel med några ytterligare funktioner. Gör

Alternativt, samma funktioner med ORDER BY:

Se till att du ansluter de två föregående frågorna till läge och kör dem. Det här nästa övningsproblemet är mycket likt exemplen, så försök att ändra ovanstående kod istället för att börja om från början.

ROW_NUMBER ()

ROW_NUMBER() gör precis som det låter – visar numret på en viss rad. Det börjar är 1 och numrerar raderna enligt ORDER BY delen av fönstret. ROW_NUMBER() kräver inte att du anger en variabel inom parentes:

Med PARTITION BY -satsen kan du börja räkna 1 igen i varje partition. Följande fråga startar räkningen igen för varje terminal:

RANK () och DENSE_RANK ()

RANK() skiljer sig något från ROW_NUMBER(). Om du till exempel beställer efter start_time kan det vara så att vissa terminaler har åkattraktioner med två identiska starttider. I det här fallet får de samma rangordning, medan ROW_NUMBER() ger dem olika siffror. I följande fråga märker du den 4: e och 5: e observationen för start_terminal 31000 – de får båda rankningen 4 och följande resultat får 6:

Du kan också använda DENSE_RANK() istället för RANK() beroende på din applikation. Föreställ dig en situation där tre poster har samma värde. Genom att använda båda kommandona får de alla samma rang. För detta exempel, låt oss säga det är s 2. Här är hur de två kommandona skulle utvärdera nästa resultat annorlunda:

• RANK() skulle ge identiska rader en rangordning på 2 och sedan hoppa över rank 3 och 4, så nästa resultat skulle vara 5
• DENSE_RANK() skulle fortfarande ge alla identiska rader 2, men följande rad skulle vara 3 — nej rank skulle hoppas över.

NTILE

Du kan använda fönsterfunktioner för att identifiera vilken percentil (eller kvartil eller vilken annan underavdelning) som en viss rad faller i. Syntaxen är NTILE(*# of buckets*) I det här fallet bestämmer ORDER BY vilken kolumn som ska användas för att bestämma kvartilerna (eller vilket antal ”brickor du vill specificera). Till exempel:

Om du tittar på resultaten från frågan ovan ser du att kolumnen percentile inte beräknar exakt som du kan förvänta dig. hade två poster och du mätte percentiler, du skulle förvänta dig att en post skulle definiera den 1: e percentilen och den andra att definiera den 100: e percentilen. Med funktionen NTILE är vad du faktiskt ser en post i 1: a percentilen och en i 2: a percentilen. Du kan se detta i resultaten för start_terminal 31000 — percentile -kolumnen ser bara ut som en numerisk ranking. Om du rullar ner till start_terminal 31007, kan se att den beräknar procentiler korrekt eftersom det finns mer än 100 poster för den start_terminal. Om du arbetar med mycket små fönster, kom ihåg detta och överväg att använda kvartiler eller liknande små band.

LAG och LEAD

Det kan ofta vara användbart att jämföra rader med föregående eller efterföljande rader, särskilt om du har fått data i en ordning som är vettig. Du kan använda LAG eller LEAD för att skapa kolumner som drar värden från andra rader – allt du behöver göra är att ange vilken kolumn du vill dra från och hur många rader du vill göra. LAG drar från tidigare rader och LEAD drar från följande rader:

Detta är särskilt användbart om du vill beräkna skillnader mellan rader:

Den första raden i kolumnen difference är noll eftersom det inte finns någon tidigare rad att dra från. På samma sätt kommer att använda LEAD att skapa nollor i slutet av datasetet. Om du vill göra resultaten lite renare kan du slå in den i en yttre fråga för att ta bort nollor:

Definiera ett fönsteralias

Om du planerar att skriva flera fönsterfunktioner i samma fråga, med samma fönster kan du skapa ett alias.Ta NTILE exempel ovan:

Detta kan skrivas om som:

WINDOW klausul, om den ingår, ska alltid komma efter WHERE klausulen.

Avancerade fönstertekniker

Du kan kolla in en fullständig lista med fönster funktioner i Postgres (syntaxläget använder) i Postgres-dokumentationen. Om du använder fönsterfunktioner i en ansluten databas bör du titta på lämplig syntaxguide för ditt system.