Topp fem SQL-vindusfunksjoner du bør kjenne til for datavitenskapsintervjuer

SQL er det universelle språket i dataverdenen og er den viktigste ferdigheten som er dataprofesjonell.

Grunnen til at SQL er så viktig er at det er hovedferdigheten som kreves under datakrangelfasen. Mye datautforskning, datamanipulering, pipelineutvikling og dashbordoppretting gjøres gjennom SQL.

Det som skiller store dataforskere fra gode dataforskere er at gode dataforskere kan krangle med data så mye som egenskapene til SQL tillater. En stor del av å kunne fullt ut bruke alt SQL har å tilby er å vite hvordan man bruker vindusfunksjoner.

Med det sagt, la oss dykke ned i det!

1. Deltaer med LEAD() og LAG()

 
LEAD() og LAG() brukes mest når man sammenligner en tidsperiode med forrige tidsperiode for en gitt beregning. For å gi noen eksempler...

• Du kan få deltaet mellom hvert års salg og forrige års salg
• Du kan få delta i antall registreringer/konverteringer/nettsidebesøk på månedlig basis
• Du kan sammenligne brukeravgang på månedlig basis

Eksempel:

Følgende spørring viser hvordan du kan spørre etter den månedlige prosentvise endringen i kostnader

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Kumulative summer med SUM() eller COUNT()

 
Beregning av løpende totaler kan enkelt gjøres gjennom en Windows-funksjon som starter med SUM() eller COUNT(). Dette er et kraftig verktøy når du vil vise veksten til en bestemt beregning over tid. Mer spesifikt er det nyttig under følgende omstendigheter:

• Få en løpende sum av inntekter og kostnader over tid
• Få en løpende total tid brukt på appen per bruker
• Få et løpende antall konverteringer over tid

Eksempel:

Følgende eksempel viser hvordan du kan inkludere en kumulativ sumkolonne med månedlige kostnader:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Glidende gjennomsnitt med AVG()

 
AVG() er veldig kraftig i Windows-funksjoner da den lar deg beregne glidende gjennomsnitt over tid.

Glidende gjennomsnitt er en enkel, men effektiv måte å forutsi verdier på kort sikt. De er også ekstremt nyttige for å jevne ut flyktige kurver på en graf. Vanligvis brukes glidende gjennomsnitt for å måle den generelle retningen for hvor ting beveger seg.

Mer spesifikt…

• De kan brukes til å få den generelle trenden for ukentlige salg (går gjennomsnittet opp over tid?). Dette indikerer vekst som et selskap.
• De kan også brukes til å få den generelle trenden med ukentlige konverteringer eller nettstedbesøk.

Eksempel:

Følgende spørring er et eksempel på å få 10-dagers glidende gjennomsnitt for konverteringer.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. ROW_NUMBER()

 
ROW_NUMBER() er spesielt nyttig når du ønsker å få den første eller siste posten. For eksempel, hvis du har en tabell over når treningsmedlemmene kom til treningsstudioet og du ønsker å få datoen for den første dagen de kom til treningsstudioet, kan du DELTE ETTER kunde (navn/id) og BESTILLE ETTER kjøpsdato. Deretter, for å få den første raden, kan du ganske enkelt filtrere etter radene med rowNumber lik én.

Eksempel:

Dette eksemplet viser hvordan du kan bruke ROW_NUMBER() for å få den første datoen for når hvert medlem (bruker) besøkte.

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

For å oppsummere, hvis du noen gang trenger å få den første eller siste posten, er ROW_NUMBER() en fin måte å oppnå det på.

5. Rekordrangering med DENSE_RANK()

 
DENSE_RANK() er lik ROW_NUMBER() bortsett fra at den returnerer samme rangering for like verdier. Tett rangering er ganske nyttig når det gjelder å hente topprekordene, for eksempel:

• Hvis du vil trekke de 10 mest sette Netflix-programmene denne uken
• Hvis du ønsker å få de 100 beste brukerne basert på brukte dollar
• Hvis du vil se oppførselen til de 1000 minst aktive brukerne

Eksempel:

Hvis du ønsker å rangere toppkundene dine etter totalt salg, vil DENSE_RANK() være en passende funksjon å bruke.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Takk for at du leste!

 
Og det er alt! Jeg håper at dette hjelper deg med forberedelsene til intervjuet - jeg er sikker på at hvis du kjenner disse 5 konseptene ut og inn, vil du gjøre det bra når det kommer til de fleste SQL-vindusfunksjonsproblemer der ute.

Som alltid ønsker jeg deg lykke til i læringsarbeidet ditt!

 
 
Terence Shin er en dataentusiast med 3+ års erfaring i SQL og 2+ års erfaring i Python, og en blogger på Towards Data Science og KDnuggets.

original. Ompostet med tillatelse.

Kilde: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html