Dans BigQuery, un tableau est une liste ordonnée composée de zéro ou plusieurs valeurs du même type de données. Vous pouvez construire des tableaux de types de données simples, tels que INT64, et de types de données complexes, tels que STRUCT. La seule exception en date est le type de données ARRAY : les tableaux de tableaux ne sont pas acceptés. Les tableaux peuvent inclure des valeurs NULL.
BigQuery permet de construire des littéraux de tableau, de créer des tableaux à partir de sous-requêtes à l'aide de la fonction ARRAY et d'agréger des valeurs dans un tableau à l'aide de la fonction ARRAY_AGG.
Vous pouvez combiner des tableaux à l'aide de fonctions telles que ARRAY_CONCAT() et convertir des tableaux en chaînes à l'aide de la fonction ARRAY_TO_STRING().
Construire des tableaux
Utiliser des littéraux de tableau
Vous pouvez créer un littéral de tableau dans BigQuery à l'aide de crochets ([ et ]). Chaque élément d'un tableau est séparé par une virgule.
SELECT [1, 2, 3] as numbers;
SELECT ["apple", "pear", "orange"] as fruit;
SELECT [true, false, true] as booleans;
Vous pouvez également créer des tableaux à partir d'expressions de types compatibles. Exemple :
SELECT [a, b, c]
FROM
(SELECT 5 AS a,
37 AS b,
406 AS c);
SELECT [a, b, c]
FROM
(SELECT CAST(5 AS INT64) AS a,
CAST(37 AS FLOAT64) AS b,
406 AS c);
Notez que le deuxième exemple contient trois expressions : une qui renvoie INT64, une qui renvoie FLOAT64 et une qui déclare un littéral. Cette expression fonctionne, car les trois expressions partagent FLOAT64 en tant que supertype.
Pour déclarer un type de données spécifique pour un tableau, utilisez des chevrons (< et >). Par exemple :
SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;
Les tableaux de la plupart des types de données, tels que INT64 ou STRING, ne nécessitent pas d'être déclarés au préalable.
SELECT [1, 2, 3] as numbers;
Vous pouvez écrire un tableau vide d'un type spécifique en utilisant ARRAY<type>[]. Vous pouvez également écrire un tableau vide non typé à l'aide de [], auquel cas BigQuery tente de déduire le type de tableau à partir du contexte environnant. Si BigQuery ne peut pas déduire un type, le type par défaut ARRAY<INT64> est utilisé.
Utiliser des valeurs générées
Vous pouvez également construire un objet ARRAY avec des valeurs générées.
Générer des tableaux d'entiers
GENERATE_ARRAY génère un tableau de valeurs à partir d'une valeur de début et de fin, et d'une valeur d'intervalle.
Par exemple, la requête suivante génère un tableau contenant tous les entiers impairs de 11 à 33 inclus :
SELECT GENERATE_ARRAY(11, 33, 2) AS odds;
+--------------------------------------------------+
| odds |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+
Vous pouvez également générer un tableau de valeurs allant dans l'ordre décroissant en indiquant une valeur d'intervalle négative :
SELECT GENERATE_ARRAY(21, 14, -1) AS countdown;
+----------------------------------+
| countdown |
+----------------------------------+
| [21, 20, 19, 18, 17, 16, 15, 14] |
+----------------------------------+
Générer des tableaux de dates
GENERATE_DATE_ARRAY génère un tableau de DATEs à partir d'une DATE de début et de fin, et d'une valeur d'intervalle INTERVAL.
Vous pouvez générer un ensemble de valeurs de DATE à l'aide de GENERATE_DATE_ARRAY. Par exemple, cette requête renvoie la DATE actuelle et les DATEs suivantes à des intervalles d'une semaine (WEEK), jusqu'à une DATE ultérieure incluse :
SELECT
GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK)
AS date_array;
+--------------------------------------------------------------------------+
| date_array |
+--------------------------------------------------------------------------+
| [2017-11-21, 2017-11-28, 2017-12-05, 2017-12-12, 2017-12-19, 2017-12-26] |
+--------------------------------------------------------------------------+
Accéder aux éléments de tableau
Prenons l'exemple de table suivant, sequences :
+---------------------+
| some_numbers |
+---------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [5, 10] |
+---------------------+
Cette table contient la colonne some_numbers comportant des données de type ARRAY.
Pour accéder aux éléments des tableaux de cette colonne, vous devez spécifier le type d'indexation que vous souhaitez utiliser : soit OFFSET (décalage), pour les index basés sur zéro, soit ORDINAL, pour les index basés sur un.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
some_numbers[OFFSET(1)] AS offset_1,
some_numbers[ORDINAL(1)] AS ordinal_1
FROM sequences;
+--------------------+----------+-----------+
| some_numbers | offset_1 | ordinal_1 |
+--------------------+----------+-----------+
| [0, 1, 1, 2, 3, 5] | 1 | 0 |
| [2, 4, 8, 16, 32] | 4 | 2 |
| [5, 10] | 10 | 5 |
+--------------------+----------+-----------+
Connaître les longueurs
La fonction ARRAY_LENGTH() renvoie la longueur d'un tableau.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
ARRAY_LENGTH(some_numbers) AS len
FROM sequences;
+--------------------+--------+
| some_numbers | len |
+--------------------+--------+
| [0, 1, 1, 2, 3, 5] | 6 |
| [2, 4, 8, 16, 32] | 5 |
| [5, 10] | 2 |
+--------------------+--------+
Aplatir des tableaux en tables
Pour convertir un objet ARRAY en un ensemble de lignes (processus appelé "aplatissement"), utilisez l'opérateur UNNEST. UNNEST prend un objet ARRAY et renvoie une table avec une seule ligne pour chaque élément de l'ARRAY.
Comme UNNEST détruit l'ordre des éléments de l'objet ARRAY, vous souhaiterez peut-être rétablir l'ordre dans la table. Pour ce faire, utilisez la clause optionnelle WITH OFFSET pour afficher une colonne supplémentaire contenant le décalage de chaque élément de tableau, puis utilisez la clause ORDER BY pour classer les lignes en fonction de leur décalage.
Exemple
SELECT *
FROM UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred'])
AS element
WITH OFFSET AS offset
ORDER BY offset;
+----------+--------+
| element | offset |
+----------+--------+
| foo | 0 |
| bar | 1 |
| baz | 2 |
| qux | 3 |
| corge | 4 |
| garply | 5 |
| waldo | 6 |
| fred | 7 |
+----------+--------+
Pour aplatir une colonne entière d'objets ARRAY tout en conservant les valeurs des autres colonnes de chaque ligne, utilisez la fonction CROSS JOIN pour joindre la table contenant la colonne d'objets ARRAY au résultat de l'opérateur UNNEST appliqué à cette colonne d'objets ARRAY.
Il s'agit d'une jointure croisée corrélée : l'opérateur UNNEST référence la colonne d'ARRAY à partir de chaque ligne de la table source, qui apparaît précédemment dans la clause FROM. Pour chaque ligne N de la table source, l'opérateur UNNEST aplatit l'objet ARRAY à partir de la ligne N en un ensemble de lignes contenant les éléments de l'objet ARRAY, puis la commande CROSS JOIN joint ce nouvel ensemble de lignes à la ligne unique N de la table source.
Exemple
L'exemple suivant utilise l'opérateur UNNEST pour renvoyer une ligne pour chaque élément de la colonne de tableaux. Avec la commande CROSS JOIN, la colonne id contient les valeurs id de la ligne dans la table sequences comportant chaque nombre.
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;
+------+-------------------+
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
+------+-------------------+
Notez que pour les jointures croisées corrélées, l'opérateur UNNEST est facultatif et le champ CROSS JOIN peut être exprimé en tant que jointure séparée par des virgules. Lorsque vous utilisez cette notation abrégée, l'exemple ci-dessus prend la forme suivante :
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM sequences, sequences.some_numbers AS flattened_numbers;
+------+-------------------+
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
+------+-------------------+
Lancer des requêtes sur des tableaux imbriqués
Si une table contient un objet ARRAY de données STRUCT, vous pouvez aplatir l'objet ARRAY pour effectuer des requêtes sur les champs de données STRUCT.
Vous pouvez également aplatir les champs ARRAY de valeurs STRUCT.
Rechercher des éléments STRUCT dans un tableau
L'exemple suivant utilise UNNEST en combinaison avec CROSS JOIN pour aplatir un ARRAY d'éléments STRUCT.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants)
SELECT
race,
participant
FROM races r
CROSS JOIN UNNEST(r.participants) as participant;
+------+---------------------------------------+
| race | participant |
+------+---------------------------------------+
| 800M | {Rudisha, [23.4, 26.3, 26.4, 26.1]} |
| 800M | {Makhloufi, [24.5, 25.4, 26.6, 26.1]} |
| 800M | {Murphy, [23.9, 26, 27, 26]} |
| 800M | {Bosse, [23.6, 26.2, 26.5, 27.1]} |
| 800M | {Rotich, [24.7, 25.6, 26.9, 26.4]} |
| 800M | {Lewandowski, [25, 25.7, 26.3, 27.2]} |
| 800M | {Kipketer, [23.2, 26.1, 27.3, 29.4]} |
| 800M | {Berian, [23.7, 26.1, 27, 29.3]} |
+------+---------------------------------------+
Vous pouvez trouver des informations spécifiques à partir de champs répétés. Par exemple, la requête suivante renvoie le coureur le plus rapide lors d'une course sur 800 m.
Cet exemple n'implique pas l'aplatissement d'un tableau, mais représente un moyen courant d'obtenir des informations à partir d'un champ répété.
Exemple
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
ORDER BY (
SELECT SUM(duration)
FROM UNNEST(splits) AS duration) ASC
LIMIT 1) AS fastest_racer
FROM races;
+------+---------------+
| race | fastest_racer |
+------+---------------+
| 800M | Rudisha |
+------+---------------+
Lancer des requêtes sur des champs de type ARRAY dans un objet STRUCT
Vous pouvez également obtenir des informations à partir de champs répétés imbriqués. Par exemple, l'instruction suivante renvoie le coureur qui a réalisé le tour de piste le plus rapide lors d'une course sur 800 m.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants),
UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
Notez que la requête précédente utilise l'opérateur "virgule" (,) pour exécuter une commande CROSS JOIN implicite. Cela équivaut à l'exemple suivant, qui utilise CROSS JOIN explicitement.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
AS participants)
SELECT
race,
(SELECT name
FROM UNNEST(participants)
CROSS JOIN UNNEST(splits) AS duration
ORDER BY duration ASC LIMIT 1) AS runner_with_fastest_lap
FROM races;
+------+-------------------------+
| race | runner_with_fastest_lap |
+------+-------------------------+
| 800M | Kipketer |
+------+-------------------------+
L'aplatissement de tableaux avec CROSS JOIN exclut les lignes contenant des tableaux vides ou NULL. Si vous souhaitez inclure ces lignes, utilisez LEFT JOIN.
WITH races AS (
SELECT "800M" AS race,
[STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits),
STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
STRUCT("David" as name, NULL as splits)]
AS participants)
SELECT
name, sum(duration) AS finish_time
FROM races, races.participants LEFT JOIN participants.splits duration
GROUP BY name;
+-------------+--------------------+
| name | finish_time |
+-------------+--------------------+
| Murphy | 102.9 |
| Rudisha | 102.19999999999999 |
| David | NULL |
| Rotich | 103.6 |
| Makhloufi | 102.6 |
| Berian | 106.1 |
| Bosse | 103.4 |
| Kipketer | 106 |
| Nathan | NULL |
| Lewandowski | 104.2 |
+-------------+--------------------+
Créer des tableaux à partir de sous-requêtes
Une tâche courante lors de l'utilisation de tableaux consiste à convertir un résultat de sous-requête en tableau. Dans BigQuery, vous pouvez effectuer cette opération à l'aide de la fonction ARRAY().
Prenons l'exemple de l'opération suivante sur la table sequences :
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x) AS doubled
FROM sequences;
+--------------------+---------------------+
| some_numbers | doubled |
+--------------------+---------------------+
| [0, 1, 1, 2, 3, 5] | [0, 2, 2, 4, 6, 10] |
| [2, 4, 8, 16, 32] | [4, 8, 16, 32, 64] |
| [5, 10] | [10, 20] |
+--------------------+---------------------+
Cet exemple utilise une table nommée "sequences". Cette table contient une colonne some_numbers de type ARRAY<INT64>.
La requête elle-même contient une sous-requête. Cette sous-requête sélectionne chaque ligne de la colonne some_numbers et utilise UNNEST pour renvoyer le tableau sous forme d'un ensemble de lignes. Ensuite, elle multiplie chaque valeur par deux, puis recombine les lignes dans un tableau à l'aide de l'opérateur ARRAY().
Filtrer des tableaux
L'exemple suivant utilise une clause WHERE dans la sous-requête de l'opérateur ARRAY() pour filtrer les lignes renvoyées.
Remarque : Dans les exemples suivants, les lignes renvoyées ne sont pas ordonnées.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x * 2
FROM UNNEST(some_numbers) AS x
WHERE x < 5) AS doubled_less_than_five
FROM sequences;
+------------------------+
| doubled_less_than_five |
+------------------------+
| [0, 2, 2, 4, 6] |
| [4, 8] |
| [] |
+------------------------+
Notez à nouveau que la troisième ligne contient un tableau vide, car le tableau de la ligne d'origine correspondante ([5, 10]) ne répondaient pas à l'exigence de filtre x < 5.
Vous pouvez également filtrer des tableaux en utilisant SELECT DISTINCT pour ne renvoyer que des éléments uniques au sein d'un tableau.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT ARRAY(SELECT DISTINCT x
FROM UNNEST(some_numbers) AS x) AS unique_numbers
FROM sequences;
+-----------------+
| unique_numbers |
+-----------------+
| [0, 1, 2, 3, 5] |
+-----------------+
Vous pouvez également filtrer des lignes de tableaux en utilisant le mot clé IN. Ce mot clé filtre les lignes contenant des tableaux en déterminant si une valeur spécifique correspond à un élément du tableau.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT
ARRAY(SELECT x
FROM UNNEST(some_numbers) AS x
WHERE 2 IN UNNEST(some_numbers)) AS contains_two
FROM sequences;
+--------------------+
| contains_two |
+--------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [] |
+--------------------+
Notez à nouveau que la troisième ligne contient un tableau vide, car le tableau de la ligne d'origine correspondante ([5, 10]) ne contenait pas 2.
Analyser des tableaux
Pour vérifier si un tableau contient une valeur spécifique, utilisez l'opérateur IN avec UNNEST. Pour vérifier si un tableau contient une valeur correspondant à une condition, utilisez la fonction EXISTS avec UNNEST.
Rechercher des valeurs spécifiques
Pour rechercher une valeur spécifique dans un tableau, utilisez l'opérateur IN avec UNNEST.
Exemple
L'exemple suivant renvoie true si le tableau contient le nombre 2.
SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;
+----------------+
| contains_value |
+----------------+
| true |
+----------------+
Pour afficher les lignes d'une table dont la colonne de tableau contient une valeur spécifique, filtrez les résultats de IN UNNEST à l'aide de la clause WHERE.
Exemple
L'exemple suivant renvoie la valeur id pour les lignes dans lesquelles la colonne de tableau contient la valeur 2.
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE 2 IN UNNEST(sequences.some_numbers)
ORDER BY matching_rows;
+---------------+
| matching_rows |
+---------------+
| 1 |
| 2 |
+---------------+
Rechercher des valeurs qui répondent à une condition
Pour rechercher dans un tableau des valeurs correspondant à une condition, utilisez UNNEST pour renvoyer une table des éléments du tableau, WHERE pour filtrer la table résultante dans une sous-requête et EXISTS pour vérifier si la table filtrée contient des lignes.
Exemple
L'exemple suivant renvoie la valeur id pour les lignes dans lesquelles la colonne de tableau contient des valeurs supérieures à 5.
WITH sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id AS matching_rows FROM sequences
WHERE EXISTS (SELECT *
FROM UNNEST(some_numbers) AS x
WHERE x > 5);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+
Rechercher des valeurs de champ STRUCT répondant à une condition
Pour rechercher dans un tableau de données STRUCT un champ dont la valeur répond à une condition, utilisez UNNEST pour renvoyer une table avec une colonne pour chaque champ STRUCT, puis filtrez les lignes non correspondantes de la table à l'aide de WHERE EXISTS.
Exemple
L'exemple suivant renvoie les lignes pour lesquelles la colonne de tableau contient un STRUCT dont le champ b possède une valeur supérieure à 3.
WITH sequences AS
(SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
UNION ALL SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
UNION ALL SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT (7 AS a, 4 AS b)]
AS some_numbers)
SELECT id AS matching_rows
FROM sequences
WHERE EXISTS (SELECT 1
FROM UNNEST(some_numbers)
WHERE b > 3);
+---------------+
| matching_rows |
+---------------+
| 2 |
| 3 |
+---------------+
Tableaux et agrégation
Avec BigQuery, vous pouvez agréger des valeurs dans un tableau à l'aide de ARRAY_AGG().
WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit) AS fruit_basket
FROM fruits;
+-----------------------+
| fruit_basket |
+-----------------------+
| [apple, pear, banana] |
+-----------------------+
Le tableau renvoyé par ARRAY_AGG() est dans un ordre arbitraire, car l'ordre dans lequel la fonction concatène des valeurs n'est pas garanti. Pour trier les éléments du tableau, utilisez ORDER BY. Exemple :
WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)
SELECT ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket
FROM fruits;
+-----------------------+
| fruit_basket |
+-----------------------+
| [apple, banana, pear] |
+-----------------------+
Vous pouvez également appliquer des fonctions d'agrégation telles que SUM() aux éléments d'un tableau. Par exemple, la requête suivante renvoie la somme des éléments du tableau pour chaque ligne de la table sequences.
WITH sequences AS
(SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT [5, 10] AS some_numbers)
SELECT some_numbers,
(SELECT SUM(x)
FROM UNNEST(s.some_numbers) x) AS sums
FROM sequences s;
+--------------------+------+
| some_numbers | sums |
+--------------------+------+
| [0, 1, 1, 2, 3, 5] | 12 |
| [2, 4, 8, 16, 32] | 62 |
| [5, 10] | 15 |
+--------------------+------+
BigQuery accepte également une fonction d'agrégation, ARRAY_CONCAT_AGG(), qui concatène les éléments d'une colonne de tableau sur plusieurs lignes.
WITH aggregate_example AS
(SELECT [1,2] AS numbers
UNION ALL SELECT [3,4] AS numbers
UNION ALL SELECT [5, 6] AS numbers)
SELECT ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg
FROM aggregate_example;
+--------------------------------------------------+
| count_to_six_agg |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
Remarque : Le tableau renvoyé par ARRAY_CONCAT_AGG() n'est pas déterministe, car l'ordre dans lequel la fonction concatène les valeurs n'est pas garanti.
Convertir des tableaux en chaînes
La fonction ARRAY_TO_STRING() permet de convertir un ARRAY<STRING> en une seule valeur STRING ou un ARRAY<BYTES> en une seule valeur BYTES où la valeur résultante est la concaténation ordonnée des éléments du tableau.
Le deuxième argument est le séparateur que la fonction insérera entre les entrées pour produire le résultat. Il doit être du même type que les éléments du premier argument.
Exemple :
WITH greetings AS
(SELECT ["Hello", "World"] AS greeting)
SELECT ARRAY_TO_STRING(greeting, " ") AS greetings
FROM greetings;
+-------------+
| greetings |
+-------------+
| Hello World |
+-------------+
Le troisième argument facultatif remplace les valeurs NULL dans le tableau d'entrée.
Si vous omettez cet argument, la fonction ignore les éléments de tableau
NULL.Si vous fournissez une chaîne vide, la fonction insère un séparateur pour les éléments de tableau
NULL.
Exemple :
SELECT
ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string,
ARRAY_TO_STRING(arr, ".", "") AS empty_string,
ARRAY_TO_STRING(arr, ".") AS omitted
FROM (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr);
+------------------+--------------+---------+
| non_empty_string | empty_string | omitted |
+------------------+--------------+---------+
| a.N.b.N.c.N | a..b..c. | a.b.c |
+------------------+--------------+---------+
Combiner des tableaux
Dans certains cas, vous pouvez combiner plusieurs tableaux en un seul.
Pour ce faire, utilisez la fonction ARRAY_CONCAT().
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
Compresser des tableaux
Vous pouvez fusionner deux tableaux de taille égale en un seul tableau composé de paires d'éléments provenant de tableaux d'entrée, issus de leurs positions correspondantes. Cette opération est parfois appelée compression.
Vous pouvez compresser des tableaux avec UNNEST et WITH OFFSET. Dans cet exemple, chaque paire de valeurs est stockée en tant que STRUCT dans un tableau.
WITH combinations AS (
SELECT
['a', 'b'] AS letters,
[1, 2, 3] AS numbers
)
SELECT ARRAY_AGG(
STRUCT(letter, numbers[OFFSET(letters_offset)] AS number)
) AS pairs
FROM combinations, UNNEST(letters) AS letter WITH OFFSET AS letters_offset;
+------------------------------+
| pairs |
+------------------------------+
| [{ letter: "a", number: 1 }, |
| { letter: "b", number: 2 }] |
+------------------------------+
Vous pouvez utiliser des tableaux d'entrée de longueurs différentes tant que la longueur du premier tableau est égale ou inférieure à celle du deuxième tableau. La longueur du tableau compressé correspondra à celle du tableau d'entrée le plus court.
Construire des tableaux de tableaux
BigQuery n'est pas compatible avec la construction directe de tableaux de tableaux. À la place, vous créez un tableau de structures, chaque structure contenant un champ de type ARRAY. Pour illustrer cela, prenons pour exemple le tableau points suivant :
+----------+
| point |
+----------+
| [1, 5] |
| [2, 8] |
| [3, 7] |
| [4, 1] |
| [5, 7] |
+----------+
Supposons maintenant que vous vouliez créer un tableau composé de chaque point de la table points. Pour ce faire, placez le tableau renvoyé par chaque ligne dans un objet STRUCT, comme indiqué ci-dessous.
WITH points AS
(SELECT [1, 5] as point
UNION ALL SELECT [2, 8] as point
UNION ALL SELECT [3, 7] as point
UNION ALL SELECT [4, 1] as point
UNION ALL SELECT [5, 7] as point)
SELECT ARRAY(
SELECT STRUCT(point)
FROM points)
AS coordinates;
+-------------------+
| coordinates |
+-------------------+
| [{point: [1,5]}, |
| {point: [2,8]}, |
| {point: [5,7]}, |
| {point: [3,7]}, |
| {point: [4,1]}] |
+--------------------+