من العمليات الشائعة في الجداول التي تحتوي على عمود Array إنشاء جدول جديد يضم صفًا لكل عنصر منفرد من عناصر المصفوفة في ذلك العمود الأصلي، مع تكرار قيم الأعمدة الأخرى. وهذه هي الحالة الأساسية لما تفعله عبارة ARRAY JOIN.
تأتي هذه التسمية من أنه يمكن النظر إليها على أنها تنفيذ JOIN مع مصفوفة أو بنية بيانات متداخلة. والغرض منها مشابه للدالة arrayJoin، لكن وظيفة العبارة أوسع.
الصياغة:
SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...
ARRAY JOIN مبيّنة أدناه:
ARRAY JOIN - في الحالة الأساسية، لا تُضمَّن المصفوفات الفارغة في نتيجة JOIN.LEFT ARRAY JOIN - تحتوي نتيجة JOIN على صفوف تتضمن مصفوفات فارغة. وتُضبط قيمة المصفوفة الفارغة على القيمة الافتراضية لنوع عنصر المصفوفة (وتكون عادةً 0 أو سلسلة فارغة أو NULL).
أمثلة أساسية على ARRAY JOIN
ARRAY JOIN و LEFT ARRAY JOIN
ARRAY JOIN و LEFT ARRAY JOIN. لنُنشئ جدولًا يحتوي على عمود من النوع Array، ثم نُدرج فيه قيمًا:
CREATE TABLE arrays_test
(
s String,
arr Array(UInt8)
) ENGINE = Memory;
INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);
┌─s───────────┬─arr─────┐
│ Hello │ [1,2] │
│ World │ [3,4,5] │
│ Goodbye │ [] │
└─────────────┴─────────┘
ARRAY JOIN:
SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;
┌─s─────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
└───────┴─────┘
LEFT ARRAY JOIN:
SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;
┌─s───────────┬─arr─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ World │ 3 │
│ World │ 4 │
│ World │ 5 │
│ Goodbye │ 0 │
└─────────────┴─────┘
ARRAY JOIN ودالة arrayEnumerate
ARRAY JOIN. وتتيح عدّ شيءٍ ما مرةً واحدة فقط لكل مصفوفة بعد تطبيق ARRAY JOIN. مثال:
SELECT
count() AS Reaches,
countIf(num = 1) AS Hits
FROM test.hits
ARRAY JOIN
GoalsReached,
arrayEnumerate(GoalsReached) AS num
WHERE CounterID = 160656
LIMIT 10
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘
ARRAY JOIN)، ويمثّل Hits عدد مشاهدات الصفحات (السلاسل قبل ARRAY JOIN). وفي هذه الحالة تحديدًا، يمكنك الحصول على النتيجة نفسها بطريقة أبسط:
SELECT
sum(length(GoalsReached)) AS Reaches,
count() AS Hits
FROM test.hits
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)
┌─Reaches─┬──Hits─┐
│ 95606 │ 31406 │
└─────────┴───────┘
ARRAY JOIN وarrayEnumerateUniq
ARRAY JOIN وتجميع عناصر المصفوفة.
في هذا المثال، يُحتسب لكل معرّف هدف عدد التحويلات (إذ إن كل عنصر في بنية البيانات المتداخلة Goals هو هدف تم بلوغه، وهو ما نشير إليه على أنه تحويل) وعدد الجلسات. ومن دون ARRAY JOIN، كنا سنحسب عدد الجلسات على أنه sum(Sign). ولكن في هذه الحالة تحديدًا، تضاعفت الصفوف بسبب البنية المتداخلة Goals، لذلك، لكي نحصي كل جلسة مرة واحدة بعد ذلك، نطبّق شرطًا على قيمة الدالة arrayEnumerateUniq(Goals.ID).
SELECT
Goals.ID AS GoalID,
sum(Sign) AS Reaches,
sumIf(Sign, num = 1) AS Visits
FROM test.visits
ARRAY JOIN
Goals,
arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC
LIMIT 10
┌──GoalID─┬─Reaches─┬─Visits─┐
│ 53225 │ 3214 │ 1097 │
│ 2825062 │ 3188 │ 1097 │
│ 56600 │ 2803 │ 488 │
│ 1989037 │ 2401 │ 365 │
│ 2830064 │ 2396 │ 910 │
│ 1113562 │ 2372 │ 373 │
│ 3270895 │ 2262 │ 812 │
│ 1084657 │ 2262 │ 345 │
│ 56599 │ 2260 │ 799 │
│ 3271094 │ 2256 │ 812 │
└─────────┴─────────┴────────┘
استخدام الأسماء المستعارة
ARRAY JOIN. في هذه الحالة، يمكن الوصول إلى أحد عناصر المصفوفة عبر هذا الاسم المستعار، بينما يُشار إلى المصفوفة نفسها باسمها الأصلي. مثال:
SELECT s, arr, a
FROM arrays_test
ARRAY JOIN arr AS a;
┌─s─────┬─arr─────┬─a─┐
│ Hello │ [1,2] │ 1 │
│ Hello │ [1,2] │ 2 │
│ World │ [3,4,5] │ 3 │
│ World │ [3,4,5] │ 4 │
│ World │ [3,4,5] │ 5 │
└───────┴─────────┴───┘
ARRAY JOIN على مصفوفة خارجية. على سبيل المثال:
SELECT s, arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external;
┌─s───────────┬─arr_external─┐
│ Hello │ 1 │
│ Hello │ 2 │
│ Hello │ 3 │
│ World │ 1 │
│ World │ 2 │
│ World │ 3 │
│ Goodbye │ 1 │
│ Goodbye │ 2 │
│ Goodbye │ 3 │
└─────────────┴──────────────┘
ARRAY JOIN. في هذه الحالة، يُنفَّذ JOIN عليها جميعًا في الوقت نفسه (الجمع المباشر، وليس الضرب الديكارتي). لاحظ أن جميع المصفوفات يجب أن تكون بالحجم نفسه افتراضيًا. مثال:
SELECT s, arr, a, num, mapped
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;
┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2] │ 1 │ 1 │ 2 │
│ Hello │ [1,2] │ 2 │ 2 │ 3 │
│ World │ [3,4,5] │ 3 │ 1 │ 4 │
│ World │ [3,4,5] │ 4 │ 2 │ 5 │
│ World │ [3,4,5] │ 5 │ 3 │ 6 │
└───────┴─────────┴───┴─────┴────────┘
SELECT s, arr, a, num, arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;
┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2] │ 1 │ 1 │ [1,2] │
│ Hello │ [1,2] │ 2 │ 2 │ [1,2] │
│ World │ [3,4,5] │ 3 │ 1 │ [1,2,3] │
│ World │ [3,4,5] │ 4 │ 2 │ [1,2,3] │
│ World │ [3,4,5] │ 5 │ 3 │ [1,2,3] │
└───────┴─────────┴───┴─────┴─────────────────────┘
SETTINGS enable_unaligned_array_join = 1. مثال:
SELECT s, arr, a, b
FROM arrays_test ARRAY JOIN arr AS a, [['a','b'],['c']] AS b
SETTINGS enable_unaligned_array_join = 1;
┌─s───────┬─arr─────┬─a─┬─b─────────┐
│ Hello │ [1,2] │ 1 │ ['a','b'] │
│ Hello │ [1,2] │ 2 │ ['c'] │
│ World │ [3,4,5] │ 3 │ ['a','b'] │
│ World │ [3,4,5] │ 4 │ ['c'] │
│ World │ [3,4,5] │ 5 │ [] │
│ Goodbye │ [] │ 0 │ ['a','b'] │
│ Goodbye │ [] │ 0 │ ['c'] │
└─────────┴─────────┴───┴───────────┘
ARRAY JOIN مع بنية بيانات متداخلة
ARRAY JOIN أيضًا مع بنية بيانات متداخلة:
CREATE TABLE nested_test
(
s String,
nest Nested(
x UInt8,
y UInt32)
) ENGINE = Memory;
INSERT INTO nested_test
VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);
┌─s───────┬─nest.x──┬─nest.y─────┐
│ Hello │ [1,2] │ [10,20] │
│ World │ [3,4,5] │ [30,40,50] │
│ Goodbye │ [] │ [] │
└─────────┴─────────┴────────────┘
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest;
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴────────┴────────┘
ARRAY JOIN، يكون المعنى مماثلًا لاستخدام ARRAY JOIN مع جميع عناصر المصفوفة التي تتألف منها. ترد الأمثلة أدناه:
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`, `nest.y`;
┌─s─────┬─nest.x─┬─nest.y─┐
│ Hello │ 1 │ 10 │
│ Hello │ 2 │ 20 │
│ World │ 3 │ 30 │
│ World │ 4 │ 40 │
│ World │ 5 │ 50 │
└───────┴────────┴────────┘
SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`;
┌─s─────┬─nest.x─┬─nest.y─────┐
│ Hello │ 1 │ [10,20] │
│ Hello │ 2 │ [10,20] │
│ World │ 3 │ [30,40,50] │
│ World │ 4 │ [30,40,50] │
│ World │ 5 │ [30,40,50] │
└───────┴────────┴────────────┘
JOIN أو المصفوفة الأصلية. مثال:
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest AS n;
┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
│ Hello │ 1 │ 10 │ [1,2] │ [10,20] │
│ Hello │ 2 │ 20 │ [1,2] │ [10,20] │
│ World │ 3 │ 30 │ [3,4,5] │ [30,40,50] │
│ World │ 4 │ 40 │ [3,4,5] │ [30,40,50] │
│ World │ 5 │ 50 │ [3,4,5] │ [30,40,50] │
└───────┴─────┴─────┴─────────┴────────────┘
SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
FROM nested_test
ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;
┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
│ Hello │ 1 │ 10 │ [1,2] │ [10,20] │ 1 │
│ Hello │ 2 │ 20 │ [1,2] │ [10,20] │ 2 │
│ World │ 3 │ 30 │ [3,4,5] │ [30,40,50] │ 1 │
│ World │ 4 │ 40 │ [3,4,5] │ [30,40,50] │ 2 │
│ World │ 5 │ 50 │ [3,4,5] │ [30,40,50] │ 3 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘
ARRAY JOIN. وعلى الرغم من أنه يجب دائمًا تحديد ARRAY JOIN قبل عبارة WHERE/PREWHERE في الاستعلام، فمن الناحية التقنية يمكن تنفيذهما بأي ترتيب، ما لم تُستخدَم نتيجة ARRAY JOIN في التصفية. ويتحكم مُحسِّن الاستعلامات في ترتيب التنفيذ.
عدم التوافق مع التقييم المختصر للدوال
if وmultiIf وand وor. وهي تمنع حدوث استثناءات محتملة، مثل القسمة على صفر، أثناء تنفيذ هذه الدوال.
يُنفَّذ arrayJoin دائمًا، ولا يدعم التقييم المختصر للدوال. ويعود ذلك إلى أنه دالة فريدة تُعالَج بشكل منفصل عن جميع الدوال الأخرى أثناء تحليل الاستعلام وتنفيذه، وتتطلب منطقًا إضافيًا لا يعمل مع التقييم المختصر للدوال. والسبب هو أن عدد الصفوف في النتيجة يعتمد على ناتج arrayJoin، كما أن تنفيذ arrayJoin بصورة كسولة معقد للغاية ومكلف.
آخر تعديل في ٢٥ يونيو ٢٠٢٦
