Fonctions de tableau

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 clause ORDER BY, la fonction ARRAY renverra un ARRAY 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 type ARRAY, la fonction ARRAY renvoie une erreur : Cloud Spanner SQL n'accepte pas les tableaux (ARRAY) avec des éléments de type ARRAY.
• Si la sous-requête ne renvoie aucune ligne, la fonction ARRAY renvoie un ARRAY vide. Elle ne renvoie jamais un ARRAY "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.

Remarque : Vous pouvez également utiliser l'opérateur de concaténation || pour concaténer des tableaux.

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           |
+----------------------------------+---------------+----------------+