يدعم 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)
1000000
لكن بما أننا نشير إلى cte_numbers مرتين، تُولَّد أرقام عشوائية في كل مرة، ولذلك نرى نتائج عشوائية مختلفة مثل 280501, 392454, 261636, 196227 وهكذا…
التعبيرات الجدولية الشائعة المُجسَّدة
MATERIALIZED إلى توجيه ClickHouse لتنفيذ الاستعلام الفرعي لـ CTE مرة واحدة فقط، وتخزين النتائج في جدول مؤقت، ثم خدمة جميع المراجع من ذلك الجدول.
ويكون هذا مفيدًا بشكل خاص عندما يُشار إلى CTE نفسه عدة مرات داخل الاستعلام (على سبيل المثال، في عمليات self-join أو في عدة استعلامات فرعية IN)، لأن العملية الحسابية الأساسية لا تحدث إلا مرة واحدة.
تُعد Materialized CTEs ميزة تجريبية.
ويتطلب استخدامها تفعيل المحلّل والإعداد enable_materialized_cte. WITH <identifier> AS MATERIALIZED (<subquery>)
SELECT ...
- عند الإشارة إلى CTE نفسه أكثر من مرة ضمن استعلام.
فبدون
MATERIALIZED، تُعيد كل إشارة تنفيذ الاستعلام الفرعي بشكل مستقل.
- عندما يحتوي CTE على دوال غير حتمية مثل
generateRandom.
يضمن materializing أن ترى جميع الإشارات البيانات نفسها.
- عندما يتضمن CTE حسابات مكلفة (مثل عمليات aggregation وJOIN وعمليات المسح الكبيرة) ولا ينبغي تكرارها.
إذا تمت الإشارة إلى materialized CTE مرة واحدة فقط، فإن ClickHouse يضمّنه تلقائيًا داخل استعلام فرعي عادي لتجنّب overhead غير الضروري.
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 │
└─────────┘
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 │
└─────────┘
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.
- المحلِّل مطلوب: لا تعمل عبارات CTE المُجسَّدة إلا عند تمكين المحلِّل (
enable_analyzer = 1).
- غير مدعوم مع
RECURSIVE: لا يُسمح بالجمع بين الكلمتين المفتاحيتين MATERIALIZED وRECURSIVE، وينتج عن ذلك استثناء UNSUPPORTED_METHOD.
- عبارات CTE المرتبطة غير مسموح بها: لا يمكن لعبارة CTE مُجسَّدة أن تُشير إلى أعمدة من نطاقات الاستعلام الخارجية.
التعبيرات القياسية الشائعة
WITH.
ويمكن الرجوع إلى التعبيرات القياسية الشائعة في أي موضع ضمن الاستعلام.
إذا كان التعبير القياسي المشترك يشير إلى شيء غير قيمة حرفية ثابتة، فقد يؤدي ذلك إلى وجود متغيرات حرة.
يحلّ ClickHouse أي معرّف ضمن أقرب نطاق ممكن، ما يعني أن المتغيرات الحرة قد تشير إلى كيانات غير متوقعة في حال تعارض الأسماء، أو قد تؤدي إلى استعلام فرعي مترابط.
يوصى بتعريف CSE على شكل دالة لامبدا (وذلك ممكن فقط عند تمكين المحلّل)، مع ربط جميع المعرّفات المستخدمة، لتحقيق سلوك أكثر قابلية للتنبؤ عند حلّ معرّفات التعبير. WITH <expression> AS <identifier>
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;
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 │
└───────────┘
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 │
└───────────┘
WITH sum(bytes) AS s
SELECT
formatReadableSize(s),
table
FROM system.parts
GROUP BY table
ORDER BY s;
/* 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;
WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;
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. إذا كنت تستخدم الإصدار 24.3+ وواجهت الاستثناء (UNKNOWN_TABLE) أو (UNSUPPORTED_METHOD)، فهذا يشير إلى أن المحلل معطّل على المثيل أو الدور أو ملف التعريف لديك. لتفعيل المحلل، فعِّل الإعداد allow_experimental_analyzer أو حدِّث إعداد compatibility إلى إصدار أحدث.
واعتبارًا من الإصدار 24.8، أصبح المحلل معتمدًا بالكامل للاستخدام في بيئات production، وأُعيدت تسمية الإعداد allow_experimental_analyzer إلى enable_analyzer. WITH التكراري دائمًا على النحو التالي: عبارة غير تكرارية، ثم UNION ALL، ثم عبارة تكرارية، ولا يجوز إلا للعبارة التكرارية أن تتضمن مرجعًا إلى مخرجات الاستعلام نفسه. ويُنفَّذ استعلام CTE التكراري كما يلي:
- قيِّم العبارة غير التكرارية. وضع نتيجة استعلام العبارة غير التكرارية في جدول عمل مؤقت.
- ما دام جدول العمل غير فارغ، كرِّر الخطوات التالية:
- قيِّم العبارة التكرارية، مع استبدال المرجع الذاتي التكراري بالمحتويات الحالية لجدول العمل. وضع نتيجة استعلام العبارة التكرارية في جدول وسيط مؤقت.
- استبدل محتويات جدول العمل بمحتويات الجدول الوسيط، ثم أفرغ الجدول الوسيط.
تُستخدم الاستعلامات التكرارية عادةً للتعامل مع البيانات الهرمية أو البيانات ذات البنية الشجرية. على سبيل المثال، يمكننا كتابة استعلام ينفِّذ اجتيازًا للشجرة:
مثال: اجتياز الشجرة
لننشئ أولًا جدول الشجرة:
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 │
└────┴──────┴───────────┴─────────┴───────┘
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)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘
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;
