ARRAY
ARRAY(subquery)
Description
La fonction ARRAY
renvoie un tableau (ARRAY
) contenant un élément pour chaque ligne d'une sous-requête.
Si la sous-requête (subquery
) génère une table SQL, celle-ci doit comporter exactement une colonne. Chaque élément de l'ARRAY
de sortie est la valeur de la colonne unique d'une ligne dans la table.
Si la sous-requête (subquery
) génère une table de valeurs, chaque élément du tableau (ARRAY
) de sortie correspond à la ligne entière de la table de valeurs.
Contraintes
- Les sous-requêtes ne sont pas ordonnées. Par conséquent, il n'existe aucune garantie que les éléments de l'
ARRAY
de sortie préservent l'ordre défini dans la table source pour la sous-requête. Toutefois, si la sous-requête inclut une clauseORDER BY
, la fonctionARRAY
renverra unARRAY
qui respecte cette clause. - Si la sous-requête renvoie plus d'une colonne, la fonction
ARRAY
renvoie une erreur. - Si la sous-requête renvoie une colonne de type
ARRAY
ou des lignes de typeARRAY
, la fonctionARRAY
renvoie une erreur : Cloud Spanner SQL n'accepte pas les tableaux (ARRAY
) avec des éléments de typeARRAY
. - Si la sous-requête ne renvoie aucune ligne, la fonction
ARRAY
renvoie unARRAY
vide. Elle ne renvoie jamais unARRAY
"NULL
".
Type renvoyé
ARRAY
Exemples
SELECT ARRAY
(SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3) AS new_array;
+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+
Pour construire un ARRAY
à partir d'une sous-requête contenant plusieurs colonnes, modifiez la sous-requête pour qu'elle utilise SELECT AS STRUCT
. La fonction ARRAY
renvoie alors un ARRAY
d'objets STRUCT
. L'ARRAY
contiendra une STRUCT
pour chaque ligne de la sous-requête, et chacune de ces STRUCT
contiendra un champ pour chaque colonne de cette ligne.
SELECT
ARRAY
(SELECT AS STRUCT 1, 2, 3
UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;
+------------------------+
| new_array |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+
De même, pour construire un ARRAY
à partir d'une sous-requête contenant un ou plusieurs ARRAY
, modifiez la sous-requête pour qu'elle utilise SELECT AS STRUCT
.
SELECT ARRAY
(SELECT AS STRUCT [1, 2, 3] UNION ALL
SELECT AS STRUCT [4, 5, 6]) AS new_array;
+----------------------------+
| new_array |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+
ARRAY_CONCAT
ARRAY_CONCAT(array_expression_1 [, array_expression_n])
Description
Concatène un ou plusieurs tableaux contenant le même type d'élément en un seul tableau.
Type renvoyé
ARRAY
Exemples
SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;
+--------------------------------------------------+
| count_to_six |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6] |
+--------------------------------------------------+
ARRAY_LENGTH
ARRAY_LENGTH(array_expression)
Description
Renvoie la taille du tableau. Renvoie 0 pour un tableau vide. Renvoie NULL
si la valeur de array_expression
est NULL
.
Type renvoyé
INT64
Exemples
WITH items AS
(SELECT ["coffee", NULL, "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT ARRAY_TO_STRING(list, ', ', 'NULL'), ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;
+---------------------------------+------+
| list | size |
+---------------------------------+------+
| [coffee, NULL, milk] | 3 |
| [cake, pie] | 2 |
+---------------------------------+------+
ARRAY_TO_STRING
ARRAY_TO_STRING(array_expression, delimiter[, null_text])
Description
Renvoie une concaténation des éléments de array_expression
sous forme de chaîne (STRING). La valeur de array_expression
peut être un tableau contenant des types de données STRING ou BYTES.
Si le paramètre null_text
est utilisé, la fonction remplace toutes les valeurs NULL
du tableau par la valeur de null_text
.
Si le paramètre null_text
n'est pas utilisé, la fonction omet la valeur NULL
et son délimiteur précédent.
Exemples
WITH items AS
(SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie", NULL] as list)
SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie |
+--------------------------------+
WITH items AS
(SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie", NULL] as list)
SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM items;
+--------------------------------+
| text |
+--------------------------------+
| coffee--tea--milk |
| cake--pie--MISSING |
+--------------------------------+
GENERATE_ARRAY
GENERATE_ARRAY(start_expression, end_expression[, step_expression])
Description
Renvoie un tableau de valeurs. Les paramètres start_expression
et end_expression
déterminent les valeurs de début et de fin inclusives du tableau.
La fonction GENERATE_ARRAY
accepte les types de données suivants en entrée :
- INT64
- NUMERIC
- FLOAT64
Le paramètre step_expression
détermine l'incrément utilisé pour générer les valeurs de tableau. La valeur par défaut pour ce paramètre est 1
.
Cette fonction renvoie une erreur si step_expression
est défini sur 0 ou si l'une des entrées est NaN
.
Si l'un des arguments est NULL
, la fonction renvoie un tableau NULL
.
Type de données renvoyé
ARRAY
Exemples
La requête suivante renvoie un tableau d'entiers, avec un pas par défaut de 1.
SELECT GENERATE_ARRAY(1, 5) AS example_array;
+-----------------+
| example_array |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+
La requête suivante renvoie un tableau utilisant une taille de pas spécifiée par l'utilisateur.
SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9] |
+---------------+
La requête suivante renvoie un tableau utilisant une valeur négative (-3
) pour la taille de pas.
SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;
+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+
La requête suivante renvoie un tableau utilisant la même valeur pour start_expression
et end_expression
.
SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;
+---------------+
| example_array |
+---------------+
| [4] |
+---------------+
La requête suivante renvoie un tableau vide, étant donné que la valeur de start_expression
est supérieure à celle de end_expression
et que la valeur de step_expression
est positive.
SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;
+---------------+
| example_array |
+---------------+
| [] |
+---------------+
La requête suivante renvoie un tableau NULL
, car la valeur de end_expression
est NULL
.
SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;
+---------------+
| example_array |
+---------------+
| NULL |
+---------------+
La requête suivante renvoie plusieurs tableaux.
SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;
+---------------+
| example_array |
+---------------+
| [3, 4, 5] |
| [4, 5] |
| [5] |
+---------------+
GENERATE_DATE_ARRAY
GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])
Description
Renvoie un tableau de dates. Les paramètres start_date
et end_date
déterminent les valeurs de début et de fin inclusives du tableau.
La fonction GENERATE_DATE_ARRAY
accepte les types de données suivants en entrée :
- Le paramètre
start_date
doit être une DATE. - Le paramètre
end_date
doit être une DATE. - Le paramètre
INT64_expr
doit être de type INT64. - Le paramètre
date_part
doit être DAY (jour), WEEK (semaine), MONTH (mois), QUARTER (trimestre) ou YEAR (année).
Le paramètre INT64_expr
détermine l'incrément utilisé pour générer les dates. La valeur par défaut pour ce paramètre est "1 jour".
Cette fonction renvoie une erreur si INT64_expr
est défini sur 0.
Type de données renvoyé
Un tableau (ARRAY) contenant 0 ou plusieurs valeurs de type DATE.
Exemples
La requête suivante renvoie un tableau de dates, avec un pas par défaut de 1.
SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;
+--------------------------------------------------+
| example |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+
La requête suivante renvoie un tableau utilisant une taille de pas spécifiée par l'utilisateur.
SELECT GENERATE_DATE_ARRAY(
'2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;
+--------------------------------------+
| example |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+
La requête suivante renvoie un tableau utilisant une valeur négative (-3
) pour la taille de pas.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL -3 DAY) AS example;
+--------------------------+
| example |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+
La requête suivante renvoie un tableau utilisant la même valeur pour start_date
et end_date
.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-05', INTERVAL 8 DAY) AS example;
+--------------+
| example |
+--------------+
| [2016-10-05] |
+--------------+
La requête suivante renvoie un tableau vide, étant donné que la valeur de start_date
est supérieure à celle de end_date
et que la valeur de step
est positive.
SELECT GENERATE_DATE_ARRAY('2016-10-05',
'2016-10-01', INTERVAL 1 DAY) AS example;
+---------+
| example |
+---------+
| [] |
+---------+
La requête suivante renvoie un tableau NULL
, car l'une de ses entrées est NULL
.
SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;
+---------+
| example |
+---------+
| NULL |
+---------+
La requête suivante renvoie un tableau de dates utilisant la valeur MONTH (mois) comme intervalle date_part
:
SELECT GENERATE_DATE_ARRAY('2016-01-01',
'2016-12-31', INTERVAL 2 MONTH) AS example;
+--------------------------------------------------------------------------+
| example |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+
La requête suivante utilise des dates non constantes pour générer un tableau.
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM (
SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
) AS items;
+--------------------------------------------------------------+
| date_range |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+
OFFSET et ORDINAL
array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]
Description
Accède à un élément ARRAY par position et renvoie l'élément. OFFSET
signifie que la numérotation commence à zéro, ORDINAL
signifie que la numérotation commence à un.
Un tableau donné peut être interprété comme étant en base 0 ou en base 1. Lorsque vous accédez à un élément de tableau, vous devez faire précéder la position du tableau par OFFSET
ou ORDINAL
selon le cas : aucun comportement n'est défini par défaut.
Les fonctions OFFSET
et ORDINAL
génèrent toutes deux une erreur si l'index est en dehors de la plage.
Type renvoyé
Varie en fonction des éléments du tableau (ARRAY).
Exemples
WITH items AS
(SELECT ["apples", "bananas", "pears", "grapes"] as list
UNION ALL
SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM items;
+----------------------------------+-----------+-----------+
| list | offset_1 | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas | apples |
| [coffee, tea, milk] | tea | coffee |
| [cake, pie] | pie | cake |
+----------------------------------+-----------+-----------+
ARRAY_REVERSE
ARRAY_REVERSE(value)
Description
Renvoie le tableau d'entrée, mais en présentant les éléments dans l'ordre inverse.
Type renvoyé
ARRAY
Exemples
WITH example AS (
SELECT [1, 2, 3] AS arr UNION ALL
SELECT [4, 5] AS arr UNION ALL
SELECT [] AS arr
)
SELECT
arr,
ARRAY_REVERSE(arr) AS reverse_arr
FROM example;
+-----------+-------------+
| arr | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1] |
| [4, 5] | [5, 4] |
| [] | [] |
+-----------+-------------+
ARRAY_IS_DISTINCT
ARRAY_IS_DISTINCT(value)
Description
Renvoie la valeur "true" si le tableau ne contient aucun élément répété, en utilisant la même logique de comparaison d'égalité que SELECT DISTINCT
.
Type renvoyé
BOOL
Exemples
WITH example AS (
SELECT [1, 2, 3] AS arr UNION ALL
SELECT [1, 1, 1] AS arr UNION ALL
SELECT [1, 2, NULL] AS arr UNION ALL
SELECT [1, 1, NULL] AS arr UNION ALL
SELECT [1, NULL, NULL] AS arr UNION ALL
SELECT [] AS arr UNION ALL
SELECT CAST(NULL AS ARRAY<INT64>) AS arr
)
SELECT
arr,
ARRAY_IS_DISTINCT(arr) as is_distinct
FROM example;
+-----------------+-------------+
| arr | is_distinct |
+-----------------+-------------+
| [1, 2, 3] | true |
| [1, 1, 1] | false |
| [1, 2, NULL] | true |
| [1, 1, NULL] | false |
| [1, NULL, NULL] | false |
| [] | true |
| NULL | NULL |
+-----------------+-------------+
SAFE_OFFSET et SAFE_ORDINAL
array_expression[SAFE_OFFSET(zero_based_offset)]
array_expression[SAFE_ORDINAL(one_based_offset)]
Description
Identiques à OFFSET
et à ORDINAL
, sauf qu'elles renvoient NULL
si l'index est en dehors de la plage.
Type renvoyé
Varient en fonction des éléments du tableau (ARRAY).
Exemple
WITH items AS
(SELECT ["apples", "bananas", "pears", "grapes"] as list
UNION ALL
SELECT ["coffee", "tea", "milk" ] as list
UNION ALL
SELECT ["cake", "pie"] as list)
SELECT list,
list[SAFE_OFFSET(3)] as safe_offset_3,
list[SAFE_ORDINAL(3)] as safe_ordinal_3
FROM items;
+----------------------------------+---------------+----------------+
| list | safe_offset_3 | safe_ordinal_3 |
+----------------------------------+---------------+----------------+
| [apples, bananas, pears, grapes] | grapes | pears |
| [coffee, tea, milk] | NULL | milk |
| [cake, pie] | NULL | NULL |
+----------------------------------+---------------+----------------+