0Pricing
Coding Interview Prep · Lezione

Filtrare un risultato di finestra

Capire perché è necessario racchiudere una funzione finestra in una sottoquery o in un CTE per filtrarla

Filtrare un risultato di finestra è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 4 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 Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.

Perché non è possibile filtrare una funzione finestra in WHERE

Un "tranello" frequente nei colloqui: scrivere WHERE ROW_NUMBER() OVER (...) = 1 genera un errore. Le funzioni finestra non sono consentite in WHERE, GROUP BY o HAVING.

Il motivo è l'ordine logico di esecuzione. WHERE viene eseguita per selezionare le righe prima della valutazione delle funzioni finestra. La finestra non è ancora stata calcolata, quindi non è possibile usarla in un filtro.

Spiegazione dell'ordine di esecuzione

Le funzioni finestra vengono calcolate in una fase dedicata che si colloca dopo FROM, WHERE, GROUP BY e HAVING, ma prima dell'ultimo ORDER BY e di LIMIT.

Pertanto, nel momento in cui viene eseguita WHERE, la posizione o il numero di riga non esistono ancora. Per filtrarli, è necessario lasciare prima che la finestra termini il calcolo, quindi filtrare la colonna prodotta in un livello di query esterno.

Schema con query annidata

La correzione standard consiste nel calcolare la funzione finestra in una query interna (una tabella derivata), assegnare un alias al risultato e filtrare quindi quell'alias nella WHERE esterna.

La tabella derivata deve avere un alias (t in questo caso): gli intervistatori fanno caso ai candidati che lo dimenticano. Ora rn è una colonna ordinaria che la query esterna può confrontare.

SELECT *
FROM (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

Lo schema CTE, spesso più chiaro

Una Common Table Expression svolge lo stesso compito con una struttura più leggibile. Definisca il ranking in un passaggio WITH, quindi lo filtri nella query principale.

Funzionalmente è identica alla query annidata, ma durante il live coding gli intervistatori preferiscono spesso le CTE, perché l'intento si legge dall'alto verso il basso.

WITH ranked AS (
  SELECT
    name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn = 1;

Esempio svolto: primi N per gruppo

Il problema più frequente con le funzioni finestra: "i 3 dipendenti con lo stipendio più alto per reparto". Calcoli la classifica nella CTE, quindi mantenga rn <= 3 all'esterno.

Scelga la funzione di ranking in base alla gestione dei pari merito: ROW_NUMBER limita a esattamente 3 righe per reparto; passi a RANK/DENSE_RANK se è necessario includere i pari merito al limite.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, rn;

Esempio svolto: filtrare un totale progressivo

Lo schema contenitore non serve solo per le posizioni. Qualsiasi risultato di una funzione finestra, inclusi totali progressivi, medie mobili e differenze con LAG, deve essere filtrato allo stesso modo.

Qui calcoliamo un saldo progressivo, quindi manteniamo solo le righe in cui ha superato per la prima volta 1000. Il filtro si trova all'esterno del livello della finestra.

WITH balances AS (
  SELECT
    account_id, txn_date, amount,
    SUM(amount) OVER (
      PARTITION BY account_id ORDER BY txn_date
    ) AS running_balance
  FROM transactions
)
SELECT *
FROM balances
WHERE running_balance > 1000;

QUALIFY: la scorciatoia in alcuni database

Snowflake, BigQuery, Teradata e DuckDB offrono una clausola QUALIFY che filtra direttamente i risultati delle funzioni finestra, senza bisogno di un contenitore. Viene eseguita dopo le funzioni finestra, esattamente nel punto necessario.

Menzioni QUALIFY per dimostrare la Sua preparazione, ma tenga presente che non fa parte dello standard SQL ed è assente in PostgreSQL, MySQL e SQL Server, dove è ancora necessario usare una query annidata o una CTE.

-- Snowflake / BigQuery only:
SELECT department, name, salary
FROM employees
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY department ORDER BY salary DESC
) = 1;

Non confonda HAVING con il filtraggio delle finestre

A volte i candidati provano a usare HAVING per filtrare una posizione. HAVING filtra i gruppi dopo l'aggregazione eseguita da GROUP BY e viene comunque eseguita prima delle funzioni finestra, quindi non può fare riferimento neppure a una colonna della finestra.

  • WHERE → filtra le righe prima del raggruppamento e prima delle funzioni finestra.
  • HAVING → filtra i gruppi aggregati, sempre prima delle funzioni finestra.
  • Per filtrare una finestra → serve una query esterna (oppure QUALIFY).

Combinare un pre-filtro con un filtro sulla finestra

Spesso è necessario filtrare sia prima sia dopo la finestra. Applichi i normali filtri sulle righe nella WHERE interna, così la finestra considera solo le righe pertinenti, quindi filtri il risultato della finestra nella query esterna.

In questo esempio limitiamo inizialmente i dati ai dipendenti attivi, quindi selezioniamo il dipendente con lo stipendio più alto di ogni reparto tra quelli rimasti. Inserire WHERE active all'interno modifica le righe che vengono classificate.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS rn
  FROM employees
  WHERE is_active = true        -- pre-filter before ranking
)
SELECT * FROM ranked
WHERE rn = 1;                   -- post-filter on the window

Nota sulle prestazioni

Gli intervistatori potrebbero chiedere se il contenitore riduce le prestazioni. Di solito no: l'ottimizzatore tratta la query annidata o la CTE come parte di un unico piano e calcola la finestra una sola volta. Il semplice fatto di averla racchiusa in un contenitore non comporta una scansione aggiuntiva.

Una precisazione: in alcuni motori una CTE può costituire una barriera all'ottimizzazione ed essere materializzata, quindi nei percorsi critici una tabella derivata o QUALIFY potrebbe produrre un piano migliore. Se è importante, analizzi il piano con EXPLAIN.

Errori comuni

Lista di controllo finale:

  • Non inserisca mai una funzione finestra in WHERE/HAVING: genera un errore.
  • Assegni sempre un alias alla tabella derivata: una query annidata senza nome in FROM viene rifiutata.
  • Scelga la funzione di ranking in base alla gestione dei pari merito richiesta dalla domanda.
  • Usi QUALIFY solo dove è supportato; negli altri casi ricorra al contenitore con CTE o query annidata.

Verifica rapida

Perché per filtrare una funzione finestra è necessario usare un contenitore?

Riepilogo: filtrare i risultati delle finestre

Ha completato il percorso sulle funzioni finestra di ranking:

  • Le funzioni finestra vengono eseguite dopo WHERE/GROUP BY/HAVING, quindi non è possibile filtrarle in queste clausole.
  • Racchiuda la finestra in una query annidata o in una CTE (sempre con un alias) e filtri il risultato nella query esterna.
  • Questo schema consente di gestire i primi N per gruppo, l'ultima riga per chiave e le soglie dei totali progressivi.
  • QUALIFY è una pratica scorciatoia non standard disponibile solo in Snowflake e BigQuery.

Ora dispone dell'intero set di strumenti sul ranking che gli intervistatori verificano più spesso.

Domande Frequenti

La lezione «Filtrare un risultato di finestra» è gratuita?

Sì — il testo completo di «Filtrare un risultato di finestra» è 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 Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Filtrare un risultato di finestra»?

Capire perché è necessario racchiudere una funzione finestra in una sottoquery o in un CTE per filtrarla Eserciti Coding 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 Coding Interview Prep?

Non è richiesta alcuna esperienza precedente. Coding 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 4 di 4.

Quanto tempo richiede la lezione «Filtrare un risultato di finestra»?

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 Coding Interview Prep?

Sì. Ogni lezione Coding 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

  1. OVER, PARTITION BY e ORDER BY
  2. ROW_NUMBER per una sequenza univoca
  3. RANK e DENSE_RANK in caso di parità
  4. Filtrare un risultato di finestra
← Torna a Coding Interview Prep