Operadores de expansão¶

Os operadores de expansão expandem uma expressão de consulta que representa uma lista para os valores individuais da lista. Atualmente, o operador de expansão (**) é o único operador de expansão suportado pelo Snowflake.

Expansão¶

O operador de expansão expande uma matriz em uma lista de valores individuais. Esse operador é útil para os seguintes casos de uso:

Consultas contendo as cláusulas IN.
Chamadas a funções definidas pelo sistema que recebem uma lista de valores como argumentos, como COALESCE, GREATEST e LEAST.
Funções SQL funções definidas pelo usuário que usam um argumento para fornecer uma matriz de valores.
Os procedimentos armazenados do Snowflake Scripting que usam uma variável de vinculação para fornecer uma matriz de valores. Para obter mais informações sobre o uso de variáveis de vinculação no Snowflake Scripting, consulte Como usar uma variável em uma instrução SQL (vinculação) e Uso de um argumento em uma instrução SQL (vinculação).

Para obter mais informações sobre esses casos de uso, consulte a postagem do blog Snowflake Introduces SQL Spread Operator (**).

Sintaxe¶

** <array>

Copy

Limitações¶

A entrada deve ser uma matriz de valores constantes, que pode ser uma matriz de valores literais ou uma variável de vinculação que represente uma matriz de valores literais.
Cada valor em uma matriz semiestruturada é do tipo VARIANT. Um valor VARIANT pode conter um valor de qualquer outro tipo de dados. O operador de expansão é compatível com os seguintes tipos de dados para o valor armazenado no valor VARIANT:
- Numérico (por exemplo, INTEGER e NUMERIC)
- Cadeias de caracteres e binários (por exemplo, VARCHAR e BINARY)
- Lógico (por exemplo, BOOLEAN)
- Data e hora (por exemplo, DATE, TIME e TIMESTAMP)
As funções definidas pelo usuário e os procedimentos armazenados escritos em linguagens diferentes de SQL não podem usar o operador de expansão.
A expansão de matrizes muito grandes com o operador de propagação pode prejudicar o desempenho.

Exemplos¶

Alguns dos exemplos usam os dados da tabela a seguir:

Criar uma tabela e inserir dados:

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

Os exemplos a seguir usam o operador de expansão.

Expansão de uma matriz de valores literais em uma cláusula IN
Expansão de uma matriz de valores literais em uma chamada de função definida pelo sistema
Uso do operador de expansão com uma variável de vinculação em uma função definida pelo usuário SQL
Use o operador de expansão com uma variável de vinculação em um procedimento armazenado do Snowflake Scripting

Expansão de uma matriz de valores literais em uma cláusula IN¶

Expanda uma matriz de números usando o operador de expansão em uma consulta na tabela spread_demo criada anteriormente:

SELECT * FROM spread_demo
  WHERE col1 IN (** [3, 4])
  ORDER BY col1;

Copy

+------+------+
| COL1 | COL2 |
|------+------|
|    3 | c    |
|    4 | d    |
+------+------+

Expanda uma matriz de cadeias de caracteres usando o operador de expansão:

SELECT * FROM spread_demo
  WHERE col2 IN (** ['b', 'd'])
  ORDER BY col1;

Copy

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

Use uma cláusula IN em uma consulta com uma combinação de valores INTEGER e valores de matriz expandida:

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

Expansão de uma matriz de valores literais em uma chamada de função definida pelo sistema¶

Expanda uma matriz de cadeias de caracteres em uma chamada para a função COALESCE:

SELECT COALESCE(** [NULL, NULL, 'my_string_1', 'my_string_2']) AS first_non_null;

Copy

+----------------+
| FIRST_NON_NULL |
|----------------|
| my_string_1    |
+----------------+

Expanda uma matriz de números em uma chamada para a função GREATEST:

SELECT GREATEST(** [1, 2, 5, 4, 5]) AS greatest_value;

Copy

+----------------+
| GREATEST_VALUE |
|----------------|
|              5 |
+----------------+

Uso do operador de expansão com uma variável de vinculação em uma função definida pelo usuário SQL¶

Crie uma função definida pelo usuário SQL que use o operador de expansão. A função recebe uma matriz como argumento e, em seguida, expande os valores da matriz para consultar a tabela spread_demo criada anteriormente:

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

Consultar a tabela usando a função:

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

Copy

+------+------+
| COL1 | COL2 |
|------+------|
|    1 | a    |
|    3 | c    |
|    5 | e    |
+------+------+

Use o operador de expansão com uma variável de vinculação em um procedimento armazenado do Snowflake Scripting¶

Crie um procedimento armazenado do Snowflake Scripting que use o operador de expansão. O procedimento armazenado recebe uma matriz como argumento e, em seguida, expande os valores da matriz em uma variável de vinculação para consultar a tabela spread_demo criada anteriormente:

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

Chame o procedimento armazenado:

CALL spread_sp_demo([2, 4]);

Copy

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