Sabemos que para fazer consultar no BQ são necessárias algumas querys. Desse modo, listamos aqui as principais que temos visualizado em nossa base
1. Consulta simples
SELECT
*
FROM `nomedatabela`
SELECT
day,
account_id,
account_name
FROM `nomedatabela`
No primeiro caso, aparecerão todos os registros da tabela, sem restrição de campos.
Já no segundo, aparecerão somente as colunas selecionadas, ou seja, day, account_id e account_name.
2. Agrupando registros
SELECT
account_id,
account_name,
SUM(cost) AS cost
FROM `nomedatabela`
GROUP BY account_id, account_name
SUM(nomedacoluna) é utilizado para somar todos os valores encontrados na coluna especificada, por isso é necessário o GROUP BY indicando que os registros serão agrupados em um único resultado. Sempre que forem utilizadas funções de agregação, como é o caso do SUM, as outras colunas deverão ser agrupadas. Outro modo, mais simples até, de se escrever essa query, seria GROUP BY 1,2 referenciando a primeira e segunda coluna da query.
nomedacoluna AS nomedacolunarenomeada é utilizado para renomear as colunas. Você pode renomear qualquer coluna com qualquer nome. Nesse caso eu renomeei para o mesmo nome, mas eu poderia colocar um SUM(cost) AS investimento, por exemplo.
Você pode trabalhar com outras funções de agregação, como por exemplo:
AVG(cost) em que será retornada uma média de valores.
MAX(cost) retornará o valor máximo encontrado na coluna cost
COUNT(account_name) retornará o numero total de contas existentes na tabela (incluindo as repetições)
COUNT(DISTINCT account_name) vai contar quantas contas distintas a tabela possui
Supondo que os registros existentes de nomes de contas na tabela são:
1. Pareto
2. Pareto
3. Paretinho
Com o COUNT, ele retornará 3 como valor final e com o COUNT DISTINCT ele retornará 2
3. Ordenando e limitando registros
SELECT
account_name,
SUM(cost) AS cost
FROM `nomedatabela`
GROUP BY account_name
ORDER BY cost DESC
LIMIT 10
ORDER BY é utilizado para definir a partir de qual coluna você deseja ordenar os dados. Nesse exemplo, eu escolhi ordenar pelo cost, de forma decrescente.
Se você escrever somente ORDER BY nomedacoluna que, por padrão, será ordenado de forma crescente. Ele também pode ser especificado utilizando o ASC. Ou seja, ASC para crescente e DESC para decrescente.
O LIMIT é utilizado para limitar o número de linhas que será retornado. No exemplo, retornará apenas 10 linhas. Você pode limitar pelo número que quiser. Esse caso é interessante se você quiser, por exemplo, visualizar os 10 clientes que mais investiram historicamente, ordenando do maior para o menor.
4 Filtrando
4.1 Filtrando Registros
SELECT
*
FROM `nomedatabela`
WHERE day = "2021-12-01"
O WHERE funciona como um filtro, em que dados são restringidos de acordo com o que será informado.
Exemplos:
WHERE day = “2021-12-01” para uma data específica
WHERE day > “2021-12-01” para todas as datas maiores que a especificada
WHERE day > = “2021-12-01” para todas as datas maiores OU igual a especificada
WHERE day BETWEEN “2021-12-01” AND “2021-12-15” para todas as datas entre os dias especificados. Nesse caso, também está incluído tanto o dia 01 quanto o dia 15
* Uma prática recomendada ao realizar consultas no BigQuery é sempre filtrá-las por um período específico. Isso reduz a quantidade de dados que serão processados, contribuindo para o uso eficiente da nossa cota mensal de processamento.
SELECT
*
FROM `nomedatabela`
WHERE LOWER(account_name) LIKE "%pareto%"
AND day = "2021-12-01"
SELECT
*
FROM `nomedatabela`
WHERE LOWER(account_name) LIKE "%pareto%"
OR LOWER(account_name) LIKE "%paretinho%"
WHERE account_name= “nomeexatodaconta” ou
WHERE LOWER(account_name) = "nomeexatodaconta" ou
WHERE LOWER(account_name) LIKE “%pareto%”
Se você souber o nome exato do que quer procurar, pode usar essa primeira opção, mas se não tiver certeza, pode utilizar essa segunda opção, em que o LOWER transformará todas os caracteres em letras minúsculas (sem isso, haverá diferenciação entre Pareto e pareto, por exemplo), ou ainda a terceira opção em que o comando LIKE “%termo%” realiza uma busca em todos os registros que possuem o termo especificado, independente da sua posição. Caso a % só apareça somente antes (LIKE "%termo") retornará todos os registros que terminem com a palavra. E se for após (LIKE "termo%") retornará somente os registros que iniciem com a palavra.
Você pode filtrar adicionando mais de uma regra, utilizando o AND, para acrescentar mais uma condição de inclusão, ou seja, os resultados exibidos obrigatoriamente atenderão a todos os critérios selecionados. Ou o OR, utilizado para acrescentar uma condição alternativa, ou seja, o resultado será exibido caso atenda ao primeiro critério OU ao segundo critério.
4.2. Filtrando por nulos
SELECT
*
FROM `nomedatabela`
WHERE campaign_name IS NOT NULL
Essa consulta retornará todos os registros que não possuam o nome de campanha nulo. Também pode ser utilizada com o IS NULL para retornar todos os registros que possuam nomes nulos.
5. Juntando duas tabelas
5.1. JOIN
SELECT
b.pareto_id,
a.account_id,
a.account_name
FROM `nomedatabela` AS a
INNER JOIN `nomedatabelapareto` AS b ON a.account_id = b.account_id
Os JOINS são utilizados para juntar duas tabelas diferentes. Nesse caso, com o INNER JOIN, os registros sem as correspondências não serão incluídos.
Também existe o LEFT JOIN, que retorna todos os registros da primeira tabela selecionada, mesmo sem correspondências com a segunda tabela, e as correspondências. O RIGHT JOIN, que funciona da mesma forma que o LEFT, porém retornando todos os registros da segunda tabela. E o FULL OUTER JOIN ou FULL JOIN que retorna todos os registros de ambas as tabelas e suas correspondências
5.2. union all
Outra forma de juntar tabelas é utilizando o UNION ALL. Essa junção só poderá acontecer caso todas as tabelas possuam as mesmas colunas selecionadas
SELECT
account_id,
account_name,
report_type
FROM `nomedatabela`
UNION ALL
SELECT
account_id,
account_name,
report_type
FROM `nomedatabela2`
6. Criando condições para registros
O CASE WHEN é utilizado para definir condições que serão testadas e retornarão um resultado determinado caso atenda aos requisitos.
SELECT
nome_produto,
CASE
WHEN LOWER(nome_produto) LIKE "%blusa%" THEN "Blusa"
WHEN LOWER(nome_produto) LIKE "%short%" THEN "Short"
WHEN LOWER(nome_produto) LIKE "%biquini%" THEN "Praia"
WHEN LOWER(nome_produto) LIKE "%sunga%" THEN "Praia"
ELSE "Sem categoria definida"
END AS categoria
FROM `nomedatabela`
No exemplo acima, ela funcionaria da seguinte forma:
caso o nome do produto contenha blusa, então retorne "Blusa" na coluna categoria
caso o nome do produto contenha short, então retorne "Short" na coluna categoria
caso o nome do produto contenha biquini, então retorne "Praia" na coluna categoria
caso o nome do produto contenha sunga, então retorne "Praia" na coluna categoria
caso não atenda a nenhuma das condições acima, então retorne "Sem categoria definida" (uma outra opção também, é retornar a própria coluna - ELSE nome_produto, ou até mesmo um ELSE NULL)
O END é necessário para fechar o conjunto de condições, e o AS, na sequencia, é utilizado para definir o nome da coluna em que os resultados dessa condição serão mostrados.
Agora vamos supor que já exista uma coluna chamada categoria, porém, não estamos satisfeitos com o que ela está retornando, e queremos criar uma nova categoria, personalizada. Nesse caso, uma alternativa seria utilizar o EXCEPT
SELECT
* EXCEPT (categoria),
CASE
WHEN LOWER(nome_produto) LIKE "%blusa%" THEN "Blusa"
WHEN LOWER(nome_produto) LIKE "%short%" THEN "Short"
WHEN LOWER(nome_produto) LIKE "%biquini%" THEN "Praia"
WHEN LOWER(nome_produto) LIKE "%sunga%" THEN "Praia"
ELSE "Sem categoria definida"
END AS categoria
FROM `nomedatabela`