Medie mobili su una finestra scorrevole
Calcolare medie mobili su N periodi con BETWEEN PRECEDING AND CURRENT ROW
Medie mobili su una finestra scorrevole è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 3 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.
La domanda sulla media mobile
Agli analisti viene posta continuamente questa domanda: "Calcoli una media mobile dei ricavi su 7 giorni." Una media mobile (o rolling) attenua la variabilità dei dati giornalieri rumorosi calcolando la media di ogni punto insieme ai valori vicini più recenti.
La risposta adatta a un colloquio consiste in una funzione AVG su finestra con un frame scorrevole esplicito. La competenza fondamentale è scegliere correttamente i limiti del frame, in modo che la finestra scorra lungo l'ordinamento.
Il modello di base
Una media mobile è AVG(value) OVER (ORDER BY ... ROWS BETWEEN n PRECEDING AND CURRENT ROW). Il frame scorre: a ogni riga comprende la riga corrente e le n righe precedenti.
Per una finestra di 7 giorni su righe giornaliere, si torna indietro di 6 righe oltre a quella corrente, ottenendo 7 righe in totale. Questo errore di conteggio è lo sbaglio più comune che gli intervistatori individuano.
SELECT
sale_date,
amount,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM daily_sales;Calcolare correttamente l'ampiezza della finestra
L'ampiezza della finestra è uguale a preceding + 1 (il +1 rappresenta la riga corrente). Quindi:
- Finestra di 3 righe:
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW - Finestra di 7 righe:
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW - Finestra di 30 righe:
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
Pronunci questa aritmetica ad alta voce durante il colloquio, così la commissione capirà che sta procedendo con consapevolezza e non sta tirando a indovinare.
Finestre trailing e centrate
L'esempio precedente è una media trailing: considera solo i valori precedenti, quindi è causale e sicura per i dashboard di previsione.
Una media centrata considera i valori in entrambe le direzioni, ad esempio ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING per una finestra centrata di 7 righe. Le finestre centrate attenuano i dati in modo più simmetrico, ma non possono essere calcolate in tempo reale per le righe più recenti. Indichi quale delle due serve al caso d'uso.
SELECT
sale_date,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
) AS centered_avg_7
FROM daily_sales;L'effetto ai margini della finestra
All'inizio dei dati la finestra completa non è ancora disponibile. Per la prima riga di una media trailing su 7 giorni è disponibile una sola riga, quindi AVG calcola la media utilizzando solo quel valore.
Di conseguenza, le prime righe mostrano una media di "avvio" calcolata su un numero inferiore di righe. Gli intervistatori chiedono come gestire questa situazione. Esistono due possibilità: accettare la finestra parziale oppure eliminare le prime righe richiedendo un conteggio completo.
Escludere le finestre parziali
Se il requisito aziendale è ottenere NULL finché non è disponibile una finestra completa, utilizzi COUNT(*) sullo stesso frame e lasci vuote le finestre brevi con un'espressione CASE.
È un dettaglio curato che dimostra di aver considerato la correttezza ai margini, un aspetto che molti candidati trascurano.
SELECT
sale_date,
CASE WHEN COUNT(*) OVER w = 7
THEN AVG(amount) OVER w
END AS moving_avg_7d
FROM daily_sales
WINDOW w AS (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);Riutilizzare un frame con WINDOW
La query precedente utilizzava una clausola WINDOW denominata. Quando lo stesso frame compare più volte, definirlo una sola volta come WINDOW w AS (...) e fare riferimento a OVER w mantiene la query DRY e leggibile.
Postgres, MySQL 8 e SQL Server supportano le finestre denominate. Utilizzarne una durante un colloquio dimostra una padronanza delle funzioni finestra che va oltre il semplice copia-incolla.
SELECT
sale_date,
AVG(amount) OVER w AS avg_7d,
SUM(amount) OVER w AS sum_7d
FROM daily_sales
WINDOW w AS (
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
);Insidia: giorni di calendario e righe
Una trappola sottile: se nella tabella mancano alcuni giorni, ROWS 6 PRECEDING copre le ultime 7 righe registrate, che possono estendersi su più di 7 giorni di calendario.
Per una vera media di 7 giorni basata sul calendario e rispettosa delle lacune, utilizzi RANGE con un offset di intervallo oppure esegua prima un join con una serie completa di date, in modo che ogni giorno abbia una riga. Esplicitare questa distinzione dimostra esattamente la comprensione di ROWS e RANGE acquisita nella lezione precedente.
SELECT
sale_date,
AVG(amount) OVER (
ORDER BY sale_date
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
) AS calendar_avg_7d
FROM daily_sales;Medie mobili per gruppo
Come per i totali progressivi, le medie mobili devono generalmente ripartire per entità. Aggiunga PARTITION BY affinché ogni prodotto o negozio abbia la propria finestra mobile, senza che i dati si estendano tra gruppi.
Il frame e l'ordinamento operano indipendentemente all'interno di ogni partizione, quindi le prime righe di ciascun gruppo avviano correttamente la propria fase di avvio.
SELECT
product_id,
sale_date,
AVG(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS product_ma_7d
FROM daily_sales;Lo smussamento in pratica
Perché farlo? I ricavi giornalieri presentano picchi; una media mobile rivela la tendenza. Una risposta comune degli analisti consiste nell'affiancare al valore grezzo la sua linea smussata, così il dashboard mostra sia il segnale sia il rumore.
Può anche confrontare una media mobile breve e una lunga (ad esempio 7 giorni contro 30 giorni) per rilevare lo slancio, l'equivalente SQL dell'incrocio delle medie mobili.
SELECT
sale_date,
amount,
AVG(amount) OVER (ORDER BY sale_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7,
AVG(amount) OVER (ORDER BY sale_date
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS ma_30
FROM daily_sales;Elenco di controllo per il colloquio
Per rispondere al meglio a una domanda sulle medie mobili, includa:
AVG OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW).- Ampiezza della finestra = righe precedenti + 1.
- Scelta tra finestra trailing e centrata.
- Fase di avvio della finestra parziale e modalità per escluderla.
ROWSeRANGEquando mancano alcuni giorni.PARTITION BYper ripartire per entità.
Verifica rapida
Scelga il frame per una media mobile trailing di 7 giorni su righe giornaliere.
Riepilogo: medie mobili
Una media mobile è AVG(value) OVER (ORDER BY ... ROWS BETWEEN n-1 PRECEDING AND CURRENT ROW), dove l'ampiezza della finestra è data dalle righe precedenti più una. Scelga una finestra trailing o centrata in base al caso d'uso, gestisca le righe della fase di avvio con un controllo tramite COUNT e passi a un intervallo RANGE quando le lacune del calendario sono rilevanti.
Faccia ripartire il calcolo per entità con PARTITION BY e riutilizzi i frame con un WINDOW denominato. Successivamente trasformeremo le somme cumulative in percentuali calcolando la quota cumulativa del totale.
Domande Frequenti
La lezione «Medie mobili su una finestra scorrevole» è gratuita?
Sì — il testo completo di «Medie mobili su una finestra scorrevole» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.
Cosa imparerò in «Medie mobili su una finestra scorrevole»?
Calcolare medie mobili su N periodi con BETWEEN PRECEDING AND CURRENT ROW Eserciti SQL Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare SQL Interview Prep?
Non è richiesta alcuna esperienza precedente. SQL Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 3 di 4.
Quanto tempo richiede la lezione «Medie mobili su una finestra scorrevole»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione SQL Interview Prep?
Sì. Ogni lezione SQL Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Somme cumulative con i frame di finestra
- Frame ROWS e RANGE
- Medie mobili su una finestra scorrevole
- Distribuzione cumulativa e percentuale del totale