Pet najboljših okenskih funkcij SQL, ki bi jih morali poznati za intervjuje o podatkovni znanosti

SQL je univerzalni jezik v podatkovnem svetu in je najpomembnejša veščina, ki jo morate pridobiti kot podatkovni strokovnjak.

Razlog, zakaj je SQL tako pomemben, je, da je to glavna veščina, ki je potrebna v fazi prepiranja podatkov. Veliko raziskovanja podatkov, manipulacije s podatki, razvoja cevovoda in ustvarjanja nadzorne plošče poteka prek SQL.

Odlične podatkovne znanstvenike od dobrih podatkovnih znanstvenikov loči to, da lahko odlični podatkovni znanstveniki prepirajo podatke, kolikor dovoljujejo zmožnosti SQL. Velik del tega, da lahko v celoti uporabite vse, kar ponuja SQL, je znanje o uporabi okenskih funkcij.

S tem rečeno, potopimo se vanj!

1. Delte z LEAD() in LAG()

 
LEAD() in LAG() se večinoma uporabljata pri primerjavi enega časovnega obdobja s prejšnjim časovnim obdobjem za določeno metriko. Če navedem nekaj primerov ...

• Dobite lahko delto med vsakoletno prodajo in prodajo prejšnjega leta
• Dobite lahko delto v številu prijav/konverzij/obiskov spletnega mesta na mesečni ravni
• Odliv uporabnikov lahko primerjate na mesečni ravni

primer:

Naslednja poizvedba prikazuje, kako lahko izvedete poizvedbo o mesečni odstotni spremembi stroškov

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. Kumulativne vsote s SUM() ali COUNT()

 
Izračun tekočih vsot je mogoče preprosto izvesti s funkcijo Windows, ki se začne s SUM() ali COUNT(). To je močno orodje, ko želite prikazati rast določene metrike skozi čas. Natančneje, uporaben je v naslednjih okoliščinah:

• Zagotovite tekoče skupne prihodke in stroške skozi čas
• Pridobite trenutno skupno količino časa, porabljenega v aplikaciji, na uporabnika
• Pridobite tekoče skupno število konverzij skozi čas

primer:

Naslednji primer prikazuje, kako lahko vključite stolpec kumulativne vsote mesečnih stroškov:

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

3. Drseča povprečja z AVG()

 
AVG() je res močan v funkcijah sistema Windows, saj vam omogoča računanje drseče povprečje čez čas.

Drseča povprečja so preprost, a učinkovit način za kratkoročno napovedovanje vrednosti. Izjemno uporabni so tudi pri glajenju nestanovitnih krivulj na grafu. Na splošno se drseča povprečja uporabljajo za merjenje splošne smeri gibanja stvari.

Natančneje…

• Uporabijo se lahko za pridobitev splošnega trenda tedenske prodaje (ali povprečje sčasoma raste?). To bi pomenilo rast podjetja.
• Prav tako jih je mogoče uporabiti za pridobitev splošnega trenda tedenskih konverzij ali obiskov spletnega mesta.

primer:

Naslednja poizvedba je primer pridobivanja 10-dnevnega drsečega povprečja za konverzije.

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

4. ROW_NUMBER()

 
ROW_NUMBER() je še posebej uporabna, ko želite pridobiti prvi ali zadnji zapis. Na primer, če imate tabelo, kdaj so člani telovadnice prišli v telovadnico, in želite dobiti datum prvega dne, ko so prišli v telovadnico, lahko RAZDELITE PO stranki (ime/ID) in VRSTITE PO datumu nakupa. Če želite nato dobiti prvo vrstico, lahko preprosto filtrirate vrstice s rowNumber, ki je enaka ena.

primer:

Ta primer prikazuje, kako lahko uporabite ROW_NUMBER(), da dobite prvi datum obiska vsakega člana (uporabnika).

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

Če povzamemo, če boste kdaj morali dobiti prvi ali zadnji zapis, je ROW_NUMBER() odličen način za to.

5. Zabeležite uvrstitev z DENSE_RANK()

 
DENSE_RANK() je podoben ROW_NUMBER(), le da vrne isti rang za enake vrednosti. Gosto razvrščanje je zelo koristno, ko gre za pridobivanje najboljših zapisov, na primer:

• Če želite izvleči 10 najbolj gledanih Netflixovih oddaj ta teden
• Če želite pridobiti 100 najboljših uporabnikov na podlagi porabljenih dolarjev
• Če želite videti vedenje 1000 najmanj aktivnih uporabnikov

primer:

Če želite svoje najboljše stranke razvrstiti glede na skupno prodajo, bi bila ustrezna funkcija DENSE_RANK().

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

Hvala za branje!

 
In to je vse! Upam, da vam bo to pomagalo pri pripravi na razgovor – prepričan sem, da boste, če poznate teh 5 konceptov od znotraj navzven, odlični, ko gre za večino težav s funkcijami oken SQL.

Kot vedno vam želim vse najboljše pri vaših učnih prizadevanjih!

 
 
Terence Shin je navdušenec nad podatki s 3+ leti izkušenj s SQL in 2+ leti izkušenj s Pythonom ter bloger na Towards Data Science in KDnuggets.

prvotni. Poročeno z dovoljenjem.

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