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 :

Requêtes contenant les clauses IN.
Appels à des fonctions définies par le système qui prennent une liste de valeurs comme arguments, telles que COALESCE, GREATEST et LEAST.
Fonctions SQL définies par l’utilisateur qui utilisent un argument pour fournir un tableau de valeurs.
Procédures stockées de Snowflake Scripting qui utilisent une variable de liaison pour fournir un tableau de valeurs. Pour plus d’informations sur l’utilisation des variables de liaison dans Snowflake Scripting, voir Utiliser une variable dans une instruction SQL (liaison) et Utilisation d’un argument dans une instruction SQL (liaison).

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>

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 :
- Numérique (par exemple, INTEGER et NUMERIC)
- Chaîne & binaire (par exemple, VARCHAR et BINARY)
- Logique (par exemple, BOOLEAN)
- Date & heure (par exemple, DATE, TIME et TIMESTAMP)
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;

+------+------+
| 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évelopper un tableau de valeurs littérales dans un appel de fonction défini par le système
Utiliser l’opérateur de diffusion avec une variable de liaison dans une fonction SQL définie par l’utilisateur
Utiliser l’opérateur de diffusion avec une variable de liaison dans une procédure stockée de Snowflake Scripting

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;

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

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

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

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

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

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

SELECT * FROM TABLE(spread_function_demo([1, 3, 5]));

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

Appelez la procédure stockée :

CALL spread_sp_demo([2, 4]);

+------+------+
| COL1 | COL2 |
|------+------|
|    2 | b    |
|    4 | d    |
+------+------+