Dati gerarchici e come interrogarli in SQL

Come si riconoscono i dati gerarchici e come si interrogano? Due domande comuni sull'SQL stanno per trovare risposta.

L'interrogazione di dati gerarchici è una situazione piuttosto comune se si lavora con SQL e i database relazionali. Non che i dati gerarchici siano qualcosa di mistico e raro; al contrario, sono ovunque. Perché i dati gerarchici sono così difficili quando si tratta di database relazionali? Principalmente, le difficoltà si verificano nel tradurre i dati gerarchici in principi di database relazionali.

Quando si lavora con i dati gerarchici, il primo passo è riconoscerli. Inizierò definendo cosa sono i dati gerarchici e fornendo alcuni esempi quotidiani. Poi passerò a spiegare come i dati gerarchici vengono solitamente memorizzati nei database. Infine, imparerete come interrogare tali dati utilizzando SQL.

Quanto appreso in questo articolo può essere messo in pratica nel nostro corso Recursive Queries .

Che cosa sono i dati gerarchici?

I dati gerarchici sono un tipo specifico di dati, caratterizzati da una relazione gerarchica tra gli insiemi di dati. Cosa vi viene in mente quando pensate alla gerarchia? Probabilmente a diversi livelli: qualcosa è sopra, sotto o allo stesso livello di qualcos'altro. Nei database relazionali, una relazione gerarchica è chiamata anche relazione genitore-figlio. Ciò significa che i dati figli hanno un solo genitore, mentre i dati genitori hanno uno o più "figli".

Si usa dire che i dati gerarchici si riconoscono per la loro struttura ad albero. Il perché lo vedremo tra poco, quando vedremo esempi comuni di dati gerarchici.

Esempi di dati gerarchici

Gerarchie di dipendenti

Uno degli esempi tipici utilizzati per spiegare i dati gerarchici sono le gerarchie dei dipendenti. Sono rappresentate da organigrammi come questo:

Come si può notare, questa struttura è stretta in alto e diventa più ampia in basso, come un albero di pino. Il presidente è in cima. I suoi subordinati sono due membri del Consiglio di amministrazione. Anche questi due membri del consiglio hanno i loro subordinati. Nel caso di Jacqueline Managerovicz, questi sono Diane Drinkalot, responsabile delle risorse umane, e Rashawn Mangarello, responsabile della contabilità. Ma anche loro sono subordinati del presidente, anche se indirettamente.

I subordinati diretti dell'altro membro del consiglio di amministrazione sono Tony Workaholio, responsabile delle vendite, e Cassandra Ninetofiver, responsabile IT. Paul Bossenheim, il presidente della società, è anche il loro superiore indiretto.

Albero genealogico

Un albero genealogico è un altro esempio comune di dati gerarchici. La sua struttura ci permette di trovare gli antenati e i loro discendenti. Un albero genealogico potrebbe avere un aspetto simile a questo:

In questo esempio, Mike Strongbow sposò Victoria Stromboli. Hanno avuto due figli, Florence e Claudio. Florence ha avuto una figlia (Valerie) e Claudio ha avuto due figli (Art e Michelle). Tutti e tre sono figli dei loro genitori, ma sono anche nipoti di Mike e Victoria. Mike e Victoria sono anche bisnonni; la loro nipote ha avuto due figli, Judy e James.

Menu bevande

Se andate in un bar, probabilmente vi siete imbattuti in una struttura gerarchica. Mi riferisco a quella che di solito si guarda subito dopo essersi accomodati: il menu delle bevande, o carta, o come lo si voglia chiamare. Ad esempio, se visitate il Panthelya Bar, scoprirete che si tratta di un bar molto primitivo che offre solo birre e vini.

Il bar offre due tipi di birre: birra chiara e birra di frumento. Per ogni categoria è possibile ordinare molte birre. La categoria dei vini è divisa in rossi e bianchi, con quattro vini per categoria.

Tassonomia degli esseri viventi

Anche voi, che leggete questo articolo, siete un record di dati gerarchici. In quanto essere umano, avete una certa posizione nella gerarchia della tassonomia degli esseri viventi.

Il diagramma qui sopra mostra la gerarchia per gli esseri umani, o homo sapiens. Sarebbe troppo complicato mostrare l'intera tassonomia degli esseri viventi. Tuttavia, il principio è lo stesso di tutti i diagrammi precedenti. Tutti gli esseri umani appartengono alla specie Sapiens, che fa parte del genere Homo. Questo genere fa parte della famiglia Hominidae, una delle famiglie dell'ordine dei Primati. I primati appartengono alla classe dei Mammalia, che è subordinata al phylum, al regno e, infine, al dominio.

Cartelle sul computer

Se state leggendo questo articolo, è molto probabile che lo stiate facendo sul vostro computer. È quindi possibile che le cartelle del vostro computer abbiano un aspetto simile a questo:

Tutte le cartelle di questo esempio (Learning e Freelancing) si trovano sul disco D:. La cartella Learning ha due sottocartelle: SQL, dove vengono inseriti tutti gli articoli interessanti relativi a SQL come questo, e Python. La cartella Freelance contiene tre sottocartelle: Lavori, Fatture e Altri documenti.

Ora che sapete come riconoscere i dati gerarchici, vediamo come vengono memorizzati nel database e come interrogarli.

Memorizzazione dei dati gerarchici in un database

Il problema dei dati gerarchici si presenta di solito quando si cerca di salvare tali dati in un database. Per farlo, è necessario racchiudere tutti i dati multilivello in un formato relativamente piatto: una tabella. Come si fa a convertire i dati gerarchici in semplici righe di dati?

Per memorizzare i dati gerarchici in un database, di solito c'è una colonna che fa riferimento alla stessa tabella. Che cosa significa? Probabilmente è meglio che vi mostri un esempio. La gerarchia dei dipendenti sembra molto adatta a questo scopo!

Interrogazione di dati gerarchici con un'autoconnessione

Vi mostrerò come interrogare una gerarchia di dipendenti. Supponiamo di avere una tabella denominata employee con i seguenti dati:

• employee_id - L'ID del dipendente e la chiave primaria (PK) della tabella.
• first_name - Il nome del dipendente.
• last_name - Il cognome del dipendente.
• reports_to - L'ID del diretto supervisore o manager del dipendente.

La colonna reports_to non è altro che la colonna employee_id che serve come strumento per mostrare quale dipendente riporta a quale dipendente. Se l'ID del dipendente compare nella colonna reports_to, questo dipendente è il capo di (almeno alcuni) altri dipendenti. Vediamo come funziona:

Vediamo che Sharon Simon riporta al dipendente che ha employee_id = 6, Martina Novak. Nel suo caso, il valore reports_to è NULL. Ciò significa che Martina Novak non riferisce a nessuno. Da ciò si deduce che è in cima alla gerarchia dei dipendenti.

Questa è la parte in cui si procede all'auto-giunzione della tabella. Non sapete cosa sia un self-join? Potete imparare facilmente leggendo questo articolo con sette esempi di self-join. Ricordate, ho già detto che i dati gerarchici in un database di solito hanno una colonna che fa riferimento alla stessa tabella. Questo è un esempio. Per ottenere i subordinati diretti dalla tabella employeeè necessario scrivere questa query:

SELECT	
sub.employee_id AS subordinate_id,
sub.first_name AS subordinate_first_name,
	sub.last_name AS subordinate_last_name,
	sup.employee_id AS superior_id,
	sup.first_name AS superior_first_name,
	sup.last_name AS superior_last_name
FROM employee sub 
JOIN employee sup 
ON sub.reports_to = sup.employee_id
ORDER BY superior_id;

Questa query unisce la tabella employee tabella con se stessa. Vi spiego come funziona. Quando si unisce una tabella con se stessa, è necessario utilizzare alias chiari in modo che SQL sappia quali dati provengono da quale tabella e che voi sa ppiate quali dati provengono da quale tabella. Nella query di cui sopra, un alias di tabella è sub. Ciò significa che è la tabella con i dati dei subordinati. L'altro alias è sup, cioè la tabella con i dati dei superiori. Anche se si tratta della stessa tabella, la trattiamo come se si trattasse di due tabelle diverse.

La query di cui sopra seleziona innanzitutto le colonne employee_id, first_name, e last_name dalla tabella sub dalla tabella. Poi prende gli stessi dati dalla tabella sup. Quindi, la tabella employee viene quindi unita a se stessa utilizzando i due alias. L'autogiunzione avviene quando la colonna reports_to dalla tabella sub è uguale alla colonna employee_id della tabella sup. I dati vengono infine ordinati in base alla colonna superior_id.

Ecco il risultato:

La tabella mostra che Kim Magnus, Sharon Simon e Vincent Trafalgar sono diretti subordinati di Martina Novak. Victor Fonseca riferisce a Patricia Rooney. A sua volta, Patricia Rooney è un subordinato diretto di Sharon Simon, così come James Palin.

Utilizzo di Recursive Queries su dati gerarchici profondi

Nell'esempio precedente, vi ho mostrato come trovare superiori/subordinati diretti. Ciò significa che avete imparato a cercare solo un livello superiore o inferiore. Anche se questo è molto utile, le gerarchie possono essere molto profonde e avere un numero immenso di livelli. Prima di interrogare tali dati, è necessario imparare a utilizzare le query ricorsive. Parliamo prima di query ricorsive e poi vi mostrerò come funzionano con uno o due esempi.

Cosa sono le Recursive Queries?

Se volete conoscere le query ricorsive, dovete prima imparare a conoscere le Common Table Expressions, o CTE.

Una CTE è un insieme di dati temporaneo restituito da una query, che viene poi utilizzato da un'altra query. È temporaneo perché il risultato non viene memorizzato da nessuna parte, ma esiste solo quando la query viene eseguita. I CTE possono essere non ricorsivi e ricorsivi. Ho già scritto a proposito delle CTE (non ricorsive) e di quando usarle; per ulteriori informazioni, si consiglia di controllare più avanti.
Una query ricorsiva è una query che fa riferimento a se stessa. In questo modo, restituisce il risultato secondario e ripete il processo fino a restituire il risultato finale. Seguendo questa logica, una CTE ricorsiva è una CTE che fa riferimento a se stessa.

La sintassi delle CTE ricorsive

La sintassi generale delle CTE ricorsive si presenta come segue e può essere suddivisa in tre parti:

WITH RECURSIVE cte_name AS (
   cte_query_definition

   UNION ALL

   cte_query_definition
   )


SELECT *
FROM cte_name;

Le CTE sono anche chiamate "query WITH". Se si osserva la sintassi di cui sopra, si capisce perché: la CTE inizia sempre con la clausola WITH. Se si vuole che la CTE sia ricorsiva, bisogna farla seguire dalla parola RECURSIVE. Dopodiché, si definisce il nome della CTE.

Quindi si deve scrivere la definizione della query CTE. Questa parte della query è chiamata membro di ancoraggio. È "collegata" all'altra CTE tramite l'istruzione UNION ALL. Questa seconda definizione di query CTE è chiamata membro ricorsivo e fa riferimento alla CTE stessa.

Alla fine si trova l'istruzione SELECT, che recupera i dati dalla CTE. Questa parte della query è chiamata invocazione.

La sintassi si impara sempre meglio quando la si vede in un esempio. Ecco il primo esempio di CTE ricorsiva!

Interrogazione della gerarchia dei dipendenti

Voglio basarmi sull'esempio in cui si è appreso come effettuare il self-join della employee tabella. Ora utilizzerò la stessa tabella, ma questa volta useremo una query ricorsiva. Il compito è trovare il capo diretto e indiretto di ogni dipendente. Questa relazione tra i dipendenti sarà mostrata come un percorso che porta dal capo in alto (il proprietario) a tutti i dipendenti della tabella.

WITH RECURSIVE employee_hierarchy AS (
  SELECT	employee_id,
    		first_name,
    		last_name,
    		reports_to,
    		'Owner' AS path
  FROM employee
  WHERE reports_to IS NULL

  UNION ALL 
  
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.reports_to,
    employee_hierarchy.path || '->' || e.last_name
  FROM employee e, employee_hierarchy
  WHERE e.reports_to = employee_hierarchy.employee_id
)
SELECT *
FROM employee_hierarchy;

Come abbiamo già imparato, si inizia a scrivere una CTE ricorsiva usando WITH RECURSIVE. Poi si dà un nome alla CTE. In questo caso è employee_hierarchy.

Il membro di ancoraggio della CTE è la prima istruzione SELECT. In questo modo, si seleziona la radice della gerarchia; è la base su cui la query ricorsiva farà la sua magia e troverà tutti gli altri livelli della gerarchia. Questa istruzione seleziona tutte le colonne della tabella employee. Aggiunge anche la nuova colonna pathil cui valore è 'Owner'. La clausola WHERE significa che questo verrà fatto solo per le righe in cui il valore della colonna reports_to è NULL. Perché? Se nella colonna reports_to c'è un valore NULL, il dipendente non riferisce a nessuno. Significa che è il proprietario dell'azienda.

Il passo successivo è quello di "collegare" il membro di ancoraggio con il membro ricorsivo della CTE con UNION ALL. L'aspetto importante dell'uso di UNION ALL è che le istruzioni di SELECT che si stanno "collegando" devono avere lo stesso numero di colonne. Altrimenti, UNION ALL non funzionerà.

Il membro ricorsivo è la seconda istruzione SELECT. Questa istruzione seleziona ancora una volta tutte le colonne della tabella employee. Prende anche il valore (che è 'Owner') dal percorso della colonna dalla CTE employee_hierarchy. Aggiunge '->' a questo valore. Vi aggiunge '->', seguito dal valore della colonna last_name della tabella. employee. (L'operatore || è un operatore di concatenazione; combina due o più valori in un unico valore). Questo sarà un percorso che porta dal proprietario a ogni dipendente.

La tabella employee e la CTE employee_hierarchy vengono unite come qualsiasi altra tabella. La colonna reports_to è uguale alla colonna employee_id. Il membro ricorsivo serve come estensione del membro di ancoraggio. Ciò significa che estende un risultato già trovato (dal membro di ancoraggio) con nuovi risultati. Pertanto, il membro ricorsivo eseguirà tutte le operazioni descritte fino a raggiungere l'ultimo dipendente.

Infine, la parte di semplice invocazione seleziona tutti i dati dalla CTE employee_hierarchy. E, voilà! Il risultato è:

Se si guarda, ad esempio, a Victor Fonseca, si può vedere che il percorso dal proprietario a lui passa attraverso Sharon Simon e Patricia Rooney.

Facciamo pratica con le query ricorsive su un altro esempio!

Interrogazione della gerarchia delle cartelle

Le aziende di solito hanno unità di rete in cui i dipendenti salvano tutto il loro lavoro. Questo porta di solito a una struttura ad albero di cartelle molto ramificata. I dati sulle cartelle sono memorizzati nella tabella folder. Le sue colonne sono:

• id - L'ID della cartella e la chiave primaria (PK) della tabella.
• name - Il nome della cartella.
• subfolder_of - Il nome della cartella di un livello superiore.

Per trovare il percorso di tutte le cartelle, è necessaria la seguente query:

WITH RECURSIVE folder_hierarchy AS (
  SELECT	id,
   	 	name,
    		subfolder_of,
    		CAST (name AS text) AS path
  FROM folder
  WHERE subfolder_of IS NULL
	
  UNION ALL 
	
  SELECT	folder.id,
    		folder.name,
    		folder.subfolder_of,
    		folder_hierarchy.path || '\' || folder.name
  FROM folder, folder_hierarchy
  WHERE folder.subfolder_of = folder_hierarchy.id
)
SELECT *
FROM folder_hierarchy;

Il principio è lo stesso dell'esempio precedente. Anche in questo caso si inizia con WITH RECURSIVE e il nome: folder_hierarchy. La prima istruzione SELECT seleziona tutte e tre le colonne della tabella folder. La quarta colonna è il percorso, che contiene i dati del nome della colonna, fusi come valori di testo. I dati vengono castati in modo che corrispondano al tipo di dati del membro ricorsivo della CTE. Infine, la clausola WHERE limita i dati solo a quelli con i valori NULL nella colonna subfolder_of. Dove c'è NULL, c'è la cartella principale (cioè quella che non ha cartelle superiori).

UNION ALL è ancora una volta utilizzato per "collegare" i membri di ancoraggio e ricorsivi della CTE. Un'altra cosa da ricordare: i tipi di dati in entrambe le istruzioni di SELECT devono essere gli stessi perché UNION ALL funzioni. In caso contrario, la query restituirà un errore.

La seconda istruzione SELECT seleziona nuovamente tutte le colonne della tabella folder. I valori delle colonne percorso e nome vengono messi insieme, con '\' che separa i dati.

Infine, tutti i dati della CTE vengono selezionati, restituendo una bella tabella:

Ora ogni cartella ha il suo percorso ed è facilmente accessibile. Non è più necessario navigare tra più livelli di cartelle per ottenere ciò che serve!

Credo che questi siano esempi convincenti della potenza delle query ricorsive. In caso contrario, c'è sempre la possibilità che vi serva un altro articolo per capire il vero potenziale delle query ricorsive. E se siete utenti di Oracle, ecco come utilizzare le query gerarchiche in questo particolare DBMS.

Interrogare i dati gerarchici è più facile che mai!

Ora che sapete cosa sono i dati gerarchici, inizierete a riconoscerli ovunque. Vi ho fornito diversi esempi quotidiani, ma sono sicuro che ne troverete ancora di più. Provate a implementare le self-join, le CTE ricorsive e tutto ciò che avete imparato qui. È l'unico modo per mantenere fresche le vostre conoscenze!

Se vi mancano esercizi pratici, potete trovarne in abbondanza nel nostro corso Recursive Queries . Che cosa si può imparare in questo corso? Come possono aiutarvi le query ricorsive? Non c'è bisogno di chiederselo: il nostro Chief Content Officer spiega tutto in dettaglio in questo articolo.