0Pricing
SQL Academy · Lezione

Generare serie e sequenze

Crei righe usando la ricorsione

Generare serie e sequenze è una lezione SQL Academy 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 Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Che cos'è una serie generata

A volte serve un elenco di numeri, date o altri valori sequenziali che non esistono in alcuna tabella. SQL consente di crearli al volo usando la ricorsione o funzioni integrate.

In questa lezione imparerà a generare sequenze usando WITH RECURSIVE, uno strumento potente che consente a una query di fare riferimento al proprio output.

La Sua prima CTE ricorsiva

Una CTE ricorsiva (Common Table Expression) è composta da due parti unite da UNION ALL: un caso base che produce la prima riga e un passaggio ricorsivo che fa riferimento alla CTE stessa per produrre la riga successiva.

La ricorsione si arresta quando il passaggio ricorsivo non restituisce righe.

WITH RECURSIVE counter(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 5
)
SELECT n FROM counter;

Come si sviluppa la ricorsione

È utile immaginare ogni passaggio separatamente. Il caso base inizializza la tabella, poi ogni passaggio ricorsivo aggiunge un'altra riga finché la condizione WHERE non viene più soddisfatta.

Con la query WHERE n < 5, il motore genera le righe 1, 2, 3, 4, 5 e poi si arresta perché 5 < 5 è falso.

WITH RECURSIVE steps(n, note) AS (
  SELECT 1, 'base case'
  UNION ALL
  SELECT n + 1, 'recursive step'
  FROM steps
  WHERE n < 4
)
SELECT n, note FROM steps;

Generare numeri pari

Può modificare semplicemente la dimensione del passo aggiungendo un valore maggiore di 1 nella parte ricorsiva. In questo modo vengono prodotti tutti i numeri pari da 2 a 10.

Lo schema è sempre: next_value = current_value + step.

WITH RECURSIVE evens(n) AS (
  SELECT 2
  UNION ALL
  SELECT n + 2 FROM evens WHERE n < 10
)
SELECT n FROM evens;

Contare alla rovescia

La ricorsione non è limitata al conteggio crescente. Sottraendo invece di aggiungere si ottiene una sequenza decrescente. Si assicuri che la condizione di arresto usi > invece di < per evitare un ciclo infinito.

WITH RECURSIVE countdown(n) AS (
  SELECT 10
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown;

Generare un intervallo di date

Uno degli usi più pratici delle CTE ricorsive consiste nella creazione di una serie di date. Si parte da una data specifica e si continua ad aggiungere un giorno fino a raggiungere una data finale.

È particolarmente utile per colmare le lacune nei report: ogni data viene visualizzata anche se per quel giorno non ci sono dati.

WITH RECURSIVE dates(d) AS (
  SELECT DATE '2024-01-01'
  UNION ALL
  SELECT d + INTERVAL '1 day'
  FROM dates
  WHERE d < DATE '2024-01-07'
)
SELECT d FROM dates;

Colmare le lacune nei report con una serie di date

Immagini una tabella sales che contiene righe solo nei giorni in cui si sono verificate vendite. Collegando una serie di date generata alla tabella delle vendite con un LEFT JOIN, ottiene ogni giorno dell'intervallo, con NULL per i giorni senza vendite.

WITH RECURSIVE cal(d) AS (
  SELECT DATE '2024-03-01'
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM cal WHERE d < DATE '2024-03-05'
),
sales(sale_date, amount) AS (
  VALUES
    (DATE '2024-03-01', 100),
    (DATE '2024-03-03', 250),
    (DATE '2024-03-05', 180)
)
SELECT cal.d, COALESCE(sales.amount, 0) AS amount
FROM cal
LEFT JOIN sales ON cal.d = sales.sale_date
ORDER BY cal.d;

Generare una tabella delle moltiplicazioni

Le CTE ricorsive possono contenere più colonne, consentendo di creare output più ricchi. In questo caso vengono tenuti traccia contemporaneamente sia dell'indice di riga sia del valore calcolato.

WITH RECURSIVE mult(n, result) AS (
  SELECT 1, 1 * 7
  UNION ALL
  SELECT n + 1, (n + 1) * 7
  FROM mult
  WHERE n < 10
)
SELECT n, result AS seven_times_n FROM mult;

Numeri di Fibonacci

La successione di Fibonacci è una sequenza classica in cui ogni numero è la somma dei due precedenti: 0, 1, 1, 2, 3, 5, 8 …

Una CTE ricorsiva tiene traccia sia del valore corrente a sia del valore successivo b, scambiandoli a ogni passaggio.

WITH RECURSIVE fib(a, b) AS (
  SELECT 0, 1
  UNION ALL
  SELECT b, a + b FROM fib WHERE a < 100
)
SELECT a AS fibonacci FROM fib;

Usare generate_series in PostgreSQL

PostgreSQL offre una scorciatoia integrata chiamata generate_series(), che produce sequenze senza dover scrivere una CTE ricorsiva. Accetta un valore iniziale, uno finale e un passo opzionale.

È il modo più conciso per generare intervalli di numeri o date in PostgreSQL.

SELECT gs AS num
FROM generate_series(1, 10, 2) AS gs;

Generare intervalli mensili

Passi un intervallo '1 month' a generate_series() per creare un calendario mensile. È perfetto per creare le intestazioni dei report mensili o verificare quali mesi non contengono dati.

SELECT gs::DATE AS month_start
FROM generate_series(
  '2024-01-01'::DATE,
  '2024-06-01'::DATE,
  INTERVAL '1 month'
) AS gs;

Verifica delle conoscenze

Verifichi la Sua comprensione della generazione di serie con le CTE ricorsive.

Riepilogo: generare serie e sequenze

In questa lezione ha imparato a produrre sequenze di numeri e date senza alcuna tabella di origine:

  • WITH RECURSIVE — un caso base più un passaggio ricorsivo uniti con UNION ALL; si arresta quando il passaggio ricorsivo non restituisce righe.
  • Dimensione del passo flessibile — aggiunga o sottragga qualsiasi valore per contare in avanti, alla rovescia o saltare alcuni valori.
  • Intervalli di date — aggiunga un intervallo per generare serie giornaliere, mensili o calendari personalizzati.
  • CTE con più colonne — mantenga informazioni aggiuntive tra le iterazioni per ottenere output più ricchi, come la successione di Fibonacci.
  • generate_series() — la funzione integrata di PostgreSQL per creare sequenze di numeri e date nel modo più conciso.

Le serie generate sono essenziali per colmare le lacune nei report, creare dati di test e gestire qualsiasi situazione in cui serva un intervallo completo di valori indipendentemente dal contenuto dei dati.

Domande Frequenti

La lezione «Generare serie e sequenze» è gratuita?

Sì — il testo completo di «Generare serie e sequenze» è 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 Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Generare serie e sequenze»?

Crei righe usando la ricorsione Eserciti SQL Academy 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 Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy 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 «Generare serie e sequenze»?

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 Academy?

Sì. Ogni lezione SQL Academy 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. Come funzionano le CTE ricorsive
  2. Percorrere un albero di categorie
  3. Generare serie e sequenze
  4. Evitare i cicli infiniti
← Torna a SQL Academy