ARRAY JOIN

Pour les tables qui contiennent une colonne de type Array, il est courant de produire une nouvelle table comportant une ligne pour chaque élément du tableau de cette colonne initiale, tandis que les valeurs des autres colonnes sont dupliquées. Il s’agit du cas de base de ce que fait la clause ARRAY JOIN. Son nom vient du fait qu’on peut la considérer comme l’exécution d’un JOIN avec un tableau ou une structure de données imbriquée. L’objectif est similaire à celui de la fonction arrayJoin, mais la fonctionnalité de la clause est plus large.

PostgreSQL FROM unnest(...), CROSS JOIN UNNEST(...) et LATERAL ne sont pas pris en charge. Utilisez plutôt ARRAY JOIN. Le nom unnest (depuis la version 26.5) est un alias d’appel de fonction de arrayJoin (SELECT unnest(arr)), et non une fonction de table.

Syntaxe :

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

Les types de ARRAY JOIN pris en charge sont listés ci-dessous :

ARRAY JOIN - Dans le cas de base, les tableaux vides ne sont pas inclus dans le résultat du JOIN.
LEFT ARRAY JOIN - Le résultat du JOIN contient des lignes avec des tableaux vides. La valeur d’un tableau vide est définie sur la valeur par défaut du type d’élément du tableau (généralement 0, une chaîne vide ou NULL).

Exemples de base d’ARRAY JOIN

ARRAY JOIN et LEFT ARRAY JOIN

Les exemples ci-dessous illustrent l’utilisation des clauses ARRAY JOIN et LEFT ARRAY JOIN. Créons une table avec une colonne de type Array et insérons-y des valeurs :

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─────┐
│ Hello       │ [1,2]   │
│ World       │ [3,4,5] │
│ Goodbye     │ []      │
└─────────────┴─────────┘

L’exemple ci-dessous utilise la clause ARRAY JOIN :

SELECT s, arr
FROM arrays_test
ARRAY JOIN arr;

┌─s─────┬─arr─┐
│ Hello │   1 │
│ Hello │   2 │
│ World │   3 │
│ World │   4 │
│ World │   5 │
└───────┴─────┘

L’exemple suivant utilise la clause LEFT ARRAY JOIN :

SELECT s, arr
FROM arrays_test
LEFT ARRAY JOIN arr;

┌─s───────────┬─arr─┐
│ Hello       │   1 │
│ Hello       │   2 │
│ World       │   3 │
│ World       │   4 │
│ World       │   5 │
│ Goodbye     │   0 │
└─────────────┴─────┘

ARRAY JOIN et la fonction arrayEnumerate

Cette fonction est généralement utilisée avec ARRAY JOIN. Elle permet de ne compter quelque chose qu’une seule fois par tableau après l’application de ARRAY JOIN. Exemple :

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

┌─Reaches─┬──Hits─┐
│   95606 │ 31406 │
└─────────┴───────┘

Dans cet exemple, Reaches correspond au nombre de conversions (les chaînes obtenues après application de ARRAY JOIN), et Hits au nombre de pages vues (les chaînes avant ARRAY JOIN). Dans ce cas précis, vous pouvez obtenir le même résultat plus simplement :

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

┌─Reaches─┬──Hits─┐
│   95606 │ 31406 │
└─────────┴───────┘

ARRAY JOIN et arrayEnumerateUniq

Cette fonction est utile lorsque l’on utilise ARRAY JOIN et que l’on agrège des éléments de tableau. Dans cet exemple, pour chaque ID d’objectif, on calcule le nombre de conversions (chaque élément de la structure de données imbriquée Goals est un objectif atteint, que nous appelons une conversion) ainsi que le nombre de sessions. Sans ARRAY JOIN, nous aurions compté le nombre de sessions avec sum(Sign). Mais dans ce cas précis, les lignes ont été multipliées par la structure imbriquée Goals ; afin de ne compter chaque session qu’une seule fois ensuite, nous appliquons une condition à la valeur de la fonction 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 │
└─────────┴─────────┴────────┘

Utilisation des alias

Un alias peut être défini pour un tableau dans la clause ARRAY JOIN. Dans ce cas, on peut accéder à un élément du tableau via cet alias, tandis que le tableau lui-même reste accessible par son nom d’origine. Exemple :

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

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

À l’aide d’alias, vous pouvez effectuer ARRAY JOIN avec un tableau externe. Par exemple :

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

Plusieurs tableaux peuvent être séparés par des virgules dans la clause ARRAY JOIN. Dans ce cas, JOIN leur est appliqué simultanément (somme directe, et non produit cartésien). Notez que, par défaut, tous les tableaux doivent avoir la même taille. Exemple :

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

L’exemple ci-dessous utilise la fonction 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]             │
└───────┴─────────┴───┴─────┴─────────────────────┘

Plusieurs tableaux de tailles différentes peuvent être joints à l’aide de : SETTINGS enable_unaligned_array_join = 1. Exemple :

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─────────┐
│ Hello   │ [1,2]   │ 1 │ ['a','b'] │
│ Hello   │ [1,2]   │ 2 │ ['c']     │
│ World   │ [3,4,5] │ 3 │ ['a','b'] │
│ World   │ [3,4,5] │ 4 │ ['c']     │
│ World   │ [3,4,5] │ 5 │ []        │
│ Goodbye │ []      │ 0 │ ['a','b'] │
│ Goodbye │ []      │ 0 │ ['c']     │
└─────────┴─────────┴───┴───────────┘

ARRAY JOIN avec une structure de données imbriquée

ARRAY JOIN fonctionne également avec les structures de données imbriquées :

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─────┐
│ Hello   │ [1,2]   │ [10,20]    │
│ World   │ [3,4,5] │ [30,40,50] │
│ Goodbye │ []      │ []         │
└─────────┴─────────┴────────────┘

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

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

Lorsque vous spécifiez les noms de structures de données imbriquées dans ARRAY JOIN, cela a le même sens qu’un ARRAY JOIN appliqué à tous les éléments de tableau qui la composent. Des exemples sont présentés ci-dessous :

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

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

Cette variante est également valable :

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

Un alias peut être utilisé pour une structure de données imbriquée afin de sélectionner soit le résultat du JOIN, soit le tableau source. Exemple :

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

Exemple d’utilisation de la fonction 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 │
└───────┴─────┴─────┴─────────┴────────────┴─────┘

Détails d’implémentation

L’ordre d’exécution des requêtes est optimisé lors de l’utilisation de ARRAY JOIN. Bien que ARRAY JOIN doive toujours être spécifié avant la clause WHERE/PREWHERE dans une requête, leur exécution peut techniquement se faire dans n’importe quel ordre, sauf si le résultat de ARRAY JOIN est utilisé pour le filtrage. L’ordre de traitement est contrôlé par l’optimiseur de requêtes.

Incompatibilité avec l’évaluation court-circuitée des fonctions

L’évaluation court-circuitée des fonctions est une fonctionnalité qui optimise l’exécution d’expressions complexes dans certaines fonctions telles que if, multiIf, and et or. Elle évite que des exceptions potentielles, comme une division par zéro, ne surviennent lors de l’exécution de ces fonctions. arrayJoin est toujours exécutée et n’est pas compatible avec l’évaluation court-circuitée des fonctions. Cela s’explique par le fait qu’il s’agit d’une fonction particulière, traitée séparément de toutes les autres fonctions lors de l’analyse et de l’exécution de la requête, et qu’elle nécessite une logique supplémentaire incompatible avec l’exécution court-circuitée des fonctions. En effet, le nombre de lignes dans le résultat dépend du résultat de arrayJoin, et il est trop complexe et coûteux d’implémenter une exécution paresseuse de arrayJoin.

Blog : Travailler avec des séries temporelles dans ClickHouse

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

Exemples de base d’ARRAY JOIN

ARRAY JOIN et LEFT ARRAY JOIN

ARRAY JOIN et la fonction arrayEnumerate

ARRAY JOIN et arrayEnumerateUniq

Utilisation des alias

ARRAY JOIN avec une structure de données imbriquée

Détails d’implémentation

Incompatibilité avec l’évaluation court-circuitée des fonctions

​Exemples de base d’ARRAY JOIN

​ARRAY JOIN et LEFT ARRAY JOIN

​ARRAY JOIN et la fonction arrayEnumerate

​ARRAY JOIN et arrayEnumerateUniq

​Utilisation des alias

​ARRAY JOIN avec une structure de données imbriquée

​Détails d’implémentation

​Incompatibilité avec l’évaluation court-circuitée des fonctions

​Articles connexes

Exemples de base d’ARRAY JOIN

ARRAY JOIN et LEFT ARRAY JOIN

ARRAY JOIN et la fonction arrayEnumerate

ARRAY JOIN et arrayEnumerateUniq

Utilisation des alias

ARRAY JOIN avec une structure de données imbriquée

Détails d’implémentation

Incompatibilité avec l’évaluation court-circuitée des fonctions

Articles connexes