{"id":4100,"date":"2024-01-15T12:34:48","date_gmt":"2024-01-15T11:34:48","guid":{"rendered":"https:\/\/www.skillup.cloud\/?p=4100"},"modified":"2024-02-11T16:23:18","modified_gmt":"2024-02-11T15:23:18","slug":"cte-ricorsive-leleganza-della-semplicita-in-sql-3","status":"publish","type":"post","link":"https:\/\/www.skillup.cloud\/it\/cte-ricorsive-leleganza-della-semplicita-in-sql-3\/","title":{"rendered":"CTE Ricorsive: L\u2019Eleganza della Semplicit\u00e0 in SQL \u2013 3"},"content":{"rendered":"\n<p class=\"has-large-font-size\"><strong>Introduzione<\/strong><\/p>\n\n\n\n<p>Come abbiamo gi\u00e0 visto in  altri Articoli, le Common Table Expressions (CTE) in SQL su IBM iSeries (AS\/400), ma presenti anche in molte altre versioni su altri sistemi non IBM, rappresentano uno strumento potente e flessibile per creare query temporanee e riutilizzabili, semplificando e migliorando la leggibilit\u00e0 delle query SQL complesse..<\/p>\n\n\n\n<p>Di seguito gli articoli della serie CTE (incluso questo corrente):<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <a href=\"https:\/\/www.skillup.cloud\/it\/iseries-sql-cte-common-table-expressions-definizioni-e-utilizzo\/\" target=\"_blank\" rel=\"noreferrer noopener\">iSeries Sql &#8211; CTE Common Table Expressions &#8211; definizioni e utilizzo<\/a> <\/span><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <a href=\"https:\/\/www.skillup.cloud\/it\/cte-ricorsive-leleganza-della-semplicita-in-sql-1\/\" target=\"_blank\" rel=\"noreferrer noopener\">CTE Ricorsive: L&#8217;Eleganza della Semplicit\u00e0 in SQL &#8211; 1<\/a> <\/span><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <a href=\"https:\/\/www.skillup.cloud\/it\/cte-ricorsive-leleganza-della-semplicita-in-sql-2\/\" target=\"_blank\" rel=\"noreferrer noopener\">CTE Ricorsive: L&#8217;Eleganza della Semplicit\u00e0 in SQL &#8211; 2<\/a> <\/span><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <a href=\"https:\/\/www.skillup.cloud\/it\/cte-ricorsive-leleganza-della-semplicita-in-sql-3\/\" target=\"_blank\" rel=\"noreferrer noopener\">CTE Ricorsive: L&#8217;Eleganza della Semplicit\u00e0 in SQL &#8211; 3<\/a><\/span><\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li><span style=\"text-decoration: underline\"> <a href=\"https:\/\/www.skillup.cloud\/it\/recursive-cte-come-concatenare-i-risultati-su-ununica-riga\/\" target=\"_blank\" rel=\"noreferrer noopener\">Recursive CTE come concatenare i risultati su un\u2019unica riga<\/a> <\/span><\/li>\n<\/ul>\n\n\n\n<p>In particolare le Common Table Expressions (CTE)&nbsp;<strong>Ricorsive<\/strong>&nbsp;sono un potente strumento in SQL che permette di&nbsp;<strong>eseguire operazioni che, in assenza di questa funzionalit\u00e0, richiederebbero lo sviluppo di programmi specifici o l\u2019utilizzo di cicli complessi<\/strong>.<\/p>\n\n\n\n<p>In questo Articolo vedremo una ulteriore estensione dell&#8217;esempio <strong><span style=\"text-decoration: underline\"> <a href=\"https:\/\/www.skillup.cloud\/cte-ricorsive-leleganza-della-semplicita-in-sql-1\/\" target=\"_blank\" rel=\"noreferrer noopener\">CTE Ricorsive: L\u2019Eleganza della Semplicit\u00e0 in SQL \u2013 1<\/a> <\/span><\/strong>e vedremo come un comando SQL con utilizzo di CTE Ricorsive pu\u00f2 dare risultati che normalmente richiederebbero molte righe di codice se risolti con un approccio programmatico tradizionale.<\/p>\n\n\n\n<p>Va detto che l&#8217;utilizzo delle CTE ricorsive non \u00e8 banale e va affrontato con attenzione ma se viene capito l&#8217;approccio pu\u00f2 dare soluzioni concise ed eleganti per risolvere problemi anche complessi.<\/p>\n\n\n\n<p class=\"has-large-font-size\"><strong>Descrizione Scenario e obiettivo<\/strong><\/p>\n\n\n\n<p>Immaginiamo uno schema di rete organizzato in una struttura ad albero, dove sono illustrati sia i nodi sia le relazioni di dipendenza che intercorrono tra di essi.<\/p>\n\n\n\n<p>Questi nodi possono simboleggiare vari elementi, come i dipartimenti di un&#8217;azienda, i dipendenti di un&#8217;organizzazione, o i nodi di un circuito elettronico, dove sono delineate le relazioni di dipendenza tra i diversi processori, e cos\u00ec via.<\/p>\n\n\n\n<p>Ipotizziamo di voler compilare un elenco di tutti i percorsi unici che partono dalla radice e raggiungono le foglie nell&#8217;albero di nodi, includendo anche quei nodi &#8220;isolati&#8221;, ossia privi di dipendenze o collegamenti con altri nodi.<\/p>\n\n\n\n<p>Di seguito uno schema della struttura ad albero:<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large is-resized\"><img decoding=\"async\" src=\"https:\/\/www.skillup.cloud\/wp-content\/uploads\/2024\/01\/tree_structure.png\" alt=\"\" style=\"width:656px;height:auto\"\/><\/figure><\/div>\n\n\n<p>In questa struttura sono presenti nodi &#8220;isolati&#8221;, ovvero senza legami e nodi aventi uno o pi\u00f9 legami.<\/p>\n\n\n\n<p>Il risultato che si vuole ottenere \u00e8 l&#8217;elenco di tutti i percorsi unici (che non siano inclusi in altri percorsi) che partono da una radice (nodo senza un &#8220;padre&#8221;) e raggiungono le foglie terminali (nodi senza &#8220;figli&#8221;) nell&#8217;albero di nodi, includendo anche quei nodi &#8220;isolati&#8221;, ossia privi di dipendenze o collegamenti con altri nodi.<\/p>\n\n\n\n<p class=\"has-large-font-size\"><strong>Query<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">WITH -- starting data\n     TreeStructure (NodeID, Description, ParentNodeID) AS (\n         VALUES  ('I011', 'Description_011',  NULL) , ('I012', 'Description_012',  NULL)\n               , ('I013', 'Description_013',  NULL) , ('I021', 'Description_021', 'I013')\n               , ('I022', 'Description_022', 'I013'), ('I023', 'Description_023', 'I013')\n               , ('I024', 'Description_024', 'I013'), ('I031', 'Description_031', 'I022')\n               , ('I032', 'Description_032', 'I022'), ('I041', 'Description_041', 'I031')\n               , ('I042', 'Description_042', 'I031'), ('I043', 'Description_043', 'I031')\n               , ('I044', 'Description_044', 'I032'), ('I045', 'Description_045', 'I032')\n               , ('I046', 'Description_046', 'I032'), ('I051', 'Description_051', 'I042')\n               , ('I052', 'Description_052', 'I046'), ('I061', 'Description_061', 'I051')\n               , ('I062', 'Description_062', 'I051'), ('I063', 'Description_063', 'I051')\n               , ('I064', 'Description_064', 'I052'), ('I065', 'Description_065', 'I052')\n               , ('I066', 'Description_066', 'I052'), ('I033', 'Description_033', 'I023')\n               , ('I034', 'Description_034', 'I024'), ('I035', 'Description_035', 'I024')\n)\n, Routes(NodeID, Path) AS (\n    -- Parte ancorata: seleziona i nodi radice\n    SELECT NodeID, CAST(NodeID AS VARCHAR(1024))\n    FROM TreeStructure\n    WHERE ParentNodeID IS NULL\n    UNION ALL\n    -- Parte ricorsiva: aggiungi il nodo corrente al Path\n    SELECT nd.NodeID, p.Path concat '-&gt;' concat nd.NodeID\n    FROM TreeStructure nd\n    JOIN Routes p ON nd.ParentNodeID = p.NodeID\n)\n, Routes2(NodeID, Path) AS (\n    SELECT NodeID, SUBSTR(Path, 1, length(Path)-6)\n      FROM Routes\n      WHERE LENGTH(Path) &gt; 6\n    UNION ALL \n    SELECT NodeID, Path\n      FROM Routes\n      WHERE LENGTH(Path) &lt;= 6\n)\n, Routes3(NodeID, Path) AS (\n    SELECT * FROM Routes P1\n     WHERE NOT EXISTS (\n           SELECT 1 from Routes2 P2\n            WHERE P2.Path = P1.Path)\n      ORDER BY 2\n) --  SELECT * FROM Routes3;\n SELECT * FROM Routes3\n  UNION ALL\n SELECT NodeID, NodeID\n   FROM TreeStructure N1\n  WHERE NOT EXISTS (\n            SELECT 1 FROM TreeStructure N2\n              WHERE N1.NodeID = N2.ParentNodeID)\n    AND N1.ParentNodeID IS NULL\n   ORDER BY 2;\n<\/pre>\n\n\n\n<p class=\"has-large-font-size\"><strong>Risultato della Query<\/strong><\/p>\n\n\n\n<div class=\"wp-block-columns\">\n<div class=\"wp-block-column\">\n<figure class=\"wp-block-table\"><table><tbody><tr><td>ID_NODO<\/td><td>PERCORSO<\/td><\/tr><tr><td>I011<\/td><td>I011<\/td><\/tr><tr><td>I012<\/td><td>I012<\/td><\/tr><tr><td>I021<\/td><td>I013-&gt;I021<\/td><\/tr><tr><td>I041<\/td><td>I013-&gt;I022-&gt;I031-&gt;I041<\/td><\/tr><tr><td>I061<\/td><td>I013-&gt;I022-&gt;I031-&gt;I042-&gt;I051-&gt;I061<\/td><\/tr><tr><td>I062<\/td><td>I013-&gt;I022-&gt;I031-&gt;I042-&gt;I051-&gt;I062<\/td><\/tr><tr><td>I063<\/td><td>I013-&gt;I022-&gt;I031-&gt;I042-&gt;I051-&gt;I063<\/td><\/tr><tr><td>I043<\/td><td>I013-&gt;I022-&gt;I031-&gt;I043<\/td><\/tr><tr><td>I044<\/td><td>I013-&gt;I022-&gt;I032-&gt;I044<\/td><\/tr><tr><td>I045<\/td><td>I013-&gt;I022-&gt;I032-&gt;I045<\/td><\/tr><tr><td>I064<\/td><td>I013-&gt;I022-&gt;I032-&gt;I046-&gt;I052-&gt;I064<\/td><\/tr><tr><td>I065<\/td><td>I013-&gt;I022-&gt;I032-&gt;I046-&gt;I052-&gt;I065<\/td><\/tr><tr><td>I066<\/td><td>I013-&gt;I022-&gt;I032-&gt;I046-&gt;I052-&gt;I066<\/td><\/tr><tr><td>I033<\/td><td>I013-&gt;I023-&gt;I033<\/td><\/tr><tr><td>I034<\/td><td>I013-&gt;I024-&gt;I034<\/td><\/tr><tr><td>I035<\/td><td>I013-&gt;I024-&gt;I035<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n\n\n\n<div class=\"wp-block-column\"><\/div>\n<\/div>\n\n\n\n<p class=\"has-large-font-size\"><strong>Descrizione dettagliata della query<\/strong><\/p>\n\n\n\n<p>La query SQL descritta \u00e8 un esempio di una Common Table Expression (CTE) ricorsiva utilizzata per elaborare una struttura ad albero. La query \u00e8 suddivisa in pi\u00f9 parti, ognuna delle quali costruisce sopra la precedente per arrivare al risultato finale.<\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>1) Definizione della Struttura dell&#8217;Albero (TreeStructure):<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Questa CTE iniziale, <strong>TreeStructure<\/strong>, definisce i dati di partenza, rappresentando l&#8217;albero dei nodi. Ogni nodo \u00e8 definito da un <strong>NodeID<\/strong> univoco, una <strong>Description<\/strong> e un <strong>ParentNodeID<\/strong> che indica il nodo genitore (se esiste; se <strong>NULL<\/strong>, il nodo \u00e8 una radice).<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size\"><strong>2) Generazione dei Percorsi (Routes):<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>La CTE <strong>Routes<\/strong> inizia con la parte &#8220;ancorata&#8221; che seleziona tutti i nodi radice (i nodi senza un nodo genitore).<\/li>\n<\/ul>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Poi, attraverso la parte &#8220;ricorsiva&#8221;, la query si espande aggiungendo progressivamente i nodi figli ai percorsi, concatenando il <strong>NodeID<\/strong> corrente al percorso (<strong>Path<\/strong>) del nodo genitore.<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size\"><strong>3) Pulizia dei Percorsi (Routes2)<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>La CTE <strong>Routes2<\/strong> \u00e8 una tabella intermedia che serve per individuare eventuali percorsi che sono inclusi in altri percorsi (sotto percorsi). In pratica per poter individuare i sotto-percorsi viene creata questa tabella intermedia come copia della tabella Routes nella quale viene modificata la colonna Path rimuovendo l&#8217;ultimo nodo (creando cos\u00ec sotto-percorsi per confrontarli).<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size\"><strong>4) <strong>Selezione dei Percorsi Unici (Routes3)<\/strong> <\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Routes3<\/strong> seleziona i percorsi finali escludendo quelli che sono sotto-percorsi di altri percorsi gi\u00e0 esistenti, assicurando che ogni percorso sia unico e non sia incluso in un altro percorso pi\u00f9 lungo.<\/li>\n<\/ul>\n\n\n\n<p class=\"has-medium-font-size\"><strong>5) <strong>Risultato Finale<\/strong><\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Infine, la query seleziona tutti i percorsi unici dall&#8217;ultima CTE (<strong>Routes3<\/strong>).<\/li>\n\n\n\n<li>Aggiunge anche i nodi &#8220;isolati&#8221;, che sono nodi radice senza figli, utilizzando un <strong>UNION ALL<\/strong> per unire questi nodi alla lista dei percorsi. I nodi isolati sono identificati confrontando <strong>TreeStructure<\/strong> con se stessa per trovare nodi che non sono genitori di altri nodi (cio\u00e8, non hanno figli) e che non hanno un nodo genitore (sono nodi radice).<\/li>\n\n\n\n<li>La parte finale della query ordina tutti i risultati basandosi sulla colonna <strong>Path<\/strong> per presentare l&#8217;elenco in un ordine leggibile.<\/li>\n<\/ul>\n\n\n\n<p>In sintesi, la query \u00e8 un modo sofisticato di trasformare una struttura ad albero rappresentata in una tabella di database in un elenco di percorsi unici da ogni nodo radice a ogni nodo foglia, includendo anche nodi isolati. Questa tecnica \u00e8 spesso utilizzata in database che supportano CTE ricorsive per manipolare dati gerarchici o ricavare informazioni da strutture ad albero complesse.<\/p>\n\n\n\n<p>Per eseguire lo script occorre assicurarsi che la sintassi specifica (<strong>concat<\/strong>, <strong>SUBSTR<\/strong>, <strong>LENGTH<\/strong>) sia supportata dal DBMS.<\/p>\n\n\n\n<p>IBM iSeries utilizza una sintassi leggermente diversa da altri sistemi SQL per alcune funzioni. Come sempre tenere presente che l\u2019implementazione e le funzionalit\u00e0 effettive potrebbero variare a seconda della versione del sistema operativo iSeries e del database DB2. \u00c8 sempre buona norma fare riferimento alla documentazione ufficiale IBM per avere informazioni pi\u00f9 precise e dettagliate.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduzione Come abbiamo gi\u00e0 visto in altri Articoli, le Common Table Expressions (CTE) in SQL su IBM iSeries (AS\/400), ma presenti anche in molte altre versioni su altri sistemi non IBM, rappresentano uno strumento potente e flessibile per creare query&#8230;<\/p>\n","protected":false},"author":3,"featured_media":4107,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"_kadence_starter_templates_imported_post":false,"_kad_post_transparent":"","_kad_post_title":"","_kad_post_layout":"","_kad_post_sidebar_id":"","_kad_post_content_style":"","_kad_post_vertical_padding":"","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","footnotes":""},"categories":[14,15,17],"tags":[46,128,129,130],"class_list":["post-4100","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-as400-architettura","category-gestire-as400-con-sql","category-piu-in-profondita","tag-sql","tag-cte-ricorsive","tag-cte-common-table-expressions","tag-tree-structure"],"_links":{"self":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/4100","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/comments?post=4100"}],"version-history":[{"count":5,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/4100\/revisions"}],"predecessor-version":[{"id":4380,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/posts\/4100\/revisions\/4380"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/media\/4107"}],"wp:attachment":[{"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/media?parent=4100"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/categories?post=4100"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.skillup.cloud\/it\/wp-json\/wp\/v2\/tags?post=4100"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}