Como usar as funções agregadas do BigQuery

Analisar grandes conjuntos de dados para obter insights pode ser uma tarefa assustadora. Com terabytes ou até petabytes de dados, é impossível revisar manualmente as linhas e colunas. É nesse ponto que a BigQuery funções agregadas são úteis.

O BigQuery é o data warehouse empresarial totalmente gerenciado do Google que permite executar consultas rápidas em grandes conjuntos de dados. Um dos maiores benefícios do BigQuery é sua capacidade de agregar dados, combinando informações de várias linhas para ajudá-lo a obter insights.

Com essas funções, você pode resumir e analisar facilmente grandes quantidades de dados.

Neste artigo, você aprenderá tudo o que precisa para começar a usar funções simples, como COUNT, SUM, AVG, MIN e MAX.

O que são funções agregadas no BigQuery?

As funções agregadas são um componente essencial do BigQuery. Essas funções poderosas permitem que você obtenha insights significativos de grandes conjuntos de dados, realizando cálculos em várias linhas de dados.

Com as funções de agregação, é possível combinar várias linhas de dados em um único resultado ou saída. Alguns exemplos incluem o cálculo de totais, médias, máximos ou mínimos em um intervalo de dados.

Não importa se você precisa somar a receita ao longo do tempo, encontrar o valor médio do pedido ou contar o número de clientes - as funções de agregação têm tudo o que você precisa. Aqui estão alguns exemplos dessas funções.

AVGRetorna a média de todos os valores não nulos.

COUNTRetorna o número total de linhas.

CONT.SERetorna o número de linhas que atendem a uma condição especificada.

MAXRetorna o valor máximo não nulo.

MINRetorna o valor mínimo não nulo.

SUMRetorna a soma de todos os valores não nulos.

Vamos dar uma olhada nessas funções agregadas e aprender como aproveitá-las ao máximo. Usaremos este conjunto de dados públicos que contém informações sobre toda a população mundial dividida por país, a partir do ano de 1960.

Funções agregadas do BigQuery: AVG

Vamos começar com a função AVG. Essa função retornará a média de todos os valores não nulos. Aqui está a sintaxe:

SELECT AVG(coluna)

FROM nome_da_tabela;

Vou escrever uma consulta para calcular a média da população desses seis países em 2000: China, Alemanha, Rússia, Japão, Índia e Brasil.

SELECT AVG(year_2000)

FROM `bigquery-public-data.world_bank_global_population.population_by_country`

WHERE country IN (‘China’, ‘Alemanha’, ‘Rússia’, ‘Japão’, ‘Índia’, ‘Brasil’)

LIMITE 100;

Vamos detalhar essa fórmula:

SELECT AVG(year_2000) - Esse comando especifica que queremos calcular a média da coluna especificada.

FROM `bigquery-public-data.world_bank_global_population.population_by_country` - Especifica a tabela que estamos consultando.

WHERE country IN ('China', 'Germany', 'Russia', 'Japan', 'India', 'Brazil'): esta cláusula WHERE filtra as linhas para manter só os países entre parênteses.

Quando executamos a consulta, o BigQuery retorna a média da população desses 6 países.

Funções agregadas do BigQuery: COUNT

Agora vamos dar uma olhada mais de perto na função COUNT. Quando executamos essa função, o BigQuery retorna o número total de linhas. Aqui está a sintaxe que usaremos:

SELECT COUNT(coluna)

FROM nome_da_tabela;

Executarei uma consulta simples para contar o número de linhas contidas na tabela. Usaremos a coluna “country” como referência.

SELECT COUNT(country)

FROM `bigquery-public-data.world_bank_global_population.population_by_country`

LIMITE 100;

Depois de executar essa consulta, o BigQuery retornou o número de linhas: 264.

Funções agregadas do BigQuery: COUNTIF

Com a função COUNTIF, o BigQuery retornará o número de linhas que atendem a uma determinada condição. Aqui está a sintaxe:

SELECT COUNTIF(condição)

FROM nome_da_tabela;

Digamos que eu queira que o BigQuery retorne o número de linhas em que a população é menor que 100.000 em um ano específico. Escreverei a consulta especificando a condição: a coluna ano_2000 é menor que 100.000.

Esta é a aparência da consulta:

SELECT COUNTIF(year_2000<100000)

FROM `bigquery-public-data.world_bank_global_population.population_by_country`

LIMITE 100;

Quando executo essa consulta, o BigQuery retorna o número de linhas que atendem a essa condição: 27.

Funções de agregação do BigQuery: MAX

Usando a função MAX, posso pedir ao BigQuery que retorne o valor máximo não nulo em uma coluna específica. Esta é a sintaxe que usaremos.

SELECT MAX(coluna)

FROM nome_da_tabela;

Escreverei uma consulta que retornará o valor máximo contido na coluna year_2000. Além disso, adicionarei uma cláusula WHERE para restringir a consulta a esses 6 países: China, Alemanha, Rússia, Japão, Índia e Brasil.

SELECT MAX(year_2000)

FROM `bigquery-public-data.world_bank_global_population.population_by_country`

WHERE country IN (‘China’,’Alemanha’,’Rússia’,’Japão’, ‘Índia’, ‘Brasil’)

LIMITE 100;

Quando executo a consulta, ela retorna o valor máximo dentro do intervalo especificado: 1,262,645,000.

Funções de agregação do BigQuery: MIN

Ao contrário da função MAX, a função MIN retornará o valor mínimo em uma coluna. Esta é a aparência da sintaxe.

SELECT MIN(coluna)

FROM nome_da_tabela;

Agora, vou substituir a função MAX pela MIN.

SELECT MIN(year_2000)

FROM `bigquery-public-data.world_bank_global_population.population_by_country`

WHERE country IN (‘China’, ‘Alemanha’, ’Rússia’, ’Japão’, ‘Índia’, ‘Brasil’)

LIMITE 100;

O BigQuery acabou de retornar o valor mínimo. A menor população está ao norte de 82 milhões.

Funções de agregação do BigQuery: SUM

Por fim, daremos uma olhada mais de perto na função SUM. Ao usar essa função, pedirei ao BigQuery que calcule a soma de todos os valores.

SELECT SUM(coluna)

FROM nome_da_tabela;

Aqui, escreverei uma consulta para calcular a soma da população dos mesmos 6 países.

SELECT SUM(year_2000)

FROM `bigquery-public-data.world_bank_global_population.population_by_country`

WHERE country IN (‘China’, ‘Alemanha’, ’Rússia’, ’Japão’, ‘Índia’, ‘Brasil’)

LIMITE 100;

Como resultado, a BigQuery calculou a população desses 6 países: mais de 2,7 bilhões.

Como usar as funções agregadas do BigQuery

É isso aí! É assim que você pode usar as funções de agregação no BigQuery. Ao usar essas funções, você pode combinar e resumir dados de várias linhas em um único valor. Se, em vez disso, você quiser combinar tabelas, consulte este artigo sobre como usar Uniões do BigQuery.