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

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

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

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

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

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

Схема IMDB

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

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

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

Типы JOIN, поддерживаемые в 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 │ 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 можно опустить.

Поведение операции 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, и поэтому (во время выполнения запроса) для столбца movie_id подставляется значение по умолчанию 0:

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"""                        │
└───────────────────────────────────────────┘
Примечание

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

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;

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

Предложение ON в ASOF JOIN обязательно и задаёт условие точного соответствия, которое дополняет условие неточного соответствия в предложении AND.

Краткое содержание​

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