Тестовые данные и ресурсы
movie_id в строке таблицы genres содержит значение id из строки таблицы movies.
Между фильмами и актёрами существует связь многие-ко-многим.
Эта связь многие-ко-многим нормализуется в две связи один-ко-многим с помощью таблицы roles.
Каждая строка в таблице roles содержит значения из столбцов id таблиц movies и actors.
Поддерживаемые в ClickHouse типы JOIN
INNER JOIN
INNER JOIN возвращает для каждой пары строк, совпадающих по ключам JOIN, значения столбцов строки из левой таблицы, объединённые со значениями столбцов строки из правой таблицы.
Если для строки находится более одного совпадения, возвращаются все совпадения (то есть для строк с совпадающими ключами JOIN формируется декартово произведение).
Этот запрос находит жанры для каждого фильма, объединяя таблицу movies с таблицей genres:
Ключевое слово
INNER можно опустить.INNER JOIN можно расширить или изменить с помощью одного из следующих типов JOIN.
(LEFT / RIGHT / FULL) OUTER JOIN
LEFT OUTER JOIN работает как INNER JOIN, но для несовпадающих строк левой таблицы ClickHouse возвращает значения по умолчанию для столбцов правой таблицы.
Запрос RIGHT OUTER JOIN устроен аналогично и также возвращает значения из несовпадающих строк правой таблицы вместе со значениями по умолчанию для столбцов левой таблицы.
Запрос FULL OUTER JOIN объединяет LEFT и RIGHT OUTER JOIN и возвращает значения из несовпадающих строк левой и правой таблиц вместе со значениями по умолчанию для столбцов правой и левой таблиц соответственно.
ClickHouse можно настроить так, чтобы он возвращал NULL вместо значений по умолчанию (однако по соображениям производительности это не рекомендуется).
movies, для которых нет совпадений в таблице genres, и поэтому они получают (во время выполнения запроса) значение по умолчанию 0 для столбца movie_id:
Ключевое слово
OUTER можно не указывать.CROSS JOIN
CROSS JOIN создает полное декартово произведение двух таблиц без учета ключей JOIN.
Каждая строка из левой таблицы объединяется с каждой строкой из правой таблицы.
Таким образом, следующий запрос объединяет каждую строку из таблицы movies с каждой строкой из таблицы genres:
WHERE, чтобы сопоставить совпадающие строки и воспроизвести поведение INNER JOIN при поиске жанров для каждого фильма:
CROSS JOIN позволяет указать несколько таблиц в предложении FROM, разделяя их запятыми.
ClickHouse переписывает CROSS JOIN в INNER JOIN, если в разделе WHERE запроса есть выражения для JOIN.
Это можно проверить на примере запроса с помощью EXPLAIN SYNTAX (он возвращает синтаксически оптимизированную версию, в которую запрос переписывается перед выполнением):
CROSS JOIN предложение INNER JOIN содержит ключевое слово ALL, явно добавленное для сохранения семантики декартова произведения CROSS JOIN даже при переписывании в INNER JOIN, для которого декартово произведение можно отключить.
OUTER в RIGHT OUTER JOIN можно опустить, а необязательное ключевое слово ALL — добавить, можно написать ALL RIGHT JOIN, и это тоже будет работать.
(LEFT / RIGHT) SEMI JOIN
LEFT SEMI JOIN возвращает значения столбцов для каждой строки из левой таблицы, у которой есть хотя бы одно совпадение по ключу JOIN в правой таблице.
Возвращается только первое найденное совпадение (декартово произведение отключено).
Запрос RIGHT SEMI JOIN работает аналогично и возвращает значения для всех строк из правой таблицы, у которых есть хотя бы одно совпадение в левой таблице, но возвращается только первое найденное совпадение.
Этот запрос находит всех актёров и актрис, сыгравших в фильме в 2023 году.
Обратите внимание: при обычном (INNER) JOIN один и тот же актёр или актриса может появиться несколько раз, если в 2023 году у него или у неё было больше одной роли:
(LEFT / RIGHT) ANTI JOIN
LEFT ANTI JOIN возвращает значения столбцов для всех несовпадающих строк левой таблицы.
Аналогично, RIGHT ANTI JOIN возвращает значения столбцов для всех несовпадающих строк правой таблицы.
Альтернативный вариант запроса из предыдущего примера с outer JOIN — использовать anti JOIN, чтобы найти фильмы, у которых в наборе данных не указан жанр:
(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 табличной функции:
RIGHT ANY JOIN:
INNER ANY JOIN:
ASOF JOIN
ASOF JOIN предоставляет возможность неточного сопоставления.
Если для строки из левой таблицы не находится точного совпадения в правой таблице, в качестве совпадения используется наиболее близкая строка из правой таблицы.
Это особенно полезно для анализа временных рядов и может значительно снизить сложность запроса.
В следующем примере выполняется анализ временных рядов на данных фондового рынка.
Таблица quotes содержит котировки тикеров акций для определённых моментов времени в течение дня.
В примере цена обновляется каждые 10 секунд.
Таблица trades содержит сделки по тикерам: определённый объём акций был куплен в определённое время:
Чтобы вычислить фактическую стоимость каждой сделки, нужно сопоставить сделки с ближайшим временем котировки.
С ASOF JOIN это делается просто и компактно: условие ON используется для задания точного совпадения, а условие AND — для задания ближайшего совпадения. Для конкретного тикера (точное совпадение) нужно найти строку с «ближайшим» временем из таблицы quotes, которое точно совпадает со временем сделки по этому тикеру или предшествует ему (неточное совпадение):
Условие
ON в ASOF JOIN является обязательным и задаёт условие точного совпадения наряду с условием неточного совпадения в условии AND.