0Pricing
SQL Interview Prep · Lezione

Troncare e raggruppare le date

Raggruppamento per settimana, mese e trimestre con DATE_TRUNC e gli equivalenti.

Troncare e raggruppare le date è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 2 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.

Perché viene chiesto il raggruppamento delle date

"Mostrare i ricavi per settimana" o "gli utenti attivi per mese" è il pane quotidiano dei colloqui per analisti. La competenza verificata consiste nel ricondurre timestamp precisi a un bucket più ampio, così da raggruppare le righe.

L'errore dei candidati junior è estrarre solo il numero del mese, unendo lo stesso mese di anni diversi. La risposta professionale è il troncamento: associare ogni timestamp all'inizio del relativo periodo.

  • Bucket settimanali, mensili, trimestrali e annuali
  • DATE_TRUNC e gli equivalenti dei vari dialetti
  • Raggruppare correttamente per allineare i grafici

DATE_TRUNC: lo strumento fondamentale

In PostgreSQL, DATE_TRUNC(unit, ts) azzera tutto ciò che è più preciso dell'unità indicata. Troncare a 'month' trasforma qualsiasi timestamp di marzo in 2024-03-01 00:00:00.

Il valore restituito è ancora un timestamp, quindi viene ordinato cronologicamente e raggruppato perfettamente. È la singola funzione per le date più utile per la reportistica.

SELECT DATE_TRUNC('month', TIMESTAMP '2024-03-17 14:30:00');
-- 2024-03-01 00:00:00

Raggruppare i ricavi per mese

L'esempio pratico per eccellenza. Si tronca il timestamp al mese, poi si raggruppa e si calcola la somma. Poiché il bucket include l'anno, gennaio 2023 e gennaio 2024 restano separati.

Ordinando per il valore troncato si ottiene una serie temporale ordinata, pronta per un grafico.

SELECT
  DATE_TRUNC('month', order_ts) AS month,
  SUM(amount)                   AS revenue
FROM orders
GROUP BY 1
ORDER BY 1;

EXTRACT vs DATE_TRUNC

Gli intervistatori verificano direttamente questa distinzione. Entrambe recuperano informazioni sul periodo, ma rispondono a domande diverse.

  • EXTRACT(MONTH FROM ts) restituisce il numero 3 per qualsiasi marzo, indipendentemente dall'anno, ed è utile per analizzare la stagionalità.
  • DATE_TRUNC('month', ts) restituisce l'inizio del mese specifico, mantenendo distinti gli anni, ed è utile per le serie temporali.

Se si raggruppa per EXTRACT(MONTH ...) per creare un grafico dell'andamento mensile, gli anni verranno uniti tra loro senza che sia evidente.

-- Seasonality: which month is busiest on average?
SELECT EXTRACT(MONTH FROM order_ts) AS month_num, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

-- Time series: month-by-month trend (years kept separate)
SELECT DATE_TRUNC('month', order_ts) AS month, COUNT(*)
FROM orders GROUP BY 1 ORDER BY 1;

Bucket settimanali e la questione del lunedì

Il raggruppamento settimanale nasconde una sottigliezza che gli intervistatori amano verificare: quando inizia la settimana? In PostgreSQL, DATE_TRUNC('week', ts) si aggancia sempre al lunedì (settimane ISO).

Se l'attività richiede settimane che iniziano di domenica, è necessario applicare un offset. Un metodo comune consiste nello spostare la data indietro di un giorno, troncarla e poi spostarla nuovamente in avanti.

-- ISO week (Monday start)
SELECT DATE_TRUNC('week', order_ts) AS iso_week FROM orders;

-- Sunday-start week
SELECT DATE_TRUNC('week', order_ts + INTERVAL '1 day') - INTERVAL '1 day'
  AS sunday_week
FROM orders;

Bucket trimestrali

La reportistica trimestrale è comune nei ruoli vicini all'ambito finanziario. DATE_TRUNC('quarter', ts) associa qualsiasi timestamp al primo giorno del trimestre: 1 gennaio, 1 aprile, 1 luglio o 1 ottobre.

Per indicare invece il trimestre con un numero, si può combinare EXTRACT(QUARTER ...) con l'anno.

SELECT
  DATE_TRUNC('quarter', order_ts)                  AS quarter_start,
  EXTRACT(YEAR FROM order_ts) || '-Q'
    || EXTRACT(QUARTER FROM order_ts)              AS quarter_label,
  SUM(amount)                                      AS revenue
FROM orders
GROUP BY 1, 2
ORDER BY 1;

MySQL non ha DATE_TRUNC

Una domanda ricorrente sui diversi dialetti è: "MySQL non ha DATE_TRUNC: come si crea un bucket mensile?" La risposta portabile consiste nel formattare la data alla granularità desiderata.

  • DATE_FORMAT(ts, '%Y-%m-01') restituisce l'inizio del mese come testo/data.
  • DATE_FORMAT(ts, '%Y-%m') restituisce una chiave testuale ordinabile come 2024-03.

Per le settimane, MySQL offre YEARWEEK() con un argomento mode che controlla il giorno di inizio della settimana.

-- MySQL month bucket
SELECT DATE_FORMAT(order_ts, '%Y-%m-01') AS month, SUM(amount)
FROM orders
GROUP BY 1
ORDER BY 1;

Creare bucket in SQL Server

Storicamente SQL Server non disponeva di un troncamento diretto, quindi si usavano DATEFROMPARTS oppure l'idioma DATEADD/DATEDIFF. Le versioni moderne (2022+) aggiungono DATETRUNC.

L'idioma classico, "contare le unità trascorse dall'epoca e poi riaggiungerle", funziona in tutte le versioni ed è importante conoscerlo.

-- Portable SQL Server month truncation
SELECT DATEADD(month, DATEDIFF(month, 0, order_ts), 0) AS month_start
FROM orders;

-- SQL Server 2022+
SELECT DATETRUNC(month, order_ts) AS month_start FROM orders;

Colmare i vuoti in una serie temporale

Il solo troncamento elimina i periodi senza righe: un mese senza ordini semplicemente non comparirà. Gli intervistatori verificano se ci si accorge di questo problema.

La soluzione consiste nel generare una sequenza completa di periodi e fare un LEFT JOIN dei dati su di essa. In Postgres, generate_series costruisce questa sequenza.

SELECT
  cal.month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-01',
                      INTERVAL '1 month') AS cal(month)
LEFT JOIN orders o
  ON DATE_TRUNC('month', o.order_ts) = cal.month
GROUP BY cal.month
ORDER BY cal.month;

Esempio avanzato: utenti attivi per settimana

Si combina il raggruppamento in bucket con il conteggio dei distinti. "Utenti attivi settimanali" significa contare gli utenti distinti per bucket settimanale, una richiesta reale nell'analisi dei prodotti.

Si tronca il timestamp dell'evento alla settimana, poi si usa COUNT(DISTINCT user_id). Menzionare che si unirebbe una sequenza completa delle settimane per mostrare quelle senza attività permette di ottenere punti extra.

SELECT
  DATE_TRUNC('week', event_ts) AS week,
  COUNT(DISTINCT user_id)      AS wau
FROM events
GROUP BY 1
ORDER BY 1;

Creare bucket su una colonna indicizzata

È importante segnalare una considerazione sulle prestazioni: racchiudere la colonna della data in DATE_TRUNC all'interno di una clausola WHERE può impedire al planner di usare un indice su quella colonna.

Nel GROUP BY va bene, ma per filtrare è preferibile confrontare la colonna non modificata con limiti calcolati. Il pattern degli intervalli semiaperti, trattato in precedenza, si applica anche in questo caso.

-- Avoid in WHERE: DATE_TRUNC('month', order_ts) = '2024-03-01'
-- Prefer:
SELECT * FROM orders
WHERE order_ts >= DATE '2024-03-01'
  AND order_ts <  DATE '2024-04-01';

Verifica rapida

Scelga lo strumento corretto per un grafico dell'andamento mensile che mantenga separati gli anni.

Riepilogo: troncare e creare bucket per le date

Da ricordare:

  • DATE_TRUNC(unit, ts) associa i timestamp all'inizio di un periodo e mantiene distinti gli anni: è lo strumento corretto per le serie temporali.
  • EXTRACT restituisce un semplice numero, utile per la stagionalità, ma unisce gli anni.
  • In Postgres le settimane iniziano di lunedì; applichi un offset se serve la domenica.
  • MySQL usa DATE_FORMAT; le versioni meno recenti di SQL Server usano l'idioma DATEADD(DATEDIFF(...)); dalla versione 2022 è disponibile DATETRUNC.
  • Usi una date spine + LEFT JOIN generata per mostrare i periodi vuoti e tenga DATE_TRUNC fuori da WHERE per preservare l'uso degli indici.

Domande Frequenti

La lezione «Troncare e raggruppare le date» è gratuita?

Sì — il testo completo di «Troncare e raggruppare le date» è 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 «Troncare e raggruppare le date»?

Raggruppamento per settimana, mese e trimestre con DATE_TRUNC e gli equivalenti. 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 2 di 4.

Quanto tempo richiede la lezione «Troncare e raggruppare le date»?

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

  1. Aritmetica delle date e intervalli
  2. Troncare e raggruppare le date
  3. Analizzare e formattare stringhe
  4. Fusi orari e timestamp
← Torna a SQL Interview Prep