Cele mai bune cinci funcții ale ferestrei SQL pe care ar trebui să le cunoașteți pentru interviurile în domeniul științei datelor

SQL este limbajul universal în lumea datelor și este cea mai importantă abilitate pe care trebuie să o obțineți ca profesionist în domeniul datelor.

Motivul pentru care SQL este atât de important este că este principala abilitate care este necesară în timpul fazei de dispută a datelor. O mulțime de explorare a datelor, manipulare a datelor, dezvoltarea conductelor și crearea tabloului de bord se realizează prin SQL.

Ceea ce îi separă pe cei mai buni oameni de știință în date de cei buni în știință de date este că marii oameni de știință în date pot discuta datele atât cât permit capabilitățile SQL. O mare parte a capacității de a utiliza pe deplin tot ceea ce SQL are de oferit este să știi cum să folosești funcțiile ferestrei.

Acestea fiind spuse, haideți să ne aprofundăm!

1. Delte cu LEAD() și LAG()

 
LEAD() și LAG() sunt utilizate în principal atunci când se compară o perioadă de timp cu perioada anterioară pentru o anumită măsură. Ca să dau câteva exemple…

• Puteți obține delta dintre vânzările din fiecare an și vânzările din anul precedent
• Puteți obține delta în numărul de înscrieri/conversii/vizite pe site-ul de la o lună la lună
• Puteți compara numărul de utilizatori lunar

Exemplu:

Următoarea interogare arată cum puteți interoga modificarea procentuală lunară a costurilor

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. Sume cumulate cu SUM() sau COUNT()

 
Calcularea totalurilor cumulate se poate face pur și simplu printr-o funcție Windows care începe cu SUM() sau COUNT(). Acesta este un instrument puternic atunci când doriți să afișați creșterea unei anumite valori în timp. Mai precis, este util în următoarele circumstanțe:

• Obțineți un total curent de venituri și costuri în timp
• Obțineți un total de timp petrecut în aplicație per utilizator
• Obțineți un total de conversii în timp

Exemplu:

Următorul exemplu arată cum puteți include o coloană cu suma cumulativă a costurilor lunare:

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

3. Medii mobile cu AVG()

 
AVG() este foarte puternic în funcțiile Windows, deoarece vă permite să calculați medii mobile peste orar.

Mediile mobile sunt o modalitate simplă, dar eficientă, de a prognoza valorile pe termen scurt. De asemenea, sunt extrem de utile pentru a netezi curbele volatile pe un grafic. În general, mediile mobile sunt folosite pentru a măsura direcția generală în care se mișcă lucrurile.

Mai exact…

• Acestea pot fi folosite pentru a obține tendința generală a vânzărilor săptămânale (media crește în timp?). Acest lucru ar indica o creștere ca companie.
• Ele pot fi, de asemenea, folosite pentru a obține tendința generală a conversiilor săptămânale sau a vizitelor pe site.

Exemplu:

Următoarea interogare este un exemplu de obținere a mediei mobile pe 10 zile pentru conversii.

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

4. ROW_NUMBER()

 
ROW_NUMBER() este deosebit de utilă atunci când doriți să obțineți prima sau ultima înregistrare. De exemplu, dacă aveți un tabel cu când membrii sălii au venit la sală și doriți să obțineți data primei zile în care au venit la sală, puteți PARTIȚIONARE DUPA client (nume/id) și ORDER BY data achiziției. Apoi, pentru a obține primul rând, puteți pur și simplu să filtrați pentru rândurile cu rowNumber egal cu unu.

Exemplu:

Acest exemplu arată cum puteți utiliza ROW_NUMBER() pentru a obține prima dată când a vizitat fiecare membru (utilizator).

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

Pentru a recapitula, dacă trebuie vreodată să obțineți prima sau ultima înregistrare, ROW_NUMBER() este o modalitate excelentă de a realiza acest lucru.

5. Clasament record cu DENSE_RANK()

 
DENSE_RANK() este similar cu ROW_NUMBER() cu excepția faptului că returnează același rang pentru valori egale. Clasamentul dens este destul de util atunci când vine vorba de preluarea înregistrărilor de top, de exemplu:

• Dacă doriți să obțineți primele 10 cele mai vizionate emisiuni Netflix săptămâna aceasta
• Dacă doriți să obțineți primii 100 de utilizatori pe baza dolarilor cheltuiți
• Dacă doriți să vedeți comportamentul celor 1000 de utilizatori cei mai puțin activi

Exemplu:

Dacă doriți să vă clasificați cei mai buni clienți în funcție de vânzările totale, DENSE_RANK() ar fi o funcție adecvată de utilizat.

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

Mulțumesc pentru lectură!

 
Și asta e tot! Sper că acest lucru vă va ajuta în pregătirea interviului - sunt sigur că, dacă cunoașteți aceste 5 concepte din interior, vă veți descurca de minune când vine vorba de majoritatea problemelor legate de funcția ferestrei SQL.

Ca întotdeauna, vă doresc tot ce este mai bun în eforturile voastre de învățare!

 
 
Terence Shin este un pasionat de date cu peste 3 ani de experiență în SQL și peste 2 ani de experiență în Python și un blogger pe Towards Data Science și KDnuggets.

Original. Repostat cu permisiunea.

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