WITH

توثيق عبارة WITH

يدعم ClickHouse التعبيرات الجدولية الشائعة (CTE)، والتعبيرات القياسية الشائعة، والاستعلامات التكرارية.

​

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

تمثل التعبيرات الجدولية الشائعة استعلامات فرعية مُسمّاة. ويمكن الإشارة إليها بالاسم في أي موضع داخل استعلام SELECT يُسمح فيه باستخدام تعبير جدولي. كما يمكن الإشارة إلى الاستعلامات الفرعية المُسمّاة بالاسم ضمن نطاق الاستعلام الحالي أو ضمن نطاقات الاستعلامات الفرعية التابعة.

يُستبدل كل مرجع إلى تعبير جدولي شائع في استعلامات SELECT دائمًا بالاستعلام الفرعي الوارد في تعريفه إذا لم يكن CTE معرّفًا صراحةً على أنه materialized (راجع التعبيرات الجدولية الشائعة المُجسَّدة). ويُمنَع التكرار العودي بإخفاء CTE الحالي من عملية حلّ المعرّفات.

يرجى ملاحظة أن CTEs لا تضمن النتائج نفسها في جميع المواضع التي يُشار إليها فيها، لأن الاستعلام يُعاد تنفيذه عند كل استخدام.

​

الصيغة

WITH <identifier> AS [MATERIALIZED] <subquery expression>

​

مثال

فيما يلي مثال على حالة يُعاد فيها تنفيذ استعلام فرعي:

WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)

إذا كانت CTEs تمرّر النتائج نفسها تمامًا، لا مجرد جزء من الشيفرة، فسترى دائمًا 1000000

لكن بما أننا نشير إلى cte_numbers مرتين، تُولَّد أرقام عشوائية في كل مرة، ولذلك نرى نتائج عشوائية مختلفة مثل 280501, 392454, 261636, 196227 وهكذا…

​

التعبيرات الجدولية الشائعة المُجسَّدة

بشكل افتراضي، يضمّن ClickHouse الاستعلام الفرعي الخاص بـ CTE في كل موضع يُشار إليه فيه، ويعيد تنفيذه في كل مرة. تؤدي إضافة الكلمة المفتاحية MATERIALIZED إلى توجيه ClickHouse لتنفيذ الاستعلام الفرعي لـ CTE مرة واحدة فقط، وتخزين النتائج في جدول مؤقت، ثم خدمة جميع المراجع من ذلك الجدول. ويكون هذا مفيدًا بشكل خاص عندما يُشار إلى CTE نفسه عدة مرات داخل الاستعلام (على سبيل المثال، في عمليات ربط ذاتي أو في عدة استعلامات فرعية IN)، لأن العملية الحسابية الأساسية لا تحدث إلا مرة واحدة.

تُعد Materialized CTEs ميزة تجريبية. ويتطلب استخدامها تفعيل الإعداد enable_materialized_cte. وإذا كان الإعداد معطّلًا، يتم تجاهل الكلمة المفتاحية MATERIALIZED: حيث يُضمَّن CTE في كل موضع إشارة إليه كأي CTE عادي، ويُسجَّل تحذير في السجل.

​

الصيغة

WITH <identifier> AS MATERIALIZED (<subquery>)
SELECT ...

​

متى تستخدم

تكون Materialized CTEs أكثر فائدة في الحالات التالية:

• عند الإشارة إلى CTE نفسه أكثر من مرة ضمن استعلام. فبدون MATERIALIZED، تُعيد كل إشارة تنفيذ الاستعلام الفرعي بشكل مستقل.
• عندما يحتوي CTE على دوال غير حتمية مثل generateRandom. يضمن materializing أن ترى جميع الإشارات البيانات نفسها.
• عندما يتضمن CTE حسابات مكلفة (مثل عمليات aggregation وJOIN وعمليات المسح الكبيرة) ولا ينبغي تكرارها.

إذا تمت الإشارة إلى materialized CTE مرة واحدة فقط، فإن ClickHouse يضمّنه تلقائيًا داخل استعلام فرعي عادي لتجنّب overhead غير الضروري.

​

أمثلة

المثال 1: ربط ذاتي على CTE مُجسَّدة

من دون MATERIALIZED، سينفّذ كل جانب من جانبي الربط الاستعلام الفرعي بشكل مستقل. مع MATERIALIZED، لا يُمسَح الجدول إلا مرة واحدة، ويقرأ جانبا الربط من الجدول المؤقت نفسه.

SET enable_materialized_cte = 1;

CREATE TABLE users (uid Int16, name String, age Int16) ENGINE = Memory;
INSERT INTO users VALUES (1231, 'John', 33), (6666, 'Ksenia', 48), (8888, 'Alice', 50);

WITH
    a AS MATERIALIZED (SELECT * FROM users WHERE name = 'Alice')
SELECT count() FROM a AS l JOIN a AS r ON l.uid = r.uid;

┌─count()─┐
│       1 │
└─────────┘

مثال 2: نتائج حتمية مع دوال غير حتمية

تنتج تعبيرات الجدول الشائعة العادية (CTEs) مع generateRandom نتائج مختلفة في كل موضع تتم فيه الإشارة إليها. يضمن تجسيد الـ CTE الاتساق:

SET enable_materialized_cte = 1;

WITH cte_numbers AS MATERIALIZED
(
    SELECT num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers);

لأن كلا المرجعين يقرآن من البيانات المجسّدة نفسها، تكون النتيجة دائمًا 1000000.

مثال 3: ربط تعابير CTE المجسّدة

يمكن لتعابير CTE المجسّدة أن تُشير إلى تعابير CTE مجسّدة أخرى. يحلّ ClickHouse التبعيات ويُجسّدها بالترتيب الصحيح:

SET enable_materialized_cte = 1;

WITH
    a AS MATERIALIZED (SELECT uid, name FROM users),
    b AS MATERIALIZED (SELECT uid FROM a)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;

┌─count()─┐
│       3 │
└─────────┘

لا يهم ترتيب تعريفات CTE — ويُسمح بالإشارة إلى تعريفات تَرِد لاحقًا:

SET enable_materialized_cte = 1;

WITH
    b AS MATERIALIZED (SELECT uid FROM a),
    a AS MATERIALIZED (SELECT uid FROM users)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;

┌─count()─┐
│       3 │
└─────────┘

​

القيود

• يتطلب إعدادًا تجريبيًا: يجب تمكين الإعداد enable_materialized_cte. وإذا كان معطّلًا، فسيتم تجاهل الكلمة المفتاحية MATERIALIZED: إذ تُضمَّن عبارة CTE في كل إشارة إليها كعبارة CTE عادية، ويُسجَّل تحذير.
• غير مدعوم مع RECURSIVE: لا يُسمح بالجمع بين الكلمتين المفتاحيتين MATERIALIZED وRECURSIVE، وينتج عن ذلك استثناء UNSUPPORTED_METHOD.
• عبارات CTE المرتبطة غير مسموح بها: لا يمكن لتعبير الجدول الشائع المُجَّسد أن تُشير إلى أعمدة من نطاقات الاستعلام الخارجية.

​

التعبيرات القياسية الشائعة

يتيح لك ClickHouse تعريف أسماء مستعارة لأي تعبيرات قياسية في عبارة WITH. ويمكن الرجوع إلى التعبيرات القياسية الشائعة في أي موضع ضمن الاستعلام.

إذا كان التعبير القياسي الشائع يشير إلى شيء غير قيمة حرفية ثابتة، فقد يؤدي ذلك إلى وجود متغيرات حرة. يحلّ ClickHouse أي معرّف ضمن أقرب نطاق ممكن، ما يعني أن المتغيرات الحرة قد تشير إلى كيانات غير متوقعة في حال تعارض الأسماء، أو قد تؤدي إلى استعلام فرعي مترابط. يوصى بتعريف CSE على شكل دالة لامبدا مع ربط جميع المعرّفات المستخدمة، لتحقيق سلوك أكثر قابلية للتنبؤ عند حلّ معرّفات التعبير.

​

الصيغة

WITH <expression> AS <identifier>

​

أمثلة

مثال 1: استخدام تعبير ثابت كـ”متغير”

WITH '2019-08-01 15:23:00' AS ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound;

مثال 2: استخدام الدوال عالية الرتبة لتقييد المعرّفات

WITH
    '.txt' as extension,
    (id, extension) -> concat(lower(id), extension) AS gen_name
SELECT gen_name('test', '.sql') as file_name;

   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘

مثال 3: استخدام الدوال عالية الرتبة مع المتغيرات الحرة

تُظهر أمثلة الاستعلامات التالية أن المعرّفات غير المقيّدة تُفسَّر على أنها كيان ضمن أقرب نطاق. هنا، لا يكون extension مقيّدًا داخل متن دالة لامبدا gen_name. مع أن extension مُعرَّف على أنه '.txt' كتعبير قياسي شائع ضمن نطاق تعريف generated_names واستخدامه، فإنه يُفسَّر على أنه عمود في الجدول extension_list، لأنه متاح في الاستعلام الفرعي generated_names.

CREATE TABLE extension_list
(
    extension String
)
ORDER BY extension
AS SELECT '.sql';

WITH
    '.txt' as extension,
    generated_names as (
        WITH
            (id) -> concat(lower(id), extension) AS gen_name
        SELECT gen_name('test') as file_name FROM extension_list
    )
SELECT file_name FROM generated_names;

   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘

مثال 4: استبعاد نتيجة التعبير sum(bytes) من قائمة أعمدة جملة SELECT

WITH sum(bytes) AS s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;

مثال 5: استخدام نتائج استعلام فرعي يُرجِع قيمة واحدة

/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

مثال 6: إعادة استخدام تعبير قياسي شائع مُعرَّف بدالة لامبدا في استعلام فرعي

عرّف التعبير القياسي الشائع على أنه دالة لامبدا بحيث يكون وسيطها مقيّدًا صراحةً:

WITH (value) -> value + 1 AS increment
SELECT increment(first_result) AS second_increment
FROM
(
    SELECT increment(number) AS first_result
    FROM numbers(3)
);

┌─second_increment─┐
│                2 │
│                3 │
│                4 │
└──────────────────┘

​

الاستعلامات التكرارية

يسمح المُعدِّل الاختياري RECURSIVE لاستعلام WITH بالإشارة إلى ناتجه الخاص. مثال:

مثال: جمع الأعداد الصحيحة من 1 إلى 100

WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table WHERE number < 100
)
SELECT sum(number) FROM test_table;

┌─sum(number)─┐
│        5050 │
└─────────────┘

تعتمد تعبيرات CTE التكرارية على محلل الاستعلام الذي طُرح في الإصدار 24.3، وهو الإعداد الافتراضي منذ ذلك الإصدار وإلزامي منذ 26.9. في الإصدارات الأقدم التي لا يزال فيها المحلل معطّلًا على المثيل أو الدور أو ملف التعريف، يؤدي CTE تكراري إلى ظهور الاستثناء (UNKNOWN_TABLE) أو (UNSUPPORTED_METHOD)؛ فعِّل إعداد enable_analyzer هناك أو قم بالترقية.

تكون الصيغة العامة لاستعلام WITH التكراري دائمًا على النحو التالي: عبارة غير تكرارية، ثم UNION ALL، ثم عبارة تكرارية، ولا يجوز إلا للعبارة التكرارية أن تتضمن مرجعًا إلى مخرجات الاستعلام نفسه. ويُنفَّذ استعلام CTE التكراري كما يلي:

1. قيِّم العبارة غير التكرارية. وضع نتيجة استعلام العبارة غير التكرارية في جدول عمل مؤقت.
2. ما دام جدول العمل غير فارغ، كرِّر الخطوات التالية:
   1. قيِّم العبارة التكرارية، مع استبدال المرجع الذاتي التكراري بالمحتويات الحالية لجدول العمل. وضع نتيجة استعلام العبارة التكرارية في جدول وسيط مؤقت.
   2. استبدل محتويات جدول العمل بمحتويات الجدول الوسيط، ثم أفرغ الجدول الوسيط.

تُستخدم الاستعلامات التكرارية عادةً للتعامل مع البيانات الهرمية أو البيانات ذات البنية الشجرية. على سبيل المثال، يمكننا كتابة استعلام ينفِّذ اجتيازًا للشجرة:

مثال: اجتياز الشجرة

لننشئ أولًا جدول الشجرة:

DROP TABLE IF EXISTS tree;
CREATE TABLE tree
(
    id UInt64,
    parent_id Nullable(UInt64),
    data String
) ENGINE = MergeTree ORDER BY id;

INSERT INTO tree VALUES (0, NULL, 'ROOT'), (1, 0, 'Child_1'), (2, 0, 'Child_2'), (3, 1, 'Child_1_1');

يمكننا اجتياز تلك الشجرة باستخدام الاستعلام التالي:

مثال: اجتياز الشجرة

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree;

┌─id─┬─parent_id─┬─data──────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │
│  1 │         0 │ Child_1   │
│  2 │         0 │ Child_2   │
│  3 │         1 │ Child_1_1 │
└────┴───────────┴───────────┘

​

ترتيب البحث

لإنشاء ترتيب العمق أولًا، نحسب لكل صف في النتيجة مصفوفةً من الصفوف التي سبق أن زرناها:

مثال: اجتياز الشجرة بترتيب العمق أولًا

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id])
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY path;

┌─id─┬─parent_id─┬─data──────┬─path────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │ [0]     │
│  1 │         0 │ Child_1   │ [0,1]   │
│  3 │         1 │ Child_1_1 │ [0,1,3] │
│  2 │         0 │ Child_2   │ [0,2]   │
└────┴───────────┴───────────┴─────────┘

لإنشاء ترتيب بالعرض أولًا، تتمثل الطريقة القياسية في إضافة عمود يتتبّع عمق البحث:

مثال: ترتيب اجتياز الشجرة بالعرض أولًا

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path, toUInt64(0) AS depth
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id]), depth + 1
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY depth;

┌─id─┬─link─┬─data──────┬─path────┬─depth─┐
│  0 │ ᴺᵁᴸᴸ │ ROOT      │ [0]     │     0 │
│  1 │    0 │ Child_1   │ [0,1]   │     1 │
│  2 │    0 │ Child_2   │ [0,2]   │     1 │
│  3 │    1 │ Child_1_1 │ [0,1,3] │     2 │
└────┴──────┴───────────┴─────────┴───────┘

​

اكتشاف الحلقات

لننشئ أولًا جدول graph:

DROP TABLE IF EXISTS graph;
CREATE TABLE graph
(
    from UInt64,
    to UInt64,
    label String
) ENGINE = MergeTree ORDER BY (from, to);

INSERT INTO graph VALUES (1, 2, '1 -> 2'), (1, 3, '1 -> 3'), (2, 3, '2 -> 3'), (1, 4, '1 -> 4'), (4, 5, '4 -> 5');

يمكننا اجتياز هذا الرسم البياني باستخدام الاستعلام التالي:

مثال: اجتياز الرسم البياني دون اكتشاف الدورات

WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
    UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;

┌─from─┬─to─┬─label──┐
│    1 │  4 │ 1 -> 4 │
│    1 │  2 │ 1 -> 2 │
│    1 │  3 │ 1 -> 3 │
│    2 │  3 │ 2 -> 3 │
│    4 │  5 │ 4 -> 5 │
└──────┴────┴────────┘

ولكن إذا أضفنا حلقة إلى ذلك المخطط، فسيفشل الاستعلام السابق مع ظهور الخطأ Maximum recursive CTE evaluation depth:

INSERT INTO graph VALUES (5, 1, '5 -> 1');

WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;

Code: 306. DB::Exception: Received from localhost:9000. DB::Exception: Maximum recursive CTE evaluation depth (1000) exceeded, during evaluation of search_graph AS (SELECT from, to, label FROM graph AS g UNION ALL SELECT g.from, g.to, g.label FROM graph AS g, search_graph AS sg WHERE g.from = sg.to). Consider raising max_recursive_cte_evaluation_depth setting.: While executing RecursiveCTESource. (TOO_DEEP_RECURSION)

الطريقة القياسية للتعامل مع الدورات هي حساب مصفوفة من العُقد التي تمت زيارتها بالفعل:

مثال: اجتياز الرسم البياني مع اكتشاف الدورات

WITH RECURSIVE search_graph AS (
    SELECT from, to, label, false AS is_cycle, [tuple(g.from, g.to)] AS path FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label, has(path, tuple(g.from, g.to)), arrayConcat(sg.path, [tuple(g.from, g.to)])
    FROM graph g, search_graph sg
    WHERE g.from = sg.to AND NOT is_cycle
)
SELECT * FROM search_graph WHERE is_cycle ORDER BY from;

┌─from─┬─to─┬─label──┬─is_cycle─┬─path──────────────────────┐
│    1 │  4 │ 1 -> 4 │ true     │ [(1,4),(4,5),(5,1),(1,4)] │
│    4 │  5 │ 4 -> 5 │ true     │ [(4,5),(5,1),(1,4),(4,5)] │
│    5 │  1 │ 5 -> 1 │ true     │ [(5,1),(1,4),(4,5),(5,1)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘

​

استعلامات لا نهائية

يمكن أيضًا استخدام استعلامات CTE التكرارية اللانهائية إذا استُخدم LIMIT في الاستعلام الخارجي:

مثال: استعلام CTE تكراري لا نهائي

WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table
)
SELECT sum(number) FROM (SELECT number FROM test_table LIMIT 100);

┌─sum(number)─┐
│        5050 │
└─────────────┘

​

الفاصلة الختامية

يُسمح بوضع فاصلة بعد آخر عنصر في عبارة WITH:

WITH
    (SELECT sum(number) FROM numbers(10)) AS total,
    total * 2 AS doubled,
SELECT total, doubled;