Google BigQuery: dominando funções lógicas e numéricas

Ajustes da base de dados - Apresentação

Olá! Meu nome é Vitorino Villa e desejo boas-vindas a mais um curso da Formação BigQuery.

Audiodescrição: Victorino é uma pessoa de pele clara e olhos escuros. Seus cabelos são curtos, lisos e grisalhos. Usa barba e bigode com fios pretos e grisalhos e está vestindo uma camiseta azul-marinho.

Neste curso, daremos ênfase às funções numéricas e lógicas.

O que Aprenderemos neste Curso?

Vamos começar ajustando a nossa base de dados para garantir que você tenha total preparação para realizar todos os exercícios práticos que mostraremos neste curso. Em seguida, começaremos a explorar as funções numéricas e os tipos de dados numéricos existentes no BigQuery para entender, por exemplo, como conseguimos arredondar números, obter sinais dos números — ou seja, descobrir se o número é positivo ou negativo — e até mesmo trabalhar com a divisão por zero.

Também vamos aprender sobre o prefixo SAFE, que sempre colocamos na frente de uma função para evitar erros. Esta é uma maneira eficaz para garantir que as consultas sempre ofereçam resultados, mesmo quando encontrarem dados problemáticos. Também aplicaremos a técnica SAFE para arrays (matrizes), ampliando a possibilidade de análise de dados não estruturados.

Outro foco deste curso será a precisão numérica. Vamos comparar como os dois grandes tipos de dados numéricos, o float e o numeric, trabalham com precisão numérica e os cuidados que precisamos ter para usar um ou outro, dependendo da situação. Vamos aprender também a função RANGE_BUCKET, que serve para classificar os dados.

Em seguida, passaremos para as funções lógicas. Vamos entender esses tipos e como o BigQuery trata resultados verdadeiros e falsos quando realiza um teste lógico numa consulta.

Vamos aproveitar e ver a função COALESCE, que serve para lidar com valores nulos e substituí-los por outros valores. Também veremos a função CAST, para converter números em textos e textos em números.

Por fim, vamos entender o comando CASE WHEN, para criar lógicas condicionais dentro das consultas do BigQuery. Todo o conteúdo deste curso será aliado a exemplos práticos, sempre usando a base de dados da Belleza Verde.

No final deste curso, esperamos que você adquira uma compreensão mais profunda das funções numéricas e lógicas no BigQuery. Isso vai possibilitar vantagens significativas no mercado de trabalho, abrindo portas para várias oportunidades de emprego.

Prepare-se para embarcar nessa nova jornada conosco. Um forte abraço e até o próximo vídeo.

Ajustes da base de dados - Ajustando a base de dados

Antes de começar este curso, precisamos fazer ajustes na base de dados da empresa Belleza Verde que temos no nosso BigQuery.

Atenção: É importante ter feito todos os ajustes que foram realizados no primeiro e no segundo curso desta formação. Se não fizeram esses ajustes na base de dados, procurem os cursos anteriores da formação de BigQuery, criem a base de trabalho e façam os ajustes que são realizados no primeiro e no segundo curso, para depois fazer o que vamos mostrar neste vídeo.

Ajustando a Base de Dados

Após realizar o download do arquivo 3687 - Video 1.2 - Download.txt, disponível nas atividades desta aula, e salvarmos na nossa máquina localmente, vamos abri-lo usando um editor de texto — no caso, o Bloco de Notas.

No interior desse arquivo, há vários comandos que vamos executar na nossa base curso-big-query-12339-belleza_verde_vendas.produtos.

Atenção: Antes de fazer a execução desses scripts, certifique-se de que o nome do projeto e do conjunto de dados estejam corretos. Se o projeto e o conjunto de dados forem diferentes do que está marcado nesse arquivo, utilize o editor de texto para substituir seus nomes para os que está usando na sua máquina.

É provável que o nome das tabelas tenham sido mantidos conforme mostramos quando criamos essa base no primeiro curso desta formação. Caso contrário, também devem modificados.

Vamos primeiro copiar todos os oito inserts.

3687 - Video 1.2 - Download.txt

INSERT INTO curso-big-query-12339.belleza_verde_vendas.produtos 
(id_produto, nome, categoria, preco, distribuicao, materiasprimas)
VALUES (11, 'Óleo Corporal Semente de Uva Especial', 'Cuidados com a pele', 0, [0.5, 0.5], ['9', '10']);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.vendas
(id_venda, id_produto, id_cliente, data, quantidade, preco, data_unix)
VALUES (10951, 11, 1, '2022-01-02', 3, 24.60, 1641081600);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.produtos 
(id_produto, nome, categoria, preco, distribuicao, materiasprimas)
VALUES (12, 'Óleo Corporal Semente de Uva Master', 'Cuidados com a pele', NULL, [0.5, 0.5], ['9', '10']);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.produtos 
(id_produto, nome, categoria, preco, distribuicao, materiasprimas)
VALUES (13, 'Óleo Corporal Semente de Uva Master Plus', 'Cuidados com a pele', 0, [0.5, 0.5], ['9', '10']);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.produtos 
(id_produto, nome, categoria, preco, distribuicao, materiasprimas)
VALUES (14, 'Óleo Corporal Semente de Uva Especial', 'Cuidados com a pele', 0, [0.5, 0.5], ['9', '10']);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.vendas
(id_venda, id_produto, id_cliente, data, quantidade, preco, data_unix)
VALUES (10951, 12, 1, '2022-01-02', 3, NULL, 1641081600);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.vendas
(id_venda, id_produto, id_cliente, data, quantidade, preco, data_unix)
VALUES (10951, 13, 1, '2022-01-02', 3, -24.60, 1641081600);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.vendas
(id_venda, id_produto, id_cliente, data, quantidade, preco, data_unix)
VALUES (10951, 14, 1, '2022-01-02', 3, 0, 1641081600);

Acessando a página do BigQuery no navegador, vamos criar um novo script de dados clicando no botão de "mais" à direita das guias de script abertas, na parte central da tela. No interior do script novo, vamos colar.

Vamos selecionar todas as linhas coladas e clicar em "EXECUTAR", acima da área de script. Com isso , veremos no canto direito a mensagem "Consulta concluída", informando que ela foi concluída com sucesso.

Em seguida, vamos apagar as linhas que executamos para não executar os mesmos comandos novamente. Vamos voltar para o arquivo que baixamos e selecionar os próximos sete comandos: três ALTER TABLE e quatro UPDATE.

ALTER TABLE curso-big-query-12339.belleza_verde_vendas.vendas
ADD COLUMN faturamentoN NUMERIC;

ALTER TABLE curso-big-query-12339.belleza_verde_vendas.vendas
ADD COLUMN faturamentoF FLOAT64;

UPDATE curso-big-query-12339.belleza_verde_vendas.vendas SET faturamentoN = quantidade * CAST(preco AS NUMERIC)
WHERE 1=1;

UPDATE curso-big-query-12339.belleza_verde_vendas.vendas SET faturamentoF = quantidade * preco
WHERE 1=1;

ALTER TABLE curso-big-query-12339.belleza_verde_vendas.vendas
ADD COLUMN venda_vista BOOL;

UPDATE curso-big-query-12339.belleza_verde_vendas.vendas SET venda_vista = TRUE
WHERE RAND() > 0.35;

UPDATE curso-big-query-12339.belleza_verde_vendas.vendas SET venda_vista = FALSE
WHERE venda_vista IS NULL;

Vamos voltar para o nosso editor de script, colar, selecionar todos os comandos e executar.

Após a execução, vamos apagar os comandos, voltar ao arquivo baixado e selecionar os seis últimos comandos do script.

INSERT INTO curso-big-query-12339.belleza_verde_vendas.vendas
(id_venda, id_produto, id_cliente, data, quantidade, preco, data_unix, faturamentoF, faturamentoN, venda_vista)
VALUES (10951, 12, 1, '2022-01-02', NULL, 15.80, 1641081600, NULL, NULL, FALSE);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.vendas
(id_venda, id_produto, id_cliente, data, quantidade, preco, data_unix, faturamentoF, faturamentoN, venda_vista)
VALUES (10951, 12, 2, '2022-01-02', NULL, NULL, 1641081600, NULL, NULL, FALSE);

INSERT INTO curso-big-query-12339.belleza_verde_vendas.vendas
(id_venda, id_produto, id_cliente, data, quantidade, preco, data_unix, faturamentoF, faturamentoN, venda_vista)
VALUES (10951, 12, 2, '2022-01-02', 3, 15.80, 1641081600, NULL, NULL, FALSE);

ALTER TABLE curso-big-query-12339.belleza_verde_vendas.produtos
ADD COLUMN precoS STRING;

UPDATE curso-big-query-12339.belleza_verde_vendas.produtos SET precoS = CAST(preco AS STRING) WHERE 1=1;

UPDATE curso-big-query-12339.belleza_verde_vendas.produtos SET precoS = 'Não Definido' WHERE id_produto = 1;

Após colá-los e executá-los da área de scripts, temos nossa base pronta para utilizar neste curso.

Ajustes da base de dados - Conhecendo os tipos de dados - Numéricos e Lógicos

Quando falamos sobre funções, estamos essencialmente discutindo como elas interagem com diferentes tipos de dados. Isso é importante, porque na maioria das vezes, as funções que utilizamos estão ligadas aos tipos de dados com os quais estamos trabalhando.

Conhecendo os Tipos de Dados

Vamos entender quais são os principais tipos de dados que o BigQuery oferece. Vamos voltar à página do BigQuery Studio no navegador, utilizando a mesma conta e o mesmo conjunto de dados da empresa Belleza Verde, que já exploramos em cursos anteriores.

Se acessarmos o explorador lateral e abrirmos uma das tabelas — por exemplo, a tabela "produtos" —, podemos ver que os campos possuem tipos que podem ser dos mais diversos: inteiro, texto, string, float (números decimais) e até o campo "materiasprimas", que vimos em um curso anterior, do tipo um array de strings.

Ao usar funções sobre esses campos, precisamos saber o tipo deles para escolher a função correta a ser utilizada. Por isso, é importante fazer uma revisão sobre os principais tipos de dados do BigQuery.

INT64 e FLOAT64

Vamos começar pelos tipos INT64 e FLOAT64. Ambos servem para armazenar números, mas de maneiras diferentes. O INT64 serve para números inteiros, que não têm casas decimais. Ele abrange um conjunto de números muito grandes, desde o 10 elevado a menos 19 até o 10 elevado a 19.

Ele é ideal para gravar códigos, fazer contagens e expressar, por exemplo, indicadores relacionados com números que não possuem casas decimais.

Já o FLOAT64 é ideal para números decimais. Dizemos que o FLOAT64 armazena números decimais com precisão dupla. Ele suporta tamanhos enormes de dados decimais, porém ele não é ideal para valores financeiros ou medidas científicas que exijam um resultado exato no cálculo das casas decimais. Existe outro tipo mais adequado para fazer isso.

BOOL e NUMERIC

O BOOL é o tipo booliano, que aborda um valor verdadeiro ou falso, como se fosse um interruptor ligado ou desligado. Podemos utilizá-lo para expressar um status de determinada entidade no banco de dados, por exemplo, um cadastro de funcionários, onde dizemos se a pessoa está ou não empregada.

Já o NUMERIC, assim como o FLOAT64, é usado para expressar números decimais — porém, com uma precisão muito maior. Ele trabalha com 38 dígitos de precisão. Então, se você quer números com um detalhamento muito maior no cálculo em relação a números decimais, o NUMERIC é ideal.

STRING e BYTE

O STRING serve para armazenar textos. Qualquer sequência de caractere Unicode cabe no campo STRING e não há limite de tamanho. Você pode, se quiser, escrever um livro e guardar dentro do campo STRING que vai funcionar perfeitamente. É ideal para gravar nomes, endereços, descrições e e-mails.

Já o BYTE possui dados binários. Ele também não possui limite de tamanho e é ótimo para armazenar itens que não sejam textos, como, por exemplo, imagens e arquivos compactados.

TIMESTAMP e DATETIME

O TIMESTAMP e o DATETIME armazenam datas como ano, mês e dia, com detalhes como hora, minuto, segundo e milissegundo. A diferença é que o DATETIME foca na data local onde está o computador que processa os dados do BigQuery, enquanto o TIMESTAMP ignora o fuso horário.

O TIMESTAMP é um horário universal, único para qualquer fuso horário. Já o DATETIME usa o fuso horário onde estamos.

GEOGRAPHY, STRUCT e ARRAY

Temos alguns conjuntos de dados mais avançados, como o GEOGRAPHY e STRUCT ou ARRAY. Os GEOGRAPHY são para dados espaciais e tudo relacionado a mapas: pontos, rotas ou até mesmo áreas em um mapa. Ele utiliza latitudes e longitudes para armazenar esses pontos.

Já o STRUCT permite agrupar várias informações diferentes, mas relacionadas num único local. Podemos representar JSON complexos dentro do STRUCT.

Por fim, o ARRAY é usado quando temos uma lista de itens do mesmo tipo, que vão ser organizados em uma única célula ou campo da tabela.

Nesse curso específico, vamos focar somente em funções do tipo numéricas e aplicadas ao que chamamos de expressões lógicas.

Este conteúdo foi uma revisão dos principais tipos de dados, e esperamos que tenha sido útil. Até o próximo vídeo!

Sobre o curso Google BigQuery: dominando funções lógicas e numéricas

O curso Google BigQuery: dominando funções lógicas e numéricas possui 136 minutos de vídeos, em um total de 62 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Ajustes da base de dados
• Funções numéricas: arredondamento, divisão por zero e tratando erros
• Funções numéricas: notação especial, precisão, RANGE_BUCKET e outras funções
• Funções lógicas - Entendendo os operadores
• Funções lógicas - Convertendo dados e expressão condicional