De fem bästa SQL-fönsterfunktionerna du bör känna till för datavetenskapsintervjuer

SQL är det universella språket i datavärlden och är den viktigaste färdigheten att spika fast som dataproffs.

Anledningen till att SQL är så viktigt är att det är den huvudsakliga färdigheten som krävs under datatvistningsfasen. En hel del datautforskning, datamanipulation, pipelineutveckling och skapande av instrumentpaneler görs genom SQL.

Det som skiljer stora datavetare från bra datavetare är att stora datavetare kan bråka data så mycket som SQL-kapaciteten tillåter. En stor del av att fullt ut kunna använda allt som SQL har att erbjuda är att veta hur man använder fönsterfunktioner.

Med det sagt, låt oss dyka in i det!

1. Delta med LEAD() och LAG()

 
LEAD() och LAG() används oftast när man jämför en tidsperiod med föregående tidsperiod för ett givet mått. För att ge några exempel...

• Du kan få deltat mellan varje års försäljning och föregående års försäljning
• Du kan få delta i antalet registreringar/konverteringar/webbplatsbesök på månadsbasis
• Du kan jämföra användaravgång på månadsbasis

Exempelvis:

Följande fråga visar hur du kan fråga den månatliga procentuella förändringen av 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. Kumulativa summor med SUM() eller COUNT()

 
Att beräkna löpande summor kan enkelt göras genom en Windows-funktion som börjar med SUM() eller COUNT(). Det här är ett kraftfullt verktyg när du vill visa tillväxten av ett visst mått över tid. Mer specifikt är det användbart under följande omständigheter:

• Få en löpande summa av intäkter och kostnader över tid
• Få en löpande total tid som spenderas på app per användare
• Få en löpande summa av konverteringar över tid

Exempelvis:

Följande exempel visar hur du kan inkludera en ackumulerad summakolumn med månatliga kostnader:

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

3. Rörliga medelvärden med AVG()

 
AVG() är verkligen kraftfull i Windows-funktioner eftersom den låter dig beräkna glidande medelvärden över tid.

Glidande medelvärden är ett enkelt men effektivt sätt att prognostisera värden på kort sikt. De är också extremt användbara för att jämna ut flyktiga kurvor på en graf. I allmänhet används glidande medelvärden för att mäta den allmänna riktningen för var saker rör sig.

Mer specifikt…

• De kan användas för att få den allmänna trenden för veckoförsäljning (går genomsnittet upp över tiden?). Detta skulle tyda på tillväxt som företag.
• De kan också användas för att se den allmänna trenden med veckokonverteringar eller webbplatsbesök.

Exempelvis:

Följande fråga är ett exempel på att få ett 10-dagars glidande medelvärde för omvandlingar.

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

4. ROW_NUMBER()

 
ROW_NUMBER() är särskilt användbart när du vill få den första eller sista posten. Om du till exempel har en tabell över när gymmedlemmar kom till gymmet och du vill få datumet för första dagen då de kom till gymmet, kan du DELA EFTER kund (namn/id) och BESTÄLL EFTER inköpsdatum. Sedan, för att få den första raden, kan du helt enkelt filtrera efter raderna med rowNumber lika med en.

Exempelvis:

Det här exemplet visar hur du kan använda ROW_NUMBER() för att få det första datumet då varje medlem (användare) 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

För att sammanfatta, om du någonsin behöver få den första eller sista posten, är ROW_NUMBER() ett bra sätt att uppnå det.

5. Rekordrankning med DENSE_RANK()

 
DENSE_RANK() liknar ROW_NUMBER() förutom att den returnerar samma rankning för lika värden. Tät ranking är ganska användbart när det gäller att hämta topprekorden, till exempel:

• Om du vill dra de 10 mest sedda Netflix-programmen den här veckan
• Om du vill få de 100 bästa användarna baserat på spenderade dollar
• Om du vill se beteendet hos de 1000 minst aktiva användarna

Exempelvis:

Om du vill ranka dina bästa kunder efter total försäljning, skulle DENSE_RANK() vara en lämplig funktion att använda.

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

Tack för att du läser!

 
Och det är allt! Jag hoppas att detta hjälper dig i din intervjuförberedelse — jag är säker på att om du kan dessa 5 begrepp utan och innan, kommer du att klara dig bra när det kommer till de flesta SQL-fönsterfunktionsproblem där ute.

Som alltid önskar jag dig lycka till i dina inlärningssträvanden!

 
 
Terence Shin är en dataentusiast med 3+ års erfarenhet av SQL och 2+ års erfarenhet av Python, och en bloggare på Towards Data Science och KDnuggets.

Ursprungliga. Skickas om med tillstånd.

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