Opérateurs d’expansion

Les opérateurs d’expansion développent une expression de requête qui représente une liste en valeurs individuelles dans la liste. Actuellement, l’opérateur d’étalement (**) est le seul opérateur d’expansion pris en charge par Snowflake.

Diffusion

L’opérateur de diffusion développe un tableau en une liste de valeurs individuelles. Cet opérateur est utile dans les cas d’utilisation suivants :

Pour plus d’informations sur ces cas d’utilisation, consultez l’article de blog Snowflake Introduces SQL Spread Operator (**) (Snowflake présente l’opérateur de diffusion).

Syntaxe

** <array>
Copy

Limitations

  • L’entrée doit être un tableau de valeurs constantes, qui peut être un tableau de valeurs littérales ou une variable de liaison qui représente un tableau de valeurs littérales.

  • Chaque valeur d’un tableau semi-structuré est de type VARIANT. Une valeur VARIANT peut contenir une valeur de tout autre type de données. L’opérateur d’étalement prend en charge les types de données suivants pour la valeur stockée dans VARIANT :

  • Les fonctions définies par l’utilisateur et les procédures stockées écrites dans des langues autres que SQL ne peuvent pas utiliser l’opérateur de diffusion.

  • L’expansion de très grands tableaux à l’aide de l’opérateur de diffusion peut nuire aux performances.

Exemples

Certains exemples utilisent les données du tableau suivant :

Créer une table et insérer des données :

CREATE OR REPLACE TABLE spread_demo (col1 INT, col2 VARCHAR);

INSERT INTO spread_demo VALUES
  (1, 'a'),
  (2, 'b'),
  (3, 'c'),
  (4, 'd'),
  (5, 'e');

SELECT * FROM spread_demo;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    2 | b    |
|    3 | c    |
|    4 | d    |
|    5 | e    |
+------+------+

Les exemples suivants utilisent l’opérateur de diffusion.

Développer un tableau de valeurs littérales dans une clause IN

Développez un tableau de nombres à l’aide de l’opérateur de diffusion dans une requête sur la table spread_demo créée précédemment :

SELECT * FROM spread_demo
  WHERE col1 IN (** [3, 4])
  ORDER BY col1;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    3 | c    |
|    4 | d    |
+------+------+

Expansion d’un tableau de chaînes à l’aide de l’opérateur de diffusion :

SELECT * FROM spread_demo
  WHERE col2 IN (** ['b', 'd'])
  ORDER BY col1;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    2 | b    |
|    4 | d    |
+------+------+

Utilisez une clause IN dans une requête contenant à la fois des valeurs INTEGER et des valeurs de tableaux étendus :

SELECT * FROM spread_demo
  WHERE col1 IN (** [1, 2], 4, 5)
  ORDER BY col1;
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    2 | b    |
|    4 | d    |
|    5 | e    |
+------+------+

Développer un tableau de valeurs littérales dans un appel de fonction défini par le système

Développez un tableau de chaînes dans un appel à la fonction COALESCE :

SELECT COALESCE(** [NULL, NULL, 'my_string_1', 'my_string_2']) AS first_non_null;
Copy
+----------------+
| FIRST_NON_NULL |
|----------------|
| my_string_1    |
+----------------+

Développez un tableau de nombres dans un appel à la fonction GREATEST :

SELECT GREATEST(** [1, 2, 5, 4, 5]) AS greatest_value;
Copy
+----------------+
| GREATEST_VALUE |
|----------------|
|              5 |
+----------------+

Utiliser l’opérateur de diffusion avec une variable de liaison dans une fonction SQL définie par l’utilisateur

Créez une fonction SQL définie par l’utilisateur qui utilise l’opérateur de diffusion. La fonction prend un tableau comme argument et développe les valeurs du tableau pour interroger la table spread_demo créée précédemment :

CREATE OR REPLACE FUNCTION spread_function_demo(col_1_values ARRAY)
  RETURNS TABLE(
    col1 INT,
    col2 VARCHAR)
AS
$$
   SELECT * FROM spread_demo
     WHERE col1 IN (** col_1_values)
     ORDER BY col1
$$;
Copy

Effectuez une requête dans la table à l’aide de la fonction :

SELECT * FROM TABLE(spread_function_demo([1, 3, 5]));
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    3 | c    |
|    5 | e    |
+------+------+

Utiliser l’opérateur de diffusion avec une variable de liaison dans une procédure stockée de Snowflake Scripting

Créez une procédure stockée Snowflake Scripting qui utilise l’opérateur de diffusion. La procédure stockée prend un tableau comme argument et développe ensuite les valeurs du tableau dans une variable de liaison pour interroger la table spread_demo créée précédemment :

CREATE OR REPLACE PROCEDURE spread_sp_demo(col_1_values ARRAY)
  RETURNS TABLE(
    col1 INT,
    col2 VARCHAR)
  LANGUAGE SQL
AS
$$
DECLARE
  res RESULTSET;
BEGIN
  res := (SELECT * FROM spread_demo
     WHERE col1 IN (** :col_1_values)
     ORDER BY col1);
  RETURN TABLE(res);
END;
$$;
Copy

Appelez la procédure stockée :

CALL spread_sp_demo([2, 4]);
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    2 | b    |
|    4 | d    |
+------+------+
Langage: Français