CTE و CTE بازگشتی: دسته‌بندی درختی و سری تاریخ

کوئری‌های خوانا با WITH

Common Table Expression (از MySQL 8.0) به یک زیرکوئری نام می‌دهد تا کوئری‌های پیچیده را مثل یک متن چندمرحله‌ای بنویسید. یک CTE می‌تواند چند بار در همان کوئری استفاده شود و به CTEهای قبلی ارجاع دهد.

WITH monthly AS (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS ym, SUM(total_amount) AS revenue
  FROM orders WHERE status IN ('paid','shipped')
  GROUP BY ym
),
stats AS (
  SELECT AVG(revenue) AS avg_rev FROM monthly
)
SELECT m.ym, m.revenue,
       ROUND(100 * m.revenue / s.avg_rev) AS pct_of_avg
FROM monthly m CROSS JOIN stats s
ORDER BY m.ym;

CTE بازگشتی: دسته‌بندی درختی

دسته‌بندی فروشگاه معمولاً درختی است: «فرش ← دستباف ← کاشان ← لچک‌ترنج». ساده‌ترین مدل، جدولی با ستون parent_id است (Adjacency List). CTE بازگشتی این درخت را در یک کوئری پیمایش می‌کند:

CREATE TABLE categories (
  id INT UNSIGNED PRIMARY KEY,
  parent_id INT UNSIGNED NULL,
  name VARCHAR(60) NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES categories (id)
);
INSERT INTO categories VALUES
 (1, NULL, 'فرش'), (2, 1, 'دستباف'), (3, 1, 'ماشینی'),
 (4, 2, 'کاشان'), (5, 2, 'تبریز'), (6, 4, 'لچک‌ترنج'), (7, 3, '۱۲۰۰ شانه');

WITH RECURSIVE tree AS (
  SELECT id, name, parent_id, 0 AS depth,
         CAST(name AS CHAR(500)) AS path            -- CAST ضروری است!
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, t.depth + 1,
         CONCAT(t.path, ' / ', c.name)
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT id, CONCAT(REPEAT('    ', depth), name) AS indented, path
FROM tree ORDER BY path;

بخش اول (anchor) ریشه‌ها را می‌دهد و بخش دوم در هر دور، فرزندان ردیف‌های دور قبل را اضافه می‌کند تا دیگر ردیف تازه‌ای پیدا نشود. برعکسش هم ممکن است: از یک دسته‌ی برگ شروع کنید و با t.parent_id = c.id به بالا بروید تا مسیر breadcrumb صفحه‌ی محصول ساخته شود.

سری تاریخ برای گزارش بدون روز خالی

WITH RECURSIVE days AS (
  SELECT DATE('2025-03-21') AS d
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < '2025-04-20'
)
SELECT days.d, COUNT(o.id) AS orders_count
FROM days
LEFT JOIN orders o ON o.created_at >= days.d AND o.created_at < days.d + INTERVAL 1 DAY
GROUP BY days.d ORDER BY days.d;

بدون سری تاریخ، روزهایی که فروش نداشته‌اند اصلاً در نمودار ظاهر نمی‌شوند و محور زمان دروغ می‌گوید.

نکته‌هایی که کمتر کسی می‌داند

• نوع ستون‌های CTE بازگشتی فقط از بخش anchor تعیین می‌شود؛ بدون CAST(name AS CHAR(500)) مسیر به طول نام ریشه بریده می‌شود و در حالت strict خطای Data too long می‌گیرید.
• حداکثر عمق بازگشت با cte_max_recursion_depth (پیش‌فرض 1000) محدود است؛ داده‌ی چرخه‌دار (دسته‌ای که والد خودش باشد) با خطا متوقف می‌شود، نه حلقه‌ی بی‌پایان. برای سری طولانی‌تر مقدار آن را در نشست بالا ببرید.
• برای تشخیص چرخه در داده، مسیر idها را نگه دارید و شرط FIND_IN_SET(c.id, t.id_path) = 0 را به بخش بازگشتی اضافه کنید.
• CTE غیربازگشتی که چند بار ارجاع شود، معمولاً یک بار ساخته (materialize) می‌شود؛ در حالی که همان زیرکوئری تکرارشده در derived table ممکن است دو بار اجرا شود.
• MariaDB از 10.2 CTE و CTE بازگشتی دارد؛ اما LATERAL را ندارد، که در کدهای مشترک باید در نظر گرفت.