Análise de Dados com SQL: Guia Inicial Parte II
Este artigo é a continuação do Guia Inicial Parte I, a ideia desta continuação é complementar o conteúdo da primeira parte, para que você seja capaz de fazer suas primeiras consultas para Análise de Dados com SQL, neste artigo iremos apresentar alguns conceitos complementares que não foram possíveis de serem apresentados na parte I, aproveite a leitura.
Caro leitor, este guia é a continuação do Guia Inicial Parte I, na primeira parte explicamos e exemplificamos o que são Bancos de Dados e o porquê da sua importância, abordamos os conceitos de bancos de dados relacionais, também falamos sobre o que é a linguagem SQL e qual a sua finalidade, apresentamos a você os conceitos básicos da estrutura de consulta em SQL, falamos sobre funções agregadoras, funções de agrupamento e ordenação, operadores de comparação e lógica booleana, e por fim, falamos sobre as uniões de tabelas, portanto, caso você tenha chegado aqui sem ter realizado a leitura da Parte I e não tenha este conhecimento prévio, é de extrema importância que você faça a leitura da primeira parte e entenda os conceitos que ali foram apresentados, segue abaixo o link do Guia Inicial Parte I:
Neste artigo, iremos abordar os seguintes tópicos:
1.0 Operadores de Lógica de Intervalo;
2.0 Subqueries;
3.0 CTE (Common Table Expression);
4.0 Window Functions.
IMPORTANTE: Gostaria de lembrá-lo que os exemplos práticos serão retirados de consultas efetuadas no site Learn SQL, que faz uso de um banco de dados SQLite, e o domínio para acessar este site é:
1.0 Operadores de Lógica de Intervalo.
São cláusulas fundamentais para você que deseja fazer Análises de Dados com SQL, com elas você consegue criar filtros mais elaborados, com o intuito de selecionar aquilo que é fundamental para geração dos seus insights.
Os operadores de lógica de intervalo são responsáveis por efetuar a seleção das linhas de uma determinada tabela em um determinado intervalo, que deve ser definido previamente, esse intervalo pode ser numérico, datas ou até mesmo textos, portanto, são funções extremamente úteis para você que deseja fazer Análise de Dados com o SQL. No SQL existem os seguintes operadores de Lógica de Intervalo: BETWEEN, IN, LIKE e HAVING.
1.1 Operador BETWEEN
O operador BETWEEN que em português podemos traduzir literalmente como sendo ENTRE, tem a função de selecionar os valores dentro de um determinado intervalo, este intervalo tem um limite inferior (valor mínimo) e um limite superior (valor máximo), fazendo uma analogia com os operadores de comparação, o operador BETWEEN pode substituir os operadores maior e igual (>=) e menor e igual (<=), sua sintaxe é bem simples, devemos utilizar apenas o BETWEEN na cláusula WHERE e incluir o operador de lógica booleana AND entre os valores do intervalo. Segue abaixo um exemplo prático:
Neste exemplo fizemos uma consulta na tabela patients, onde o objetivo era conseguirmos os patient_id, first_name e gender dos pacientes onde o peso estivesse entre 50 e 70 Kg, observe que a consulta retornou apenas valores dentro deste limite, nenhum valor fora do mesmo foi retornado.
1.2 Operador IN
O operador IN que traduzindo de forma literal para o portugês significa EM, tem o objetivo de efetuar a comparação dos valores de uma determinada coluna com uma lista de valores pré definidas pelo o usuário, fazendo uma analogia com os operadores de comparação, o operador IN é equivalente ao operador de comparação de igualdade sendo utilizado em conjunto com o comprador de lógica booleana OR, por exemplo, WHERE coluna_1 = A OR coluna_1 = B OR coluna_1 = C. O operador IN assim como os demais operadores deve ser utilizado na cláusula WHERE, segue abaixo um exemplo prático de utilização deste operador:
Neste exemplo, para facilitar sua compreensão, mantive a mesma consulta utilizada no exemplo do BETWEEN, porém, enquanto que no BETWEEN nós fizemos uma consulta que retorna as linhas com os valores dentro de um determinado intervalo da coluna weight, no operador IN nós selecionamos as linhas onde os valores da coluna weight estivessem contidos na lista de valores pré definidos (40, 50, 60, 70, 80), portanto, retornamos apenas as linhas onde o peso fosse exatamente o mesmo que um dos valores contidos nessa lista, lembrando que essa lista pode ser constituída por valores do tipo string ou caractere.
1.3 Operador LIKE
O operador LIKE é utilizado para realizar buscas das linhas com determinados padrões de texto, assim como os demais operadores de lógica de intervalo, ele também deve ser utilizado na cláusula WHERE, e sua sintaxe é apenas LIKE. Imagine que você deseja retornar as linhas de uma determinada tabela onde deseja apenas os pacientes que seus nomes iniciem com a letra A, você necessita fazer o uso do operador LIKE juntamente ao símbolo de porcentagem, isso mesmo o símbolo ‘%’, temos 3 possibilidades de consulta com o operador LIKE e o símbolo de %, são elas:
‘caracteres_desejados%’: Neste caso, ele retornará todas as linhas que apresentam os caracteres contidos antes do símbolo de %. Exemplo:
Perceba que a consulta em questão retornou apenas as linhas da coluna first_name onde os nomes comecem com a letra A, pois, o símbolo de % neste caso, exclui tudo aquilo que vem após ele, e a consulta considera apenas o que vem antes a ele, que no caso foi a letra A, lembrando que é necessário colocar o valor da consulta entre ‘’, por se tratar de uma string ou caractere, como neste exemplo.
‘%caracteres_desejados’: Neste caso, ele retornará todas as linhas que apresentam os caracteres contidos após o símbolo de %. Exemplo:
Perceba que neste exemplo a consulta retornou apenas as linhas onde os conteúdos da coluna first_name finalizaram com a string ‘old’
‘%caracteres_desejados%’: Neste caso, ele retornará todas as linhas que apresentam os caracteres contidos entre os símbolos de %. Exemplo:
Perceba que neste exemplo, a consulta retornou as linhas com os conteúdos da tabela first_name em que de maneira exclusiva possui a string ‘oma’ no meio de seu conteúdo, que é o caso dos nomes: Thomas, Paloma, Roman e Romary.
1.4 Operador HAVING
Este operador é responsável por realizar filtros de determinados valores após um agrupamento, lembrando que os agrupamentos acontecem fazendo uso da cláusula GROUP BY. Podemos dizer então, que a cláusula HAVING é equivalente a cláusula WHERE, mas, só deve ser utilizada após um agrupamento, ou seja, se o seu intuito é efetuar o filtro do resultado de uma operação proveniente de um agrupamento, utilize a cláusula HAVING. Ex:
Neste exemplo, executamos uma consulta para descobrirmos quais são os tipos de alergias que possuem mais de 50 pacientes, então contamos a quantidade de patient_id agrupadas pela coluna allergies, perceba que também utilizamos uma condição com a cláusula WHERE, filtramos os valores NULLOS dessa coluna, executei essa função para mostrar a você que mesmo a cláusula HAVING tendo uma função parecida, elas são utilizadas em momentos diferentes da sua consulta, observe que na cláusula HAVING eu adicionei a seguinte condição: HAVING COUNT(patient_id) > 50, ou seja, eu pedi que sejam exibidos apenas as alergias com mais de 50 pacientes, e foi o que a consulta retornou, veja que todas as alergias exibidas no resultado são maiores do que 50.
2.0 Subqueries
As Subqueries são fundamentais para que você consiga efetuar consultas mais elaboradas, com elas você consegue executar consultas aninhadas em consultas, ou seja, consultas dentro de consultas, elevando o nível dos seus scripts criados em SQL para fazer suas Análises de Dados.
As subqueries ou subconsultas em português, são consultas incorporadas nas Cláusulas SELECT, FROM, WHERE ou HAVING. As Subqueries te abrem um leque de grandes possibilidades, onde você vai conseguir executar consultas que antes seriam inviáveis de serem executadas.
2.1 Subquery na Cláusula SELECT
As Subqueries na cláusula SELECT são utilizadas para que você crie uma coluna ao resultado final da consulta que antes seria muito difícil sem o uso dessa técnica. Para criar uma subquery, basta executar uma query dentro de parênteses ‘( )’ na cláusula SELECT, conforme o exemplo abaixo:
Neste exemplo, era necessário exibir o ID do paciente, seu first_name e comparar com o último ID cadastrado, portanto, criamos uma subquery dentro da cláusula select, pois, o intuito era exibir uma coluna que mostrasse linha a linha qual o ultimo ID de paciente cadastrado no banco de dados, e comparar com o ID de cada um dos pacientes, ou seja, o Donald, é o paciente 1 de 4530, o Mickey o paciente 2 de 4530 e assim por diante.
2.1 Subquery na Cláusula FROM
As subqueries na Cláusula FROM são utilizadas para criar tabelas temporárias, que são utilizadas na consulta principal, da mesma forma que na cláusula SELECT, elas são criadas na cláusula FROM através de parênteses ‘( )’, observe o exemplo abaixo:
Neste exemplo, o objetivo era de efetuar uma consulta que retornasse a quantidade de pacientes total cadastrados que são moradores das cidades de Barrie, Dundas e Hamilton. Foi criado uma tabela temporária fazendo uso de uma subquery na cláusula from, nesta tabela selecionamos as colunas patient_id, first_name e a city, efetuamos o filtro selecionando apenas as cidades solicitadas. Após a criação da subquery temos uma tabela temporária, que foi utilizada na nossa consulta principal, onde, agrupamos as determinadas cidades e contamos os pacientes que moram nelas, chegando no resultado apresentado anteriormente.
2.1 Subquery na Cláusula WHERE
A Subquery na Cláusula WHERE tem como objetivo efetuar um filtro na consulta principal em virtude de uma condição que é definida dentro da subquery. assim como as subqueries nas cláusulas SELECT e FROM, ela também é definida por parênteses ‘( )’, só que dessa vez, na cláusula WHERE. Observe o exemplo abaixo:
Neste exemplo, era necessário efetuar uma query que retornasse os ID’s dos pacientes a partir do paciente que recebeu exatamente o registro do meio, ou seja, se tivéssemos um total de 100 ID’s, seriam exibidos os pacientes cadastrados a partir do ID 50 em diante.
2.1 Subquery na Cláusula HAVING
Assim como a Subquery na cláusula WHERE, também tem a finalidade de efetuar um filtro baseado no resultado de uma subquery, porém, com a diferença que esse filtro se dará no resultado da agregação feita no GROUP BY, conceito esse explicado no tópico 1.4 de operadores de Lógica de Intervalo. Segue abaixo o exemplo:
Neste exemplo, o objetivo era efetuar uma consulta que retornasse os diagnósticos que possuem um valor de pacientes superior a seguinte condição: Total de Pacientes / Total de Diagnósticos. Para isso, efetuamos uma subconsulta na cláusula HAVING onde efetuamos esta operação na tabela patients, dividindo a contagem total de patient_id por 305, onde 305 foi o número checado préviamente de diagnósticos já realizados, e então a consulta retornou os diagnósticos que possuem uma quantidade superior a esta operação.
3.0 CTE — Common Table Expression
As CTE são tabelas construídas de maneira temporária, para auxiliá-lo em consultas complexas, diferente das subqueries, ela não é construída dentro de nenhuma cláusula, neste tópico você vai entender como utilizá-las.
As CTE, ou Common Table Expression, são tabelas temporárias muito úteis quando queremos fazer análises muito complexas, pois, elas facilitam muito as nossas vidas quando é necessário efetuar uma consulta que se fossemos fazer sem o uso dessa função, seria necessário a união de diversas tabelas e efetuar diversas operações, podendo ser necessário a criação de uma outra tabela dedicada apenas para essa consulta, ou seja, seria uma query muito complexa, com o plus de ser necessário criar essa nova tabela, apenas para efetuá-la. Elas são chamadas de tabelas temporárias, pois não são gravadas no disco rígido, elas são criadas e utilizadas no momento da consulta e apagadas após o fim do processo, trazendo a vantagem de não ocupar grandes espaços, além de manter a organização no seu Banco de Dados.
A sua sintaxe é muito simples, é necessário utilizarmos a cláusula WITH nome_da_cte AS ( consulta que vai criar a tabela ), observe abaixo um exemplo de uso dessa função:
Neste exemplo, o objetivo era encontrar a data da última internação de cada paciente no banco de dados, e depois contar quantos pacientes foram internados por cada uma dessas datas. Para trazer essa resposta, construímos uma tabela temporária que é criada a partir de uma query, e essa tabela exibe a última data de internação de cada paciente. Após a criação dessa tabela, efetuamos uma consulta agrupando e contando os pacientes pelas datas, e assim obtivemos o resultado exibido anteriormente.
4.0 Window Functions — Funções de Janela.
São um grupo de funções “especiais” que podem operar em uma determinada janela de linhas pré-definidas, elas fazem o agrupamento dessas linhas sem afetar todas as outras colunas, como acontece com o GROUP BY . São funções incríveis de onde tiramos diversos insights úteis quando estamos analisando dados.
As Window Functions foram criadas com o intuito de fazermos análises em ranges definidos, trazendo a vantagem de podermos agrupar os valores desses ranges, sem impedir as outras operações nas demais colunas da tabela na sua consulta. Existem funções de média, soma,ranqueamento, primeiro e último valor, clusterização de valores, ranking percentual, contagem e etc. Não irei abordar todas as possibilidades neste tópico, caso contrário iria ficar enorme, e o intuito é que seja um guia rápido, falarei das mais comuns, entendendo o princípio de funcionamento de uma delas, as demais seguem a mesma lógica, porém, com outras finalidades.
A estrutura das window functions são definidas da seguinte maneira:
SELECT
coluna_1,
coluna_2,
.
.
.
WINDOW FUNCTION ( ‘coluna_da_operação’ ) OVER (
PARTITION BY ‘coluna_que_queremos_particionar’
ORDER BY ‘coluna_que_iremos_usar_para_ordenar’
ROWS ‘definição_da_janela’ )
FROM tabela
WINDOW FUNCTION: Aqui é definido o nome da operação que desejamos realizar no conjunto de dados selecionado, dentro do parênteses deve ser definido em qual coluna essa operação ocorrerá;
OVER: Utilizado para definir que aquela operação se trata de uma Window Function;
PARTITION BY: Neste campo definimos a partir de qual coluna iremos segmentar a operação da Window Function;
ORDER BY: Definimos por qual coluna iremos ordenar o resultado da operação, além disso, devemos usar as cláusulas ASC e DESC para definir o tipo de ordenação;
ROWS: Aqui iremos definir qual os limites da janela, ou seja, a partir de qual ponto ela inicia e até qual ponto ela tem que ir.
**OBS: Não é obrigatório o uso de todos campos mencionados anteriormente em todas as Window Functions, algumas não necessitam de preenchimento algum dentro do parênteses e uso simultâneo do PARTITION BY e ORDER BY, isso varia de função para função, sendo necessário ler sua documentação para entendê-la a fundo.
4.1 Window Function: AVG( )
Como já é conhecido, a função AVG calcula a média aritmética de uma determinada coluna que é definida dentro de seu parênteses, sua sintaxe como função de janela é:
AVG(<coluna_x> ) OVER (PARTITION BY <coluna_y>)Observe o exemplo abaixo:
As duas imagens acima são resultados da mesma query, perceba nas imagens que obtivemos a média de altura pelos dois gêneros, onde, nas linhas que o gênero foi igual a F, era exibido o resultado da média para este grupo, enquanto que para as linhas onde o gênero é igual a M, foi exibido a média deste outro grupo.
4.2 Window Function: MAX( ) e MIN( )
Assim como a média, as funções MIN e MAX são utilizadas para encontrar o valor mínimo e o valor máximo de uma determinada coluna, sua sintaxe como função de janela é:
MAX(<coluna_x> ) OVER (PARTITION BY <coluna_y>)e
MIN(<coluna_x> ) OVER (PARTITION BY <coluna_y>)Segue exemplo de aplicação:
Assim como no exemplo da função AVG, dessa vez desejamos exibir qual a maior altura por gênero, veja que para o gênero feminino a maior altura foi de 206 cm, enquanto que para o gênero masculino a maior altura foi de 226 cm. A função mínimo funciona da mesma forma, você mesmo pode testar no SQL-PRACTICE, faça a mesma query substituindo MAX por MIN e compare os resultados.
4.3 Window Function: ROW_NUMBER( )
O objetivo da função de janela ROW_NUMBER( ) é de criar rankings em função dos parâmetros definidos nela, ou seja, ela atribui valores que vão de um até a quantidade máxima de linhas da tabela, ela vai ranquear do maior para o menor, como do menor para o maior, segue abaixo a sua sintaxe:
ROW_NUMBER( ) OVER (PARTITION BY <coluna_base_agrupamento>
ORDER BY <coluna_com_valores_que_serao_ranqueados> ASC/DESC)Escolha ASC se você deseja que o ranking vá do menor para o maior, ou, escolha DESC se você deseja que o ranking vai do maior para o menor.
Observe o exemplo abaixo:
Observe que ele ranqueou os pacientes com maior altura para os de menor altura, com base no agrupamento por gênero, caso você não quisesse agrupar pelo gênero, é só remover a cláusula PARTITION BY e a coluna de gênero, deixando apenas a ordenação, e ele irá fazer um ranking geral.
4.4 W. Function: FIRST_VALUE( ) e LAST_VALUE( )
Essa função de janela tem o intuito de retornar o menor valor dentro do agrupamento definido, por exemplo, nós temos diversos pacientes no dataset do hospital utilizado até o momento, esses pacientes foram até o hospital mais de uma vez e em dias diferentes, se queremos descobrir qual foi o primeiro dia de cada um dos pacientes, ou, o último dia de cada um deles, podemos utilizar as funções de janela FIRST_VALUE ou LAST_VALUE. A sintaxe do uso dessas funções é:
FIRST_VALUE('coluna_valor_desejado') OVER (PARTITION BY 'coluna_que_desejamos_agrupar'
ORDER BY 'coluna_que_desejamos_ordenar')e
LAST_VALUE('coluna_valor_desejado') OVER (PARTITION BY 'coluna_que_desejamos_agrupar'
ORDER BY 'coluna_que_desejamos_ordenar')Observe os exemplos abaixo:
4.4.1 FIRST_VALUE( ):
Observe que neste exemplo nós descobrimos qual foi a data da primeira consulta dos pacientes e comparamos com a data de todas as consultas dele(a).
4.4.2 LAST_VALUE( ):
Observe que neste exemplo nós descobrimos qual foi a data da última consulta dos pacientes e comparamos com a data de todas as consultas dele(a).
4.5 Parametrização da Janela
Até o momento mostramos as funções de janela e não fizemos a parametrização da definição da janela, isso porque os exemplos mostrados anteriormente excluíam essa necessidade, mas, vamos falar um pouco sobre como funciona a parametrização da janela de exibição em consultas SQL. Na cláusula ROWS fazemos o uso de outras palavras reservadas para definirmos o tamanho da janela, são elas:
PRECEDING — Utilizado para definir a quantidade de linhas antes da linha atual.
CURRENT — Utilizado para dizer que queremos exibir até a linha atual
FOLLOWING — Utilizado para dizer a quantidade de linhas após a linha atual
UNBOUNDED PRECEDING — Quando adicionamos o UNBOUNDED antes do PRECEDING, estamos dizendo para a consulta que queremos todas as linhas que precedem a atual.
UNBOUNDED FOLLOWING — Quando adicionamos o UNBOUNDED antes do FOLLOWING, estamos dizendo para a consulta que queremos todas as linhas que vem após a atual.
Por exemplo, digamos que o diretor do hospital deseja saber qual a média móvel da altura dos pacientes nos últimos 5 dias. Segue abaixo o script para efetuar essa consulta:
Neste exemplo fizemos a parametrização da janela utilizando o UNBOUNDED PRECEDING e o UNBOUNDED FOLLOWING, pois, adicionamos no filtro todas as linhas onde a data fosse maior ou igual a ‘2019–06–01’ que é o primeiro dos últimos 5 dias para o cálculo da média móvel solicitado pelo diretor do hospital, portanto, usamos a palavra UNBOUNDED para dizer que queremos que seja considerado tudo que vem antes da linha atual e tudo que vem após a linha atual, ou seja, selecionamos o range inteiro.
Suponhamos que o diretor pediu para que fosse calculado a média móvel da altura, de 3 em 3 pacientes, ou seja, o primeiro paciente tem sua média calculado com a altura dos 2 pacientes seguintes, o segundo paciente com a altura dos 2 seguintes e assim sucessivamente, a consulta ficaria da seguinte maneira:
Peguemos a primeira linha para simular se o resultado apresentado está correto, a altura do primeiro paciente é 150 enquanto que as dos dois pacientes em sequência a ele é 173 e 160, a soma de 150 + 173 + 160 é igual a 483 que dividido por 3 (range parametrizado) é igual a 161, resultado apresentado pela consulta SQL.
Caso queira adquirir mais conhecimento a respeito de Windows Functions, assista ao vídeo abaixo que foi disponibilizado no canal da CDS, vídeo muito completo e que pode te ajudar ainda mais a entender essas funções que são excelentes para Análise de Dados com consultas em SQL.
5.0 Conclusão
Finalizamos nosso guia prático de SQL para Análise de Dados II, aqui você adquiriu o restante do conhecimento necessário para realizar suas primeiras queries em SQL, mas, não pare por aqui, continue se dedicando e indo atrás de mais conteúdo, colocando em prática o que aprendeu neste guia, o mundo de consultas em SQL é enorme, utilize o site SQL-PRACTICE para estudar, lá existe outro dataset completo com o diagrama entidade relacionamento, além disso, ele possui questões que vão de nível simples ao nível avançado, você executa as queries direto no site e o mesmo te retorna dizendo se a query está correta ou não para aquela determinada pergunta pré definida pelos desenvolvedores.
Espero que tenha gostado deste guia e agradeço por ter ficado até aqui.
