função arrayJoin

Esta é uma função muito incomum.

Funções normais não alteram um conjunto de linhas, apenas os valores em cada linha (map). Funções de agregação comprimem um conjunto de linhas (fold ou reduce). A função arrayJoin pega cada linha e gera um conjunto de linhas (unfold).

Essa função recebe um array como argumento e replica a linha de origem em várias linhas, de acordo com o número de elementos no array. Todos os valores das colunas são simplesmente copiados, exceto os valores da coluna à qual essa função é aplicada; eles são substituídos pelo valor correspondente do array.

unnest (desde a versão 26.5) é um alias de arrayJoin que não diferencia maiúsculas de minúsculas na forma de chamada de função (SELECT unnest(arr)). A sintaxe de fonte de tabela do PostgreSQL não é compatível (FROM unnest(...), CROSS JOIN UNNEST(...) e LATERAL). Use a cláusula ARRAY JOIN para essas consultas.

Por exemplo:

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] │
└─────┴───────────┴─────────┘

A função arrayJoin afeta todas as seções da consulta, incluindo a seção WHERE. Observe que, no exemplo abaixo, o resultado da consulta é 2, embora a subconsulta tenha retornado 1 linha.

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

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

Há uma exceção: arrayJoin, inclusive por meio de seu alias unnest, não é permitido em uma condição JOIN ON avaliada durante o join, pois essa condição deve preservar o número de linhas e é avaliada separadamente para cada lote de linhas combinadas. Uma condição que se aplica a apenas um lado e uma chave de igualdade baseada em arrayJoin são extraídas antes do join e não são afetadas. Uma condição não disjuntiva de ALL INNER JOIN também não é afetada, pois, nesse caso, a condição é aplicada após o join. Quando a expansão depende de apenas um lado, mova-a para um ARRAY JOIN em uma subconsulta antes do join; uma condição cujo argumento de arrayJoin lê colunas de ambos os lados precisa ser reestruturada.

Uma consulta pode usar várias funções arrayJoin. Nesse caso, a transformação é realizada várias vezes, e o número de linhas é multiplicado. Por exemplo:

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 │
└─────────────┴──────────┴─────────┘

Usar várias chamadas de arrayJoin com a mesma expressão pode não produzir os resultados esperados devido à eliminação de subexpressões comuns. Nesses casos, considere modificar expressões de array repetidas com operações adicionais que não afetam o resultado do JOIN. Por exemplo, arrayJoin(arraySort(arr)), arrayJoin(arrayConcat(arr, []))

Exemplo:

SELECT
    arrayJoin(dice) AS first_throw,
    /* arrayJoin(dice) as second_throw */ -- é tecnicamente correto, mas aniquilará o result set
    arrayJoin(arrayConcat(dice, [])) AS second_throw -- expressão intencionalmente alterada para forçar reavaliação
FROM (
    SELECT [1, 2, 3, 4, 5, 6] AS dice
);

Observe a sintaxe de ARRAY JOIN na consulta SELECT, que oferece possibilidades mais amplas. ARRAY JOIN permite converter várias Array com o mesmo número de elementos de uma só vez.

Exemplo:

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  │
└─────────────┴──────────┴─────────┘

Ou você pode usar Tuple

Exemplo:

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  │
└─────────────┴──────────┴─────────┘

O nome arrayJoin no ClickHouse vem de sua semelhança conceitual com a operação JOIN, mas aplicada a arrays dentro de uma única linha. Enquanto as junções tradicionais combinam linhas de tabelas diferentes, arrayJoin "junta" cada elemento de um array em uma linha, produzindo várias linhas — uma para cada elemento do array — enquanto duplica os valores das outras colunas. O ClickHouse também fornece a sintaxe da cláusula ARRAY JOIN, o que torna essa relação com as operações JOIN tradicionais ainda mais explícita ao usar a terminologia familiar do SQL JOIN. Esse processo também é chamado de "desdobramento" do array, mas o termo "join" é usado tanto no nome da função quanto na cláusula porque ele se assemelha a unir a tabela aos elementos do array, expandindo efetivamente o conjunto de dados de forma semelhante a uma operação JOIN.