الـ subqueries والـ CTE والـ CTE التكراري

يطرح كريم سؤالًا يخفي في داخله سؤالًا ثانيًا: «أي العملاء أنفقوا أكثر من العميل المتوسط في 2025؟». للإجابة تحتاج إلى المتوسط أولًا، وهذا بدوره يحتاج إلى الإيراد لكل عميل. إنها ثلاث خطوات: الإيراد لكل عميل، ثم متوسطه، ثم المقارنة. يعطيك SQL طريقتين لتداخل الخطوات، الـ subqueries والـ CTEs، والاختيار بينهما يحدّد هل يستطيع المحلّل التالي أن يقرأ عملك.

ثلاثة أنواع من الـ subqueries

استخدمت اثنين منها بالفعل. الجدول المشتق subquery داخل FROM يتصرّف كجدول (الإيراد لكل طلب الذي أصلح الـ fan-out). والـ subquery المترابط يشير إلى الصف الخارجي ويعمل لكل صف (الـ anti-join بـ NOT EXISTS). والثالث هو الـ scalar subquery: query يعيد قيمة واحدة بالضبط، ويمكن استخدامه في أي مكان تصلح فيه قيمة.

هذا سؤال كريم مكتوبًا بالتداخل وحده:

SELECT COUNT(*) AS above_average_customers
FROM (
  SELECT o.customer_id, SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
    AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
  GROUP BY o.customer_id
) AS r
WHERE r.revenue > (
  SELECT AVG(revenue) FROM (
    SELECT SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
      AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
    GROUP BY o.customer_id
  )
);

إنه يعمل: 140 عميلًا. لكنه صعب القراءة أيضًا، ومنطق الإيراد مكتوب فيه مرتين. حين يغيّر أحدهم تعريف الإيراد في الربع القادم، سيحدّث نسخة وينسى الأخرى.

التحليل نفسه بالـ CTEs

الـ CTE (common table expression) يسمّي query باستخدام WITH name AS (...)، وتشير إليه الخطوات اللاحقة كما تشير إلى جدول:

WITH revenue_2025 AS (
  -- grain: one row per customer who bought in 2025
  SELECT o.customer_id,
         SUM(oi.quantity * oi.unit_price * (1 - oi.discount)) AS revenue
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.status <> 'cancelled'
    AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
  GROUP BY o.customer_id
),
benchmark AS (
  SELECT AVG(revenue) AS avg_revenue FROM revenue_2025
)
SELECT
  COUNT(*) AS buyers,
  SUM(r.revenue > b.avg_revenue) AS above_average,
  ROUND(b.avg_revenue, 2) AS avg_revenue
FROM revenue_2025 AS r
CROSS JOIN benchmark AS b;

الآن يُقرأ التحليل كالجملة التي يجيب عنها: الإيراد لكل عميل، ثم المعيار، ثم المقارنة. الإيراد معرّف مرة واحدة. وأثناء البناء يمكنك استبدال الـ SELECT الأخير بـ SELECT * FROM revenue_2025 LIMIT 5 لتفحص أي خطوة، وهذه أفضل عادة منفردة لتصحيح الـ SQL الطويل. والـ CROSS JOIN آمن هنا لأن في benchmark صفًا واحدًا بالضبط. في SQLite تكون المقارنة 1 أو 0، فيعدّ SUM(condition) الصفوف التي قيمتها TRUE؛ وفي PostgreSQL ستكتب COUNT(*) FILTER (WHERE ...).

إذن 140 من أصل 418 مشتريًا يقعون فوق المتوسط البالغ 781.84. هذا معتاد في بيانات الإيرادات: أقلية من العملاء الكبار ترفع المتوسط، ولهذا ستعرض الوسيط بجانبه في كثير من الأحيان.

الـ CTE التكراري: تتبّع تسلسل هرمي

يعود طلب رانيا الخاص بإعادة الهيكلة: «اعرض كل من في فريق الهندسة الذي يقوده بيتر ويبر، على أي مستوى، مع بُعد كل شخص عنه». الـ self-join يعطيك مستوى واحدًا. فريق بيتر فيه مستويان، وقد يكون في قسم آخر خمسة. الـ CTE التكراري يتعمّق بقدر ما تتعمّق البيانات:

WITH RECURSIVE org AS (
  -- anchor: where the walk starts
  SELECT employee_id, first_name, title, 0 AS depth, first_name AS path
  FROM employees
  WHERE employee_id = 4
  UNION ALL
  -- recursive step: people who report to anyone found so far
  SELECT e.employee_id, e.first_name, e.title, org.depth + 1,
         org.path || ' > ' || e.first_name
  FROM employees AS e
  JOIN org ON e.manager_id = org.employee_id
)
SELECT depth, path, title
FROM org
ORDER BY path;

البنية هي نفسها دائمًا. query الارتكاز يعمل مرة واحدة وينتج صفوف البداية. والـ query التكراري يربط employees بـ org نفسه، لكنه في كل تكرار لا يرى إلا الصفوف التي أضافها التكرار السابق. كل جولة تضيف المستوى التالي نزولًا، وينتهي التتبّع حين لا يجد تكرارٌ أحدًا.

يُمرَّر depth وpath عبر التكرار: كل صف جديد يحسب قيمه من الصف الأب الذي ارتبط به. والترتيب حسب path يطبع النتيجة كشجرة متدرّجة. يصلح النمط نفسه لتجميع التكاليف صعودًا في شجرة فئات، أو لتتبّع سلاسل الإحالة، أو لتفكيك قائمة مكوّنات منتج.

بالـ CTEs تستطيع أن تسمّي كل خطوة. يضيف الدرس التالي أداة تتيح لكل صف أن يرى جيرانه دون ضغط أي شيء: دوال النافذة.