Перейти к основному содержанию
Перейти к основному содержанию

Работа с операциями JOIN в ClickHouse

ClickHouse полностью поддерживает стандартные операции JOIN в SQL, что позволяет эффективно анализировать данные. В этом руководстве вы ознакомитесь с некоторыми из наиболее распространённых типов JOIN и узнаете, как использовать их с помощью диаграмм Венна и примеров запросов к нормализованному набору данных IMDb из репозитория реляционных наборов данных.

Тестовые данные и ресурсы​

Инструкции по созданию и загрузке таблиц можно найти здесь. Набор данных также доступен в playground для пользователей, которые не хотят создавать и загружать таблицы локально.

Вы будете использовать следующие четыре таблицы из примерного набора данных:

Схема IMDB

Данные в этих четырёх таблицах описывают фильмы, которые могут относиться к одному или нескольким жанрам. Роли в фильме исполняются актёрами.

Стрелки на диаграмме выше обозначают связи внешнего ключа с первичным ключом, например, столбец movie_id строки в таблице genres содержит значение id из строки таблицы movies.

Между фильмами и актёрами существует отношение многие-ко-многим. Это отношение многие-ко-многим нормализовано в два отношения один-ко-многим с помощью таблицы roles. Каждая строка в таблице roles содержит значения столбцов id из таблиц movies и actors.

Типы соединений, поддерживаемые в ClickHouse​

ClickHouse поддерживает следующие типы соединений (JOIN):

В следующих разделах будут приведены примерные запросы для каждого из перечисленных выше типов JOIN.

INNER 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 │ Боевик    │
│ Harry Potter and the Half-Blood Prince │ Приключения │
│ Harry Potter and the Half-Blood Prince │ Семейный    │
│ Harry Potter and the Half-Blood Prince │ Фэнтези   │
│ Harry Potter and the Half-Blood Prince │ Триллер  │
│ DragonBall Z                           │ Боевик    │
│ DragonBall Z                           │ Приключения │
│ DragonBall Z                           │ Комедия    │
│ DragonBall Z                           │ Фэнтези   │
│ DragonBall Z                           │ Фантастика    │
└────────────────────────────────────────┴───────────┘
Примечание

Ключевое слово INNER можно опустить.

Поведение INNER JOIN может быть расширено или изменено с помощью одного из следующих типов соединения.

(LEFT / RIGHT / FULL) OUTER JOIN​

LEFT OUTER JOIN ведёт себя как INNER JOIN, а для несовпадающих строк левой таблицы ClickHouse возвращает значения по умолчанию для столбцов правой таблицы.

Запрос RIGHT OUTER JOIN аналогичен и также возвращает значения из несовпадающих строк правой таблицы вместе со значениями по умолчанию для столбцов левой таблицы.

Запрос FULL OUTER JOIN сочетает в себе LEFT и RIGHT OUTER JOIN и возвращает значения из несовпадающих строк как левой, так и правой таблиц вместе со значениями по умолчанию для столбцов правой и левой таблиц соответственно.

Outer Join
Примечание

ClickHouse можно настроить так, чтобы он возвращал NULL вместо значений по умолчанию (однако по соображениям производительности это менее предпочтительно).

Этот запрос находит все фильмы без жанра, выбирая все строки из таблицы 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──────────────────────────────────────┐
│ """Тихоокеанская война"""                 │
│ """Турин 2006: XX Зимние Олимпийские игры""" │
│ Артур, фильм                              │
│ Мост в Терабитию                          │
│ Марс в Овне                               │
│ Повелитель пространства и времени         │
│ Девятая жизнь Луи Дракса                  │
│ Парадокс                                  │
│ Рататуй                                   │
│ """Американский папаша"""                 │
└───────────────────────────────────────────┘
Примечание

Ключевое слово OUTER можно не указывать.

CROSS JOIN​

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 аналогичен и возвращает значения столбцов для всех строк из правой таблицы с хотя бы одним совпадением в левой таблице, при этом также возвращается только первое найденное совпадение.

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

Альтернативная формулировка предыдущего примера запроса с внешним соединением — использование 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──────────────────────────────────────┐
│ """Тихоокеанская война"""                 │
│ """Турин 2006: XX Зимние Олимпийские игры""" │
│ Артур, фильм                              │
│ Мост в Терабитию                          │
│ Марс в Овне                               │
│ Повелитель пространства и времени         │
│ Девятая жизнь Луи Дракса                  │
│ Парадокс                                  │
│ Рататуй                                   │
│ """Американский папаша"""                 │
└───────────────────────────────────────────┘

(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 с отключённым декартовым произведением.

Any Join

Следующий пример демонстрирует LEFT ANY JOIN на абстрактном примере с использованием двух временных таблиц (left_table и right_table), созданных с помощью values — табличной функции:

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 предоставляет возможности неточного сопоставления. Если строка из левой таблицы не имеет точного совпадения в правой таблице, то вместо этого в качестве совпадения используется ближайшая по значению строка из правой таблицы.

Это особенно полезно для аналитики временных рядов и может значительно снизить сложность запросов.

Asof Join

Следующий пример показывает аналитику временных рядов биржевых данных по акциям. Таблица quotes содержит котировки биржевых тикеров в определённые моменты времени в течение дня. В примере данные о цене обновляются каждые 10 секунд. Таблица trades перечисляет сделки с тикерами — указаны сделки по определённому объёму тикера, купленному в конкретный момент времени:

Asof Example

Чтобы вычислить точную стоимость каждой сделки, нужно сопоставить сделки с ближайшим по времени значением котировки.

Это просто и компактно делается с помощью 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;

Строка 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

Строка 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
Примечание

Клауза ON в ASOF JOIN является обязательной и задаёт условие точного соответствия в дополнение к неточному условию соответствия в клаузе AND.

Краткое описание​

В этом руководстве описано, как ClickHouse поддерживает все стандартные типы операций SQL JOIN, а также специализированные варианты для выполнения аналитических запросов. Подробнее об операциях JOIN см. в документации по команде JOIN.