Funções de agregação¶

Funções de agregação operam em valores de várias linhas para realizar cálculos matemáticos como soma, média, contagem, valores mínimos/máximos, desvio padrão e estimativa, assim como algumas operações não matemáticas.

Uma função de agregação toma várias linhas (na verdade, zero, uma ou mais linhas) como entrada e produz uma única saída. Em contraste, as funções escalares tomam uma linha como entrada e produzem uma linha (um valor) como saída.

Uma função de agregação sempre retorna exatamente uma linha, mesmo quando a entrada contém zero linhas. Normalmente, se a entrada contiver zero linhas, a saída será NULL. Entretanto, uma função de agregação poderia retornar 0, uma cadeia de caracteres vazia ou algum outro valor quando passadas zero linhas.

Neste tópico:

Lista de funções (por subcategoria)¶

Nome da função	Notas
Agregação geral
ANY_VALUE
AVG
CORR
COUNT
COUNT_IF
COVAR_POP
COVAR_SAMP
LISTAGG
MAX
MAX_BY
MEDIAN
MIN
MIN_BY
MODE
PERCENTILE_CONT	Utiliza uma sintaxe diferente das outras funções de agregação.
PERCENTILE_DISC	Utiliza uma sintaxe diferente das outras funções de agregação.
STDDEV, STDDEV_SAMP	STDDEV e STDDEV_SAMP são aliases.
STDDEV_POP
SUM
VAR_POP
VAR_SAMP
VARIANCE_POP	Alias para VAR_POP.
VARIANCE , VARIANCE_SAMP	Alias para VAR_SAMP.
Agregação bit a bit
BITAND_AGG
BITOR_AGG
BITXOR_AGG
Agregação booleana
BOOLAND_AGG
BOOLOR_AGG
BOOLXOR_AGG
Hash
HASH_AGG
Agregação de dados semiestruturados
ARRAY_AGG
OBJECT_AGG
Regressão linear
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY
Estatística e probabilidade
KURTOSIS
SKEW
Contagem de valores distintos
ARRAY_UNION_AGG
ARRAY_UNIQUE_AGG
BITMAP_BIT_POSITION
BITMAP_BUCKET_NUMBER
BITMAP_COUNT
BITMAP_CONSTRUCT_AGG
BITMAP_OR_AGG
Estimativa de cardinalidade . (usando HyperLogLog)
APPROX_COUNT_DISTINCT	Alias para HLL.
HLL
HLL_ACCUMULATE
HLL_COMBINE
HLL_ESTIMATE	Não é uma função de agregação; utiliza entrada escalar de HLL_ACCUMULATE ou HLL_COMBINE.
HLL_EXPORT
HLL_IMPORT
Estimativa de similaridade . (usando MinHash)
APPROXIMATE_JACCARD_INDEX	Alias para APPROXIMATE_SIMILARITY.
APPROXIMATE_SIMILARITY
MINHASH
MINHASH_COMBINE
Estimativa de frequência . (usando Space-Saving)
APPROX_TOP_K
APPROX_TOP_K_ACCUMULATE
APPROX_TOP_K_COMBINE
APPROX_TOP_K_ESTIMATE	Não é uma função de agregação; utiliza entrada escalar de APPROX_TOP_K_ACCUMULATE ou APPROX_TOP_K_COMBINE.
Estimativa percentil . (usando t-Digest)
APPROX_PERCENTILE
APPROX_PERCENTILE_ACCUMULATE
APPROX_PERCENTILE_COMBINE
APPROX_PERCENTILE_ESTIMATE	Não é uma função de agregação; utiliza entrada escalar de APPROX_PERCENTILE_ACCUMULATE ou APPROX_PERCENTILE_COMBINE.
Utilitários de agregação
GROUPING	Não é uma função de agregação, mas pode ser usada em conjunto com funções de agregação para determinar o nível de agregação de uma linha produzida por uma consulta GROUP BY.
GROUPING_ID	Alias para GROUPING.

Exemplo introdutório¶

O exemplo a seguir ilustra a diferença entre uma função de agregação (AVG) e uma função escalar (COS). A função escalar retorna uma linha de saída para cada linha de entrada, enquanto a função de agregação retorna uma linha de saída para múltiplas linhas de entrada:

A tabela contém os seguintes dados:

SELECT x, y 
    FROM simple
    ORDER BY x,y;
+----+----+
|  X |  Y |
|----+----|
| 10 | 20 |
| 20 | 44 |
| 30 | 70 |
+----+----+

Copy

A função escalar retorna uma linha de saída para cada linha de entrada.

SELECT COS(x)
    FROM simple
    ORDER BY x;
+---------------+
|        COS(X) |
|---------------|
| -0.8390715291 |
|  0.4080820618 |
|  0.1542514499 |
+---------------+

Copy

A função de agregação retorna uma linha de saída para múltiplas linhas de entrada:

SELECT SUM(x)
    FROM simple;
+--------+
| SUM(X) |
|--------|
|     60 |
+--------+

Copy

Funções de agregação e valores NULL¶

Algumas funções de agregação ignoram os valores NULL. Por exemplo, AVG calcula a média dos valores 1, 5 e NULL para ser 3, com base na seguinte fórmula:

(1 + 5) / 2 = 3

Tanto no numerador quanto no denominador, somente os dois valores não NULL são utilizados.

Se todos os valores passados para a função de agregação forem NULL, então a função de agregação retorna NULL.

Algumas funções de agregação podem receber mais de uma coluna. Por exemplo:

SELECT COUNT(col1, col2) FROM table1;

Copy

Nesses casos, a função de agregação ignora uma linha se alguma coluna individual for NULL.

Por exemplo, na consulta seguinte, COUNT retorna 1, não 4, porque três das quatro linhas contêm pelo menos um valor NULL nas colunas selecionadas:

Crie uma tabela e preencha-a com valores:

CREATE TABLE t (x INT, y INT);
INSERT INTO t (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.

Copy

Consultar a tabela:

SELECT COUNT(x, y) FROM t;
+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

Copy

Da mesma forma, se SUM é chamado com uma expressão que faz referência a duas ou mais colunas, e se uma ou mais dessas colunas é NULL, a expressão avalia para NULL, e a linha é ignorada:

SELECT SUM(x + y) FROM t;
+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+

Copy

Observe que esse comportamento difere do comportamento de GROUP BY, que não descarta linhas quando algumas colunas são NULL:

SELECT x AS X_COL, y AS Y_COL FROM t GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+

Copy