función arrayJoin

Esta es una función muy inusual.

Las funciones normales no modifican un conjunto de filas, sino que solo cambian los valores de cada fila (map). Las funciones de agregado comprimen un conjunto de filas (fold o reduce). La función arrayJoin toma cada fila y genera un conjunto de filas (unfold).

Esta función toma un array como argumento y expande la fila de origen en varias filas, una por cada elemento del array. Todos los valores de las columnas se copian sin más, excepto los de la columna en la que se aplica esta función; estos se sustituyen por el valor correspondiente del array.

unnest (desde la versión 26.5) es un alias de arrayJoin que no distingue entre mayúsculas y minúsculas y se usa como llamada a función (SELECT unnest(arr)). La sintaxis de PostgreSQL como fuente de tabla no es compatible (FROM unnest(...), CROSS JOIN UNNEST(...) y LATERAL). Use la cláusula ARRAY JOIN para esas consultas.

Por ejemplo:

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

La función arrayJoin afecta a todas las secciones de la consulta, incluida la cláusula WHERE. Observe que el resultado de la siguiente consulta es 2, aunque la subconsulta devolvió 1 fila.

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

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

Hay una excepción: arrayJoin, incluido a través de su alias unnest, no se permite en una condición JOIN ON que se evalúa durante el JOIN, porque dicha condición debe conservar el número de filas y se evalúa por separado para cada lote de filas del JOIN. Una condición que se aplica solo a uno de los lados, así como una clave de igualdad basada en arrayJoin, se extraen antes del JOIN y no se ven afectadas. Una condición no disyuntiva de ALL INNER JOIN tampoco se ve afectada, ya que en ese caso la condición se aplica después del JOIN. Cuando la expansión depende solo de un lado, muévala a un ARRAY JOIN en una subconsulta antes del JOIN; una condición cuyo argumento de arrayJoin lee columnas de ambos lados debe reestructurarse.

Una consulta puede usar varias funciones arrayJoin. En este caso, la transformación se realiza varias veces y las filas se multiplican. Por ejemplo:

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 varios arrayJoin con la misma expresión puede no producir los resultados esperados debido a la eliminación de subexpresiones comunes. En esos casos, considere modificar las expresiones de array que se repiten con operaciones adicionales que no afecten al resultado del JOIN. Por ejemplo, arrayJoin(arraySort(arr)), arrayJoin(arrayConcat(arr, []))

Ejemplo:

SELECT
    arrayJoin(dice) AS first_throw,
    /* arrayJoin(dice) as second_throw */ -- es técnicamente correcto, pero eliminará el conjunto de resultados
    arrayJoin(arrayConcat(dice, [])) AS second_throw -- expresión modificada intencionalmente para forzar la reevaluación
FROM (
    SELECT [1, 2, 3, 4, 5, 6] AS dice
);

Observe la sintaxis de ARRAY JOIN en la consulta SELECT, que ofrece posibilidades más amplias. ARRAY JOIN le permite convertir varias arrays con el mismo número de elementos a la vez.

Ejemplo:

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

O bien, puedes usar Tuple

Ejemplo:

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

El nombre arrayJoin en ClickHouse proviene de su similitud conceptual con la operación JOIN, pero aplicada a arrays dentro de una sola fila. Mientras que los JOIN tradicionales combinan filas de distintas tablas, arrayJoin "une" cada elemento de un array en una fila, produciendo múltiples filas -una por cada elemento del array- mientras duplica los valores de las demás columnas. ClickHouse también proporciona la sintaxis de la cláusula ARRAY JOIN, lo que hace que esta relación con las operaciones JOIN tradicionales sea aún más explícita al usar la terminología familiar de SQL JOIN. Este proceso también se denomina "desplegar" el array, pero el término "join" se usa tanto en el nombre de la función como en la cláusula porque se asemeja a unir la tabla con los elementos del array, expandiendo efectivamente el conjunto de datos de una forma similar a una operación JOIN.