Viis parimat SQL-akna funktsiooni, mida peaksite andmeteaduse intervjuude jaoks teadma

SQL on andmemaailma universaalne keel ja see on andmeprofessionaalina kõige olulisem oskus.

Põhjus, miks SQL on nii oluline, seisneb selles, et see on peamine oskus, mida andmete vaidlustamise etapis nõutakse. SQL-i kaudu tehakse palju andmete uurimist, andmetega manipuleerimist, konveieri arendust ja armatuurlaua loomist.

Suurepäraseid andmeteadlasi eristab headest andmeteadlastest see, et suured andmeteadlased saavad andmetega kaklema nii palju, kui SQL-i võimalused seda võimaldavad. SQL-i kõigi pakutavate võimaluste täielikuks kasutamiseks on suur osa aknafunktsioonide kasutamise teadmisest.

Seda öeldes sukeldume sellesse!

1. Deltad koos LEAD() ja LAG()

 
LEAD() ja LAG() kasutatakse enamasti ühe ajavahemiku võrdlemisel antud mõõdiku eelmise perioodiga. Toon paar näidet…

• Saate määrata iga aasta müügi ja eelmise aasta müügi vahelise delta
• Saate näha registreerumiste/konversioonide/veebisaidikülastuste arvu delta kuust kuusse
• Saate võrrelda kasutajate vähenemist igakuiselt

Näide:

Järgmine päring näitab, kuidas saate küsida kulude igakuist protsendimuutust

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. Kumulatiivsed summad koos SUM() või COUNT()

 
Jooksvaid kogusummasid saab arvutada lihtsalt Windowsi funktsiooni kaudu, mis algab tähega SUM() või COUNT(). See on võimas tööriist, kui soovite näidata konkreetse mõõdiku kasvu aja jooksul. Täpsemalt on see kasulik järgmistel juhtudel:

• Saate aja jooksul jooksva tulude ja kulude kogusumma
• Saate ühe kasutaja kohta rakendusele kulutatud aja kokku
• Saate aja jooksul jooksva konversioonide koguarvu

Näide:

Järgmine näide näitab, kuidas saate lisada igakuiste kulude kumulatiivse summa veeru.

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

3. Liikuvad keskmised AVG() abil

 
AVG() on Windowsi funktsioonides tõesti võimas, kuna võimaldab arvutada libisev keskmine üle aja.

Liikuvad keskmised on lihtne, kuid tõhus viis väärtuste prognoosimiseks lühiajaliselt. Samuti on need äärmiselt kasulikud graafiku muutlike kõverate tasandamiseks. Üldiselt kasutatakse libisevaid keskmisi, et mõõta asjade liikumise üldist suunda.

Täpsemalt…

• Neid saab kasutada iganädalase müügi üldise trendi saamiseks (kas keskmine aja jooksul tõuseb?). See viitab ettevõtte kasvule.
• Neid saab kasutada ka iganädalaste konversioonide või veebisaidi külastuste üldise trendi saamiseks.

Näide:

Järgmine päring on näide konversioonide 10 päeva libiseva keskmise hankimisest.

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

4. ROW_NUMBER()

 
ROW_NUMBER() on eriti kasulik, kui soovite saada esimest või viimast kirjet. Näiteks kui teil on tabel selle kohta, millal jõusaali liikmed jõusaali tulid ja soovite saada esimese päeva, mil nad jõusaali tulid, kuupäeva, saate JAOTADA kliendi (nimi/id) JÄRGI ja TELLIDA ostukuupäeva järgi. Seejärel saate esimese rea saamiseks lihtsalt filtreerida read, mille reanumber on võrdne ühega.

Näide:

See näide näitab, kuidas saate kasutada ROW_NUMBER(), et saada iga liikme (kasutaja) külastuse esimene kuupäev.

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

Kokkuvõtteks, kui teil on kunagi vaja saada esimene või viimane kirje, on ROW_NUMBER() suurepärane viis selle saavutamiseks.

5. Kirje paremusjärjestus DENSE_RANK()

 
DENSE_RANK() on sarnane ROW_NUMBER()-ga, välja arvatud see, et see tagastab võrdsete väärtuste korral sama asetuse. Tihe paremusjärjestus on üsna kasulik, kui tegemist on näiteks parimate rekordite hankimisega:

• Kui soovite selle nädala 10 enimvaadatud Netflixi saadet kokku tõmmata
• Kui soovite kulutatud dollarite põhjal saada 100 parimat kasutajat
• Kui soovite näha 1000 kõige vähem aktiivsema kasutaja käitumist

Näide:

Kui soovite oma parimaid kliente kogumüügi põhjal järjestada, oleks DENSE_RANK() sobiv funktsioon kasutamiseks.

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

Täname lugemast!

 
Ja see on kõik! Loodan, et see aitab teid intervjuude ettevalmistamisel – olen kindel, et kui teate neid viit mõistet põhjalikult, saate enamiku SQL-akna funktsiooniprobleemide lahendamisel suurepäraselt hakkama.

Nagu alati, soovin teile õppimisel edu!

 
 
Terence Shin on andmeentusiast, kellel on 3+ aastat kogemust SQL-is ja 2+ aastat Pythonis, ning blogija teemadel Towards Data Science ja KDnuggets.

Originaal. Loaga uuesti postitatud.

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