التعبيرات الجدولية الشائعة (CTEs)

-- بناء جملة CTE الأساسي WITH cte_name AS ( SELECT column1, column2 FROM table_name WHERE condition ) SELECT * FROM cte_name;

-- بدون CTE (صعب القراءة) SELECT c.customer_name, o.total_spent FROM customers c JOIN ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders WHERE order_date >= '2024-01-01' GROUP BY customer_id ) o ON c.customer_id = o.customer_id WHERE o.total_spent > 1000; -- مع CTE (أوضح بكثير) WITH customer_spending AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders WHERE order_date >= '2024-01-01' GROUP BY customer_id ) SELECT c.customer_name, cs.total_spent FROM customers c JOIN customer_spending cs ON c.customer_id = cs.customer_id WHERE cs.total_spent > 1000;

-- حساب الإحصائيات باستخدام CTE WITH product_stats AS ( SELECT category, AVG(price) AS avg_price, COUNT(*) AS product_count, MAX(price) AS max_price FROM products GROUP BY category ) SELECT p.product_name, p.category, p.price, ps.avg_price, ROUND((p.price - ps.avg_price) / ps.avg_price * 100, 2) AS pct_diff_from_avg FROM products p JOIN product_stats ps ON p.category = ps.category WHERE p.price > ps.avg_price ORDER BY pct_diff_from_avg DESC;

-- CTEs متعددة للتحليل المعقد WITH high_value_customers AS ( SELECT customer_id, SUM(total_amount) AS total_spent FROM orders GROUP BY customer_id HAVING total_spent > 5000 ), recent_orders AS ( SELECT customer_id, COUNT(*) AS recent_order_count FROM orders WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) GROUP BY customer_id ), product_diversity AS ( SELECT o.customer_id, COUNT(DISTINCT oi.product_id) AS unique_products FROM orders o JOIN order_items oi ON o.order_id = oi.order_id GROUP BY o.customer_id ) SELECT c.customer_name, c.email, hvc.total_spent, COALESCE(ro.recent_order_count, 0) AS recent_orders, COALESCE(pd.unique_products, 0) AS products_purchased FROM customers c JOIN high_value_customers hvc ON c.customer_id = hvc.customer_id LEFT JOIN recent_orders ro ON c.customer_id = ro.customer_id LEFT JOIN product_diversity pd ON c.customer_id = pd.customer_id ORDER BY hvc.total_spent DESC;

-- CTEs تبني على بعضها البعض WITH monthly_sales AS ( SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total_amount) AS revenue FROM orders GROUP BY DATE_FORMAT(order_date, '%Y-%m') ), sales_with_growth AS ( SELECT month, revenue, LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue FROM monthly_sales ), growth_analysis AS ( SELECT month, revenue, prev_month_revenue, revenue - prev_month_revenue AS growth_amount, ROUND((revenue - prev_month_revenue) * 100.0 / prev_month_revenue, 2) AS growth_pct FROM sales_with_growth WHERE prev_month_revenue IS NOT NULL ) SELECT * FROM growth_analysis WHERE ABS(growth_pct) > 10 ORDER BY month;

-- بناء جملة CTE المتكررة الأساسية WITH RECURSIVE cte_name AS ( -- العضو الأساسي (الحالة الأساسية) SELECT columns FROM table WHERE base_condition UNION ALL -- العضو المتكرر SELECT columns FROM table JOIN cte_name ON join_condition WHERE recursive_condition ) SELECT * FROM cte_name;

-- تسلسل الموظفين الهرمي (المخطط التنظيمي) WITH RECURSIVE employee_hierarchy AS ( -- الأساس: ابدأ بالرئيس التنفيذي (بدون مدير) SELECT employee_id, employee_name, manager_id, position, 1 AS level, CAST(employee_name AS CHAR(500)) AS hierarchy_path FROM employees WHERE manager_id IS NULL UNION ALL -- متكرر: أضف الموظفين الذين يتبعون المستوى السابق SELECT e.employee_id, e.employee_name, e.manager_id, e.position, eh.level + 1, CONCAT(eh.hierarchy_path, ' > ', e.employee_name) FROM employees e JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id WHERE eh.level < 10 -- منع الحلقات اللانهائية ) SELECT REPEAT(' ', level - 1) AS indent, employee_name, position, level, hierarchy_path FROM employee_hierarchy ORDER BY hierarchy_path;

-- شجرة الفئات مع جميع الأسلاف WITH RECURSIVE category_tree AS ( -- الأساس: الفئات الورقية SELECT category_id, category_name, parent_category_id, 1 AS depth, category_name AS full_path FROM categories WHERE category_id = 42 -- ابدأ من فئة محددة UNION ALL -- متكرر: أضف الفئات الأصلية SELECT c.category_id, c.category_name, c.parent_category_id, ct.depth + 1, CONCAT(c.category_name, ' > ', ct.full_path) FROM categories c JOIN category_tree ct ON c.category_id = ct.parent_category_id ) SELECT * FROM category_tree ORDER BY depth DESC;

-- البحث عن جميع المرؤوسين لمدير WITH RECURSIVE subordinates AS ( -- الأساس: المدير SELECT employee_id, employee_name, manager_id, 0 AS levels_down FROM employees WHERE employee_id = 5 -- معرف المدير UNION ALL -- متكرر: تقاريرهم SELECT e.employee_id, e.employee_name, e.manager_id, s.levels_down + 1 FROM employees e JOIN subordinates s ON e.manager_id = s.employee_id WHERE s.levels_down < 5 ) SELECT levels_down, employee_name, (SELECT COUNT(*) - 1 FROM subordinates) AS total_subordinates FROM subordinates ORDER BY levels_down, employee_name;

-- توليد نطاق التاريخ WITH RECURSIVE date_range AS ( SELECT DATE('2024-01-01') AS date UNION ALL SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM date_range WHERE date < '2024-12-31' ) SELECT dr.date, COALESCE(SUM(o.total_amount), 0) AS daily_revenue FROM date_range dr LEFT JOIN orders o ON DATE(o.order_date) = dr.date GROUP BY dr.date ORDER BY dr.date;

-- توليد تسلسل الأرقام (1 إلى 100) WITH RECURSIVE numbers AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM numbers WHERE n < 100 ) SELECT n FROM numbers;

-- أداء CTE مقابل الاستعلام الفرعي -- CTE (يمكن الإشارة إليها عدة مرات) WITH top_products AS ( SELECT product_id, product_name, sales_count FROM products WHERE sales_count > 100 ) SELECT * FROM top_products WHERE product_name LIKE 'A%' UNION ALL SELECT * FROM top_products WHERE product_name LIKE 'B%'; -- استعلام فرعي (يُحسب مرتين - أقل كفاءة) SELECT * FROM (SELECT ...) WHERE product_name LIKE 'A%' UNION ALL SELECT * FROM (SELECT ...) WHERE product_name LIKE 'B%';

-- تحليل الاحتفاظ بمجموعة العملاء WITH RECURSIVE months AS ( SELECT DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS month FROM orders UNION ALL SELECT DATE_ADD(month, INTERVAL 1 MONTH) FROM months WHERE month < DATE_FORMAT(CURDATE(), '%Y-%m-01') ), first_purchases AS ( SELECT customer_id, DATE_FORMAT(MIN(order_date), '%Y-%m-01') AS cohort_month FROM orders GROUP BY customer_id ), customer_months AS ( SELECT fp.customer_id, fp.cohort_month, DATE_FORMAT(o.order_date, '%Y-%m-01') AS purchase_month, TIMESTAMPDIFF(MONTH, fp.cohort_month, DATE_FORMAT(o.order_date, '%Y-%m-01')) AS months_since_first FROM first_purchases fp JOIN orders o ON fp.customer_id = o.customer_id ) SELECT cohort_month, COUNT(DISTINCT customer_id) AS cohort_size, SUM(CASE WHEN months_since_first = 0 THEN 1 ELSE 0 END) AS month_0, SUM(CASE WHEN months_since_first = 1 THEN 1 ELSE 0 END) AS month_1, SUM(CASE WHEN months_since_first = 2 THEN 1 ELSE 0 END) AS month_2, SUM(CASE WHEN months_since_first = 3 THEN 1 ELSE 0 END) AS month_3, ROUND(SUM(CASE WHEN months_since_first = 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(DISTINCT customer_id), 2) AS retention_3month_pct FROM customer_months GROUP BY cohort_month ORDER BY cohort_month;