Categorias:

Funções de dados semiestruturados e estruturados (Ordem superior)

REDUCE

Reduz uma matriz a um único valor com base na lógica de uma expressão lambda.

A função REDUCE recebe uma matriz, um valor acumulador inicial e uma função lambda. Ele aplica a função lambda a cada elemento da matriz, atualizando o acumulador com cada resultado. Após processar todos os elementos, REDUCE retorna o valor final do acumulador.

Consulte também:

Usar funções lambda em dados com funções de ordem superior do Snowflake

Sintaxe

REDUCE( <array> , <init> , <lambda_expression> )

Argumentos

array

A matriz com os elementos a serem reduzidos. A matriz pode ser semiestruturada ou estruturada.

init

O valor inicial do acumulador.

lambda_expression

Uma expressão lambda que define a lógica de redução em cada elemento da matriz.

A expressão lambda deve ser especificada na seguinte sintaxe:

<acc> [ <datatype> ] , <value> [ <datatype> ] -> <expr>

O argumento acc é o acumulador, e o argumento value é o elemento atual que está sendo processado na matriz.

Retornos

Esta função pode retornar um valor de qualquer tipo de dados.

Se a matriz de entrada estiver vazia, a função retornará o valor inicial do acumulador.

A função retorna NULL nestes casos:

  • A matriz de entrada é NULL.

  • O valor inicial do acumulador é NULL.

  • A função lambda retorna NULL.

Notas de uso

  • Quando o tipo de dados para um argumento value lambda é especificado explicitamente, o elemento da matriz é forçado para o tipo especificado antes da invocação do lambda. Para obter informações sobre coerção, consulte Conversão do tipo de dados.

  • A verificação de tipo impõe que o valor inicial do acumulador, o argumento lambda do acumulador e o valor de retorno da execução lambda tenham todos os mesmos tipos lógicos e físicos. Se a conversão for usada para atender a esse requisito, o maior tipo físico dos três será usado.

  • O argumento value pode ter valores NULL intermediários. Para obter um exemplo, consulte Como ignorar valores NULL em uma matriz.

Exemplos

Os exemplos a seguir usam a função REDUCE.

Cálculo da soma dos valores em uma matriz

Use a função REDUCE para retornar a soma dos valores em uma matriz e especificar 0 como o valor inicial do acumulador:

SELECT REDUCE([1,2,3],
              0,
              (acc, val) -> acc + val
       ) AS sum_of_values;

+---------------+
| SUM_OF_VALUES |
|---------------|
|             6 |
+---------------+

Este exemplo é o mesmo que o exemplo anterior, mas especifica uma matriz estruturada do tipo INT:

SELECT REDUCE([1,2,3]::ARRAY(INT),
              0,
              (acc, val) -> acc + val
       ) AS sum_of_values_structured;

+--------------------------+
| SUM_OF_VALUES_STRUCTURED |
|--------------------------|
|                        6 |
+--------------------------+

Use a função REDUCE para retornar a soma dos valores em uma matriz e especificar 10 como o valor inicial do acumulador:

SELECT REDUCE([1,2,3],
              10,
              (acc, val) -> acc + val
       ) AS sum_of_values_plus_10;

+-----------------------+
| SUM_OF_VALUES_PLUS_10 |
|-----------------------|
|                    16 |
+-----------------------+

Cálculo da soma do quadrado de cada valor em uma matriz

Use a função REDUCE para retornar a soma do quadrado de cada valor na matriz e especifique 0 como o valor inicial do acumulador:

SELECT REDUCE([1,2,3],
              0,
              (acc, val) -> acc + val * val
       ) AS sum_of_squares;

+----------------+
| SUM_OF_SQUARES |
|----------------|
|             14 |
+----------------+

Como ignorar valores NULL em uma matriz

Neste exemplo, o argumento array inclui valores NULL. Quando esta matriz é passada para a função REDUCE, o acumulador terá valores NULL intermediários.

Use a função REDUCE para retornar a soma dos valores na matriz e use a função ZEROIFNULL na lógica da expressão lambda para ignorar valores NULL na matriz. A expressão lambda usa a função ZEROIFNULL para processar cada valor na matriz usando a seguinte lógica:

  • Se val for NULL, o resultado da expressão lambda será acc + 0.

  • Se val não for NULL, o resultado da expressão lambda será acc + val.

Executar a consulta:

SELECT REDUCE([1,NULL,2,NULL,3,4],
              0,
              (acc, val) -> acc + ZEROIFNULL(val))
  AS sum_of_values_skip_null;

+-------------------------+
| SUM_OF_VALUES_SKIP_NULL |
|-------------------------|
|                      10 |
+-------------------------+

Geração de valores de cadeia de caracteres

Use a função REDUCE para retornar uma lista de valores de cadeia de caracteres concatenando cada valor na matriz:

SELECT REDUCE(['a', 'b', 'c'],
              '',
              (acc, val) -> acc || ' ' || val
       ) AS string_values;

+---------------+
| STRING_VALUES |
|---------------|
|  a b c        |
+---------------+

Como usar uma matriz para o acumulador

Use a função REDUCE junto com a função ARRAY_PREPEND na lógica da expressão lambda para retornar uma matriz que inverta a ordem da matriz de entrada:

SELECT REDUCE([1, 2, 3, 4],
              [],
              (acc, val) -> ARRAY_PREPEND(acc, val)
       ) AS reverse_order;

+---------------+
| REVERSE_ORDER |
|---------------|
| [             |
|   4,          |
|   3,          |
|   2,          |
|   1           |
| ]             |
+---------------+

Como usar lógica condicional

Use a função REDUCE junto com a função IFF na lógica da expressão lambda para executar uma ação baseada na lógica condicional semelhante a uma expressão if-then. Este exemplo usa a seguinte lógica na expressão lambda:

  • Se o valor da matriz for menor que sete, então eleve-o ao quadrado e adicione-o ao acumulador.

  • Se o valor da matriz for maior ou igual a sete, adicione-o ao acumulador sem elevá-lo ao quadrado.

SELECT REDUCE([5,10,15],
              0,
              (acc, val) -> IFF(val < 7, acc + val * val, acc + val)
       ) AS conditional_logic;

+-------------------+
| CONDITIONAL_LOGIC |
|-------------------|
|                50 |
+-------------------+

Redução de uma matriz de elementos em uma tabela para um único valor

Suponha que você tenha uma tabela chamada orders com as colunas order_id, order_date e order_detail. A coluna order_detail é uma matriz dos itens de linha, sua quantidade de compra e subtotal. A tabela contém duas linhas de dados. A seguinte instrução SQL cria esta tabela e insere as linhas:

CREATE OR REPLACE TABLE orders AS
  SELECT 1 AS order_id, '2024-01-01' AS order_date, [
    {'item':'UHD Monitor', 'quantity':3, 'subtotal':1500},
    {'item':'Business Printer', 'quantity':1, 'subtotal':1200}
  ] AS order_detail
  UNION
  SELECT 2 AS order_id, '2024-01-02' AS order_date, [
    {'item':'Laptop', 'quantity':5, 'subtotal':7500},
    {'item':'Noise-canceling Headphones', 'quantity':5, 'subtotal':1000}
  ] AS order_detail;

SELECT * FROM orders;

+----------+------------+-------------------------------------------+
| ORDER_ID | ORDER_DATE | ORDER_DETAIL                              |
|----------+------------+-------------------------------------------|
|        1 | 2024-01-01 | [                                         |
|          |            |   {                                       |
|          |            |     "item": "UHD Monitor",                |
|          |            |     "quantity": 3,                        |
|          |            |     "subtotal": 1500                      |
|          |            |   },                                      |
|          |            |   {                                       |
|          |            |     "item": "Business Printer",           |
|          |            |     "quantity": 1,                        |
|          |            |     "subtotal": 1200                      |
|          |            |   }                                       |
|          |            | ]                                         |
|        2 | 2024-01-02 | [                                         |
|          |            |   {                                       |
|          |            |     "item": "Laptop",                     |
|          |            |     "quantity": 5,                        |
|          |            |     "subtotal": 7500                      |
|          |            |   },                                      |
|          |            |   {                                       |
|          |            |     "item": "Noise-canceling Headphones", |
|          |            |     "quantity": 5,                        |
|          |            |     "subtotal": 1000                      |
|          |            |   }                                       |
|          |            | ]                                         |
+----------+------------+-------------------------------------------+

Use a função REDUCE para retornar a soma do subtotal de todos os itens em cada pedido:

SELECT order_id,
       order_date,
       REDUCE(o.order_detail,
              0,
              (acc, val) -> acc + val:subtotal
       ) AS subtotal_sum
  FROM orders o;

+----------+------------+--------------+
| ORDER_ID | ORDER_DATE | SUBTOTAL_SUM |
|----------+------------+--------------|
|        1 | 2024-01-01 |         2700 |
|        2 | 2024-01-02 |         8500 |
+----------+------------+--------------+

Use a função REDUCE para retornar uma lista dos itens vendidos em cada pedido:

SELECT order_id,
       order_date,
       REDUCE(o.order_detail,
              '',
              (acc, val) -> val:item || '\n' || acc
       ) AS items_sold
  FROM orders o;

+----------+------------+-----------------------------+
| ORDER_ID | ORDER_DATE | ITEMS_SOLD                  |
|----------+------------+-----------------------------|
|        1 | 2024-01-01 | Business Printer            |
|          |            | UHD Monitor                 |
|          |            |                             |
|        2 | 2024-01-02 | Noise-canceling Headphones  |
|          |            | Laptop                      |
|          |            |                             |
+----------+------------+-----------------------------+

Faça referência a uma coluna de tabela em uma expressão lambda para reduzir elementos de matriz em dados de tabela

Criar uma tabela com uma coluna do tipo ARRAY e outra coluna do tipo INT:

CREATE OR REPLACE TABLE reduce_column_ref_demo AS
  SELECT [ 1, 2, 3 ] AS col1, 0 AS col2
  UNION
  SELECT [ 1, 2, 3 ] AS col1, 10 AS col2;

SELECT * FROM reduce_column_ref_demo;

+------+------+
| COL1 | COL2 |
|------+------|
| [    |    0 |
|   1, |      |
|   2, |      |
|   3  |      |
| ]    |      |
| [    |   10 |
|   1, |      |
|   2, |      |
|   3  |      |
| ]    |      |
+------+------+

Use a função REDUCE para retornar a soma dos valores na matriz em cada linha adicionando o valor em col2 ao valor do acumulador:

SELECT REDUCE(col1,
              10,
              (acc, val) -> (acc + col2) + val
       ) AS reduce_col_ref
  FROM reduce_column_ref_demo;

+----------------+
| REDUCE_COL_REF |
|----------------|
|             16 |
|             46 |
+----------------+