Un CTE (Common Table Expression), definit cu WITH, este un set de rezultate temporar numit care face ca interogările complexe să fie ușor de citit. PostgreSQL suportă complet CTE-urile, inclusiv CTE-urile recursive (WITH RECURSIVE) pentru traversarea datelor ierarhice/de tip arbore.
WITH active_users AS (
SELECT id, name FROM users WHERE active = true
),
order_counts AS (
SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
)
SELECT u.name, oc.cnt
FROM active_users u
JOIN order_counts oc ON u.id = oc.user_id; -- compose named steps cleanly
CTE-urile descompun o interogare complexă în pași numiți și ușor de citit (înlănțuiți, fiecare referind la cei anteriori) — mult mai clar decât subinterogări imbricate.
-- find all subordinates under employee 1 (an org-chart hierarchy)
WITH RECURSIVE subordinates AS (
-- BASE CASE: the starting point
SELECT id, name, manager_id FROM employees WHERE id = 1
UNION ALL
-- RECURSIVE STEP: find employees whose manager is in the result so far
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates; -- the entire tree under employee 1
Un CTE recursiv are un caz de bază (rândurile de start) și un pas recursiv (UNION ALL care se conectează înapoi la CTE-ul însuși), iterând până când nu mai apar rânduri noi — perfect pentru arbori și ierarhii (organigrame, arbori de categorii, structuri de materiale, căi în grafuri).
-- Postgres 12+ may INLINE CTEs (optimize them) by default; force behavior if needed:
WITH x AS MATERIALIZED (...) -- force materialization (compute once, store)
WITH x AS NOT MATERIALIZED (...) -- force inlining (optimize into the main query)
Înainte de Postgres 12, CTE-urile erau întotdeauna un "gard de optimizare" (materializate); acum pot fi inline pentru performanță mai bună — MATERIALIZED/NOT MATERIALIZED controlează acest lucru.
CTE-urile și interogările recursive sunt caracteristici valoroase ale PostgreSQL pentru lizibilitate și date ierarhice, deci înțelegerea lor este utilă pentru scrierea interogărilor complexe ușor de întreținut și pentru gestionarea datelor structurate ca arbore.
Beneficiul zilnic este lizibilitatea: interogările complexe cu mai multe niveluri de subinterogări devin dificil de întreținut, în timp ce CTE-urile vă permit să le descompuneți în pași logici numiți și secvențiali (ușor de citit de sus în jos), îmbunătățind dramatic claritatea — făcând CTE-urile un instrument important pentru scrierea SQL-ului ușor de întreținut.
Capacitatea de remarcat este CTE-urile recursive (WITH RECURSIVE), care permit interogarea datelor ierarhice și structurate ca arbore (organigrame, arbori de categorii, structuri de materiale, comentarii înlănțuite, traversare de grafuri) — o clasă de interogări altfel foarte dificile în SQL, făcând CTE-urile recursive soluția standard pentru traversarea ierarhică.
Înțelegerea structurii CTE recursive (caz de bază + pas recursiv care se conectează înapoi la sine) este cunoaștere cheie pentru această necesitate frecventă.
Un detaliu util specific Postgres este comportamentul materializării CTE (Postgres 12+ poate inline CTE-urile pentru performanță mai bună, controlabil cu MATERIALIZED/NOT MATERIALIZED) — relevant pentru optimizare.
Deoarece interogările complexe sunt frecvente (și lizibilitatea contează pentru mentenanță), și deoarece datele ierarhice (pe care CTE-urile recursive le gestionează exceptând bine) apar frecvent (structuri organizaționale, categorii imbricate), înțelegerea CTE-urilor și a interogărilor recursive este cunoaștere PostgreSQL valoroasă și practic relevantă care îmbunătățește mentenabilitatea interogărilor și deblochează interogări ierarhice — o capacitate frecvent relevantă pentru SQL-ul curat și sofisticat și un subiect frecvent, în special forma recursivă pentru nevoia recurentă de a interoga date structurate ca arbore.
O bibliotecă de întrebări de interviu IT cu răspunsuri detaliate — de la Junior la Senior.
Donează