Az öt legjobb SQL-ablakfunkció, amelyet tudnia kell az adattudományi interjúkhoz

Az SQL az univerzális nyelv az adatvilágban, és ez a legfontosabb készség, amelyet adatszakértőként le kell szögezni.

Az SQL azért annyira fontos, mert ez a fő készség, amelyre az adatrázás fázisában szükség van. Sok adatfeltárás, adatkezelés, folyamatfejlesztés és irányítópult létrehozása történik SQL-en keresztül.

A nagy adattudósokat az különbözteti meg a jó adattudósoktól, hogy a nagy adattudósok annyit tudnak vitatkozni az adatokkal, amennyire az SQL képességei lehetővé teszik. Az SQL által kínált lehetőségek teljes kihasználásának nagy része az ablakfunkciók használatának ismerete.

Ennek tudatában merüljünk el benne!

1. Delták LEAD() és LAG()

 
A LEAD() és a LAG() általában akkor használatos, ha egy adott metrika egy időszakát az előző időszakkal hasonlítják össze. Hogy néhány példát mondjak…

• Megkaphatja a deltát az egyes év eladásai és az előző év eladásai között
• Megtekintheti a regisztrációk/konverziók/webhelylátogatások számának deltáját hónapról hónapra
• Havi rendszerességgel összehasonlíthatja a felhasználói lemorzsolódást

Példa:

A következő lekérdezés bemutatja, hogyan kérdezheti le a költségek havi százalékos változását

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. Összesített összegek SUM() vagy COUNT()

 
A futó összegek kiszámítása egyszerűen elvégezhető a SUM() vagy COUNT() karakterekkel kezdődő Windows függvényen keresztül. Ez egy hatékony eszköz, ha egy adott mutató időbeli növekedését szeretné megjeleníteni. Pontosabban a következő esetekben hasznos:

• Szerezzen be egy futó bevételt és költséget az idő múlásával
• Felhasználónként elérheti az alkalmazással eltöltött összes futó időt
• Szerezzen be egy futó konverziót az idő múlásával

Példa:

A következő példa bemutatja, hogyan veheti fel a havi költségek összesített összegű oszlopát:

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

3. Mozgóátlagok az AVG() segítségével

 
Az AVG() nagyon erős a Windows függvényekben, mivel lehetővé teszi a számítást mozgóátlagok túlóra.

A mozgóátlagok egyszerű, de hatékony módja az értékek rövid távú előrejelzésének. Rendkívül hasznosak az illékony görbék grafikonon történő kisimításához is. Általában a mozgóátlagokat arra használják, hogy felmérjék a dolgok mozgásának általános irányát.

Pontosabban…

• Használhatók a heti eladások általános trendjének megállapítására (az átlag idővel emelkedik?). Ez vállalati növekedést jelezne.
• Ugyanígy használhatók a heti konverziók vagy webhelylátogatások általános trendjének megállapítására.

Példa:

A következő lekérdezés egy példa a konverziók 10 napos mozgóátlagának meghatározására.

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

4. ROW_NUMBER()

 
A ROW_NUMBER() különösen hasznos, ha az első vagy az utolsó rekordot szeretné lekérni. Például, ha van egy táblázata arról, hogy az edzőterem tagjai mikor érkeztek az edzőterembe, és meg szeretné kapni az első nap dátumát, amikor az edzőterembe érkeztek, akkor az ügyfél (név/azonosító) SZERINTI FESZÜLETÉSE és a vásárlás dátuma szerint MEGRENDELHETŐ. Ezután az első sor megszerzéséhez egyszerűen szűrhet azokra a sorokra, amelyek sorszáma eggyel egyenlő.

Példa:

Ez a példa bemutatja, hogyan használhatja a ROW_NUMBER() segítségével az egyes tagok (felhasználók) látogatásának első dátumát.

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

Összefoglalva, ha valaha is meg kell szereznie az első vagy az utolsó rekordot, a ROW_NUMBER() nagyszerű módja ennek elérésére.

5. Rekordrangsorolás a DENSE_RANK() paraméterrel

 
A DENSE_RANK() hasonló a ROW_NUMBER()-hoz, kivéve, hogy ugyanazt a rangot adja vissza egyenlő értékek esetén. A sűrű rangsor nagyon hasznos, ha például a legjobb rekordokról van szó:

• Ha szeretnéd begyűjteni a 10 legnézettebb Netflix-műsort ezen a héten
• Ha szeretné megszerezni a legjobb 100 felhasználót az elköltött dollárok alapján
• Ha szeretné látni az 1000 legkevésbé aktív felhasználó viselkedését

Példa:

Ha szeretné rangsorolni a legjobb ügyfeleit az összértékesítés alapján, akkor a DENSE_RANK() a megfelelő függvény.

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

Köszönöm, hogy elolvasta!

 
És ennyi! Remélem, hogy ez segít az interjúra való felkészülésben – biztos vagyok benne, hogy ha jól ismeri ezt az 5 fogalmat, akkor remekül fog járni az SQL-ablakfunkciókkal kapcsolatos legtöbb problémával kapcsolatban.

Mint mindig, most is a legjobbakat kívánom a tanuláshoz!

 
 
Terence Shin egy adatrajongó, 3+ év SQL és 2+ év Python tapasztalattal, valamint a Towards Data Science és a KDnuggets bloggerje.

eredeti. Engedéllyel újra közzétéve.

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