Common Table Expressions في SQLite: شرح WITH

ما هو CTE في sqlite؟ ببساطة استعلام فرعي بإسم

الـ CTE (اختصار لـ Common Table Expression) هو ببساطة استعلام فرعي (subquery) أخرجته من داخل الاستعلام وأعطيته اسماً. فبدلاً من أن تُعشّش SELECT داخل SELECT آخر، تُعرّفه في الأعلى باستخدام جملة WITH، تختار له اسماً، ثم تستخدم هذا الاسم داخل الاستعلام الرئيسي وكأنه جدول عادي.

الصيغة العامة ثابتة دائماً:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

اقرأ الاستعلام من الأعلى للأسفل: أولاً نُنشئ نتيجة باسم customer_totals، ثم نستعلم منها. الـ CTE يتصرّف وكأنه view مؤقت لا يعيش إلا أثناء تنفيذ هذه الجملة فقط.

نفس الاستعلام بدون استخدام CTE

وهذا هو نفس المنطق مكتوبًا على هيئة استعلام فرعي (subquery)، حتى تشاهد ما الذي يحلّ محلّه الـ CTE:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

SELECT customer, total
FROM (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
WHERE total > 100;

نفس النتيجة. لكن انتبه لطريقة القراءة: عينك مضطرة تغوص جوّا الأقواس، تفهم إيش بيتحسب، ثم ترجع للخارج. أما نسخة الـ CTE فتُقرأ بنفس ترتيب تنفيذ العمل — تُعرِّف النتيجة الوسيطة أولًا، ثم تستخدمها. في استعلام صغير الفرق لا يُذكر. لكن في استعلام مكوّن من ثلاث أو أربع خطوات، هذا هو الفرق بين كود تستطيع تصفّحه بسرعة وكود تضطر لفكّ شيفرته.

أكثر من CTE في استعلام واحد

تقدر تربط عدة CTEs مع بعض، مفصولة بفواصل. كل واحد منها يقدر يشير إلى التي قبله، فتبني بهذا الشكل خط معالجة (pipeline) من خطوات مُسمّاة:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH
    customer_totals AS (
        SELECT customer, SUM(amount) AS total
        FROM orders
        GROUP BY customer
    ),
    big_spenders AS (
        SELECT customer, total
        FROM customer_totals
        WHERE total > 100
    )
SELECT customer, total
FROM big_spenders
ORDER BY total DESC;

WITH واحدة فقط، ثم تعريفات الـ CTE مفصولة بفواصل. الـ CTE الثاني (big_spenders) يقرأ من الأول (customer_totals) تمامًا كأنه يقرأ من جدول عادي. أما جملة SELECT الرئيسية فتأتي بعد آخر تعريف لـ CTE.

من الأخطاء الشائعة: تكرار كلمة WITH قبل الـ CTE الثاني. لا تفعل ذلك — سيعطيك خطأ في الصياغة. كلمة WITH واحدة تكفي لتغطيتها جميعًا.

استخدام نفس الـ CTE أكثر من مرة

هنا تحديدًا يتفوّق الـ CTE على الاستعلامات الفرعية (subqueries). إذا احتجت إلى نفس النتيجة الوسيطة في موضعين مختلفين، فإن الـ CTE يتيح لك حسابها مرة واحدة والإشارة إليها مرتين:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
SELECT
    customer,
    total,
    total - (SELECT AVG(total) FROM customer_totals) AS diff_from_avg
FROM customer_totals
ORDER BY diff_from_avg DESC;

لاحظ أن الـ CTE هنا مستخدم مرتين: مرة لحساب المتوسط، ومرة كمصدر رئيسي للبيانات. لو استغنينا عن الـ CTE، لاضطررنا لتكرار استعلام GROUP BY مرتين، وأي تعديل عليه سيلزمنا تغييره في موضعين.

استخدام CTE مع INSERT و UPDATE و DELETE

الـ CTE في sqlite لا تقتصر على SELECT فقط، بل يمكنك وضع جملة WITH قبل INSERT أو UPDATE أو DELETE للاستفادة من استعلام فرعي مُسمّى داخل عمليات الكتابة:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
CREATE TABLE flagged_customers (customer TEXT, total REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30);

WITH customer_totals AS (
    SELECT customer, SUM(amount) AS total
    FROM orders
    GROUP BY customer
)
INSERT INTO flagged_customers (customer, total)
SELECT customer, total
FROM customer_totals
WHERE total > 100;

SELECT * FROM flagged_customers;

الـ CTE يحدّد الصفوف التي نريد وسمها، وINSERT ... SELECT يستخدمه كمصدر للبيانات. ونفس الفكرة تعمل مع DELETE FROM ... WHERE id IN (SELECT id FROM cte) لتنفيذ عمليات حذف على مراحل عندما يكون منطق تحديد الصفوف معقّدًا.

متى تستخدم CTE في sqlite؟

إليك بعض القواعد العملية التي تساعدك على القرار:

• عندما يحتوي الاستعلام على أكثر من خطوة منطقية. التجميع، ثم التصفية على نتيجة التجميع، ثم الربط مع جدول آخر — هذه سلسلة معالجة (pipeline)، وتخصيص CTE لكل خطوة يجعل الاستعلام أوضح بكثير.
• عندما تجد نفسك مضطرًا لتكرار نفس الاستعلام الفرعي. عرّفه مرّة واحدة باسم واضح، ثم استخدمه مرّتين أو أكثر.
• عندما يستحق الاستعلام الفرعي اسمًا. لو كنت ستكتب تعليقًا فوق الـ subquery لتشرح ماذا يمثّل، فإن اسم الـ CTE هو ذلك التعليق، لكن مفروض عليك من قِبَل الصياغة نفسها.
• عندما توشك على كتابة استعلام تكراري. هذا غير ممكن إلا عبر WITH RECURSIVE، وسنتناوله في الصفحة التالية.

ومتى لا داعي للتعب نفسك؟

• استعلام فرعي واحد بسيط يُستخدم في مكان واحد فقط. كتابة WHERE id IN (SELECT id FROM ...) مقبولة تمامًا كما هي.
• الاستعلامات الحسّاسة للأداء التي تأكدت فعليًا من أن دمج المنطق مباشرة داخلها أفضل. صحيح أن SQLite يتعامل مع الـ CTE كحاجز تحسين (optimization fence) بشكل أخف من بعض قواعد البيانات الأخرى، لكن في المسارات الحرجة من الأفضل التحقق باستخدام EXPLAIN QUERY PLAN.

مثال تطبيقي شامل

لنجمع كل ما سبق في مثال واحد — تقرير صغير يستخرج أكبر طلب لكل عميل ويقارنه بمتوسط طلباته:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount REAL);
INSERT INTO orders (customer, amount) VALUES
    ('Ada', 120), ('Boris', 45), ('Ada', 80),
    ('Carla', 200), ('Boris', 30), ('Ada', 50),
    ('Carla', 75), ('Boris', 90);

WITH
    per_customer AS (
        SELECT customer, MAX(amount) AS biggest, AVG(amount) AS average
        FROM orders
        GROUP BY customer
    ),
    with_ratio AS (
        SELECT customer, biggest, average, biggest / average AS ratio
        FROM per_customer
    )
SELECT customer, biggest, ROUND(average, 2) AS average, ROUND(ratio, 2) AS ratio
FROM with_ratio
ORDER BY ratio DESC;

تعبيران CTE، كل واحد يؤدي مهمة واحدة فقط. ثم يأتي SELECT الرئيسي ليُنسّق النتيجة النهائية. تستطيع قراءة الاستعلام من الأعلى إلى الأسفل وفهم كل خطوة على حِدة — وهذا بالضبط جوهر فكرة الـ CTE.

التالي: الـ CTE العَودي (WITH RECURSIVE)

كل ما رأيناه حتى الآن كان CTE عاديًا — استعلام فرعي مُسمّى يُنفَّذ مرة واحدة. لكن SQLite يدعم أيضًا WITH RECURSIVE، وفيه يستدعي الـ CTE نفسه بنفسه، ما يُتيح لك التنقّل عبر التسلسلات الهرمية، أو توليد متتاليات أرقام، أو السير داخل الرسوم البيانية (graphs). وهذا موضوع الصفحة التالية.