SQL-vensterfuncties

Hier beginnen? Deze les maakt deel uit van een volledige tutorial over het gebruik van SQL voor gegevensanalyse. Bekijk het begin.

In deze les behandelen we:

• Inleiding tot vensterfuncties
• Basissyntaxis voor vensters
• De gebruikelijke verdachten: SUM, COUNT en AVG
• ROW_NUMBER ()
• RANK () en DENSE_RANK ()
• NTILE
• LAG en LEAD
• Een vensteralias definiëren
• Geavanceerde venstertechnieken

Deze les maakt gebruik van gegevens van het Capital Bikeshare-programma in Washington DC, dat publiceert gedetailleerde historische gegevens op ritniveau op hun website. De gegevens zijn gedownload in februari 2014, maar zijn beperkt tot gegevens verzameld tijdens het eerste kwartaal van 2012. Elke rij vertegenwoordigt één rit. De meeste velden spreken voor zich, behalve rider_type: “Geregistreerd” duidt op een maandelijks lidmaatschap van het rideshare-programma, “Casual” geeft aan dat de rijder een driedaagse pas heeft gekocht. De start_time en end_time velden zijn opgeschoond van hun oorspronkelijke formulieren zodat ze passen bij de SQL-datumnotatie – ze worden in deze tabel opgeslagen als tijdstempels.

Inleiding tot vensterfuncties

De documentatie van PostgreSQL introduceert uitstekend het concept van vensterfuncties:

Een vensterfunctie voert een berekening uit over een set tabelrijen die op de een of andere manier gerelateerd zijn aan de huidige rij. Dit is vergelijkbaar met het type berekening dat kan worden gedaan met een aggregatiefunctie. Maar in tegenstelling tot gewone aggregatiefuncties, is het gebruik van een venster functie zorgt er niet voor dat rijen worden gegroepeerd in een enkele uitvoerrij – de rijen behouden hun afzonderlijke identiteiten. Achter de schermen heeft de vensterfunctie toegang tot meer dan alleen de huidige rij van het queryresultaat.

Het meest praktische voorbeeld hiervan is een lopend totaal:

U kunt zien dat de bovenstaande zoekopdracht een aggregatie (running_total) zonder GROUP BY te gebruiken. Laten we de syntaxis opsplitsen en kijken hoe het werkt.

Basissyntaxis voor vensters

Het eerste deel van de bovenstaande aggregatie, SUM(duration_seconds) , lijkt veel op elke andere aggregatie. Door OVER toe te voegen, wordt het als een vensterfunctie aangeduid. Je zou de bovenstaande aggregatie kunnen lezen als “neem de som van duration_seconds over de gehele resultatenset, in volgorde van start_time. “

Als u” het venster van de gehele dataset wilt verkleinen tot individuele groepen binnen de dataset, kunt u PARTITION BY gebruiken om dit te doen:

De bovenstaande query groepeert en rangschikt de query op start_terminal . Binnen elke waarde van start_terminal wordt deze geordend op start_time, en de lopende totale sommen voor de huidige rij en alle voorgaande rijen van duration_seconds. Scroll naar beneden totdat de start_terminal -waarde verandert en je zult zien dat running_total opnieuw begint. Dat is wat er gebeurt als je groepeert met PARTITION BY. In het geval je “nog steeds versteld staat van ORDER BY, wordt het geordend op basis van de aangegeven kolom (s) op dezelfde manier als de ORDER BY -clausule, behalve dat het elke partitie als afzonderlijk behandelt. Het maakt ook het lopende totaal. Zonder ORDER BY is elke waarde gewoon een som van alle duration_seconds waarden in de respectievelijke start_terminal. Probeer de bovenstaande zoekopdracht uit te voeren zonder ORDER BY om een idee te krijgen:

De ORDER en PARTITION definiëren wat wordt aangeduid als het” venster “- de geordende subset van gegevens waarover berekeningen worden gemaakt.

Opmerking: u kunt” vensterfuncties en standaardaggregaties niet in dezelfde query. Meer specifiek, u kunt “geen vensterfuncties opnemen in een GROUP BY -clausule.

De gebruikelijke verdachten: SUM, COUNT en AVG

Wanneer u vensterfuncties gebruikt, kunt u dezelfde aggregaten toepassen die u onder normale omstandigheden zou toepassen – SUM, COUNT, en AVG. De eenvoudigste manier om deze te begrijpen, is door het vorige voorbeeld opnieuw uit te voeren met enkele extra functies. Maak

Als alternatief, dezelfde functies met ORDER BY:

Zorg ervoor dat je die vorige twee queries in Mode plugt en voer ze uit. Dit volgende oefenprobleem lijkt sterk op de voorbeelden, dus probeer de bovenstaande code te wijzigen in plaats van helemaal opnieuw te beginnen.

ROW_NUMBER ()

ROW_NUMBER() doet precies wat het klinkt: geeft het nummer van een bepaalde rij weer. Het begint met 1 en nummert de rijen volgens het ORDER BY -gedeelte van de window-instructie. ROW_NUMBER() vereist niet dat u een variabele tussen haakjes specificeert:

Door de PARTITION BY clausule te gebruiken, kunt u begin opnieuw 1 te tellen in elke partitie. De volgende zoekopdracht start het tellen opnieuw voor elke terminal:

RANK () en DENSE_RANK ()

RANK() verschilt enigszins van ROW_NUMBER(). Als je bijvoorbeeld bestelt per start_time, kan het zijn dat sommige terminals ritten hebben met twee identieke starttijden. In dit geval krijgen ze dezelfde rang, terwijl ROW_NUMBER() ze verschillende nummers geeft. In de volgende zoekopdracht ziet u de 4e en 5e observatie voor start_terminal 31000 – ze krijgen allebei de rangorde 4 en het volgende resultaat krijgt de rang 6:

U kunt ook DENSE_RANK() gebruiken in plaats van RANK(), afhankelijk van uw toepassing. Stel je een situatie voor waarin drie items dezelfde waarde hebben. Met een van beide commando’s krijgen ze allemaal dezelfde rang. Laten we voor dit voorbeeld zeggen dat het “s” 2 is. ” Dit is hoe de twee commando’s de volgende resultaten anders zouden evalueren:

• RANK() zouden de identieke rijen een rangorde van 2 geven en dan rang 3 overslaan en 4, dus het volgende resultaat zou 5 zijn
• DENSE_RANK() zou nog steeds alle identieke rijen een positie van 2 geven, maar de volgende rij zou 3 zijn – nee rangen worden overgeslagen.

NTILE

U kunt vensterfuncties gebruiken om te bepalen in welk percentiel (of kwartiel of een andere onderverdeling) een bepaalde rij valt. De syntaxis is NTILE(*# of buckets*). In dit geval bepaalt ORDER BY welke kolom moet worden gebruikt om de kwartielen te bepalen (of welk aantal “tegels u ook specificeren). Bijvoorbeeld:

Als u de resultaten van de bovenstaande zoekopdracht bekijkt, kunt u zien dat de kolom percentile niet precies zo berekent als u zou verwachten. had twee records en je was percentielen aan het meten, je zou verwachten dat het ene record het eerste percentiel definieert en het andere record het 100e percentiel. Als u de functie NTILE gebruikt, “zou u eigenlijk één record in het eerste percentiel en één in het tweede percentiel zien. U kunt dit zien in de resultaten voor start_terminal 31000 – de percentile -kolom ziet eruit als een numerieke rangschikking. Als u omlaag scrolt naar start_terminal 31007, kan zien dat het percentielen correct berekent, omdat er meer dan 100 records zijn voor die start_terminal. Als u met zeer kleine vensters werkt, houd hier dan rekening mee en overweeg om kwartielen of vergelijkbare kleine bands.

LAG en LEAD

Het kan vaak handig zijn om rijen te vergelijken met voorgaande of volgende rijen, vooral als u “de gegevens in een logische volgorde hebt geplaatst. U kunt LAG of LEAD gebruiken om kolommen te maken die waarden uit andere rijen – het enige dat u hoeft te doen, is invoeren uit welke kolom u wilt trekken en uit hoeveel rijen verder u de pull wilt doen. LAG haalt uit vorige rijen en LEAD haalt uit volgende rijen:

Dit is vooral handig als je wilt berekenen verschillen tussen rijen:

De eerste rij van de difference kolom is null omdat er geen vorige rij is om uit te halen. Evenzo zal het gebruik van LEAD null-waarden creëren aan het einde van de dataset. Als u “de resultaten wat schoner wilt maken, kunt u deze in een buitenste query plaatsen om null-waarden te verwijderen:

Een vensteralias definiëren

Als u van plan bent om te schrijven verschillende vensterfuncties in dezelfde query, met hetzelfde venster kunt u een alias maken.Neem het NTILE voorbeeld hierboven:

Dit kan worden herschreven als:

Het WINDOW clausule, indien opgenomen, moet altijd na de WHERE clausule komen.

Geavanceerde venstertechnieken

U kunt een volledige lijst met vensters bekijken functies in Postgres (de syntaxis die Mode gebruikt) in de Postgres-documentatie. Als u vensterfuncties op een aangesloten database gebruikt, moet u de juiste syntaxisgids voor uw systeem raadplegen.