Narcis Miclaus

8 min de citit

SQL Server, de la zero Lecția 16 / 40

Common Table Expressions pe bune: CTE-uri recursive

Organigrame, arbori de foldere, liste de materiale și generatorul de dimensiuni de date dintr-o singură expresie. Cum să gândești CTE-uri recursive fără să te pierzi.

În lecția 11 am văzut un exemplu de CTE recursiv și am trecut mai departe. Astăzi îi dedicăm întreaga lecție, pentru că recursia în SQL e una dintre acele funcționalități care, odată ce îți „pică fisa”, nu te mai temi niciodată de query-uri pe ierarhii.

Runehold are cel puțin trei seturi de date genuin recursive:

  1. Organigrama. Fiecare angajat are un manager, care are la rândul lui un manager, până la CEO.
  2. Categoriile de produse. „Decor casă > Iluminat > Lămpi de podea > Lămpi de podea în formă de pinguin.”
  3. Lista de materiale (BOM). Bundle-ul „Enchanted Starter Kit” conține o carte de vrăji, o cană de călătorie și un prosop de bucătărie „ușor blestemat” — fiecare dintre acestea putând fi compus, la rândul lui, din SKU-uri mai simple.

De fiecare dată când răspunsul la „dar ce e dedesubt?” are aceeași formă cu întrebarea — ai o problemă recursivă. CTE-urile recursive sunt cea mai curată cale de a o exprima.

Structura din trei părți

Fiecare CTE recursiv are aceeași formă:

WITH my_cte AS (
    -- Partea 1: ANCORA — de unde pornim?
    SELECT ...
    FROM base_table
    WHERE seed_condition

    UNION ALL            -- obligatoriu, nu UNION

    -- Partea 2: MEMBRUL RECURSIV — cum facem un pas mai departe?
    SELECT ...
    FROM base_table AS t
    JOIN my_cte AS r ON r.col = t.col
)
-- Partea 3: QUERY-UL EXTERIOR care citește CTE-ul
SELECT * FROM my_cte ORDER BY ...;

Trei părți. În ordine. UNION ALL obligatoriu; UNION ar deduplica în tăcere rezultatele intermediare și ar strica recursia. Fără excepții.

SQL Server îl execută așa:

  1. Rulează ancora. Pune rezultatul într-un set virtual „curent”.
  2. Rulează membrul recursiv, făcând join cu „curent”. Rezultatul devine noul „curent”.
  3. Repetă pasul 2 până când membrul recursiv întoarce zero rânduri.
  4. Rezultatul final este reuniunea tuturor seturilor „curent” produse.

Două reguli pe care le primești pe gratis:

  • Membrul recursiv trebuie să refere numele CTE-ului (altfel nu e recursiv).
  • Trebuie să existe o condiție de terminare — altfel intri în recursie infinită. SQL Server limitează implicit la 100 de iterații; suprascrii cu OPTION (MAXRECURSION n), iar 0 înseamnă nelimitat (periculos).

Exemplul 1: organigrama

WITH org AS (
    -- Ancora: CEO-ul (fără manager)
    SELECT EmployeeId,
           FullName,
           ManagerId,
           Team,
           0               AS level,
           CAST(FullName AS NVARCHAR(4000)) AS chain
    FROM HR.Employee
    WHERE ManagerId IS NULL

    UNION ALL

    -- Recursiv: angajații al căror manager e deja în CTE
    SELECT e.EmployeeId,
           e.FullName,
           e.ManagerId,
           e.Team,
           o.level + 1,
           CAST(o.chain + N' > ' + e.FullName AS NVARCHAR(4000))
    FROM HR.Employee AS e
    JOIN org         AS o ON o.EmployeeId = e.ManagerId
)
SELECT level, REPLICATE(N'  ', level) + FullName AS indented_name, Team, chain
FROM org
ORDER BY chain;

Output-ul e o organigramă indentată:

CEO - Ilse Jansen
  VP Engineering - Tomasz Kowalski
    Eng Manager - Franz Hofmann
      Dev - Ada Lovelace
      Dev - Grace Hopper
    Eng Manager - Sofia Bianchi
  VP Marketing - Djenna Akkad
    ...

chain acumulează drumul de la rădăcină. Îl poți folosi pentru filtrare („toți cei sub VP Engineering”) sau pentru afișare.

Atenție la lungimea lanțului. Cast la un NVARCHAR(4000) mare, altfel șirul s-ar putea trunchia la lungimea primei valori din ancoră. Capcană clasică de CTE recursiv.

Exemplul 2: categoriile de produse

Același pattern, alte date:

-- Având Catalog.Category (CategoryId, ParentCategoryId, Name)
WITH tree AS (
    SELECT CategoryId, ParentCategoryId, Name, 0 AS depth,
           CAST(Name AS NVARCHAR(4000)) AS path
    FROM Catalog.Category
    WHERE ParentCategoryId IS NULL       -- categorii rădăcină

    UNION ALL

    SELECT c.CategoryId, c.ParentCategoryId, c.Name, t.depth + 1,
           CAST(t.path + N' > ' + c.Name AS NVARCHAR(4000))
    FROM Catalog.Category AS c
    JOIN tree               AS t ON t.CategoryId = c.ParentCategoryId
)
SELECT depth, REPLICATE(N'    ', depth) + Name AS indented, path
FROM tree
ORDER BY path;

Îți dă arborele complet de categorii, sortat astfel încât părinții apar înaintea copiilor în output. Marketing-ul îl iubește pentru meniuri de navigare; ops-ul pentru rapoarte pe categorii.

Exemplul 3: lista de materiale

Produse compuse din alte produse:

-- Având Catalog.Bom (BundleId, ComponentProductId, Quantity)
WITH bom AS (
    -- Ancora: bundle-ul de top
    SELECT BundleId AS product_id,
           ComponentProductId,
           Quantity,
           1 AS depth
    FROM Catalog.Bom
    WHERE BundleId = 12345           -- „Enchanted Starter Kit"

    UNION ALL

    -- Recursiv: componente ale componentelor
    SELECT b.BundleId, b.ComponentProductId, b.Quantity * p.Quantity, p.depth + 1
    FROM Catalog.Bom AS b
    JOIN bom         AS p ON p.ComponentProductId = b.BundleId
)
SELECT ComponentProductId, SUM(Quantity) AS total_qty
FROM bom
GROUP BY ComponentProductId;

Expandează un bundle până la frunze și înmulțește cantitățile pe drum. „Starter Kit conține 1 Spellbook, iar un Spellbook conține 3 Magical Bookmarks, deci Kit-ul conține efectiv 3 Bookmarks la nivelul frunzelor.”

Exemplul 4: generarea intervalelor de date

Fără tabelă. Generezi datele din zbor:

-- Fiecare zi din T1 2026
WITH days AS (
    SELECT CAST('2026-01-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM days WHERE d < '2026-03-31'
)
SELECT d, DATENAME(WEEKDAY, d) AS dow
FROM days
OPTION (MAXRECURSION 500);

Pattern-ul ăsta e aur curat pentru rapoarte cu umplerea găurilor. Raportul vrea „venit pe zi pentru ultimele 30 de zile, cu zero pe zilele fără comenzi”? LEFT JOIN între seria de date generată și datele agregate:

WITH days AS (
    SELECT CAST(DATEADD(DAY, -29, GETDATE()) AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM days WHERE d < CAST(GETDATE() AS DATE)
),
daily_rev AS (
    SELECT CAST(OrderDate AS DATE) AS d, SUM(Total) AS rev
    FROM Sales.Orders
    WHERE OrderDate >= DATEADD(DAY, -30, GETDATE())
    GROUP BY CAST(OrderDate AS DATE)
)
SELECT d.d, COALESCE(r.rev, 0) AS revenue_eur
FROM days AS d
LEFT JOIN daily_rev AS r ON r.d = d.d
ORDER BY d.d;

Treizeci de rânduri, unul pe zi, zero unde nu au fost comenzi. Dashboard-ul nu mai sare zile. Marketing-ul nu mai poate spune „dar am avut o campanie pe 14” și să primească răspunsul „nu există rând pentru 14 în raport”.

Capcana MAXRECURSION

SQL Server are implicit 100 de iterații. Pentru o organigramă adâncă, e de obicei suficient. Pentru generare de date sau parcurgeri de grafuri, nu este.

OPTION (MAXRECURSION 5000)   -- permite până la 5000 de iterații
OPTION (MAXRECURSION 0)      -- nelimitat (cu prudență)

0 înseamnă „fără limită” — periculos cu o recursie nemărginită din greșeală. Preferă numere explicite; alege unul confortabil peste maximul real și lasă-l să eșueze zgomotos dacă ceva nu e în regulă.

Hint-ul OPTION se pune chiar la finalul SELECT-ului exterior, nu în interiorul CTE-ului.

Parcurgere în lățime vs. în adâncime

CTE-ul recursiv din SQL Server e fundamental breadth-first: produce toate rândurile de nivel 1 înainte de orice rând de nivel 2. Dacă vrei depth-first (toți descendenții înaintea fraților), trebuie să simulezi prin sortare. Trucul clasic: construiești un path sortabil pe drum, apoi ORDER BY path:

WITH tree AS (
    SELECT CategoryId, ParentCategoryId, Name, 0 AS depth,
           CAST(RIGHT(REPLICATE('0', 10) + CAST(CategoryId AS VARCHAR(10)), 10) AS VARCHAR(4000)) AS sort_path
    FROM Catalog.Category
    WHERE ParentCategoryId IS NULL

    UNION ALL

    SELECT c.CategoryId, c.ParentCategoryId, c.Name, t.depth + 1,
           CAST(t.sort_path + '/' + RIGHT(REPLICATE('0', 10) + CAST(c.CategoryId AS VARCHAR(10)), 10) AS VARCHAR(4000))
    FROM Catalog.Category AS c
    JOIN tree             AS t ON t.CategoryId = c.ParentCategoryId
)
SELECT depth, REPLICATE('  ', depth) + Name AS indented
FROM tree
ORDER BY sort_path;

ID-uri umplute cu zero-uri în path-ul de sortare asigură că ordinea alfabetică se potrivește cu cea numerică. ORDER BY sort_path îți dă o parcurgere în adâncime.

Note de performanță

  • CTE-urile recursive sunt de obicei rapide când graful e mic. Organigrame de 200 de angajați? Banal. Arbori de categorii cu 500 de noduri? Instant.
  • CTE-urile recursive pot fi lente pe grafuri mari. O parcurgere recursivă a unui graf cu un milion de noduri va doare. Pentru probleme de graf cu adevărat mari, tabelele Graph din SQL Server (introduse în 2017) sau o bază de date dedicată grafurilor sunt mai potrivite.
  • Join-ul dintre membrul recursiv și CTE rulează o dată per iterație. Dacă tabela de bază nu are indice pe coloana de join, fiecare iterație face scan. Pentru „angajați făcuți join pe ManagerId” — asigură-te că există un indice pe ManagerId.
  • Pentru generare adâncă de date, o tabelă de numere sau o tabelă dedicată dimensiunii date e mai rapidă decât recursia. Dar pentru rapoarte ad-hoc, generatorul recursiv de date e perfect — fără modificări de schemă.

Rulează asta pe mașina ta

USE Runehold;
GO

-- 1. Generator de date + raport cu umplerea găurilor
WITH days AS (
    SELECT CAST('2026-03-01' AS DATE) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM days WHERE d < '2026-04-30'
),
daily AS (
    SELECT CAST(OrderDate AS DATE) AS d, SUM(Total) AS rev
    FROM Sales.Orders
    GROUP BY CAST(OrderDate AS DATE)
)
SELECT d.d,
       COALESCE(daily.rev, 0) AS revenue_eur,
       DATENAME(WEEKDAY, d.d) AS dow
FROM days AS d
LEFT JOIN daily ON daily.d = d.d
ORDER BY d.d
OPTION (MAXRECURSION 500);

-- 2. Construiește o mică organigramă pentru demo
IF OBJECT_ID('HR.Employee', 'U') IS NULL
BEGIN
    IF SCHEMA_ID('HR') IS NULL EXEC('CREATE SCHEMA HR AUTHORIZATION dbo');
    CREATE TABLE HR.Employee (
        EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
        FullName   NVARCHAR(200) NOT NULL,
        Team       NVARCHAR(50)  NOT NULL,
        ManagerId  INT NULL,
        FOREIGN KEY (ManagerId) REFERENCES HR.Employee(EmployeeId)
    );

    INSERT INTO HR.Employee (FullName, Team, ManagerId) VALUES
    ('Ilse Jansen',     'Executive',  NULL);       -- 1, CEO
    INSERT INTO HR.Employee (FullName, Team, ManagerId) VALUES
    ('Tomasz Kowalski', 'Executive',  1),            -- 2, VP Eng
    ('Djenna Akkad',    'Executive',  1),            -- 3, VP Mktg
    ('Franz Hofmann',   'Engineering',2),            -- 4
    ('Sofia Bianchi',   'Engineering',2),            -- 5
    ('Ada Lovelace',    'Engineering',4),            -- 6
    ('Grace Hopper',    'Engineering',4),            -- 7
    ('Piotr Nowak',     'Marketing',  3);            -- 8
END;

-- 3. Organigrama
WITH org AS (
    SELECT EmployeeId, FullName, ManagerId, Team, 0 AS lvl,
           CAST(FullName AS NVARCHAR(4000)) AS chain
    FROM HR.Employee WHERE ManagerId IS NULL

    UNION ALL

    SELECT e.EmployeeId, e.FullName, e.ManagerId, e.Team, o.lvl + 1,
           CAST(o.chain + N' > ' + e.FullName AS NVARCHAR(4000))
    FROM HR.Employee AS e
    JOIN org         AS o ON o.EmployeeId = e.ManagerId
)
SELECT lvl, REPLICATE(N'    ', lvl) + FullName AS indented, Team, chain
FROM org
ORDER BY chain;

-- 4. Toți cei sub VP Engineering (filtrare după chain)
WITH org AS (
    SELECT EmployeeId, FullName, ManagerId, 0 AS lvl
    FROM HR.Employee WHERE EmployeeId = 2   -- VP Eng

    UNION ALL

    SELECT e.EmployeeId, e.FullName, e.ManagerId, o.lvl + 1
    FROM HR.Employee AS e
    JOIN org         AS o ON o.EmployeeId = e.ManagerId
)
SELECT * FROM org WHERE lvl > 0;
-- Exclude VP-ul însuși; pornește de la subordonații lui direcți.

Modulul 2 e încheiat. Querying acoperit cap-coadă: SELECT, WHERE, NULL, ORDER BY, JOIN-uri, GROUP BY, subquery-uri, CTE-uri, ferestre, UNION, șiruri, date, recursie. Cu acest set de unelte răspunzi la 95% dintre întrebările de business care ajung pe biroul unui data engineer la orice e-commerce din UE.

Următorul modul: DML și tranzacții. Lecția 17 începe cu povestea de dragoste complicată dintre INSERT, UPDATE, DELETE și de ce MERGE e periculos.

Caută