SQL vs Pandas - ,Média, mediana e quartis

Neste artigo vamos ver como calcular média, mediana e quartis utilizando o SQL e o Pandas. Vamos ver neste artigo alguns exemplos de estatística descritiva, que é um ramo da estatística que aplica várias técnicas para descrever e resumir um conjunto de dados. Você vai ver alguns exemplos de medidas de tendência central e medidas de variabilidade.

Primeiro vamos criar a tabela com os dados que serão utilizados:

Pandas

import numpy as np

import pandas as pd

dados = np.array([[1,1,10], [1,2,20], [1,2,30], [2,1,12], [2,2,26], [2,2,34], [3,1,13], [3,2,21], [3,2,36], [4,1,14], [4,2,27], [4,2,32], [5,1,12], [5,2,23], [5,2,35], [6,1,14], [6,2,26], [6,2,38], [7,1,12], [7,2,27], [7,2,35], [8,1,14], [8,2,28], [8,2,32], [9,1,11], [9,2,23], [9,2,35], [10,1,11], [10,2,21], [10,2,31], [11,1,9], [11,2,17], [11,2,25], [12,1,8], [12,2,16], [12,2,27]])

colunas = ["mes", "produto", "quantidade"]

dataframe = pd.DataFrame(data=dados, columns=colunas)

dataframe.sort_values(by=["mes"], ascending=True)

print(dataframe)

MySQL

CREATE TABLE vendas (

id int NOT NULL AUTO_INCREMENT,

mes int,

produto int,

quantidade int,

PRIMARY KEY (id)

);

SQL SERVER

CREATE TABLE vendas (

id int IDENTITY(1,1) PRIMARY KEY,

mes int,

produto int,

quantidade int

);

INSERT INTO vendas (mes, produto, quantidade) VALUES (1,1,10);

INSERT INTO vendas (mes, produto, quantidade) VALUES (1,2,20);

INSERT INTO vendas (mes, produto, quantidade) VALUES (1,2,30);

INSERT INTO vendas (mes, produto, quantidade) VALUES (2,1,12);

INSERT INTO vendas (mes, produto, quantidade) VALUES (2,2,26);

INSERT INTO vendas (mes, produto, quantidade) VALUES (2,2,34);

INSERT INTO vendas (mes, produto, quantidade) VALUES (3,1,13);

INSERT INTO vendas (mes, produto, quantidade) VALUES (3,2,21);

INSERT INTO vendas (mes, produto, quantidade) VALUES (3,2,36);

INSERT INTO vendas (mes, produto, quantidade) VALUES (4,1,14);

INSERT INTO vendas (mes, produto, quantidade) VALUES (4,2,27);

INSERT INTO vendas (mes, produto, quantidade) VALUES (4,2,32);

INSERT INTO vendas (mes, produto, quantidade) VALUES (5,1,12);

INSERT INTO vendas (mes, produto, quantidade) VALUES (5,2,23);

INSERT INTO vendas (mes, produto, quantidade) VALUES (5,2,35);

INSERT INTO vendas (mes, produto, quantidade) VALUES (6,1,14);

INSERT INTO vendas (mes, produto, quantidade) VALUES (6,2,26);

INSERT INTO vendas (mes, produto, quantidade) VALUES (6,2,38);

INSERT INTO vendas (mes, produto, quantidade) VALUES (7,1,12);

INSERT INTO vendas (mes, produto, quantidade) VALUES (7,2,27);

INSERT INTO vendas (mes, produto, quantidade) VALUES (7,2,35);

INSERT INTO vendas (mes, produto, quantidade) VALUES (8,1,14);

INSERT INTO vendas (mes, produto, quantidade) VALUES (8,2,28);

INSERT INTO vendas (mes, produto, quantidade) VALUES (8,2,32);

INSERT INTO vendas (mes, produto, quantidade) VALUES (9,1,11);

INSERT INTO vendas (mes, produto, quantidade) VALUES (9,2,23);

INSERT INTO vendas (mes, produto, quantidade) VALUES (9,2,35);

INSERT INTO vendas (mes, produto, quantidade) VALUES (10,1,11);

INSERT INTO vendas (mes, produto, quantidade) VALUES (10,2,21);

INSERT INTO vendas (mes, produto, quantidade) VALUES (10,2,31);

INSERT INTO vendas (mes, produto, quantidade) VALUES (11,1,9);

INSERT INTO vendas (mes, produto, quantidade) VALUES (11,2,17);

INSERT INTO vendas (mes, produto, quantidade) VALUES (11,2,25);

INSERT INTO vendas (mes, produto, quantidade) VALUES (12,1,8);

INSERT INTO vendas (mes, produto, quantidade) VALUES (12,2,16);

INSERT INTO vendas (mes, produto, quantidade) VALUES (12,2,27);

Agora vamos aos cálculos.

Para obter essas informações no Pandas é muito fácil. Basta executar o método describe() no campo desejado. Em nosso caso vamos utilizar o campo quantidade.

dataframe["quantidade"].describe()

O retorno é:

count 36.000000

mean 22.361111

std 9.240138

min 8.000000

25% 13.750000

50% 23.000000

75% 30.250000

max 38.000000

O método describe() retornou 8 informações, que são as seguintes:

count - contagem de elementos.

mean - média aritmética

std - desvio padrão

min - menor valor

25% - priumeiro quartil

50% - mediana

75% - terceiro quartiL

max - maior valor

Veremos detalhes sobre essas operações a seguir. Agora vamos ver como obter estes mesmos resultados utilizando o SQL. Em negrito foi colocado o retorno obtido no Pandas, para facilitar o entendimento.

CONTAGEM

count - 36.000000

select count(quantidade) as total from vendas

MÉDIA ARITMÉTICA

mean - 22.361111

select avg(quantidade) as total from vendas

DESVIO PADRÃO

std - 9.240138

O desvio-padrão é uma medida de dispersão, ela é bastante utilizada na estatística. Ao determinar o desvio padrão, podemos estabelecer um intervalo em torno da média aritmética, que é onde se concentra a maior parte dos dados. Quanto maior o valor do desvio-padrão, maior a variabilidade dos dados, ou seja, maior o afastamento em relação à média aritmética.

No MySQL obtemos esta informação utilizado a função STDDEV_SAMP()

select STDDEV_SAMP(quantidade) as total from vendas

Já no SQL Server utilzamos a função STDEV()

select STDEV(quantidade) as total from vendas

MENOR VALOR

min - 8.000000

select min(quantidade) as total from vendas

MAIOR VALOR

max - 38.000000

select max(quantidade) as total from vendas

QUARTIL

Deixamos para o final o mais complexo.

Os quartis são valores que dividem uma amostra de dados ordenada em quatro partes iguais e são usados para avaliar a dispersão e a tendência central de um conjunto de dados. Da mesma forma que o desvio padrão, quartis são muito utilizados na estatística.

O primeiro quartil é o valor da posição central entre o primeiro termo e a mediana. Isso quer dizer que até o seu valor, há 25% dos dados.

O segundo quartil, também chamado de mediana, é o valor até 50% da amostra. É o valor que pode ser encontrado na metade de uma amostra ordenada.

O terceiro quartil, também chamado de superior, é o meio do caminho entre a mediana e o último termo. Isso quer dizer que até o seu valor, há 75% dos dados.

Para nosso controle, os valores cálculados no Pandas foram os seguintes:

25% 13.750000

50% 23.000000

75% 30.250000

Para o cálculo dos quartis e da mediana, vamos ter que fazer um esforço maior do que nos exemplos anteriores. Vamos mostrar uma forma de obter isso pelo MySQL.

SET @temp_rows = (SELECT GROUP_CONCAT(quantidade ORDER BY quantidade ASC SEPARATOR ",") FROM vendas);

SET @temp_count = (SELECT COUNT(quantidade) FROM vendas);

SELECT

(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp_rows, ",", ROUND(@temp_count * 0.25 + 1)), ",", -1)) AS q1,

(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp_rows, ",", ROUND(@temp_count * 0.5 + 1)), ",", -1)) AS mediana,

(SUBSTRING_INDEX(SUBSTRING_INDEX(@temp_rows, ",", ROUND(@temp_count * 0.75 + 1)), ",", -1)) AS q3;

Perceba que no MySQL criamos duas varíaveis, a primeira chamada @temp_rows, tem uma consulta ordenada pelo campo quantidade. A função GROUP_CONCAT() foi utilizada para juntar todas as quantidade numa string separada por vírgula, este vai ser o conteúdo da primeira varíavel. Já a segunda variável, chamada @temp_count, tem um número inteiro com o total de registros da tabela.

Com estas duas variáveis, podemos fazer o cálculo dos quartis e da mediana. Para isso foi usada uma função de manipulação de strings chamada SUBSTRING_INDEX(). Esta função vai nos permitir selecionar uma determinada posição de nossa strings com as quantidades ordenadas e separadas por vírgula. Você vai perceber que os resultados obtidos por essa consulta são números inteiros arredondados para cima.

No SQL Server podemos chegar nestes resultados de outra forma. Primeiro vamos executar uma consulta que liste as quantidades ordenadas e nos informe qual é o quartil daquela quantidade

SELECT

quantidade,

NTILE(4) OVER (ORDER BY quantidade) AS Quartil

FROM

vendas

A função OVER permite criar uma partição de nossos dados e ordenar por um determinado critértio. Já a função NTILE distribui as linhas de uma partição ordenada em um número de grupos especificado. Como informarmos quatro grupos, os registros foram classificados em 1 (0 - 25, 2 (25 - 50, 3 (50 - 75 e 4 (75 - 100).

Com esta consulta como referência, podemos criar uma segunta consulta para extrair as informações que precisamos.

SELECT

MAX(CASE WHEN Quartil = 1 THEN quantidade END) as q1,

MAX(CASE WHEN Quartil = 2 THEN quantidade END) as Mediana,

MAX(CASE WHEN Quartil = 3 THEN quantidade END) as q3

FROM (

SELECT

quantidade,

NTILE(4) OVER (ORDER BY quantidade) AS Quartil

FROM

vendas

) as resultado

No SQL Server utilizamos CASE para criar os quartis e a função MAX() para obter o maior valor da quantidade de acordo com o critério informado no CASE.