扩展运算符

扩展运算符将表示列表的查询表达式扩展为列表中的单个值。目前，扩展运算符（**）是 Snowflake 支持的唯一扩展运算符。

扩展运算符将 数组 扩展为单个值的列表。此运算符适用于以下用例：

语法

** <array>
Copy

限制

  • 输入必须是常量值的数组，可以是字面量值的数组，也可以是表示字面量值数组的绑定变量。

  • 半结构化数组中的每个值都是 VARIANT 类型。VARIANT 值可以包含任何其他数据类型的值。对于存储在 VARIANT 值中的值，扩展运算符支持以下数据类型：

    • :doc:`数值 </sql-reference/data-types-numeric>`（例如，INTEGER 和 NUMERIC）

    • :doc:`字符串和二进制 </sql-reference/data-types-text>`（例如，VARCHAR 和 BINARY）

    • :doc:`逻辑 </sql-reference/data-types-logical>`（例如，BOOLEAN）

    • :doc:`日期和时间 </sql-reference/data-types-datetime>`（例如，DATE、TIME 和 TIMESTAMP）

  • 用 SQL 以外的语言编写的用户定义函数和存储过程不能使用扩展运算符。

  • 使用扩展运算符扩展非常大的数组可能会降低性能。

示例

一些示例使用下表中的数据：

创建表并插入数据：

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

以下示例使用扩展运算符。

在 IN 子句中扩展字面量值的数组

在之前创建的 spread_demo 表上的查询中使用扩展运算符扩展数字的数组：

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

使用扩展运算符扩展字符串的数组：

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

在混合使用 INTEGER 值和扩展数组值的查询中使用 IN 子句：

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

在系统定义的函数调用中扩展字面量值的数组

在对 COALESCE 函数的调用中扩展字符串的数组：

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

在对 GREATEST 函数的调用中扩展数字的数组：

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

在 SQL 用户定义函数中使用带有绑定变量的扩展运算符

创建使用扩展运算符的 SQL 用户定义函数。该函数将数组作为实参，然后扩展数组值以查询之前创建的 spread_demo 表：

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

使用函数查询表：

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

在 Snowflake Scripting 存储过程中将扩展运算符与绑定变量一起使用

创建使用扩展运算符的 Snowflake Scripting 存储过程。存储过程将数组作为实参，然后扩展绑定变量中的数组值以查询之前创建的 spread_demo 表：

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

调用存储过程：

CALL spread_sp_demo([2, 4]);
Copy
+------+------+
| COL1 | COL2 |
|------+------|
|    2 | b    |
|    4 | d    |
+------+------+
语言: 中文