استخدام JOINs في ClickHouse

يدعم ClickHouse بشكل كامل عمليات JOIN القياسية في SQL، مما يتيح تحليل البيانات بكفاءة. في هذا الدليل، ستتعرّف على بعض أنواع JOIN الشائعة المتاحة وكيفية استخدامها بمساعدة مخططات فن واستعلامات مثال على مجموعة بيانات IMDB مُطبَّعة من مستودع مجموعات البيانات العلائقية.

بيانات الاختبار والموارد

يمكن العثور على إرشادات إنشاء الجداول وتحميلها هنا. كما أن مجموعة البيانات متاحة أيضًا في Playground إذا كنت لا تريد إنشاء الجداول وتحميلها محليًا.

ستستخدم الجداول الأربع التالية من مجموعة البيانات النموذجية:

تمثل البيانات في هذه الجداول الأربع أفلامًا يمكن أن تنتمي إلى نوع واحد أو عدة أنواع. ويؤدي الممثلون الأدوار في هذه الأفلام.

تمثل الأسهم في المخطط أعلاه علاقات المفتاح الأجنبي بالمفتاح الأساسي. على سبيل المثال، يحتوي العمود movie_id في صف من جدول genres على القيمة id من صف في جدول movies.

توجد علاقة متعددة إلى متعددة بين الأفلام والممثلين. وتُطبَّع هذه العلاقة متعددة إلى متعددة إلى علاقتين من واحد إلى متعدد باستخدام جدول roles. ويحتوي كل صف في جدول roles على قيم العمود id من جدولي movies وactors.

أنواع JOIN التي يدعمها ClickHouse

يدعم ClickHouse أنواع JOIN التالية:

INNER JOIN
OUTER JOIN
CROSS JOIN
SEMI JOIN
ANTI JOIN
ANY JOIN
ASOF JOIN

ستكتب في الأقسام التالية استعلامات نموذجية لكل نوع من أنواع JOIN المذكورة أعلاه.

INNER JOIN

يعيد INNER JOIN، لكل زوج من الصفوف المتطابقة بحسب مفاتيح الربط، قيم الأعمدة من الصف في الجدول الأيسر مدمجةً مع قيم الأعمدة من الصف في الجدول الأيمن. إذا كان للصف أكثر من تطابق واحد، فستُعاد جميع التطابقات (أي يُنتَج الجداء الديكارتي للصفوف ذات مفاتيح الربط المتطابقة).

يستخرج هذا الاستعلام الأنواع لكل فيلم من خلال ربط جدول movies بجدول genres:

SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
INNER JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

┌─name───────────────────────────────────┬─genre─────┐
│ Harry Potter and the Half-Blood Prince │ Action    │
│ Harry Potter and the Half-Blood Prince │ Adventure │
│ Harry Potter and the Half-Blood Prince │ Family    │
│ Harry Potter and the Half-Blood Prince │ Fantasy   │
│ Harry Potter and the Half-Blood Prince │ Thriller  │
│ DragonBall Z                           │ Action    │
│ DragonBall Z                           │ Adventure │
│ DragonBall Z                           │ Comedy    │
│ DragonBall Z                           │ Fantasy   │
│ DragonBall Z                           │ Sci-Fi    │
└────────────────────────────────────────┴───────────┘

يمكن توسيع سلوك INNER JOIN أو تغييره باستخدام أحد أنواع الربط الأخرى التالية.

(LEFT / RIGHT / FULL) OUTER JOIN

يعمل LEFT OUTER JOIN بطريقة مشابهة لـ INNER JOIN؛ لكنه يعيد أيضًا، بالنسبة إلى صفوف الجدول الأيسر غير المتطابقة، القيم الافتراضية لأعمدة الجدول الأيمن.

يكون استعلام RIGHT OUTER JOIN مشابهًا لذلك، ويعيد أيضًا قيم الصفوف غير المتطابقة من الجدول الأيمن مع القيم الافتراضية لأعمدة الجدول الأيسر.

ويجمع استعلام FULL OUTER JOIN بين LEFT OUTER JOIN وRIGHT OUTER JOIN، ويعيد قيم الصفوف غير المتطابقة من الجدولين الأيسر والأيمن، مع القيم الافتراضية لأعمدة الجدول الأيمن والأيسر، على الترتيب.

يعثر هذا الاستعلام على جميع الأفلام التي ليس لها نوع، وذلك عبر الاستعلام عن كل الصفوف من جدول movies التي لا تحتوي على تطابقات في جدول genres، وبالتالي تحصل (وقت الاستعلام) على القيمة الافتراضية 0 للعمود movie_id:

SELECT m.name
FROM movies AS m
LEFT JOIN genres AS g ON m.id = g.movie_id
WHERE g.movie_id = 0
ORDER BY
    m.year DESC,
    m.name ASC
LIMIT 10;

┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘

CROSS JOIN

ينتج CROSS JOIN حاصل الجداء الديكارتي الكامل للجدولين من دون مراعاة مفاتيح الربط. يُدمَج كل صف من الجدول الأيسر مع كل صف من الجدول الأيمن.

لذلك، فإن الاستعلام التالي يدمج كل صف من جدول movies مع كل صف من جدول genres:

SELECT
    m.name,
    m.id,
    g.movie_id,
    g.genre
FROM movies AS m
CROSS JOIN genres AS g
LIMIT 10;

┌─name─┬─id─┬─movie_id─┬─genre───────┐
│ #28  │  0 │        1 │ Documentary │
│ #28  │  0 │        1 │ Short       │
│ #28  │  0 │        2 │ Comedy      │
│ #28  │  0 │        2 │ Crime       │
│ #28  │  0 │        5 │ Western     │
│ #28  │  0 │        6 │ Comedy      │
│ #28  │  0 │        6 │ Family      │
│ #28  │  0 │        8 │ Animation   │
│ #28  │  0 │        8 │ Comedy      │
│ #28  │  0 │        8 │ Short       │
└──────┴────┴──────────┴─────────────┘

مع أن استعلام المثال السابق بمفرده لم يكن ذا معنى كبير، فإنه يمكن توسيعه بإضافة عبارة WHERE لربط الصفوف المتطابقة ومحاكاة سلوك INNER JOIN بهدف العثور على أنواع كل فيلم:

SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

تحدِّد صياغة بديلة لـ CROSS JOIN عدة جداول في عبارة FROM مفصولة بفواصل.

يعيد ClickHouse كتابة CROSS JOIN إلى INNER JOIN إذا وُجدت تعبيرات ربط في قسم WHERE من الاستعلام.

يمكنك التحقق من ذلك في الاستعلام المثال باستخدام EXPLAIN SYNTAX (الذي يعرض النسخة المُحسَّنة نحويًا التي يُعاد إليها كتابة الاستعلام قبل تنفيذه):

EXPLAIN SYNTAX
SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

┌─explain─────────────────────────────────────┐
│ SELECT                                      │
│     name AS name,                           │
│     genre AS genre                          │
│ FROM movies AS m                            │
│ ALL INNER JOIN genres AS g ON id = movie_id │
│ WHERE id = movie_id                         │
│ ORDER BY                                    │
│     year DESC,                              │
│     name ASC,                               │
│     genre ASC                               │
│ LIMIT 10                                    │
└─────────────────────────────────────────────┘

تتضمن عبارة INNER JOIN في نسخة الاستعلام CROSS JOIN المُحسَّنة نحويًا الكلمة المفتاحية ALL، وقد أُضيفت صراحةً للحفاظ على دلالات الجداء الديكارتي في CROSS JOIN حتى عند إعادة كتابته إلى INNER JOIN، حيث يمكن تعطيل الجداء الديكارتي في هذه الحالة.

ALL

وبما أنه، كما ذُكر أعلاه، يمكن حذف الكلمة المحجوزة OUTER من RIGHT OUTER JOIN، كما يمكن إضافة الكلمة المحجوزة الاختيارية ALL، لذا يمكنك كتابة ALL RIGHT JOIN وسيعمل ذلك دون مشكلة.

‏(LEFT / RIGHT) SEMI JOIN

يعيد استعلام LEFT SEMI JOIN قيم الأعمدة لكل صف من الجدول الأيسر له تطابق واحد على الأقل في مفتاح الربط مع الجدول الأيمن. ولا يُعاد إلا أول تطابق يتم العثور عليه (إذ يكون الجداء الديكارتي معطّلًا).

ويعمل استعلام RIGHT SEMI JOIN بالطريقة نفسها، إذ يعيد القيم لجميع الصفوف من الجدول الأيمن التي لها تطابق واحد على الأقل في الجدول الأيسر، ولكن لا يُعاد إلا أول تطابق يتم العثور عليه.

يعثر هذا الاستعلام على جميع الممثلين/الممثلات الذين شاركوا في فيلم خلال عام 2023. لاحظ أنه عند استخدام ربط عادي (INNER)، سيظهر الممثل/الممثلة نفسه أكثر من مرة إذا كان لديه أكثر من دور واحد في عام 2023:

SELECT
    a.first_name,
    a.last_name
FROM actors AS a
LEFT SEMI JOIN roles AS r ON a.id = r.actor_id
WHERE toYear(created_at) = '2023'
ORDER BY id ASC
LIMIT 10;

┌─first_name─┬─last_name──────────────┐
│ Michael    │ 'babeepower' Viera     │
│ Eloy       │ 'Chincheta'            │
│ Dieguito   │ 'El Cigala'            │
│ Antonio    │ 'El de Chipiona'       │
│ José       │ 'El Francés'           │
│ Félix      │ 'El Gato'              │
│ Marcial    │ 'El Jalisco'           │
│ José       │ 'El Morito'            │
│ Francisco  │ 'El Niño de la Manola' │
│ Víctor     │ 'El Payaso'            │
└────────────┴────────────────────────┘

(LEFT / RIGHT) ANTI JOIN

يعيد LEFT ANTI JOIN قيم الأعمدة لجميع الصفوف غير المتطابقة من الجدول الأيسر.

وبالمثل، يعيد RIGHT ANTI JOIN قيم الأعمدة لجميع الصفوف غير المتطابقة من الجدول الأيمن.

يمكن إعادة صياغة استعلام مثال الربط الخارجي السابق باستخدام anti join للعثور على الأفلام التي لا تملك نوعًا في مجموعة البيانات:

SELECT m.name
FROM movies AS m
LEFT ANTI JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    year DESC,
    name ASC
LIMIT 10;

┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘

(LEFT / RIGHT / INNER) ANY JOIN

يمثل LEFT ANY JOIN مزيجًا من LEFT OUTER JOIN وLEFT SEMI JOIN، ما يعني أن ClickHouse يعيد قيم الأعمدة لكل صف من الجدول الأيسر، إما مقترنةً بقيم أعمدة صف مطابق من الجدول الأيمن، أو بقيم الأعمدة الافتراضية للجدول الأيمن إذا لم يوجد تطابق. إذا كان لصف من الجدول الأيسر أكثر من تطابق واحد في الجدول الأيمن، فإن ClickHouse يعيد فقط قيم الأعمدة المقترنة من أول تطابق يعثر عليه (مع تعطيل الجداء الديكارتي).

وبالمثل، فإن RIGHT ANY JOIN هو مزيج من RIGHT OUTER JOIN وRIGHT SEMI JOIN.

أما INNER ANY JOIN فهو INNER JOIN مع تعطيل الجداء الديكارتي.

يوضح المثال التالي LEFT ANY JOIN من خلال مثال تجريدي يستخدم جدولين مؤقتين (left_table وright_table) أُنشئا باستخدام دالة الجدول values table function:

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
LEFT ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   1 │   0 │
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘

هذا هو الاستعلام نفسه مع استخدام RIGHT ANY JOIN:

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
RIGHT ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   2 │   2 │
│   2 │   2 │
│   3 │   3 │
│   3 │   3 │
│   0 │   4 │
└─────┴─────┘

هذا هو الاستعلام الذي يستخدم INNER ANY JOIN:

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
INNER ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘

ASOF JOIN

يوفّر ASOF JOIN إمكانات مطابقة غير دقيقة. إذا لم يكن لصف من الجدول الأيسر تطابق تام في الجدول الأيمن، فسيُستخدَم بدلًا منه أقرب صف مطابق من الجدول الأيمن.

ويُعد هذا مفيدًا بشكل خاص في تحليلات السلاسل الزمنية، ويمكن أن يقلّل تعقيد الاستعلام بشكل كبير.

يوضح المثال التالي تحليلات سلاسل زمنية لبيانات سوق الأسهم. يحتوي جدول quotes على أسعار لرموز الأسهم استنادًا إلى أوقات محددة خلال اليوم. ويُحدَّث السعر كل 10 ثوانٍ في بيانات المثال. أما جدول trades فيسرد صفقات الرموز — أي أن حجمًا محددًا من أحد الرموز قد تم شراؤه في وقت محدد:

لحساب التكلفة الفعلية لكل صفقة، نحتاج إلى مطابقة الصفقات مع أقرب وقت لسعر العرض الخاص بها.

ويصبح ذلك سهلًا وموجزًا باستخدام ASOF JOIN، حيث تُستخدَم عبارة ON لتحديد شرط التطابق التام، وتُستخدَم عبارة AND لتحديد شرط أقرب مطابقة — فبالنسبة إلى رمز محدد (تطابق تام)، تبحث عن الصف ذي الوقت "الأقرب" من جدول quotes عند وقت صفقة ذلك الرمز أو قبله مباشرةً (مطابقة غير دقيقة):

SELECT
    t.symbol,
    t.volume,
    t.time AS trade_time,
    q.time AS closest_quote_time,
    q.price AS quote_price,
    t.volume * q.price AS final_price
FROM trades t
ASOF LEFT JOIN quotes q ON t.symbol = q.symbol AND t.time >= q.time
FORMAT Vertical;

Row 1:
──────
symbol:             ABC
volume:             200
trade_time:         2023-02-22 14:09:05
closest_quote_time: 2023-02-22 14:09:00
quote_price:        32.11
final_price:        6422

Row 2:
──────
symbol:             ABC
volume:             300
trade_time:         2023-02-22 14:09:28
closest_quote_time: 2023-02-22 14:09:20
quote_price:        32.15
final_price:        9645

الملخص

يوضح هذا الدليل كيف يدعم ClickHouse جميع أنواع JOIN القياسية في SQL، بالإضافة إلى أنواع JOIN متخصصة تدعم الاستعلامات التحليلية. راجع وثائق عبارة JOIN لمزيد من التفاصيل حول JOIN.