As funções de agregação calculam um único resultado a partir do conjunto de valores da entrada. [88] As funções nativas de agregação de uso geral estão listadas na Tabela 9.62, enquanto as funções de agregação para estatísticas estão listadas na Tabela 9.63. As funções de agregação para conjunto ordenado estão listadas na Tabela 9.64, enquanto as funções de agregação para conjunto hipotético estão listadas na Tabela 9.65. As operações de agrupamento, que são intimamente ligadas às funções de agregação, estão listadas na Tabela 9.66. As considerações especiais de sintaxe para funções de agregação são explicadas na Seção 4.2.7. Veja a Seção 2.7 para obter informações introdutórias adicionais.
As funções de agregação que oferecem suporte ao Modo Parcial estão qualificadas para participar de várias otimizações, como agregação paralela.
Embora todas os agregações abaixo aceitem a cláusula opcional
ORDER BY
(conforme delineado na Seção 4.2.7),
esta cláusula foi adicionada apenas às agregações cuja saída é
afetada pela ordenação.
Tabela 9.62. Funções de agregação de propósito geral
Função Descrição | Modo parcial |
|---|---|
Retorna um valor arbitrário dentre os valores de entrada não nulos. | Sim |
Coleta todos os valores de entrada, incluindo valores nulos, em uma matriz. | Sim |
Concatena todas as matrizes da entrada em uma matriz de dimensão superior. (Todas as entradas devem ter o mesmo número de dimensões, e não podem ser vazias ou nulas.) | Sim |
|
Calcula a média (média aritmética) de todos os valores não nulos da entrada. | Sim |
Calcula o | Sim |
Calcula o | Sim |
Calcula o | Sim |
Retorna verdade se todos os valores de entrada não nulos forem verdade, caso contrário, retorna falso. | Sim |
|
Retorna verdade se qualquer valor não nulo da entrada for verdade, caso contrário retorna falso. | Sim |
|
Calcula o número de linhas da entrada. | Sim |
Calcula o número de linhas da entrada nas quais o valor da entrada não é nulo. | Sim |
|
O equivalente do padrão SQL para a função
| Sim |
Coleta todos os valores de entrada, incluindo valores nulos,
em uma matriz JSON.
Os valores são convertidos para JSON segundo
as funções | Não |
Coleta todos os valores de entrada, ignorando os valores nulos,
em uma matriz JSON.
Os valores são convertidos para JSON segundo
as funções | Não |
Se comporta da mesma maneira que a função
| Não |
Se comporta da mesma maneira que a função
| Não |
Coleta todos os pares chave/valor em um objeto
JSON.
Os argumentos chave são convertidos em texto;
os argumentos de valor são convertidos conforme as funções
| Não |
Coleta todos os pares chave/valor em um objeto
JSON.
Os argumentos chave são convertidos em texto;
os argumentos de valor são convertidos conforme as funções
| Não |
Coleta todos os pares chave/valor em um objeto
JSON.
Os argumentos chave são convertidos em texto;
os argumentos de valor são convertidos conforme as funções
| Não |
Coleta todos os pares chave/valor em um objeto
JSON.
Os argumentos chave são convertidos em texto;
os argumentos de valor são convertidos conforme as funções
| Não |
Calcula o maior dos valores não nulos da entrada.
Disponível para qualquer tipo de dados numérico, cadeia de
caracteres, data e hora ou enum, assim como para os tipo de dados
| Sim |
Calcula o menor dos valores não nulos da entrada.
Disponível para qualquer tipo de dados numérico, cadeia de
caracteres, data e hora ou enum, assim como para os tipo de dados
| Sim |
Calcula a união dos valores não nulos da entrada. | Não |
Calcula a interseção dos valores não nulos da entrada. | Não |
Concatena os valores não nulos da entrada em uma cadeia de caracteres.
Cada valor após o primeiro é precedido pelo delimitador
( | Sim |
|
Calcula a soma dos valores não nulos da entrada. | Sim |
Concatena os valores de entrada XML não nulos. (veja a Seção 9.15.1.8). | Não |
Deve-se notar que, exceto para count, estas
funções retornam o valor nulo quando nenhuma linha é selecionada.
Em particular, sum de nenhuma linha retorna nulo,
e não zero como se poderia esperar, e array_agg
retorna nulo, em vez de uma matriz vazia, quando não há linhas na
entrada. A função coalesce pode ser usada para
substituir nulo por zero, ou por uma matriz vazia, quando for necessário.
As funções de agregação array_agg,
json_agg, jsonb_agg,
json_agg_strict, jsonb_agg_strict,
json_object_agg, jsonb_object_agg,
json_object_agg_strict, jsonb_object_agg_strict,
json_object_agg_unique, jsonb_object_agg_unique,
json_object_agg_unique_strict,
jsonb_object_agg_unique_strict,
string_agg e xmlagg,
assim como as funções de agregação semelhantes definidas pelo usuário,
produzem valores de resultado muito diferentes dependendo
da ordem dos valores da entrada.
Esta ordem não é especificada por padrão, mas pode ser controlada
colocando uma cláusula ORDER BY dentro da chamada
de agregação, conforme descrito na Seção 4.2.7.
Como alternativa, fornecer os valores da entrada a partir de uma
subconsulta ordenada geralmente funciona. Por exemplo:
SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;
Esteja ciente que esta abordagem pode falhar se o nível da consulta externa contiver processamento adicional, como uma junção, porque isto pode fazer com que a saída da subconsulta seja reordenada antes que a agregação seja computada.
As funções de agregação booleanas bool_and e
bool_or correspondem às agregações do padrão
SQL every e
any ou some.
O PostgreSQL oferece suporte a
every, mas não a any
ou some, porque há uma ambiguidade incorporada
na sintaxe padrão:
SELECT b1 = ANY((SELECT b2 FROM t2 ...)) FROM t1 ...;
Aqui ANY pode ser considerada como
introdução a uma subconsulta, ou como uma função de agregação,
se a subconsulta retornar uma linha com valor booleano.
Assim, não pode ser dado o nome padrão a estas agregações.
Os usuários acostumados a trabalhar com outros sistemas de
gerenciamento de banco de dados SQL podem ficar
desapontados com o desempenho da agregação count
quando aplicado à tabela inteira. Uma consulta como:
SELECT count(*) FROM alguma_tabela;
exigirá um esforço proporcional ao tamanho da tabela: O PostgreSQL precisará varrer a tabela inteira, ou a totalidade de um índice que inclua todas as linhas da tabela.
A Tabela 9.63 descreve as
funções de agregação normalmente usadas em análise estatística
[89]
[90].
(Estão separadas apenas para evitar desordenar a lista das agregações
mais usadas.) As funções descritas como aceitando
tipo_numérico estão disponíveis para
os tipos de dados smallint, integer,
bigint, numeric, real,
e double precision.
Onde a descrição menciona N, significa o
número de linhas da entrada para as quais todas as expressões da
entrada não são nulas.
Em todos os casos, será retornado nulo se a computação não fizer
sentido, por exemplo, quando N for zero.
Tabela 9.63. Funções de agregação de estatísticas
Função Descrição | Modo Parcial |
|---|---|
Calcula o coeficiente de correlação. [a]. | Sim |
Calcula a covariância da população. [b]. | Sim |
Calcula a covariância da amostra. [c]. | Sim |
Calcula a média da variável independente,
| Sim |
Calcula a média da variável dependente,
| Sim |
Calcula o número de linhas onde as duas entradas não são nulas. [f]. | Sim |
Calcule a intersecção com o eixo y da equação de regressão
linear por mínimos quadrados determinada pelos pares de valores
( | Sim |
Calcula o quadrado do coeficiente de correlação. [h]. | Sim |
Calcula a inclinação da linha da equação de ajuste de mínimos
quadrados lineares determinada pelos pares de valores
( | Sim |
Calcula a “soma dos quadrados” da variável independente,
| Sim |
Calcula a “soma dos produtos” de variáveis
independentes multiplicadas por variáveis dependentes,
| Sim |
Calcula a “soma dos quadrados” da variável dependente,
| Sim |
Este é um alias histórico para a função
| Sim |
Calcula o desvio padrão da população para os valores de entrada. [k]. | Sim |
Calcula o desvio padrão da amostra dos valores de entrada. [l]. | Sim |
Este é um alias histórico para a função
| Sim |
Calcula a variância da população para os valores de entrada (quadrado do desvio padrão da população). [m]. | Sim |
Calcula a variância da amostra para os valores de entrada (quadrado do desvio padrão da amostra). [n]. | Sim |
[a]
[b]
[c]
[d]
[e]
[f]
[g]
[h]
[i]
[j]
[k]
[l]
[m]
[n]
| |
A Tabela 9.64 descreve algumas
funções de agregação que usam a sintaxe de
agregação para conjunto ordenado.
Estas funções são algumas vezes chamadas de funções de
“distribuição inversa”.
Sua entrada de agregação é introduzida pela cláusula
ORDER BY, e também podem receber um
argumento direto, que não é agregação, mas é
calculado apenas uma vez.
Todas estas funções ignoram os valores nulos em sua entrada de agregação.
Para as funções que recebem o parâmetro fraction
(fração) o valor da fração deve estar entre 0 e 1; senão é gerado um erro.
Entretanto, um valor nulo para fraction
simplesmente produz um resultado nulo.
Tabela 9.64. Funções de agregação de conjunto ordenado
Cada uma das agregações para “conjunto hipotético”
listadas na Tabela 9.65 está
associada a uma função de janela com o mesmo nome definida na
Seção 9.22.
Em cada caso, o resultado da agregação é o valor que a função de janela
associada teria retornado para a linha “hipotética”,
construída a partir de args, se tal linha
tivesse sido adicionada ao grupo ordenado de linhas representado
por sorted_args.
Para cada uma dessas funções, a lista de argumentos diretos fornecida
em args deve corresponder ao número e tipos
de argumentos de agregação fornecidos em
sorted_args.
Ao contrário da maioria das agregações nativas, estas agregações não
são estritas, ou seja, não descartam linhas de entrada contendo nulos.
Os valores nulos são ordenados segundo a regra especificada na
cláusula ORDER BY.
Tabela 9.65. Funções de agregação de conjunto hipotético
Tabela 9.66. Operações de agrupamento
As operações de agrupamento descritas na
Tabela 9.66 são usados em conjunto
com conjuntos de agrupamento (veja a
Seção 7.2.4) para distinguir as linhas
de resultados.
Os argumentos para a função GROUPING não são
realmente avaliados, mas devem corresponder exatamente às expressões
fornecidas na cláusula GROUP BY do nível de
consulta associado.
Por exemplo:
SELECT * FROM items_sold;
make | model | sales -------+-------+------- Foo | GT | 10 Foo | Tour | 20 Bar | City | 15 Bar | Sport | 5 (4 linhas)
SELECT make, model, GROUPING(make,model), sum(sales) FROM items_sold GROUP BY ROLLUP(make,model);
make | model | grouping | sum
-------+-------+----------+-----
Foo | GT | 0 | 10
Foo | Tour | 0 | 20
Bar | City | 0 | 15
Bar | Sport | 0 | 5
Foo | | 1 | 30
Bar | | 1 | 20
| | 3 | 50
(7 linhas)
Acima, o valor de grouping igual a
0 nas quatro primeiras linhas mostra que elas
foram agrupadas normalmente, pelas duas colunas de agrupamento.
O valor 1 indica que model
não foi agrupado nas duas penúltimas linhas,
e o valor 3 indica que nem make,
nem model, foram agrupadas na última linha
(que é, portanto, uma agregação sobre todas as linhas da entrada).
Exemplo 9.6. Exemplo do tradutor
Neste exemplo é criada uma tabela contendo 400 valores aleatórios
entre 0 e 999 e usadas funções de agregação de propósito geral
e funções de agregação para estatísticas sobre os valores da tabela.
Como os valores são aleatórios, se este exemplo for repetido os
resultados obtidos serão um pouco diferentes, exceto para
count.
-- Função para retornar uma matriz contendo o
-- número especificado de valores entre 0 e 999.
CREATE OR REPLACE FUNCTION amostra (n int)
RETURNS int[] LANGUAGE plpgsql AS $$
DECLARE
i int;
matriz int[];
BEGIN
matriz = array_fill(0, array[n]);
FOR i IN 1..n LOOP
matriz[i] = floor(random()*1000)::int;
END LOOP;
RETURN matriz;
END $$;
CREATE FUNCTION
-- Criação da tabela temporária de amostra contendo 400 valores CREATE TEMPORARY TABLE amostra AS SELECT unnest FROM UNNEST(amostra(400));
SELECT 400
-- Funções de agregação de propósito geral SELECT avg(unnest), min(unnest), max(unnest), count(unnest) FROM amostra;
avg | min | max | count
----------------------+-----+-----+-------
496.2275000000000000 | 1 | 996 | 400
(1 linha)
-- Funções de agregação de estatísticas (desvio padrão) SELECT stddev_pop(unnest), stddev_samp(unnest) FROM amostra;
stddev_pop | stddev_samp
------------------+------------------
291.513139916111 | 291.878216001452
(1 linha)
-- Funções de agregação de estatísticas (variância) SELECT var_pop(unnest), var_samp(unnest) FROM amostra;
var_pop | var_samp
--------------------+--------------------
84979.910743750000 | 85192.892976190476
(1 linha)
Exemplo 9.7. Exemplo do tradutor
Este exemplo mostra o uso das funções de agregação disponíveis
para cadeias de caracteres string_agg,
array_agg, json_agg e
jsonb_agg.
No último exemplo foi usado DISTINCT para
eliminar os valores repetidos.
CREATE VIEW nomes(nome) AS WITH nomes AS (
VALUES ('José'), ('João'), ('Maria'), ('Manoel'), ('João'), ('Jose')
) SELECT * FROM nomes;
CREATE VIEW
SELECT * FROM nomes;
nome -------- José João Maria Manoel João Jose (6 linhas)
SELECT string_agg(nome, ';') FROM nomes;
string_agg
----------------------------------
José;João;Maria;Manoel;João;Jose
(1 linha)
SELECT array_agg(nome) FROM nomes;
array_agg
------------------------------------
{José,João,Maria,Manoel,João,Jose}
(1 linha)
SELECT json_agg(nome) FROM nomes;
json_agg
-----------------------------------------------------
["José", "João", "Maria", "Manoel", "João", "Jose"]
(1 linha)
SELECT jsonb_agg(nome) FROM nomes;
jsonb_agg
-----------------------------------------------------
["José", "João", "Maria", "Manoel", "João", "Jose"]
(1 linha)
SELECT jsonb_agg(DISTINCT nome) FROM nomes;
jsonb_agg
---------------------------------------------
["João", "Jose", "José", "Manoel", "Maria"]
(1 linha)
[88]
Oracle Aggregate Functions: As funções de agregação
retornam uma única linha de resultado com base em grupos de linhas,
em vez de uma linha apenas. As funções de agregação podem aparecer
em listas de seleção e em cláusulas ORDER BY e
HAVING. (N. T.)
[89] As funções estatísticas calculam valores relacionados a distribuições estatísticas e probabilidade, como desvio padrão e número de permutações.(Microsoft Learn) (N. T.)
[90] O que são as variáveis dependentes, independentes e controladas? (Science19.com) (N. T.)