Оператор ARRAY JOIN
Часто требуется преобразовать таблицы, содержащие колонку массива, в новую таблицу, где каждая строка будет содержать отдельный элемент массива из начальной колонки, при этом значения других колонок будут дублироваться. Это основной случай, который выполняет оператор ARRAY JOIN
.
Его название происходит от того, что его можно рассматривать как выполнение JOIN
с массивом или вложенной структурой данных. Намерение похоже на функцию arrayJoin, но функциональность оператора шире.
Синтаксис:
Поддерживаемые типы ARRAY JOIN
перечислены ниже:
ARRAY JOIN
- В базовом случае пустые массивы не включаются в результатJOIN
.LEFT ARRAY JOIN
- РезультатJOIN
содержит строки с пустыми массивами. Значение для пустого массива устанавливается в значение по умолчанию для типа элемента массива (обычно 0, пустая строка или NULL).
Примеры базового ARRAY JOIN
ARRAY JOIN и LEFT ARRAY JOIN
Примеры ниже демонстрируют использование операторов ARRAY JOIN
и LEFT ARRAY JOIN
. Давайте создадим таблицу с колонкой типа Array и вставим в неё значения:
Ниже приведён пример, использующий оператор ARRAY JOIN
:
Следующий пример использует оператор LEFT ARRAY JOIN
:
ARRAY JOIN и функция arrayEnumerate
Эта функция обычно используется с ARRAY JOIN
. Она позволяет подсчитывать что-то всего один раз для каждого массива после применения ARRAY JOIN
. Пример:
В данном примере Reaches - это количество конверсий (строк, полученных после применения ARRAY JOIN
), а Hits - это количество просмотров страниц (строк до ARRAY JOIN
). В данном случае можно получить тот же результат более простым способом:
ARRAY JOIN и arrayEnumerateUniq
Эта функция полезна при использовании ARRAY JOIN
и агрегации элементов массива.
В этом примере каждый ID цели имеет подсчёт количества конверсий (каждый элемент во вложенной структуре данных Goals является достигнутой целью, которую мы называем конверсией) и количества сессий. Без ARRAY JOIN
мы бы посчитали количество сессий как sum(Sign). Но в этом конкретном случае строки были умножены на вложенную структуру Goals, поэтому, чтобы посчитать каждую сессию один раз после этого, мы применяем условие к значению функции arrayEnumerateUniq(Goals.ID)
.
Использование псевдонимов
Псевдоним может быть указан для массива в операторе ARRAY JOIN
. В этом случае элемент массива можно получить по этому псевдониму, но сам массив доступен по оригинальному имени. Пример:
Используя псевдонимы, вы можете выполнять ARRAY JOIN
с внешним массивом. Например:
Несколько массивов могут быть разделены запятыми в операторе ARRAY JOIN
. В этом случае JOIN
выполняется с ними одновременно (прямое суммирование, а не декартово произведение). Обратите внимание, что все массивы должны иметь одинаковый размер по умолчанию. Пример:
Пример ниже использует функцию arrayEnumerate:
Несколько массивов с разными размерами могут быть объединены, используя: SETTINGS enable_unaligned_array_join = 1
. Пример:
ARRAY JOIN с вложенной структурой данных
ARRAY JOIN
также работает с вложенными структурами данных:
При указании имен вложенных структур данных в ARRAY JOIN
смысл остаётся тем же, как и в ARRAY JOIN
со всеми элементами массива, из которых она состоит. Примеры приведены ниже:
Эта вариация также имеет смысл:
Для вложенной структуры данных может быть использован псевдоним, чтобы выбрать либо результат JOIN
, либо исходный массив. Пример:
Пример использования функции arrayEnumerate:
Подробности реализации
Порядок выполнения запроса оптимизирован при выполнении ARRAY JOIN
. Хотя ARRAY JOIN
всегда должен быть указан перед клаузами WHERE/PREWHERE в запросе, технически их можно выполнять в любом порядке, если результат ARRAY JOIN
используется для фильтрации. Порядок обработки контролируется оптимизатором запросов.
Несовместимость с коротким замыканием функции
Короткое замыкание функции - это функция, которая оптимизирует выполнение сложных выражений в специфических функциях, таких как if
, multiIf
, and
и or
. Она предотвращает потенциальные исключения, такие как деление на ноль, возникающие во время выполнения этих функций.
arrayJoin
всегда выполняется и не поддерживается для короткого замыкания функции. Это связано с тем, что это уникальная функция, которая обрабатывается отдельно от всех других функций во время анализа и выполнения запроса и требует дополнительной логики, которая не работает с коротким замыканием функции. Причина в том, что количество строк в результате зависит от результата arrayJoin, и это слишком сложно и затратно для реализации ленивого выполнения arrayJoin
.