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 pode 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 |
|
Utiliza uma sintaxe diferente das outras funções de agregação. |
|
Utiliza uma sintaxe diferente das outras funções de agregação. |
|
STDDEV e STDDEV_SAMP são aliases. |
|
Alias para VAR_POP. |
|
Alias para VAR_SAMP. |
|
Agregação bit a bit |
|
Agregação booleana |
|
Hash |
|
Agregação de dados semiestruturados |
|
Regressão linear |
|
Estatística e probabilidade |
|
Contagem de valores distintos |
|
Estimativa de cardinalidade . (usando HyperLogLog) |
|
Alias para HLL. |
|
Não é uma função de agregação; utiliza entrada escalar de HLL_ACCUMULATE ou HLL_COMBINE. |
|
Estimativa de similaridade . (usando MinHash) |
|
Alias para APPROXIMATE_SIMILARITY. |
|
Estimativa de frequência . (usando Space-Saving) |
|
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) |
|
Não é uma função de agregação; utiliza entrada escalar de APPROX_PERCENTILE_ACCUMULATE ou APPROX_PERCENTILE_COMBINE. |
|
Utilitários de agregação |
|
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. |
|
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:
Crie uma tabela e preencha-a com valores:
CREATE TABLE simple (x INTEGER, y INTEGER);
INSERT INTO simple (x, y) VALUES
(10, 20),
(20, 44),
(30, 70);
Consultar a tabela:
SELECT x, y
FROM simple
ORDER BY x,y;
+----+----+
| X | Y |
|----+----|
| 10 | 20 |
| 20 | 44 |
| 30 | 70 |
+----+----+
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 |
+---------------+
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 |
+--------+
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;
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 OR REPLACE TABLE test_null_aggregate_functions (x INT, y INT);
INSERT INTO test_null_aggregate_functions (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.
Consultar a tabela:
SELECT COUNT(x, y) FROM test_null_aggregate_functions;
+-------------+
| COUNT(X, Y) |
|-------------|
| 1 |
+-------------+
Se SUM for chamado com uma expressão que faz referência a duas ou mais colunas, e se uma ou mais dessas colunas for NULL, então a expressão é avaliada como NULL, e a linha é ignorada:
SELECT SUM(x + y) FROM test_null_aggregate_functions;
+------------+
| SUM(X + Y) |
|------------|
| 3 |
+------------+
Este 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 test_null_aggregate_functions
GROUP BY x, y;
+-------+-------+
| X_COL | Y_COL |
|-------+-------|
| 1 | 2 |
| 3 | NULL |
| NULL | 6 |
| NULL | NULL |
+-------+-------+