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

Оператор ARRAY JOIN

Для таблиц, содержащих столбец-массив, часто требуется получить новую таблицу, в которой для каждого отдельного элемента массива исходного столбца создаётся отдельная строка, а значения остальных столбцов дублируются. Это базовый случай работы оператора ARRAY JOIN.

Его название связано с тем, что его можно рассматривать как выполнение операции JOIN с массивом или вложенной структурой данных. Назначение похоже на функцию arrayJoin, но функциональность оператора шире.

Синтаксис:

SELECT <expr_list>
FROM <left_subquery>
[LEFT] ARRAY JOIN <array>
[WHERE|PREWHERE <expr>]
...

Поддерживаемые типы ARRAY JOIN перечислены ниже:

  • ARRAY JOIN — по умолчанию пустые массивы не включаются в результат JOIN.
  • LEFT ARRAY JOIN — результат JOIN содержит строки с пустыми массивами. Значение для пустого массива устанавливается в значение по умолчанию для типа элемента массива (обычно 0, пустая строка или NULL).

Базовые примеры ARRAY JOIN​

ARRAY JOIN и LEFT ARRAY JOIN​

Примеры ниже демонстрируют использование операторов ARRAY JOIN и LEFT ARRAY JOIN. Создадим таблицу со столбцом типа Array и вставим в него значения:

CREATE TABLE arrays_test
(
    s String,
    arr Array(UInt8)
) ENGINE = Memory;

INSERT INTO arrays_test
VALUES ('Hello', [1,2]), ('World', [3,4,5]), ('Goodbye', []);

┌─s───────────┬─arr─────┐
│ Привет      │ [1,2]   │
│ Мир         │ [3,4,5] │
│ До свидания │ []      │
└─────────────┴─────────┘

В приведённом ниже примере используется предложение ARRAY JOIN:

SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;

┌─s─────┬─arr─┐
│ Привет │   1 │
│ Привет │   2 │
│ Мир │   3 │
│ Мир │   4 │
│ Мир │   5 │
└───────┴─────┘

В следующем примере используется оператор LEFT ARRAY JOIN:

SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;

┌─s───────────┬─arr─┐
│ Привет       │   1 │
│ Привет       │   2 │
│ Мир       │   3 │
│ Мир       │   4 │
│ Мир       │   5 │
│ До свидания     │   0 │
└─────────────┴─────┘

ARRAY JOIN и функция arrayEnumerate​

Эта функция обычно используется совместно с ARRAY JOIN. Она позволяет посчитать что-либо один раз для каждого массива после применения ARRAY JOIN. Пример:

SELECT
    count() AS Reaches,
    countIf(num = 1) AS Hits
FROM test.hits
ARRAY JOIN
    GoalsReached,
    arrayEnumerate(GoalsReached) AS num
WHERE CounterID = 160656
LIMIT 10

┌─Охваты─┬──Хиты─┐
│  95606 │ 31406 │
└────────┴───────┘

В этом примере Reaches — это количество конверсий (строки, полученные после применения операции ARRAY JOIN), а Hits — количество просмотров страниц (строки до ARRAY JOIN). В этом конкретном случае тот же результат можно получить более простым способом:

SELECT
    sum(length(GoalsReached)) AS Reaches,
    count() AS Hits
FROM test.hits
WHERE (CounterID = 160656) AND notEmpty(GoalsReached)

┌─Охваты─┬──Хиты─┐
│  95606 │ 31406 │
└────────┴───────┘

ARRAY JOIN и arrayEnumerateUniq​

Эта функция полезна при использовании ARRAY JOIN и агрегации элементов массива.

В этом примере для каждого идентификатора цели рассчитывается количество конверсий (каждый элемент вложенной структуры данных Goals — это достигнутая цель, которую мы называем конверсией) и количество сессий. Без ARRAY JOIN мы бы посчитали количество сессий как sum(Sign). Но в данном случае строки были продублированы из‑за развёртывания вложенной структуры Goals, поэтому, чтобы посчитать каждую сессию ровно один раз, мы применяем условие к результату функции arrayEnumerateUniq(Goals.ID).

SELECT
    Goals.ID AS GoalID,
    sum(Sign) AS Reaches,
    sumIf(Sign, num = 1) AS Visits
FROM test.visits
ARRAY JOIN
    Goals,
    arrayEnumerateUniq(Goals.ID) AS num
WHERE CounterID = 160656
GROUP BY GoalID
ORDER BY Reaches DESC
LIMIT 10

┌──GoalID─┬─Reaches─┬─Visits─┐
│   53225 │    3214 │   1097 │
│ 2825062 │    3188 │   1097 │
│   56600 │    2803 │    488 │
│ 1989037 │    2401 │    365 │
│ 2830064 │    2396 │    910 │
│ 1113562 │    2372 │    373 │
│ 3270895 │    2262 │    812 │
│ 1084657 │    2262 │    345 │
│   56599 │    2260 │    799 │
│ 3271094 │    2256 │    812 │
└─────────┴─────────┴────────┘

Использование псевдонимов​

Для массива можно задать псевдоним в предложении ARRAY JOIN. В этом случае к элементу массива можно обратиться по этому псевдониму, но сам массив по‑прежнему доступен по исходному имени. Пример:

SELECT s, arr, a
FROM arrays_test
ARRAY JOIN arr AS a;

┌─s─────┬─arr─────┬─a─┐
│ Привет │ [1,2]   │ 1 │
│ Привет │ [1,2]   │ 2 │
│ Мир │ [3,4,5] │ 3 │
│ Мир │ [3,4,5] │ 4 │
│ Мир │ [3,4,5] │ 5 │
└───────┴─────────┴───┘

Используя псевдонимы, вы можете выполнить операцию ARRAY JOIN с внешним массивом. Например:

SELECT s, arr_external
FROM arrays_test
ARRAY JOIN [1, 2, 3] AS arr_external;

┌─s───────────┬─arr_external─┐
│ Hello       │            1 │
│ Hello       │            2 │
│ Hello       │            3 │
│ World       │            1 │
│ World       │            2 │
│ World       │            3 │
│ Goodbye     │            1 │
│ Goodbye     │            2 │
│ Goodbye     │            3 │
└─────────────┴──────────────┘

Несколько массивов могут быть перечислены через запятую в предложении ARRAY JOIN. В этом случае JOIN выполняется с ними одновременно (прямая сумма, а не декартово произведение). Обратите внимание, что по умолчанию все массивы должны иметь одинаковый размер. Пример:

SELECT s, arr, a, num, mapped
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num, arrayMap(x -> x + 1, arr) AS mapped;

┌─s─────┬─arr─────┬─a─┬─num─┬─mapped─┐
│ Hello │ [1,2]   │ 1 │   1 │      2 │
│ Hello │ [1,2]   │ 2 │   2 │      3 │
│ World │ [3,4,5] │ 3 │   1 │      4 │
│ World │ [3,4,5] │ 4 │   2 │      5 │
│ World │ [3,4,5] │ 5 │   3 │      6 │
└───────┴─────────┴───┴─────┴────────┘

В примере ниже используется функция arrayEnumerate:

SELECT s, arr, a, num, arrayEnumerate(arr)
FROM arrays_test
ARRAY JOIN arr AS a, arrayEnumerate(arr) AS num;

┌─s─────┬─arr─────┬─a─┬─num─┬─arrayEnumerate(arr)─┐
│ Hello │ [1,2]   │ 1 │   1 │ [1,2]               │
│ Hello │ [1,2]   │ 2 │   2 │ [1,2]               │
│ World │ [3,4,5] │ 3 │   1 │ [1,2,3]             │
│ World │ [3,4,5] │ 4 │   2 │ [1,2,3]             │
│ World │ [3,4,5] │ 5 │   3 │ [1,2,3]             │
└───────┴─────────┴───┴─────┴─────────────────────┘

Несколько массивов разного размера можно объединить с помощью настройки SETTINGS enable_unaligned_array_join = 1. Пример:

SELECT s, arr, a, b
FROM arrays_test ARRAY JOIN arr AS a, [['a','b'],['c']] AS b
SETTINGS enable_unaligned_array_join = 1;

┌─s───────┬─arr─────┬─a─┬─b─────────┐
│ Привет   │ [1,2]   │ 1 │ ['a','b'] │
│ Привет   │ [1,2]   │ 2 │ ['c']     │
│ Мир   │ [3,4,5] │ 3 │ ['a','b'] │
│ Мир   │ [3,4,5] │ 4 │ ['c']     │
│ Мир   │ [3,4,5] │ 5 │ []        │
│ До свидания │ []      │ 0 │ ['a','b'] │
│ До свидания │ []      │ 0 │ ['c']     │
└─────────┴─────────┴───┴───────────┘

ARRAY JOIN с вложенной структурой данных​

ARRAY JOIN также работает с вложенными структурами данных:

CREATE TABLE nested_test
(
    s String,
    nest Nested(
    x UInt8,
    y UInt32)
) ENGINE = Memory;

INSERT INTO nested_test
VALUES ('Hello', [1,2], [10,20]), ('World', [3,4,5], [30,40,50]), ('Goodbye', [], []);

┌─s───────┬─nest.x──┬─nest.y─────┐
│ Привет  │ [1,2]   │ [10,20]    │
│ Мир     │ [3,4,5] │ [30,40,50] │
│ Прощай  │ []      │ []         │
└─────────┴─────────┴────────────┘

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest;

┌─s─────┬─nest.x─┬─nest.y─┐
│ Привет │      1 │     10 │
│ Привет │      2 │     20 │
│ Мир │      3 │     30 │
│ Мир │      4 │     40 │
│ Мир │      5 │     50 │
└───────┴────────┴────────┘

При указании имён вложенных структур данных в ARRAY JOIN это эквивалентно ARRAY JOIN по всем элементам массива, из которых они состоят. Примеры приведены ниже:

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`, `nest.y`;

┌─s─────┬─nest.x─┬─nest.y─┐
│ Привет │      1 │     10 │
│ Привет │      2 │     20 │
│ Мир │      3 │     30 │
│ Мир │      4 │     40 │
│ Мир │      5 │     50 │
└───────┴────────┴────────┘

Такой вариант тоже имеет смысл:

SELECT s, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN `nest.x`;

┌─s─────┬─nest.x─┬─nest.y─────┐
│ Hello │      1 │ [10,20]    │
│ Hello │      2 │ [10,20]    │
│ World │      3 │ [30,40,50] │
│ World │      4 │ [30,40,50] │
│ World │      5 │ [30,40,50] │
└───────┴────────┴────────────┘

Псевдоним можно использовать для вложенной структуры данных, чтобы выбрать либо результат JOIN, либо исходный массив. Пример:

SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`
FROM nested_test
ARRAY JOIN nest AS n;

┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┐
│ Hello │   1 │  10 │ [1,2]   │ [10,20]    │
│ Hello │   2 │  20 │ [1,2]   │ [10,20]    │
│ World │   3 │  30 │ [3,4,5] │ [30,40,50] │
│ World │   4 │  40 │ [3,4,5] │ [30,40,50] │
│ World │   5 │  50 │ [3,4,5] │ [30,40,50] │
└───────┴─────┴─────┴─────────┴────────────┘

Пример использования функции arrayEnumerate:

SELECT s, `n.x`, `n.y`, `nest.x`, `nest.y`, num
FROM nested_test
ARRAY JOIN nest AS n, arrayEnumerate(`nest.x`) AS num;

┌─s─────┬─n.x─┬─n.y─┬─nest.x──┬─nest.y─────┬─num─┐
│ Hello │   1 │  10 │ [1,2]   │ [10,20]    │   1 │
│ Hello │   2 │  20 │ [1,2]   │ [10,20]    │   2 │
│ World │   3 │  30 │ [3,4,5] │ [30,40,50] │   1 │
│ World │   4 │  40 │ [3,4,5] │ [30,40,50] │   2 │
│ World │   5 │  50 │ [3,4,5] │ [30,40,50] │   3 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘

Подробности реализации​

Порядок выполнения запроса оптимизируется при использовании ARRAY JOIN. Хотя ARRAY JOIN всегда должен указываться в запросе перед секцией WHERE/PREWHERE, технически они могут выполняться в любом порядке, если только результат ARRAY JOIN не используется для фильтрации. Порядок обработки контролируется оптимизатором запросов.

Несовместимость с коротким замыканием при вычислении функций​

Short-circuit function evaluation — это механизм, оптимизирующий выполнение сложных выражений в ряде функций, таких как if, multiIf, and и or. Он предотвращает возможные исключения (например, деление на ноль) во время выполнения этих функций.

arrayJoin всегда выполняется и не поддерживает вычисление функций с коротким замыканием. Это связано с тем, что это особая функция, которая обрабатывается отдельно от всех прочих функций при анализе и выполнении запроса и требует дополнительной логики, несовместимой с коротким замыканием при вычислении. Причина в том, что количество строк в результате зависит от результата arrayJoin, и реализация отложенного (lazy) выполнения arrayJoin была бы слишком сложной и ресурсоёмкой.

Связанные материалы​