Le cinque principali funzioni della finestra SQL che dovresti conoscere per le interviste sulla scienza dei dati

SQL è il linguaggio universale nel mondo dei dati ed è l'abilità più importante da definire come professionista dei dati.

Il motivo per cui SQL è così importante è che è l'abilità principale richiesta durante la fase di data wrangling. Molte attività di esplorazione dei dati, manipolazione dei dati, sviluppo di pipeline e creazione di dashboard vengono eseguite tramite SQL.

Ciò che separa i grandi data scientist dai buoni data scientist è che i grandi data scientist possono gestire i dati tanto quanto le capacità di SQL lo consentono. Una parte importante dell'essere in grado di utilizzare completamente tutto ciò che SQL ha da offrire è sapere come utilizzare le funzioni della finestra.

Detto questo, tuffiamoci!

1. Delta con LEAD() e LAG()

 
LEAD() e LAG() vengono utilizzati principalmente quando si confronta un periodo di tempo con il periodo di tempo precedente per una determinata metrica. Per fare alcuni esempi...

• È possibile ottenere il delta tra le vendite di ogni anno e le vendite dell'anno precedente
• Puoi ottenere il delta nel numero di iscrizioni/conversioni/visite al sito web su base mensile
• Puoi confrontare l'abbandono degli utenti su base mensile

Esempio:

La query seguente mostra come eseguire query sulla variazione percentuale mensile dei costi

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. Somme cumulative con SUM() o COUNT()

 
Il calcolo dei totali parziali può essere eseguito semplicemente tramite una funzione di Windows che inizia con SUM() o COUNT(). Questo è uno strumento potente quando vuoi mostrare la crescita di una determinata metrica nel tempo. Più in particolare, è utile nelle seguenti circostanze:

• Ottieni un totale parziale di entrate e costi nel tempo
• Ottieni un totale parziale di tempo speso sull'app per utente
• Ottieni un totale parziale di conversioni nel tempo

Esempio:

L'esempio seguente mostra come includere una colonna di somma cumulativa dei costi mensili:

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

3. Medie mobili con AVG()

 
AVG() è davvero potente nelle funzioni di Windows in quanto consente di calcolare medie mobili col tempo.

Le medie mobili sono un modo semplice, ma efficace, per prevedere i valori a breve termine. Sono anche estremamente utili per smussare le curve volatili su un grafico. In genere, le medie mobili vengono utilizzate per misurare la direzione generale di dove si stanno muovendo le cose.

Più specificamente…

• Possono essere utilizzati per ottenere l'andamento generale delle vendite settimanali (la media aumenta nel tempo?). Ciò indicherebbe una crescita come azienda.
• Possono anche essere utilizzati per ottenere l'andamento generale delle conversioni settimanali o delle visite al sito web.

Esempio:

La query seguente è un esempio di come ottenere la media mobile di 10 giorni per le conversioni.

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

4. NUMERO_RIGA()

 
ROW_NUMBER() è particolarmente utile quando vuoi ottenere il primo o l'ultimo record. Ad esempio, se hai una tabella di quando i membri della palestra sono venuti in palestra e vuoi ottenere la data del primo giorno in cui sono venuti in palestra, puoi PARTIZIONE PER cliente (nome/ID) e ORDINA PER data di acquisto. Quindi, per ottenere la prima riga, puoi semplicemente filtrare per le righe con rowNumber uguale a uno.

Esempio:

Questo esempio mostra come utilizzare ROW_NUMBER() per ottenere la prima data di visita di ogni membro (utente).

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

Per ricapitolare, se hai bisogno di ottenere il primo o l'ultimo record, ROW_NUMBER() è un ottimo modo per ottenerlo.

5. Classifica record con DENSE_RANK()

 
DENSE_RANK() è simile a ROW_NUMBER() tranne per il fatto che restituisce lo stesso rango per valori uguali. Una classifica densa è molto utile quando si tratta di recuperare i record migliori, ad esempio:

• Se vuoi estrarre i primi 10 programmi Netflix più visti questa settimana
• Se vuoi ottenere i primi 100 utenti in base ai dollari spesi
• Se vuoi vedere il comportamento dei 1000 utenti meno attivi

Esempio:

Se desideri classificare i tuoi migliori clienti in base alle vendite totali, DENSE_RANK() sarebbe una funzione appropriata da utilizzare.

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

Grazie per aver letto!

 
E questo è tutto! Spero che questo ti aiuti nella preparazione del colloquio: sono sicuro che se conosci questi 5 concetti alla perfezione, farai grandi cose quando si tratta della maggior parte dei problemi con le funzioni della finestra SQL là fuori.

Come sempre, ti auguro il meglio nei tuoi sforzi di apprendimento!

 
 
Terence Shin è un appassionato di dati con oltre 3 anni di esperienza in SQL e 2+ anni di esperienza in Python e un blogger su Towards Data Science e KDnuggets.

Originale. Ripubblicato con il permesso.

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