Query SQL lunga vs. query SQL ricorsiva

La ricorsione è una delle idee centrali dell'informatica. Possiamo definirla come un metodo per risolvere problemi la cui soluzione dipende dalla soluzione di un'istanza più piccola di un problema. Se vi sembra complicato non preoccupatevi, in questo articolo impareremo a conoscere la ricorsione in SQL che potrete praticare e approfondire in Vertabelo Academy.

La ricorsione è un modo per risolvere i problemi gerarchici che troviamo nei dati con il comune SQL. Questi tipi di query sono chiamati anche query gerarchiche. La capacità di ricorsione è presente nell'SQL standard fin da SQL:1999 attraverso le CTE ricorsive o espressioni di tabella comuni.

Alcuni sistemi RDMBS hanno un proprio modo di implementare la ricorsione, in particolare i database di Oracle con l'istruzione CONNECT BY. Dal momento che le CTE ricorsive fanno parte dello standard SQL e sono condivise da tutti i principali fornitori di RDBMS, esploreremo questo tipo di ricorsione.

RICORSIONE CON CTE

La ricorsione si impara meglio con la visualizzazione di una struttura gerarchica. Non c'è esempio migliore di dati gerarchici della struttura organizzativa di una grande azienda. Esploreremo quindi una tipica tabella employees che contiene dati sui dipendenti e sui loro diretti superiori.

La tabella ha l'identificativo del dipendente corrente e del suo diretto superiore come riferimento alla stessa tabella. Oltre agli identificatori, nella tabella sono presenti anche il nome e il cognome del dipendente.

Costruiremo una query che cerchi tra tutte le righe della tabella, partendo dalla prima riga, solitamente chiamata riga di ancoraggio. Nella nostra tabella la riga di ancoraggio è il top manager, che non ha alcun responsabile nella gerarchia sopra di lui, quindi il suo attributo manager_id è nullo.

  SELECT id,
         manager_id,
         first_name,
         last_name,
         0 depth_level
  FROM   employees
  WHERE  manager_id IS NULL

Supponiamo di voler vedere chi gestisce John: come sarebbe la query? Qualcosa del genere:

SELECT id,
         manager_id,
         first_name,
         last_name
  FROM   employees cur
  WHERE  manager_id in (
    SELECT id
    FROM   employees
    WHERE  manager_id IS NULL
  )

E per i manager di questi manager:

SELECT id,
  manager_id,
  first_name,
  last_name
FROM employees
WHERE manager_id IN
  (SELECT id
  FROM employees
  WHERE manager_id IN
    ( SELECT id FROM employees WHERE manager_id IS NULL
    )
  ) 

Come vedete, si sta delineando un modello per ogni nuovo livello di gestione, per il quale costruiamo una nuova sotto-query. Questo approccio è sbagliato perché tiene conto di un numero fisso di livelli.

La ricorsione è un modo per prendere queste sottoquery e trasformarle in modo che siano generali e rappresentino il risultato precedente della query.

Nel nostro esempio di gestione, la parte generale è costruita in modo da unire il risultato precedente a quello attuale in base all'id della gestione.

  SELECT cur.id,
         cur.manager_id,
         cur.first_name,
         cur.last_name
  FROM   employees cur, previous
  WHERE  cur.manager_id = previous.id

Questo insieme di dati precedenti è definito come CTE.

Quindi la funzione ricorsiva completa si presenta così:

WITH previous(id, manager_id,first_name,last_name) AS (
  SELECT id,
         manager_id,
         first_name,
         last_name
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT cur.id,
         cur.manager_id,
         cur.first_name,
         cur.last_name
  FROM   employees cur, previous
  WHERE  cur.manager_id = previous.id
)
SELECT *
FROM   previous;

La ricorsione inizia con il top manager e viene unita a ogni nuovo livello della gerarchia di gestione. La SELECT finale restituisce l'intero set di dati.

Possiamo espandere questa query per renderla più utile, diciamo che vogliamo vedere i livelli gerarchici. Lo faremo costruendo un nuovo parametro che incrementeremo, chiamandolo depth_level. Per ogni livello della gerarchia, il numero viene incrementato di 1.

WITH previous(id, manager_id,first_name,last_name,depth_level) AS (
  SELECT id,
         manager_id,
         first_name,
         last_name,
         0 depth_level
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT cur.id,
         cur.manager_id,
         cur.first_name,
         cur.last_name,
         previous.depth_level+1 depth_level
  FROM   employees cur, previous
  WHERE  cur.manager_id = previous.id
)
SELECT previous.*
FROM   previous;

Quindi otteniamo i livelli della gerarchia.

Possiamo utilizzare questo livello_di_profondità per rappresentare i dati in modo più grafico con la query

WITH previous(id, manager_id,first_name,last_name,depth_level) AS (
  SELECT id,
         manager_id,
         first_name,
         last_name,
         0 depth_level
  FROM   employees
  WHERE  manager_id IS NULL
  UNION ALL
  SELECT cur.id,
         cur.manager_id,
         cur.first_name,
         cur.last_name,
         previous.depth_level+1 depth_level
  FROM   employees cur, previous
  WHERE  cur.manager_id = previous.id
)
SELECT previous.*,
RPAD('.', (depth_level)*2, '.') || id AS tree
FROM   previous;

e l'insieme dei risultati:

La ricorsione non è la parte più intuitiva dell'informatica, ma è parte integrante. Il modo migliore per imparare la ricorsione è una pratica diligente e costante. Per esercitarsi con l'SQL non c'è posto migliore di LearnSQL.it. Aprite quindi il vostro browser e seguite gli esempi del corso. Recursive Queries e sarete sulla strada della padronanza di SQL.