Функция arrayJoin

Это очень необычная функция.

Обычные функции не изменяют набор строк, а лишь меняют значения в каждой строке (map). Агрегатные функции сжимают набор строк (fold или reduce). Функция arrayJoin берёт каждую строку и порождает набор строк (unfold).

Эта функция принимает массив в качестве аргумента и разворачивает исходную строку в несколько строк — по числу элементов массива. Все значения в столбцах просто копируются, кроме значений в столбце, к которому применяется эта функция: они заменяются соответствующим значением из массива.

unnest (начиная с версии 26.5) — регистронезависимый псевдоним arrayJoin в форме вызова функции (SELECT unnest(arr)). Синтаксис PostgreSQL для указания источника таблицы не поддерживается (FROM unnest(...), CROSS JOIN UNNEST(...) и LATERAL). Для таких запросов используйте предложение ARRAY JOIN.

Например:

SELECT arrayJoin([1, 2, 3] AS src) AS dst, 'Hello', src

┌─dst─┬─\'Hello\'─┬─src─────┐
│   1 │ Hello     │ [1,2,3] │
│   2 │ Hello     │ [1,2,3] │
│   3 │ Hello     │ [1,2,3] │
└─────┴───────────┴─────────┘

Функция arrayJoin влияет на все части запроса, включая раздел WHERE. Обратите внимание, что результат запроса ниже — 2, хотя подзапрос вернул 1 строку.

SELECT sum(1) AS impressions
FROM
(
    SELECT ['Istanbul', 'Berlin', 'Babruysk'] AS cities
)
WHERE arrayJoin(cities) IN ['Istanbul', 'Berlin'];

┌─impressions─┐
│           2 │
└─────────────┘

Есть одно исключение: arrayJoin, в том числе через его псевдоним unnest, нельзя использовать в условии JOIN ON, которое вычисляется в ходе JOIN, поскольку такое условие должно сохранять количество строк и вычисляется отдельно для каждого батча объединяемых строк. Условие, применяемое только к одной стороне, и ключ равенства по arrayJoin извлекаются до JOIN и не затрагиваются. Недизъюнктивное условие ALL INNER JOIN также не затрагивается, поскольку в этом случае условие применяется после JOIN. Если разворачивание зависит только от одной стороны, перенесите его в ARRAY JOIN в подзапросе перед JOIN; условие, аргумент arrayJoin которого использует столбцы с обеих сторон, необходимо переработать.

Запрос может использовать несколько функций arrayJoin. В этом случае преобразование выполняется несколько раз, а количество строк увеличивается. Например:

SELECT
    sum(1) AS impressions,
    arrayJoin(cities) AS city,
    arrayJoin(browsers) AS browser
FROM
(
    SELECT
        ['Istanbul', 'Berlin', 'Babruysk'] AS cities,
        ['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
    2,
    3

┌─impressions─┬─city─────┬─browser─┐
│           2 │ Istanbul │ Chrome  │
│           1 │ Istanbul │ Firefox │
│           2 │ Berlin   │ Chrome  │
│           1 │ Berlin   │ Firefox │
│           2 │ Babruysk │ Chrome  │
│           1 │ Babruysk │ Firefox │
└─────────────┴──────────┴─────────┘

Использование нескольких arrayJoin с одним и тем же выражением может не дать ожидаемого результата из-за устранения общих подвыражений. В таких случаях попробуйте изменить повторяющиеся выражения массива, добавив дополнительные операции, которые не влияют на результат JOIN. Например, arrayJoin(arraySort(arr)), arrayJoin(arrayConcat(arr, []))

Пример:

SELECT
    arrayJoin(dice) AS first_throw,
    /* arrayJoin(dice) as second_throw */ -- технически корректно, но уничтожит результирующий набор
    arrayJoin(arrayConcat(dice, [])) AS second_throw -- выражение намеренно изменено для принудительного повторного вычисления
FROM (
    SELECT [1, 2, 3, 4, 5, 6] AS dice
);

Обратите внимание на синтаксис ARRAY JOIN в SELECT-запросе: он даёт больше возможностей. ARRAY JOIN позволяет одновременно преобразовывать несколько массивов с одинаковым числом элементов.

Пример:

SELECT
    sum(1) AS impressions,
    city,
    browser
FROM
(
    SELECT
        ['Istanbul', 'Berlin', 'Babruysk'] AS cities,
        ['Firefox', 'Chrome', 'Chrome'] AS browsers
)
ARRAY JOIN
    cities AS city,
    browsers AS browser
GROUP BY
    2,
    3

┌─impressions─┬─city─────┬─browser─┐
│           1 │ Istanbul │ Firefox │
│           1 │ Berlin   │ Chrome  │
│           1 │ Babruysk │ Chrome  │
└─────────────┴──────────┴─────────┘

Или можно использовать Tuple

Пример:

SELECT
    sum(1) AS impressions,
    (arrayJoin(arrayZip(cities, browsers)) AS t).1 AS city,
    t.2 AS browser
FROM
(
    SELECT
        ['Istanbul', 'Berlin', 'Babruysk'] AS cities,
        ['Firefox', 'Chrome', 'Chrome'] AS browsers
)
GROUP BY
    2,
    3

┌─impressions─┬─city─────┬─browser─┐
│           1 │ Istanbul │ Firefox │
│           1 │ Berlin   │ Chrome  │
│           1 │ Babruysk │ Chrome  │
└─────────────┴──────────┴─────────┘

Название arrayJoin в ClickHouse связано с его концептуальным сходством с операцией JOIN, но применительно к массивам в пределах одной строки. Если традиционные JOIN объединяют строки из разных таблиц, то arrayJoin «соединяет» каждый элемент массива в строке, создавая несколько строк — по одной на каждый элемент массива — и при этом дублируя значения остальных столбцов. В ClickHouse также есть синтаксис оператора ARRAY JOIN, который делает эту связь с традиционными операциями JOIN ещё более очевидной благодаря использованию привычной терминологии SQL JOIN. Этот процесс также называют «разворачиванием» массива, но термин «join» используется и в имени функции, и в операторе, потому что по сути это похоже на соединение таблицы с элементами массива, то есть на расширение набора данных способом, схожим с операцией JOIN.