Fonctions de tableau en SQL standard

ARRAY

ARRAY(subquery)

Description

La fonction ARRAY renvoie un tableau (ARRAY) avec un élément pour chaque ligne dans 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 tableau (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 : BigQuery 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 tableau (ARRAY) à partir d'une sous-requête contenant plusieurs colonnes, modifiez la sous-requête pour qu'elle utilise SELECT AS STRUCT. Dorénavant, la fonction ARRAY renverra un ARRAY de 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 array_expression a la valeur NULL.

Type renvoyé

INT64

Exemples


WITH items AS
  (SELECT ["apples", "bananas", NULL, "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie"] as list)

SELECT list, ARRAY_LENGTH(list) AS size
FROM items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [apples, bananas, NULL, grapes] | 4    |
| [coffee, tea, 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 la forme d'une 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 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 ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL
  SELECT ["cake", "pie", NULL] as list)

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

WITH items AS
  (SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL
  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                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| 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 le début et la fin (inclus) 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 le début et la fin (inclus) 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 JOUR, SEMAINE, MOIS, TRIMESTRE ou 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 "2 MONTH" (2 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.

WITH StartsAndEnds AS (
  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"
)
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM StartsAndEnds;

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

GENERATE_TIMESTAMP_ARRAY

GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp,
                         INTERVAL step_expression date_part)

Description

Renvoie un tableau (ARRAY) d'horodatages (TIMESTAMPS) séparés par un intervalle donné. Les paramètres start_timestamp et end_timestamp déterminent les limites inférieure et supérieure (incluses) de l'ARRAY.

La fonction GENERATE_TIMESTAMP_ARRAY accepte les types de données suivants en entrée :

  • start_timestamp : TIMESTAMP
  • end_timestamp : TIMESTAMP
  • step_expression : INT64
  • Valeurs date_part autorisées : MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR ou DAY.

Le paramètre step_expression détermine l'incrément utilisé pour générer des horodatages.

Type de données renvoyé

Un tableau (ARRAY) contenant zéro, une ou plusieurs valeurs TIMESTAMP.

Exemples

L'exemple suivant renvoie un tableau (ARRAY) d'horodatages (TIMESTAMP) à des intervalles d'une seconde.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:02',
                                INTERVAL 1 SECOND) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 00:00:01+00, 2016-10-05 00:00:02+00] |
+--------------------------------------------------------------------------+

L'exemple suivant renvoie un tableau (ARRAY) d'horodatages (TIMESTAMPS) avec un intervalle négatif.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-01 00:00:00',
                                INTERVAL -2 DAY) AS timestamp_array;

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-06 00:00:00+00, 2016-10-04 00:00:00+00, 2016-10-02 00:00:00+00] |
+--------------------------------------------------------------------------+

L'exemple suivant renvoie un tableau (ARRAY) comportant un seul élément, car start_timestamp et end_timestamp ont la même valeur.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+--------------------------+
| timestamp_array          |
+--------------------------+
| [2016-10-05 00:00:00+00] |
+--------------------------+

L'exemple suivant renvoie un tableau (ARRAY) vide, car start_timestamp est postérieur à end_timestamp.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-06 00:00:00', '2016-10-05 00:00:00',
                                INTERVAL 1 HOUR) AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| []              |
+-----------------+

L'exemple suivant renvoie un tableau (ARRAY) NULL, car l'une des entrées est NULL.

SELECT GENERATE_TIMESTAMP_ARRAY('2016-10-05 00:00:00', NULL, INTERVAL 1 HOUR)
  AS timestamp_array;

+-----------------+
| timestamp_array |
+-----------------+
| NULL            |
+-----------------+

L'exemple suivant génère des tableaux (ARRAY) d'horodatages (TIMESTAMP) à partir de colonnes contenant des valeurs pour start_timestamp et end_timestamp.

SELECT GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL 1 HOUR)
  AS timestamp_array
FROM
  (SELECT
    TIMESTAMP '2016-10-05 00:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 02:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 12:00:00' AS start_timestamp,
    TIMESTAMP '2016-10-05 14:00:00' AS end_timestamp
   UNION ALL
   SELECT
    TIMESTAMP '2016-10-05 23:59:00' AS start_timestamp,
    TIMESTAMP '2016-10-06 01:59:00' AS end_timestamp);

+--------------------------------------------------------------------------+
| timestamp_array                                                          |
+--------------------------------------------------------------------------+
| [2016-10-05 00:00:00+00, 2016-10-05 01:00:00+00, 2016-10-05 02:00:00+00] |
| [2016-10-05 12:00:00+00, 2016-10-05 13:00:00+00, 2016-10-05 14:00:00+00] |
| [2016-10-05 23:59:00+00, 2016-10-06 00:59:00+00, 2016-10-06 01:59:00+00] |
+--------------------------------------------------------------------------+

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

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é

Varie 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           |
+----------------------------------+---------------+----------------+
Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Besoin d'aide ? Consultez notre page d'assistance.