Funções de agregação

A função de agregação resume as linhas de um grupo em um único valor. COUNT, MIN e MAX são exemplos de funções de agregação.

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM (SELECT NULL as fruit UNION ALL
      SELECT "apple" as fruit UNION ALL
      SELECT "pear" as fruit UNION ALL
      SELECT "orange" as fruit)

+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

Quando usados em conjunto com uma cláusula GROUP BY, os grupos resumidos costumam ter pelo menos uma linha. Quando o SELECT associado não tem nenhuma cláusula GROUP BY ou quando determinados modificadores de função de agregação filtram as linhas do grupo para resumi-lo, é possível que a função de agregação precise resumir um grupo vazio. Nesse caso, as funções COUNT e COUNTIF retornam 0, enquanto todas as outras funções agregadas retornam NULL.

As seções a seguir descrevem as funções de agregação compatíveis com o Cloud Spanner SQL.

ANY_VALUE

ANY_VALUE(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna expression para alguma linha escolhida do grupo. A escolha da linha é um processo não determinístico e não aleatório. Retorna NULL quando a entrada não produz linhas. Retorna NULL quando expression é NULL para todas as linhas no grupo.

ANY_VALUE se comporta como se IGNORE NULLS fosse especificado. As linhas para as quais expression é NULL não são consideradas e não serão selecionadas.

Tipos de argumentos compatíveis

Qualquer um

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

Corresponde aos tipos de dados de entrada.

Exemplos

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [HAVING {MAX | MIN} expression2])

Descrição

Retorna um ARRAY de valores expression.

Tipos de argumentos compatíveis

Todos os tipos de dados, exceto ARRAY.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. IGNORE NULLS ou RESPECT NULLS: se IGNORE NULLS for especificado, os valores NULL serão excluídos do resultado. Se RESPECT NULLS for especificado ou se nenhum for especificado, os valores NULL serão incluídos no resultado.
  3. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Ordem dos elementos na saída

A ordem dos elementos na saída não é determinista, o que significa que você pode receber um resultado diferente cada vez que usar essa função.

Tipos de dados retornados

ARRAY

Se houver zero linhas de entrada, essa função retornará NULL.

Exemplos

SELECT ARRAY_AGG(x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+
SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+
SELECT ARRAY_AGG(x IGNORE NULLS) AS array_agg
FROM UNNEST([NULL, 1, -2, 3, -2, 1, NULL]) AS x;

+-------------------+
| array_agg         |
+-------------------+
| [1, -2, 3, -2, 1] |
+-------------------+

ARRAY_CONCAT_AGG

ARRAY_CONCAT_AGG(expression [HAVING {MAX | MIN} expression2])

Descrição

Concatena elementos de expression do tipo ARRAY, retornando um único ARRAY como resultado. Essa função ignora matrizes de entrada NULL, mas respeita os elementos NULL em matrizes de entrada diferentes de NULL.

Tipos de argumentos compatíveis

ARRAY

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Ordem dos elementos na saída

A ordem dos elementos na saída não é determinista, o que significa que você pode receber um resultado diferente cada vez que usar essa função.

Tipos de dados retornados

ARRAY

Retorna NULL se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Exemplos

SELECT ARRAY_CONCAT_AGG(x) AS array_concat_agg FROM (
  SELECT [NULL, 1, 2, 3, 4] AS x
  UNION ALL SELECT NULL
  UNION ALL SELECT [5, 6]
  UNION ALL SELECT [7, 8, 9]
);

+-----------------------------------+
| array_concat_agg                  |
+-----------------------------------+
| [NULL, 1, 2, 3, 4, 5, 6, 7, 8, 9] |
+-----------------------------------+

AVG

AVG([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a média de valores de entrada NULL, ou NaN se a entrada tiver um NaN.

Tipos de argumentos compatíveis

Qualquer tipo de entrada numérica, como INT64. Para tipos de entrada de ponto flutuante, o resultado retornado não é determinista. Isso significa que o resultado pode ser diferente sempre que essa função for usada.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

Exemplos

SELECT AVG(x) as avg
FROM UNNEST([0, 2, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+
SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+

BIT_AND

BIT_AND([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Executa uma operação bit a bit AND em expression e retorna o resultado.

Tipos de argumentos compatíveis

  • INT64

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Executa uma operação bit a bit OR em expression e retorna o resultado.

Tipos de argumentos compatíveis

  • INT64

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Executa uma operação bit a bit XOR em expression e retorna o resultado.

Tipos de argumentos compatíveis

  • INT64

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+
SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 5678    |
+---------+
SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1. COUNT(*)

2. COUNT([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

  1. Retorna o número de linhas na entrada.
  2. Retorna o número de linhas com expression avaliado para qualquer valor diferente de NULL.

Tipos de argumentos compatíveis

expression pode ser qualquer tipo de dado. Se DISTINCT estiver presente, expression só poderá ser um tipo de dados agrupável.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT
  COUNT(*) AS count_star,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, 4, 5]) AS x;

+------------+--------------+
| count_star | count_dist_x |
+------------+--------------+
| 4          | 3            |
+------------+--------------+
SELECT COUNT(*) AS count_star, COUNT(x) AS count_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+
| count_star | count_x |
+------------+---------+
| 5          | 4       |
+------------+---------+

COUNTIF

COUNTIF([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a contagem de valores TRUE para expression. Retorna 0 se houver zero linhas de entrada ou se expression for avaliado como FALSE ou NULL para todas as linhas.

Tipos de argumentos compatíveis

BOOL

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INT64

Exemplos

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

LOGICAL_AND

LOGICAL_AND(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a lógica AND de todas as expressões diferentes de NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Tipos de argumentos compatíveis

BOOL

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

BOOL

Exemplos

SELECT LOGICAL_AND(x) AS logical_and FROM UNNEST([true, false, true]) AS x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a lógica OR de todas as expressões diferentes de NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas.

Tipos de argumentos compatíveis

BOOL

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

BOOL

Exemplos

SELECT LOGICAL_OR(x) AS logical_or FROM UNNEST([true, false, true]) AS x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna o valor máximo de expressões não NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas. Retorna NaN se a entrada contiver um NaN.

Tipos de argumentos compatíveis

Qualquer tipo de dado, exceto: ARRAY STRUCT

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

Igual ao tipo de dados usado como os valores da entrada.

Exemplos

SELECT MAX(x) AS max
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna o valor mínimo de expressões não NULL. Retorna NULL, se não houver nenhuma linha de entrada ou expression for avaliado como NULL para todas as linhas. Retorna NaN se a entrada contiver um NaN.

Tipos de argumentos compatíveis

Qualquer tipo de dado, exceto: ARRAY STRUCT

Cláusula opcional

HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte Cláusula HAVING MAX e HAVING MIN para detalhes.

Tipos de dados retornados

Igual ao tipo de dados usado como os valores da entrada.

Exemplos

SELECT MIN(x) AS min
FROM UNNEST([8, 37, 4, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter] [HAVING {MAX | MIN} expression2])

Descrição

A concatenação de valores não nulos retorna um valor STRING ou BYTES.

Se um delimiter for especificado, os valores concatenados serão separados por esse delimitador; caso contrário, uma vírgula será usada como um delimitador.

Tipos de argumentos compatíveis

STRING BYTES

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Ordem dos elementos na saída

A ordem dos elementos na saída não é determinista, o que significa que você pode receber um resultado diferente cada vez que usar essa função.

Tipos de dados retornados

STRING BYTES

Exemplos

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & pear & banana |
+-----------------------+

SUM

SUM([DISTINCT] expression [HAVING {MAX | MIN} expression2])

Descrição

Retorna a soma de valores não nulos.

Se a expressão for um valor de ponto flutuante, a soma não será determinista. Isso significa que o resultado pode ser diferente sempre que essa função for usada.

Tipos de argumentos compatíveis

Quaisquer tipos de dados numéricos compatíveis.

Cláusulas opcionais

As cláusulas são aplicadas na seguinte ordem:

  1. DISTINCT: cada valor distinto de expression é agregado apenas uma vez no resultado.
  2. HAVING MAX ou HAVING MIN: restringe o conjunto de linhas que a função agrega por um valor máximo ou mínimo. Consulte a cláusula HAVING MAX e HAVING MIN para mais detalhes.

Tipos de dados retornados

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

Casos especiais:

Retorna NULL, se a entrada contiver apenas NULLs.

Retorna NULL, se a entrada não contiver linhas.

Retorna Inf, se a entrada contiver Inf.

Retorna -Inf se a entrada contiver -Inf.

Retorna NaN se a entrada contiver um NaN.

Retorna NaN se a entrada contiver uma combinação de Inf e -Inf.

Exemplos

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+
SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+
SELECT SUM(x) AS sum
FROM UNNEST([]) AS x;

+------+
| sum  |
+------+
| NULL |
+------+

Cláusulas comuns

Cláusula HAVING MAX e HAVING MIN

A maioria das funções de agregação é compatível com duas cláusulas opcionais chamadas HAVING MAX e HAVING MIN, que restringem o conjunto de linhas que uma função agrega a linhas que têm um valor máximo ou mínimo em uma coluna específica. A sintaxe geralmente tem esta aparência:

aggregate_function(expression1 [HAVING {MAX | MIN} expression2])
  • HAVING MAX: restringe o conjunto de linhas que a função agrega àquelas com um valor para expression2 igual ao valor máximo para expression2 no grupo. O valor máximo é igual ao resultado de MAX(expression2).
  • HAVING MIN: restringe o conjunto de linhas que a função agrega àquelas com um valor para expression2 igual ao valor mínimo para expression2 no grupo. O valor mínimo é igual ao resultado de MIN(expression2).

Essas cláusulas ignoram os valores NULL ao calcular o valor máximo ou mínimo, a menos que expression2 seja avaliado como NULL para todas as linhas.

Essas cláusulas não são compatíveis com os tipos de dados a seguir: ARRAY STRUCT

Exemplo

Neste exemplo, a chuva média é retornada para o ano mais recente, 2001.

WITH Precipitation AS
 (SELECT 2001 as year, 'spring' as season, 9 as inches UNION ALL
  SELECT 2001, 'winter', 1 UNION ALL
  SELECT 2000, 'fall', 3 UNION ALL
  SELECT 2000, 'summer', 5 UNION ALL
  SELECT 2000, 'spring', 7 UNION ALL
  SELECT 2000, 'winter', 2)
SELECT AVG(inches HAVING MAX year) as average FROM Precipitation

+---------+
| average |
+---------+
| 5       |
+---------+

Primeiro, a consulta recebe as linhas com o valor máximo na coluna year. Há dois:

+------+--------+--------+
| year | season | inches |
+------+--------+--------+
| 2001 | spring | 9      |
| 2001 | winter | 1      |
+------+--------+--------+

Por fim, a consulta faz uma média dos valores na coluna inches (9 e 1) com este resultado:

+---------+
| average |
+---------+
| 5       |
+---------+