CTE في SQL: دليل شامل مع أمثلة

كيفية إنشاء CTE في SQL

عند إنشاء CTE، نستخدم كلمة رئيسية تُدعى WITH لبدء تعريف CTE. يكون بناء CTE العام كالتالي:

WITH cte_name (column1, column2, ...)
AS (
    -- الاستعلام الذي يعرف CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- الاستعلام الرئيسي
SELECT ...
FROM cte_name;

حيث:

• WITH: تبدأ تعريف CTE، مشيرة إلى أن الاسم التالي يمثل مجموعة نتائج مؤقتة.

• cte_name: يتم تعيين الاسم لـ CTE للإشارة إليه في الاستعلام الرئيسي.

• قائمة اختيارية للأعمدة (column1, column2, …): تحدد أسماء الأعمدة لمجموعة نتائج CTE. هذا مفيد عندما تحتاج إلى ضبط أسماء الأعمدة.

• الاستعلام الذي يحدد CTE: الاستعلام الداخلي الذي يختار البيانات ويشكل مجموعة النتائج المؤقتة.

• الاستعلام الرئيسي: يشير إلى CTE بواسطة اسمه، باستخدامه كجدول.

دعونا نلقي نظرة على المثال التالي لإنشاء جدول مشترك معبأ باستخدام نهج متدرج. لنفترض أن لدينا جدول Employees، ونريد إنشاء جدول مشترك يختار الموظفين الذين يحصلون على راتب يزيد عن 50,000 دولار.

الخطوة 1: كتابة الاستعلام الأساسي

نبدأ بكتابة الاستعلام الأساسي SELECT:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

الخطوة 2: لف الاستعلام باستخدام الكلمة الرئيسية WITH لإنشاء جدول مشترك

استخدم الكلمة الرئيسية WITH لإعطاء الجدول المشترك اسمًا.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

الخطوة 3: استخدام الجدول المشترك في الاستعلام الرئيسي

أخيرًا، أشير إلى الجدول المشترك في جملة SELECT عن طريق استدعاء اسم الجدول المشترك المحدد أعلاه.

-- تعريف جدول مشترك
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- استخدام الجدول المشترك لتحديد الموظفين الذين يحصلون على أجور عالية
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

لتلخيص الخطوات أعلاه، لقد استخدمنا كلمة الرئيسية WITH لتعريف الجدول المشترك بالاسم HighEarningEmployees. تم استخدام الاستعلام الداخلي لإنشاء مجموعة البيانات المؤقتة. الاستعلام الرئيسي يشير إلى HighEarningEmployees لعرض الأعمدة المحددة EmployeeID، FirstName، و LastName.

لماذا تكون جداول البيانات المشتركة في SQL مفيدة

من المثال أعلاه، قد تتساءل لماذا نستخدم CTEs عندما تعيد الاستعلامات البسيطة نفس النتائج. وفيما يلي الأسباب:

تبسيط الاستعلامات المعقدة

تقسم CTEs البيانات الكبيرة في الاستعلامات الخاصة بقواعد البيانات إلى أجزاء أصغر وأكثر إدارة، مما يجعل الكود أسهل في القراءة والكتابة والصيانة.

لنفترض أن لدينا ثلاث جداول: الطلبيات، العملاء، و المنتجات. نريد أن نجد إجمالي الإيرادات التي تم توليدها من قبل كل عميل اشترى في عام 2024. عندما نكتب الاستعلام دون استخدام CTE، يبدو مليئًا بالفوضى وصعب القراءة والفهم.

-- حدد أسماء العملاء وإجمالي الإيرادات من طلباتهم
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- انضم للحصول على الجدول الخاص بالعملاء والمنتجات
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

من خلال استخدام CTE، يمكننا تقسيم المنطق إلى تنسيق أكثر قراءة:

-- حدد CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
-- الاستعلام الرئيسي
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

إعادة استخدام الكود

تساعد CTEs في تجنب تكرار البيانات عن طريق السماح بإعادة استخدام نفس مجموعة النتائج عبر أجزاء مختلفة من الاستعلام. إذا كانت هناك عمليات أو حسابات متعددة تعتمد على نفس المجموعة من البيانات، يمكنك تعريفها مرة واحدة في CTE والرجوع إليها حسب الحاجة.

لنفترض أنه علينا حساب المتوسط وإجمالي المبيعات لكل فئة منتج في قاعدة بيانات التجارة الإلكترونية. يمكننا استخدام CTE لتعريف الحسابات مرة واحدة وإعادة استخدامها في الاستعلامات التالية.

-- تعريف CTE لحساب المبيعات الإجمالية والمتوسطة لكل فئة
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- اختيار الفئة وإجمالي المبيعات والمتوسط من CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

تطبيقات أخرى

بالإضافة إلى تبسيط الاستعلامات وإعادة استخدام الكود، للـ CTE استخدامات أخرى أيضًا. لا أستطيع تغطية كل استخدام محتمل للـ CTE بالتفصيل. دورتنا تلاعب البيانات في SQL هي خيار رائع إذا كنت ترغب في مواصلة التمارين. ومع ذلك، سأوثق بعض الأسباب الرئيسية الأخرى هنا:

• تنظيم الاستعلام وقراءة الكود: تحسن CTEs قراءة الكود SQL عن طريق تقسيم الاستعلامات إلى خطوات منطقية ومتسلسلة. يمكن تمثيل كل خطوة في عملية الاستعلام بـ CTE الخاص بها، مما يجعل الاستعلام بأكمله أسهل في المتابعة.
• تجوال البيانات التسلسلي: يمكن لـ CTEs المساعدة في تصفح العلاقات التسلسلية، مثل الهياكل التنظيمية، وعلاقات الأب والابن، أو أي نموذج بيانات يتضمن مستويات متداخلة. تكون CTEs العاودة مفيدة للاستعلام عن البيانات التسلسلية لأنها تسمح لك بالتجوال عبر المستويات تكرارياً.
• التجميعات متعددة المستويات: يمكن لـ CTEs المساعدة في أداء عمليات التجميع على مستويات متعددة، مثل حساب أرقام المبيعات على مستويات حبوبية مختلفة (على سبيل المثال، حسب الشهر، الربع، والسنة). باستخدام CTEs لفصل خطوات التجميع هذه، يتأكد أن يتم حساب كل مستوى بشكل مستقل ومنطقي.
• دمج البيانات من جداول متعددة: يمكن استخدام CTEs متعددة لدمج البيانات من جداول مختلفة، مما يجعل خطوة الدمج النهائية أكثر تنظيمًا. يبسط هذا النهج الانضمامات المعقدة ويضمن تنظيم البيانات المصدرية بشكل منطقي لتحسين قراءتها.

تقنيات CTE SQL المتقدمة

تدعم CTEs تقنيات SQL المتقدمة، مما يجعلها متعددة الاستخدامات ومفيدة لحالات الاستخدام المختلفة. وفيما يلي بعض التطبيقات المتقدمة لـ CTEs.

CTEs متعددة في استعلام واحد

يمكنك تحديد عدة CTE في استعلام واحد، مما يسمح بتحويلات وحسابات معقدة. يعد هذا الأسلوب مفيدًا عندما يتطلب المشكلة مراحل متعددة من معالجة البيانات، حيث يمثل كل CTE مرحلة متميزة.

لنفترض أن لدينا بيانات مبيعات في جدول يُسمى المبيعات ونريد حساب إجمالي المبيعات لكل منتج، وتحديد المنتجات ذات المبيعات الإجمالية التي تفوق المتوسط، وتصنيف هذه المنتجات بناءً على إجمالي مبيعاتها.

WITH ProductSales AS (
    -- الخطوة 1: حساب إجمالي المبيعات لكل منتج
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- الخطوة 2: حساب المتوسط الإجمالي للمبيعات عبر جميع المنتجات
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- الخطوة 3: تصفية المنتجات ذات المبيعات الإجمالية التي تفوق المتوسط
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- الخطوة 4: تصنيف المنتجات ذات المبيعات العالية
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

في المثال أعلاه؛

• يقوم CTE الأول (إجمالي_مبيعات_المنتج) بحساب إجمالي المبيعات لكل منتج.

• الCTE الثاني (AverageSales) يحسب المتوسط الإجمالي للمبيعات عبر جميع المنتجات.

• الCTE الثالث (HighSalesProducts) يقوم بتصفية المنتجات التي تتجاوز إجمالي مبيعاتها المتوسط.

• الاستعلام النهائي يرتب هذه المنتجات استنادًا إلى إجمالي مبيعاتها.

CTEs في عبارات UPDATE، DELETE، وMERGE

عند دمجها في عمليات UPDATE، DELETE، وMERGE، يمكن لـ CTEs تبسيط مهام تلاعب البيانات، خصوصًا عند التعامل مع عوامل تصفية معقدة أو بيانات تسلسلية.

استخدام CTE مع عبارة UPDATE

لنفترض أن لدينا جدول Employees بعمود EmployeeSalary. نريد زيادة رواتب جميع الموظفين الذين عملوا للشركة لأكثر من 5 سنوات بنسبة 10٪.

-- تعريف CTE للعثور على الموظفين الذين تم توظيفهم منذ أكثر من 5 سنوات
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- تحديث الرواتب بنسبة 10٪ للموظفين طويلي الأجل المحددين في CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

CTE LongTermEmployees يحدد الموظفين الذين عملوا لأكثر من خمس سنوات. تستخدم عبارة UPDATE هذه CTE لزيادة الرواتب بشكل انتقائي.

استخدام CTE مع عبارة DELETE

الآن لنفترض أن لدينا جدول يسمى Products ونريد حذف جميع المنتجات التي لم يتم بيعها خلال السنتين الأخيرتين. يمكننا استخدام CTE لفرز المنتجات:

-- تعريف CTE لتحديد المنتجات التي لم يتم بيعها خلال السنتين الأخيرتين
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- استخدام DATEADD للعثور على المنتجات التي تم بيعها لأول مرة قبل أكثر من سنتين
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- حذف المنتجات المحددة كقديمة من الجدول الرئيسي
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

يحدد CTE OldProducts المنتجات التي لم يتم بيعها خلال السنتين الأخيرتين، ثم تستخدم عبارة DELETE هذه CTE لإزالة تلك المنتجات.

استخدام CTE مع عبارة MERGE

تسمح عبارة MERGE في SQL بالتحديثات الشرطية، وإدراجات، أو حذفات في جدول هدف بناءً على البيانات في جدول مصدر. في المثال التالي، يجمع CTE MergedInventory بيانات المخزون الجديدة والحالية. تقوم بعبارة MERGE بعد ذلك بتحديث الكميات للمنتجات الحالية أو إدراج منتجات جديدة بناءً على بيانات CTE.

- CTE لدمج بيانات المخزون الجديدة والحالية
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    - استخدم LEFT JOIN لتضمين جميع البيانات الجديدة، حتى لو لم تكن موجودة في المخزون الحالي
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
- دمج البيانات المعدة في جدول المخزون
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
- تحديث المنتجات الحالية بكميات جديدة
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
- إدراج منتجات جديدة إذا لم تكن موجودة في المخزون
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Common Table Expressions (CTEs) التكرارية

تساعد CTEs التكرارية في أداء العمليات المتقدمة والمتكررة.

مقدمة لـ CTEs التكرارية

CTEs التكرارية هي نوع خاص من CTEs تشير إلى نفسها ضمن تعريفها، مما يسمح للاستعلام بأداء عمليات متكررة. يجعل ذلك منها مثالية للعمل مع البيانات الهرمية أو ذات التركيب الشجري، مثل الرسوم البيانية التنظيمية، هياكل الدليل، أو تجميعات المنتجات. تقوم CTEs التكرارية بمعالجة البيانات تدريجياً، وتعيد النتائج خطوة بخطوة حتى يتم تحقيق شرط الإنهاء.

أعضاء الربط والتكرار

CTE التكرارية تتكون من جزئين رئيسيين:

• عضو الربط: الجزء الذي يحدد الاستعلام الأساسي الذي يبدأ الانعكاس.
• العضو العائد: الجزء الذي يشير إلى CTE نفسه، مما يسمح له بأداء العمليات “العائدة”.

لنفترض أن لدينا جدول Employees، حيث تحتوي كل صف على EmployeeID، EmployeeName، وManagerID. إذا كنا نريد العثور على جميع التقارير المباشرة وغير المباشرة لمدير معين، نبدأ بعضو الربط الذي يحدد المدير على مستوى الأعلى. يبدأ عضو الربط بالموظف الذي يحمل EmployeeID = 1.

يعثر العضو العائد على الموظفين الذين يتطابق ManagerID الخاص بهم مع EmployeeID من التكرار السابق. كل تكرار يسترد المستوى التالي من التسلسل الهرمي.

WITH EmployeeHierarchy AS (
    -- عضو الربط: حدد المدير على مستوى الأعلى
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- بدءاً من المدير على مستوى الأعلى
    UNION ALL
    -- العضو العائد: العثور على الموظفين الذين يعملون تحت إشراف المدير الحالي
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

القضايا المحتملة أو القيود على CTE في SQL

فهم ميزات وقيود CTE مهم لكتابة استعلامات منطقية وقابلة للقراءة. دعونا نلقي نظرة على بعض القيود والقضايا المحتملة لاستخدام CTE في قواعد بيانات مختلفة.

القيود على SQL Server وAzure

هناك بعض القيود الخاصة بالبيئة لـ CTE في SQL عند العمل مع SQL Server أو Azure Synapse Analytics. تشمل ما يلي:

• SQL Server: مستوى الانتقال الافتراضي الأقصى لـ CTE العاودية هو 100، ويمكن تعديله باستخدام تلميحة OPTION (MAXRECURSION). إذا تجاوز هذا الحد دون تعديل، يحدث خطأ. لا يمكن تضمين CTE داخل بعضها مباشرة أو تعريفها داخل CTE آخر.

• تحليلات Azure Synapse: CTEs لديها دعم محدود لبعض العمليات الخاصة بـ SQL مثل INSERT، UPDATE، DELETE، و MERGE. أيضًا، CTEs التكرارية غير مدعومة في بيئات تحليلات Azure Synapse القائمة على السحابة مما يقيد القدرة على تنفيذ بعض عمليات البيانات الهرمية.

إذا وجدت نفسك تعمل مع خادم SQL، فاعلم أن DataCamp لديه الكثير من الموارد الرائعة للمساعدة. للبدء، أوصي بأخذ دورة مقدمة إلى خادم SQL من DataCamp لإتقان أساسيات خادم SQL لتحليل البيانات. يمكنك تجربة مسارنا المهني مطور خادم SQL، الذي يغطي كل شيء من المعاملات ومعالجة الأخطاء إلى تحليل السلاسل الزمنية. دورتنا الاستعلامات الهرمية والعاكسة في خادم SQL تتناول مباشرة كيفية كتابة استعلامات متقدمة في خادم SQL، بما في ذلك الأساليب التي تتضمن CTEs.

قضايا أخرى محتملة

على الرغم من أن CTEs مفيدة لتبسيط الاستعلامات المعقدة، إلا أن هناك بعض المخاطر الشائعة يجب أن تكون على علم بها. تشمل الأخطاء الشائعة ما يلي:

• حلقات لانهائية في CTEs العاكسة: إذا لم تتم مقابلة شرط الإنهاء لـ CTE العاكسة، فقد يؤدي ذلك إلى حلقة لانهائية، مما يتسبب في تشغيل الاستعلام بشكل لا نهائي. لتجنب تشغيل CTE العاكسة بشكل لا نهائي، استخدم تلميح OPTION (MAXRECURSION N) لتحديد الحد الأقصى لعدد التكرارات العاكسة، حيث N هو الحد المحدد.

• الاعتبارات الأدائية: قد تصبح CTEs العاودية مكلفة من حيث الموارد إذا كانت عمق العودية عالية أو تم معالجة مجموعات بيانات كبيرة. لتحسين الأداء، حدد البيانات المعالجة في كل تكرار وتأكد من تصفية مناسبة لتجنب مستويات العودية الزائدة.

متى يجب استخدام CTEs مقابل تقنيات أخرى

على الرغم من أن CTEs مناسبة لتبسيط الاستعلامات التي تنطوي على مهام متكررة، إلا أن الجداول المشتقة، والعرض، والجداول المؤقتة تخدم أغراضًا مماثلة أيضًا. يسلط الجدول التالي الضوء على مزايا وعيوب كل طريقة ومتى يجب استخدام كل واحدة.

الختام

تتطلب اتقان CTEs الممارسة، مثل أي شيء آخر: أوصي بتجربة مسار العمل الخاص بـ DataCamp Associate Data Analyst in SQL لتصبح محلل بيانات ماهرًا. سيساعدك دورة Reporting in SQL أيضًا على أن تصبح ماهرًا في بناء تقارير معقدة ولوحات معلومات لعرض البيانات بفعالية. وأخيرًا، يجب عليك الحصول على SQL Associate Certification لعرض احترافك في استخدام SQL لحل مشاكل الأعمال وتبرز بين المحترفين الآخرين.