Se o seu banco de dados incluir algum tipo de dados do tipo inteiro, decimais ou pontos flutuantes é provável que você vai precisar realizar alguns cálculos com esses dados. Por exemplo, se você precisar saber qual é o valor da soma de todos os elementos de uma coluna, ou você precise adicionar um valor a cada elemento de uma coluna ou linha. Felizmente podemos fazer essas tarefas utilizando a linguagem SQL. Com ela podemos realizar desde operações matemáticas básicas a estatísticas avançadas.
Nesse tutorial, vamos iniciar no básico das operações matemáticas e avançar para as funções matemáticas mais complexas e iniciar em estatística. O dataset que vamos usar pode ser baixado aqui.
Veja abaixo como importar esses dados:
MariaDB [(none)]> create database meu_db;
Query OK, 1 row affected (0.151 sec)
MariaDB [(none)]> use meu_db;
Database changed
MariaDB [meu_db]> create table censu_2015
-> (CensusTract INT,State VARCHAR(15),County VARCHAR(15),
-> TotalPop INT,Men INT,Women INT,
-> Hispanic FLOAT,White FLOAT,Black FLOAT,
-> Native FLOAT,Asian FLOAT,Pacific FLOAT,
-> Citizen INT,Income FLOAT,IncomeErr FLOAT,
-> IncomePerCap FLOAT,IncomePerCapErr FLOAT,Poverty FLOAT,
-> ChildPoverty FLOAT,Professional FLOAT,Service FLOAT,
-> Office FLOAT,Construction FLOAT,Production FLOAT,
-> Drive FLOAT,Carpool FLOAT,Transit FLOAT,
-> Walk FLOAT,OtherTransp FLOAT,WorkAtHome FLOAT,
-> MeanCommute FLOAT,Employed INT,PrivateWork FLOAT,
-> PublicWork FLOAT,SelfEmployed FLOAT,FamilyWork FLOAT,
-> Unemployment FLOAT);
Query OK, 0 rows affected (0.914 sec)
MariaDB [meu_db]> LOAD DATA LOCAL INFILE
-> 'C:/Users/user/acs2015_census_tract_data.csv'
-> INTO TABLE censu_2015
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\n'
-> IGNORE 1 LINES;
Query OK, 74001 rows affected, 65535 warnings (8.923 sec)
Records: 74001 Deleted: 0 Skipped: 0 Warnings: 97104
Operações matemáticas
Nos primeiros exemplos vamos utilizar as operações básicas que você aprendeu na escola. Na tabela abaixo é mostrado as operações que vamos utilizar com mais frequência.
| Operador | Operação |
|---|---|
| + | Adição |
| - | Subtração |
| * | Multiplicação |
| / | Divisão sem resto |
| %, MOD(x, y) | Modulo ou resto |
| ^ | Exponenciação |
| SQRT(x) | Raiz quadrada |
Esses operadores podem ser utilizados com números, como numa calculadora, ou com valores em tabelas. Nesse tutorial vamos mostrar os dois.
Tipos de dados e matemática
Quando estiver trabalhando com operações matemáticas é importante saber o tipo de dado onde essas operações serão aplicadas. Não ter conhecimento do tipo de dado pode resultar num erro ou numa operação indesejada.
Aplicando uma operação entre dois números, o resultado retornado segue o padrão abaixo:
- Dois inteiros retorna um inteiro.
- Um NUMERIC com outro retorna um NUMERIC.
- Qualquer operação com um float retorna um float.
Mas com a exponenciação e a raiz quadrada isso não acontece. A operação de raiz quadrada pode retornar um número de ponto flutuante mesmo quando a operação é com um inteiro e a exponenciação pode retornar um inteiro.
Adição, subtração e multiplicação
Até o momento utilizamos a declaração SELECT para recuperar dados de uma tabela. Nesse tutorial vamos utilizá-lo em conjunto com os operadores para realizar operações matemáticas. Vamos começar com as operações de adição, subtração e multiplicação.
MariaDB [(none)]> SELECT 2 + 2; +-------+ | 2 + 2 | +-------+ | 4 | +-------+ 1 row in set (0.038 sec) MariaDB [(none)]> SELECT 9 - 2; +-------+ | 9 - 2 | +-------+ | 7 | +-------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT 3 * 4; +-------+ | 3 * 4 | +-------+ | 12 | +-------+ 1 row in set (0.007 sec)
Não temos surpresas aqui. Como esperado dois mais dois é quatro, nove menos dois é sete e, como sempre, três vezes quatro é doze. O resultado é mostrado numa coluna, mas não se preocupe, nenhuma tabela está sendo alterada.
Divisão, modulo e raiz quadrada
Na divisão, o resultado sempre será um número de ponto flutuante. Isso porque o operador de divisão faz a divisão exata. Já o operador módulo retorna o resto de uma divisão. Esse operador realiza uma divisão e retorna o resto dessa divisão. Se a divisão for exata o resto é zero, do contrário, o resto será um inteiro. A função de raiz quadrada retorna a raiz quadrada do número passado como parâmetro. Veja um exemplo de utilização:
MariaDB [(none)]> SELECT 9 / 2; +--------+ | 9 / 2 | +--------+ | 4.5000 | +--------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT MOD(9,2); +----------+ | MOD(9,2) | +----------+ | 1 | +----------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT 9 % 2; -- O mesmo da declaração acima +-------+ | 9 % 2 | +-------+ | 1 | +-------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT SQRT(9); +---------+ | SQRT(9) | +---------+ | 3 | +---------+ 1 row in set (0.001 sec)
Ordem das operações
Nas expressões matemáticas existe uma ordem em que as operações devem ser feitas. No SQL é a mesma coisa. Por exemplo, qual operação deve ser executada primeiro? A subtração ou a multiplicação? Por isso é importante saber qual operação deve ser aplicada primeiro para atingir o resultado esperado. Essa é a ordem de prioridade dos operadores:
- Funções. No caso a função SQRT().
- Multiplicação, divisão e módulo.
- Adição e subtração.
Seguindo essa ordem as funções serão executadas primeiro, depois a multiplicação, divisão e módulo e, por último, a adição e subtração.
Se em algum momento você precisar a operação de adição ocorra antes da multiplicação, você pode alterar essa ordem de prioridade com parênteses. No exemplo abaixo, o resultado é diferente para cada declaração. Isso porque foi alterada a prioridade das operações.
MariaDB [(none)]> SELECT 5 + 4 * 9; +-----------+ | 5 + 4 * 9 | +-----------+ | 41 | +-----------+ 1 row in set (0.003 sec) MariaDB [(none)]> SELECT (5 + 4) * 9; +-------------+ | (5 + 4) * 9 | +-------------+ | 81 | +-------------+ 1 row in set (0.060 sec)
Operações com colunas
Agora que já aprendemos como utilizar os operadores na linguagem SQL vamos aprender a usar esses mesmos operadores com valores em colunas. Quando usamos esses operadores em colunas a operação é realizada em cada linha da tabela.
MariaDB [meu_db]> SELECT State AS 'Estado',
-> County AS 'Município',
-> Asian AS 'Asiático',
-> Hispanic AS 'Hispanico',
-> Hispanic + Asian AS "Total de Hispânicos e Asiáticos"
-> FROM censu_2015
-> WHERE State = 'Kansas';
+--------+--------------+----------+-----------+---------------------------------+
| Estado | Município | Asiático | Hispanico | Total de Hispânicos e Asiáticos |
+--------+--------------+----------+-----------+---------------------------------+
| Kansas | Allen | 0 | 3 | 3 |
| Kansas | Allen | 1.5 | 1 | 2.5 |
| Kansas | Allen | 0 | 0.1 | 0.10000000149011612 |
| Kansas | Allen | 1.3 | 6.3 | 7.6000001430511475 |
| Kansas | Allen | 0 | 5.9 | 5.900000095367432 |
| Kansas | Anderson | 0 | 0.5 | 0.5 |
| Kansas | Anderson | 0 | 0.3 | 0.30000001192092896 |
| Kansas | Wyandotte | 0 | 7.7 | 7.699999809265137 |
| Kansas | Wyandotte | 0 | 44 | 44 |
| Kansas | Wyandotte | 16.5 | 33.4 | 49.900001525878906 |
| Kansas | Wyandotte | 2.3 | 28.9 | 31.199999570846558 |
+--------+--------------+----------+-----------+---------------------------------+
770 rows in set (0.130 sec)
Essa não é a saída completa da busca, na sua máquina deve está bem maior. Mas, para o nosso proposito vai servir. Vamos começar explicando o código linha por linha. Na primeira linha selecionamos a coluna “State” e demos um apelido para ela (Estado). Fizemos a mesma coisa na linha dois, três e quatro. Depois pedimos para o gerenciador do banco de dados fazer a soma da coluna Hispanic e Asian e, como nas linhas anteriores, demos um apelido a essa coluna – Lembre-se que cada consulta retorna os dados organizados em colunas. Na linha seis informamos onde esses dados devem ser procurados. E por último fazemos uma filtragem dos dados com WHERE. Com a declaração WHERE estamos dizendo que só queremos os dados das colunas State, County, Asian e Hispanic onde o valor da coluna State seja igual a Kansas.
Com esse exemplo fica fácil perceber como as operações funcionam em tabelas. Como exercício tente realizar as outras operações e mudar o estado.
Porcentagem
Para o nosso próximo exemplo vamos descobrir qual é a porcentagem de homens no estado do Texas. Para isso vamos precisar saber a quantidade total da população e a quantidade total de homens. Nosso banco de dados já tem essas informações.
Veja como descobrir a porcentagem de homens no estado do Texas no exemplo abaixo:
MariaDB [meu_db]> SELECT State AS 'Estado',
-> SUM(Men) AS 'Total Homens',
-> SUM(Women) AS 'Total Mulheres',
-> (SUM(Men) * 100) / (SUM(Men) + SUM(Women)) AS 'Porcentagem de Homens'
-> FROM censu_2015
-> WHERE State = 'Texas';
+--------+--------------+----------------+-----------------------+
| Estado | Total Homens | Total Mulheres | Porcentagem de Homens |
+--------+--------------+----------------+-----------------------+
| Texas | 13171316 | 13367298 | 49.6308 |
+--------+--------------+----------------+-----------------------+
1 row in set (0.316 sec)
Nesse exemplo usamos a função SUM() para somar todos as linhas das colunas Men e Women, lembre-se que estamos somando apenas os valores das linhas que tenham o valor Texas na coluna State. Depois utilizamos as operações de multiplicação, divisão e soma para descobrir a porcentagem de homens no estado do Texas (Regra de três).
Função de soma e médias
As duas funções mais utilizadas na análise de dados são a SUM() e AVG(). Isso porque a função SUM() faz a soma de todas as linhas de uma coluna, como vimos no exemplo anterior. E a função AVG() retorna o valor médio de uma coluna numérica. O que a função AVG() faz é somar todos os valores de uma coluna e depois dividir pelo número de linhas. Veja um exemplo de utilização das duas funções abaixo:
MariaDB [meu_db]> SELECT AVG(TotalPop) FROM censu_2015; +---------------+ | AVG(TotalPop) | +---------------+ | 4325.5915 | +---------------+ 1 row in set (1.760 sec) MariaDB [meu_db]> SELECT SUM(TotalPop) FROM censu_2015; +---------------+ | SUM(TotalPop) | +---------------+ | 320098094 | +---------------+ 1 row in set (0.168 sec)
Com a função AVG() descobrimos que a média dos valores da coluna TotalPop é 4325.5915 e com a função SUM() descobrimos que o total da soma da coluna TotalPop é 320098094.
