SQL Interview Prep · Lezione

FIRST_VALUE, LAST_VALUE e limiti del frame

Estrarre i valori ai limiti e comprendere l'insidia di LAST_VALUE con i frame

FIRST_VALUE, LAST_VALUE e limiti del frame è una lezione SQL 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 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.

Recuperare i valori ai limiti

I selezionatori chiedono: «Mostri ogni riga insieme al primo e all'ultimo valore del proprio gruppo». Pensi, ad esempio, alla data del primo accesso per utente o all'ultimo prezzo di una partizione affiancato a ogni riga di dettaglio.

Le funzioni sono FIRST_VALUE e LAST_VALUE. Sembrano semplici, ma LAST_VALUE nasconde uno dei più noti problemi relativi ai frame delle finestre in SQL. In questa lezione imparerà a usare entrambe in modo affidabile.

Nozioni di base su FIRST_VALUE

FIRST_VALUE(col) restituisce il valore di col dalla prima riga della finestra e lo associa a ogni riga. Ordinando per data, assegna a ogni riga il valore più antico della propria partizione.

Poiché il frame predefinito inizia dalla prima riga della partizione, FIRST_VALUE di solito si comporta esattamente come ci si aspetta.

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS first_login
FROM logins;

La cornice predefinita della finestra

Questo è il punto cruciale. Quando aggiunge ORDER BY a una finestra, il frame predefinito è RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Ciò significa che la finestra di ogni riga si estende solo dall'inizio della partizione fino alla riga corrente, non fino alla fine. FIRST_VALUE non ne risente, perché la prima riga è sempre compresa nell'intervallo, mentre LAST_VALUE ne risente pesantemente.

La trappola di LAST_VALUE

Se esegue LAST_VALUE specificando solo un ORDER BY, la maggior parte dei candidati si aspetta di ottenere l'ultimo valore della partizione. Invece, poiché il frame termina alla riga corrente, l'ultimo valore del frame è semplicemente il valore della riga corrente.

Perciò questa query restituisce login_date stesso in ogni riga, dando l'impressione che qualcosa non funzioni. È l'insidia delle funzioni finestra che viene chiesta più spesso.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
  ) AS wrong_last_login
FROM logins;

Correggere LAST_VALUE con un frame completo

La soluzione consiste nell'estendere il frame fino a comprendere l'intera partizione: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Ora la finestra di ogni riga si estende all'intera partizione, quindi LAST_VALUE restituisce il vero valore finale. Espliciti questa correzione durante un colloquio: dimostra che comprende i frame, non solo i nomi delle funzioni.

SELECT
  user_id,
  login_date,
  LAST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS last_login
FROM logins;

Un'alternativa più semplice

Molti sviluppatori evitano completamente di specificare il frame: per ottenere l'ultimo valore, usano FIRST_VALUE con l'ordine di ordinamento invertito.

FIRST_VALUE(login_date) OVER (... ORDER BY login_date DESC) restituisce la data più recente senza bisogno di una clausola frame. È un trucco semplice e facile da ricordare, che vale la pena menzionare.

SELECT
  user_id,
  login_date,
  FIRST_VALUE(login_date) OVER (
    PARTITION BY user_id
    ORDER BY login_date DESC
  ) AS last_login
FROM logins;

ROWS e RANGE nei frame

I frame sono di due tipi. ROWS conta le righe fisiche, mentre RANGE raggruppa in base ai valori uguali di ORDER BY (i peer).

Il frame predefinito usa RANGE, perciò i valori di ordinamento uguali condividono il limite del frame. Per correggere LAST_VALUE, preferisca ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING scritto esplicitamente, così da evitare sorprese in presenza di valori uguali.

NTH_VALUE per posizioni arbitrarie

Oltre al primo e all'ultimo valore, NTH_VALUE(col, n) recupera il valore nella posizione n all'interno del frame, ad esempio il secondo prezzo più alto.

Segue le stesse regole sui frame di LAST_VALUE, quindi lo combini con un frame completo quando desidera ottenere l'ennesimo valore dell'intera partizione, anziché solo quello fino alla riga corrente.

SELECT
  product_id,
  price,
  NTH_VALUE(price, 2) OVER (
    PARTITION BY product_id
    ORDER BY price DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
  ) AS second_highest_price
FROM prices;

Esempio completo: primo e ultimo valore insieme

Un report comune mostra ogni transazione accanto all'importo della prima e dell'ultima transazione del cliente. Combini entrambe le funzioni, ricordando di specificare esplicitamente il frame per LAST_VALUE.

Ora ogni riga contiene il primo e l'ultimo valore dell'intera partizione, pronti per calcolare una differenza o aggiungere un'etichetta.

SELECT
  customer_id,
  txn_date,
  amount,
  FIRST_VALUE(amount) OVER w AS first_amt,
  LAST_VALUE(amount) OVER w AS last_amt
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Le finestre denominate mantengono il codice DRY

Noti che la query precedente utilizzava una clausola WINDOW w AS (...) e faceva riferimento due volte a OVER w. Definire la finestra una sola volta evita di ripetere una lunga specifica del frame e impedisce che le due funzioni divergano.

La maggior parte dei database principali supporta le finestre denominate. Usarne una è una scelta elegante, apprezzata durante i colloqui quando diverse colonne condividono la stessa finestra.

Esempio completo: differenza tra primo e ultimo valore

Una domanda frequente di approfondimento riguarda la variazione tra la prima e l'ultima transazione di un cliente. Con entrambi i valori limite presenti in ogni riga, li sottragga e, se necessario, elimini i duplicati per ottenere una sola riga per cliente.

Questo esempio combina la correzione del frame completo con una semplice operazione aritmetica: è il tipo di risposta completa che gli intervistatori vogliono vedere costruita in modo ordinato.

SELECT DISTINCT
  customer_id,
  LAST_VALUE(amount) OVER w - FIRST_VALUE(amount) OVER w AS first_to_last_delta
FROM transactions
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY txn_date
  ROWS BETWEEN UNBOUNDED PRECEDING
           AND UNBOUNDED FOLLOWING
);

Verifica rapida

La classica insidia di LAST_VALUE.

Riepilogo

Le funzioni dei valori limite dipendono dal frame:

FIRST_VALUE funziona con il frame predefinito; LAST_VALUE no.
Il frame predefinito termina alla riga corrente, quindi corregga LAST_VALUE con ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, oppure inverta l'ordinamento e usi FIRST_VALUE.
NTH_VALUE(col, n) recupera posizioni arbitrarie; le finestre denominate mantengono DRY le specifiche usate da più colonne.

Con questo si completa il kit di strumenti per LAG, LEAD, NTILE e i valori limite.

Domande Frequenti

La lezione «FIRST_VALUE, LAST_VALUE e limiti del frame» è gratuita?

Sì — il testo completo di «FIRST_VALUE, LAST_VALUE e limiti del frame» è 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 «FIRST_VALUE, LAST_VALUE e limiti del frame»?

Estrarre i valori ai limiti e comprendere l'insidia di LAST_VALUE con i frame 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 4 di 4.

Quanto tempo richiede la lezione «FIRST_VALUE, LAST_VALUE e limiti del frame»?

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

LAG e LEAD per le righe adiacenti
Variazione tra periodi
NTILE per creare fasce
FIRST_VALUE, LAST_VALUE e limiti del frame

← Torna a SQL Interview Prep