سلسلة sql للمحترفين التعابير الجدولية الشائعة Common Table Expressions في SQL

توليد قيم

لا توفّر معظم قواعد البيانات طريقة أصلية لإنشاء سلاسل الأرقام؛ بيْد أنّه يمكن استخدام تعبيرات الجدول الشائعة أو التعبيرات الجدولية (common table expressions) مع العوديّة (recursion) لمحاكاة هذا النوع من الوظائف.

يولّد المثال التالي تعبيرًا جدوليًا يُسمّى ‎Numbers‎، واسم عموده ‎i‎، ويحتوي أرقام الصفوف (1-5):

-- لتخزين الأعداد `i` واسم العمود `Numbers"إعطاء اسم الجدول
WITH Numbers(i) AS (
 -- البداية
 SELECT 1
 -- ضروري لأجل العودية UNION ALL المعامل
 UNION ALL
 -- تعبير التكرار
 SELECT i + 1
 -- التعبير الجدولي الذي أعلنا عنه والمُستخدم كمصدر للعودية
 FROM Numbers
 -- عبارة إنهاء العودية
 WHERE i < 5
)
-- استخدام التعبير الجدولي المُنشأ كما لو كان جدولا عاديا
SELECT i FROM Numbers;

الخرج الناتج:

يمكن استخدام هذه الطريقة مع أي مجال من الأعداد، وكذلك مع أنواع أخرى من البيانات.

الترقيم العودي لشجيرة recursively enumerating a subtree

المثال التالي يوضّح كيفية ترقيم شجيرة (subtree) عوديًا:

WITH RECURSIVE ManagedByJames(Level, ID, FName, LName) AS (
 -- البدء بهذا الصف
 SELECT 1, ID, FName, LName
 FROM Employees
 WHERE ID = 1
 UNION ALL
 -- الحصول على الموظفين الذين يعملون تحت إمرة أيّ من المدراء المُختارين سابقا
 SELECT ManagedByJames.Level + 1,
           Employees.ID,
           Employees.FName,
           Employees.LName
 FROM Employees
 JOIN ManagedByJames
 ON Employees.ManagerID = ManagedByJames.ID
 ORDER BY 1 DESC -- depth-first search البحث الأولي-العميق
)
SELECT * FROM ManagedByJames;

الخرج الناتج:

الاستعلامات المؤقتة Temporary query

تتصرف الاستعلامات المؤقتة (Temporary query) مثل الاستعلامات المتشعّبة (nested subqueries)، إلّا أنّ صياغتها مختلفة.

WITH ReadyCars AS (
   SELECT *
    FROM Cars
    WHERE Status = 'READY'
)
SELECT ID, Model, TotalCost
FROM ReadyCars
ORDER BY TotalCost;

الخرج الناتج:

هذا استعلام فرعي مكافئ:

SELECT ID, Model, TotalCost
FROM (
    SELECT *
    FROM Cars
    WHERE Status = 'READY'
) AS ReadyCars
ORDER BY TotalCost

التسلّق العوديّ لشجرة recursively going up in a tree

المثال التالي يوضّح كيفية تسلق شجرة عوديًا:

WITH RECURSIVE ManagersOfJonathon AS (
 -- البدء بهذا الصف
 SELECT *
 FROM Employees
 WHERE ID = 4
 UNION ALL
 -- الحصول على مدراء كل الصفوف المُختارة سابقا
 SELECT Employees.*
 FROM Employees
 JOIN ManagersOfJonathon
 ON Employees.ID = ManagersOfJonathon.ManagerID
)
SELECT * FROM ManagersOfJonathon;

الخرج الناتج:

التوليد العودي للتواريخ

المثال التالي يولّد تواريخ مع تضمين الجداول الزمنية لفِرَق العمل:

DECLARE @DateFrom DATETIME = '2016-06-01 06:00'
DECLARE @DateTo DATETIME = '2016-07-01 06:00'
DECLARE @IntervalDays INT = 7
-- Transition Sequence = وقت الاستراحة في المناوبات الليلية والنهارية
-- RR (Rest & Relax) = 1
-- DS (Day Shift) = 2
-- NS (Night Shift) = 3
;WITH roster AS
(
    SELECT @DateFrom AS RosterStart, 1 AS TeamA, 2 AS TeamB, 3 AS TeamC
    UNION ALL
    SELECT DATEADD(d, @IntervalDays, RosterStart),
       CASE TeamA WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamA,
       CASE TeamB WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamB,
       CASE TeamC WHEN 1 THEN 2 WHEN 2 THEN 3 WHEN 3 THEN 1 END AS TeamC
    FROM roster WHERE RosterStart < DATEADD(d, -@IntervalDays, @DateTo)
)
SELECT RosterStart,
       ISNULL(LEAD(RosterStart) OVER (ORDER BY RosterStart), RosterStart + @IntervalDays) AS RosterEnd,
       CASE TeamA WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamA,
       CASE TeamB WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamB,
       CASE TeamC WHEN 1 THEN 'RR' WHEN 2 THEN 'DS' WHEN 3 THEN 'NS' END AS TeamC
FROM roster

النتيجة المُعادة:

استخدام CONNECT BY في Oracle مع تعبير جدولي عودي

توفّر الوظيفة CONNECT BY المُستخدمة في Oracle العديد من الميزات المفيدة التي لا يوجد لها مثيل في التعبيرات الجدولية العودية القياسية في SQL.

يحاول هذا المثال محاكاة هذه الميزات (مع بعض الإضافات التكميلية) باستخدام صياغة SQL Server. هذه الوظائف مفيدة للغاية لمطوّري Oracle - إذ توفّر لهم العديد من الميزات في الاستعلامات المتشعبة (hierarchical queries) غير الموجودة في قواعد البيانات الأخرى، كما أنّها مفيدة أيضًا في توضيح استخدامات الاستعلامات المتشعبة عمومًا.

 WITH tbl AS (
     SELECT id, name, parent_id
         FROM mytable)
 ,     tbl_hierarchy AS (
     /* Anchor */
     SELECT 1 AS "LEVEL"
         --, 1 AS CONNECT_BY_ISROOT
         --, 0 AS CONNECT_BY_ISBRANCH
         , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
         , 0 AS CONNECT_BY_ISCYCLE
         , '/' + CAST(t.id AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
         , '/' + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
         , t.id AS root_id
         , t.*
     FROM tbl t
   WHERE t.parent_id IS NULL -- START WITH parent_id IS NULL
 UNION ALL
 /* العودية*/
 SELECT th."LEVEL" + 1 AS "LEVEL"
         --, 0 AS CONNECT_BY_ISROOT
         --, CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 1 ELSE 0 END AS CONNECT_BY_ISBRANCH
         , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
         , CASE WHEN th.SYS_CONNECT_BY_PATH_id LIKE '%/' + CAST(t.id AS VARCHAR(MAX)) + '/%'
        THEN 1 ELSE 0 END AS CONNECT_BY_ISCYCLE
         , th.SYS_CONNECT_BY_PATH_id + CAST(t.id AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
         , th.SYS_CONNECT_BY_PATH_name + CAST(t.name AS VARCHAR(MAX)) + '/' AS     SYS_CONNECT_BY_PATH_name
         , th.root_id
         , t.*
     FROM tbl t
         JOIN tbl_hierarchy th ON (th.id = t.parent_id) -- CONNECT BY PRIOR id = parent_id
     WHERE th.CONNECT_BY_ISCYCLE = 0) -- NOCYCLE
SELECT th.*
 --, REPLICATE(' ', (th."LEVEL" - 1) * 3) + th.name AS tbl_hierarchy
     FROM tbl_hierarchy th
     JOIN tbl CONNECT_BY_ROOT ON (CONNECT_BY_ROOT.id = th.root_id)
  ORDER BY th.SYS_CONNECT_BY_PATH_name; -- ORDER SIBLINGS BY name

هذا شرح لميزات CONNECT BY الموضّحة أعلاه:

• العبارات
  • CONNECT BY: تحدّد العلاقة التي تعرّف التشعّب
  • START WITH: تحدّد العقدة الجذرية (root nodes).
  • ORDER SIBLINGS BY: تحدّد ترتيب النتائج
• المعاملات
  • NOCYCLE: توقِف معالجة فرع معيّن عند رصد شعبة دورية (loop). لأنّ الشعب الصالحة هي الشعب غير الدورية (Directed Acyclic)، أي الشعب التي لا يمكن العودة عبرها إلى العقدة نفسها.
• العمليات
  • PRIOR: تحصل على البيانات من العقدة الأب (node's parent).
  • CONNECT_BY_ROOT: تحصل على البيانات من العقدة الجذرية.
• أشباه الأعمدة Pseudocolumns
  • LEVEL: تشير إلى مسافة العقدة من جذرها.
  • CONNECT_BY_ISLEAF: تشير إلى عقدة بدون فروعها.
  • CONNECT_BY_ISCYCLE: تشير إلى عقدة ذات مرجع دائري (circular reference).
• الدوال
  • SYS_CONNECT_BY_PATH: تعيد سلسلة نصية تمثّل المسار من الجذر إلى العقدة.

ترجمة -وبتصرّف- للفصل 46 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: مواضيع متفرقة في SQL
• المقال السابق: دوال التعامل مع النصوص في SQL
• النسخة العربية الكاملة من كتاب ملاحظات للعاملين بلغة SQL 1.0.0