ARRAY JOIN

من العمليات الشائعة في الجداول التي تحتوي على عمود Array إنشاء جدول جديد يضم صفًا لكل عنصر منفرد من عناصر المصفوفة في ذلك العمود الأصلي، مع تكرار قيم الأعمدة الأخرى. وهذه هي الحالة الأساسية لما تفعله عبارة ARRAY JOIN. تأتي هذه التسمية من أنه يمكن النظر إليها على أنها تنفيذ JOIN مع مصفوفة أو بنية بيانات متداخلة. والغرض منها مشابه للدالة arrayJoin، لكن وظيفة العبارة أوسع.

صيغ مصادر الجداول FROM unnest(...) وCROSS JOIN UNNEST(...) وLATERAL unnest(...) المستخدمة في PostgreSQL غير مدعومة. استخدم ARRAY JOIN بدلًا من ذلك. الاسم unnest (منذ الإصدار 26.5) هو اسم بديل لاستدعاء الدالة arrayJoin (SELECT unnest(arr))، وليس دالة جدول. أما JOIN LATERAL <subquery> مع استعلام فرعي مترابط فهو مدعوم بشكل منفصل كميزة تجريبية، راجع JOIN.

الصياغة:

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 │
└─────────┴───────┘

في هذا المثال، يمثّل Reaches عدد التحويلات (السلاسل الناتجة بعد تطبيق 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 │
└───────┴─────────┴───┴─────┴────────┘

يستخدم المثال التالي الدالة arrayEnumerate:

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] │
└───────┴─────┴─────┴─────────┴────────────┘

مثال لاستخدام الدالة arrayEnumerate:

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 بصورة كسولة معقد للغاية ومكلف.

مدونة: العمل مع بيانات السلاسل الزمنية في ClickHouse

مرجع SQL

أنواع البيانات

المحركات

الدوال

الصيغ

الإعدادات

جداول النظام

بحيرات البيانات

أمثلة أساسية على ARRAY JOIN

ARRAY JOIN و LEFT ARRAY JOIN

ARRAY JOIN ودالة arrayEnumerate

ARRAY JOIN وarrayEnumerateUniq

استخدام الأسماء المستعارة

‏ARRAY JOIN مع بنية بيانات متداخلة

تفاصيل التنفيذ

عدم التوافق مع التقييم المختصر للدوال

​أمثلة أساسية على ARRAY JOIN

​ARRAY JOIN و LEFT ARRAY JOIN

​ARRAY JOIN ودالة arrayEnumerate

​ARRAY JOIN وarrayEnumerateUniq

​استخدام الأسماء المستعارة

​‏ARRAY JOIN مع بنية بيانات متداخلة

​تفاصيل التنفيذ

​عدم التوافق مع التقييم المختصر للدوال

​محتوى مرتبط

أمثلة أساسية على ARRAY JOIN

ARRAY JOIN و LEFT ARRAY JOIN

ARRAY JOIN ودالة arrayEnumerate

ARRAY JOIN وarrayEnumerateUniq

استخدام الأسماء المستعارة

‏ARRAY JOIN مع بنية بيانات متداخلة

تفاصيل التنفيذ

عدم التوافق مع التقييم المختصر للدوال

محتوى مرتبط