SQL X Pandas

Duas abordagens para uma mesma consulta

Foto por Nika Benedictova no Unsplash

Introdução

Esse blog-post apresenta uma análise de dados realizada através de consultas (queries) que podem ser realizadas tanto por SQL (Structured Query Language) quanto pela biblioteca Pandas. Ou seja, serão feitas diversas consultas com SQL, em seguida, as mesmas consultas realizadas com o pandas, onde cada etapa para a realização das consultas será explicada.

Ferramentas

O banco de dados utilizado é o SQLite, que é um banco de dados relacional de código aberto. Para realização das consultas, utilizou-se o VS Code com a extensão do SQLite criada por alexcvzz. Além disso, a biblioteca Pandas e SQLite3 são utilizadas para a realização das consultas com Python.

Banco de dados

O banco de dados utilizado aqui é composto por quatro tabelas, a saber: vendas, clientes, produtos e lojas. A Figura abaixo apresenta o esquema e relacionamento entre as tabelas, onde a tabela vendas possui relacionamentos com as outras através do IDs do cliente, produto e loja.

Banco de dados

A tabela vendas contém informações sobre as vendas, como preço unitário do produto, quantidade de produtos e data da venda. A tabela clientes contém informações sobre o estado e cidade do cliente que realizou a compra. Já a tabela produtos, têm informações sobre o nome e tamanho do produto vendido. Por fim, a tabela lojas contém informações sobre a loja a qual o produto foi vendido, como cidade e estado, caso a venda tenha sido realizada fisicamente.

Passos iniciais

Para extrair as tabelas para as consultas com Pandas, utilizou-se a biblioteca SQLite3 para realizar a conexão com o banco de dados. O código abaixo cria a conexão com o banco de dados database.db.

import sqlite3

conn = sqlite3.connect('database.db')
c = conn.cursor()

Em seguida, é necessário executar um comando SQL para realizar a extração dos dados de cada tabela. O código abaixo apresenta como extrair os dados de uma tabela genérica.

import pandas as pd

nome_tabela = 'lojas'

comando = f'''SELECT * FROM {nome_tabela}'''  
c.execute(comando)

colunas = [coluna[0] for coluna in c.description]  

tabela = pd.DataFrame(c.fetchall(), columns=colunas)

A consulta é armazenada na variável comando, cujo resultado é selecionar todas a colunas (SELECT *) da (FROM) da tabela nome_tabela (lojas, conforme mostra o exemplo). Em seguida são extraídos os nomes das colunas e o dataframe é criado com os dados e as colunas corretas.

No caso da tabela vendas, é necessário transformar a coluna data de venda (dtVenda) para datetime, para evitar futuros problemas ao se trabalhar com essa coluna no Pandas. A transformação é feita após a criação do dataframe, conforme exemplifica o código abaixo.

vendas['dtVenda'] = pd.to_datetime(vendas['dtVenda'])

Onde vendas representa o dataframe com todos os dados da tabela vendas no banco de dados.

Perguntas

Cinco perguntas sobre os dados do banco de dados são feitas e respondidas tanto no SQL quanto no Pandas. As perguntas são:

1. Qual a quantidade de produtos únicos que foram vendidos?
2. Quais os três produtos mais vendidos e as quantidades destes?
3. Quais as quantidades mensais vendidas do produto camisa de tamanho G no ano de 2019?
4. Qual a média da receita líquida mensal de vendas no ano de 2019?
5. Qual loja teve a maior receita líquida de vendas no ano de 2019?

Consultas

Pergunta 1

A pergunta 1 é respondida de maneira simples, bastando contar (de maneira distinta) os produtos vendidos (idProduto) da tabela vendas, conforme exemplifica a consulta:

SELECT
    COUNT(DISTINCT idProduto) AS qtProdutosUnicosVendidos
    
FROM vendas

A simplicidade da resposta da pergunta 1 permanece no Pandas, onde esta é obtida pelo tamanho dos idProduto únicos do dataframe vendas, como mostra o código abaixo:

len(vendas['idProduto'].unique())

Ambas as respostas alcançam o valor 73, ou seja, foram vendidos 73 produtos únicos.

Pergunta 2

A pergunta 2 poderia ser respondida apenas com a tabela/dataframe vendas (idProduto), porém, para mostrar o nome (descNomeProduto) e o tamanho (descTamanhoProduto) do produto, é feita uma junção (LEFT JOIN) das tabelas vendas e produtos através do idProduto.

Para mostrar a quantidade de produtos vendidos é criada uma nova coluna através da agregação soma (SUM) do SQL, os dados são agrupados pelo idProduto, ordenado de maneira decrescente pela quantidade de produtos vendidos e o resultado é limitado a somente 3 linhas (três produtos mais vendidos).

A consulta abaixo responde a pergunta 2 com SQL:

SELECT
   T1.idProduto,
   T2.descNomeProduto,
   T2.descTamanhoProduto,
   SUM(T1.qtProduto) AS somaQtProduto

FROM vendas AS T1

LEFT JOIN produtos AS T2
ON T1.idProduto = T2.idProduto

GROUP BY T1.idProduto

ORDER BY somaQtProduto DESC

LIMIT 3

O código abaixo apresenta a resposta da pergunta 2 com Pandas:

join = pd.merge(vendas, produtos, on='idProduto', how='left')

colunas = ['idProduto', 'descNomeProduto', 'descTamanhoProduto']
groupby = join.groupby(colunas, as_index=False).agg(somaQtProduto=('qtProduto', 'sum'))

orderby = groupby.sort_values(by='somaQtProduto', ascending=False)

orderby.head(3)

Em Python a junção das tabelas são feitos com a função merge. Para se obter o mesmo resultado do agrupamento no SQL, as colunas que contém o nome e o tamanho do produto são adicionadas ao agrupamento. A agregação de soma é feita na coluna qtProduto, em seguida os dados são ordenados de maneira descendente (ascending=False) e somente 3 linhas são exibidas.

A consulta de ambas as abordagens é mostrada na figura abaixo.

Onde Casaco, Camisa e blusa (todos de tamanho G), foram os produtos mais vendidos com as quantidades de 1020, 1018 e 998, respectivamente.

Pergunta 3

Inicialmente, para responder a pergunta 3, é necessário adicionar uma coluna que não existe explicitamente na tabela vendas, o mês da venda. No SQLite é feita com o auxílio da função strftime com parâmetro %m (mês), já no Pandas é feita com a função assign, extraindo somente o mês da data da venda (dt.month).

Em seguida é feita a junção das tabelas vendas e produtos (left join) pelo idProduto e os dados dessa junção são filtrados pelo ano, nome e tamanho do produto. Por fim, a agregação soma da qtProduto é agrupada pelo mês de venda do produto.

A consulta em SQL que responde a pergunta 3 é mostrada abaixo:

SELECT
    strftime('%m', T1.dtVenda) AS dtMesVenda,
    SUM(T1.qtProduto) AS somaQtProduto
    
FROM vendas AS T1

LEFT JOIN produtos AS T2
ON T1.idProduto = T2.idProduto  

WHERE strftime('%Y', T1.dtVenda) = '2019'
AND T2.descNomeProduto = 'Camisa'
AND T2.descTamanhoProduto = 'G'

GROUP BY dtMesVenda

E o código em Python, que também responde a pergunta 3, é mostrado a seguir:

select = vendas.assign(dtMesVenda=vendas['dtVenda'].dt.month)

left_join = pd.merge(select, produtos, on='idProduto', how='left')

where = left_join[(left_join['dtVenda'].dt.year == 2019) & \
                  (left_join['descNomeProduto'] == 'Camisa') & \
                  (left_join['descTamanhoProduto'] == 'G')]

groupby = where.groupby('dtMesVenda', as_index=False).agg(somaQtProduto=('qtProduto', 'sum'))

groupby.head(12)

De ambas as maneiras, temos a consulta que mostra as quantidades mensais de camisa de tamanho G no ano de 2019, cujo resultado é mostrado a seguir:

Pergunta 4

Para se obter a média da receita líquida mensal de vendas no ano de 2019 (pergunta 4) no SQL, é necessário utilizar uma subquery. A subquery deve possuir a receita líquida mensal do ano de 2019, em seguida é feita uma agregação de média (AVG) da receita líquida mensal obtida com a subquery.

Tanto no SQL quanto no Pandas, assim como na resposta da pergunta 3, é necessário criar a coluna com o mês da venda na tabela/dataframe vendas. Além disso, é necessário criar a coluna preço, que é o preço unitário multiplicado pela quantidade de produtos vendidos, onde no SQL essa conta irá dentro da agregação SUM, já no Python, essa coluna é criada antes da agregação de mesmo propósito. Após isso, os dados filtrados pelo ano e agrupados pelo mês da venda (dtMesVenda).

Nesta etapa, possuímos a receita líquida mensal, a qual no SQL é obtida através de uma subquery, cuja tabela temporária possui o nome receita_mes. Com isso, obtemos a média da receita líquida mensal através da agrageção AVG no SQL, conforme mostra a consulta completa abaixo:

WITH receita_mes AS (

    SELECT
        strftime('%m', dtVenda) AS dtMesVenda,
        SUM((vlPrecoUnitario*qtProduto)) AS vlSomaReceitaLiquida
        
    FROM vendas
    WHERE strftime('%Y', dtVenda) = '2019'
    GROUP BY dtMesVenda
)

SELECT
    AVG(vlSomaReceitaLiquida) AS vlMediaReceitaLiquida
    
FROM receita_mes

Já no Pandas, a etapa de se obter a média da receita líquida mensal, é ligeiramente mais simples, onde utiliza-se .mean() na coluna que possui a receita líquida mensal (vlSomaReceitaLiquida). O código completo com Pandas para se responder a pergunta 4 é mostrado abaixo:

select = vendas.assign(mesVenda = vendas['dtVenda'].dt.month,
                       vlPreco = vendas['vlPrecoUnitario']*vendas['qtProduto'])

where = select[(select['dtVenda'].dt.year == 2019)]

groupby = where.groupby('mesVenda', as_index=False).agg(vlSomaReceitaLiquida=('vlPreco', 'sum'))

groupby['vlSomaReceitaLiquida'].mean()

Onde ambas as abordagens obtêm a média da receita líquida mensal no valor, aproximadamente, de R$ 324.240,93.

Pergunta 5

Para responder a pergunta 5, assim como na pergunta 4, é necessário criar a coluna preço (valor unitário x quantidade). Como queremos obter informações da loja que possui a maior receita líquida no ano de 2019, é necessário realizar a junção (LEFT JOIN) das tabelas vendas e lojas através do idLoja. No SQL é necessário somente realizar o agrupamento pelo idLoja, porém no Pandas, é necessário agrupar também pelo nome da loja (descCidadeLoja).

Após a junção, os dados são filtrados pelo ano de 2019. Em ambos (SQL e Pandas), o preço (vlPreco) é agregada através da função soma (SUM), e por fim, os dados são ordenados de forma descendente pela soma da receita líquida. O resultado das consultas (limitado em 3 linhas) das duas abordagens é mostrado abaixo:

Onde a cidade de Florianópolis possui a maior receita líquida no ano de 2019, no valor de, aproximadamente, R$ 992.059,6.

Conclusão

Esse blog post demonstra que é possível, de maneira relativamente simples, obter um mesmo resultado de uma consulta (query) ao banco de dados utilizando tanto com SQL (SQLite, neste caso) quanto a biblioteca Pandas, do Python. Desse modo, não ficamos restritos a uma única ferramenta em caso de necessidade da realização de uma consulta.

Os códigos com as consultas com SQL e Pandas estão disponíveis no GitHub. Siga o blog se o post foi útil para você.

Se você está interessado em predição de séries temporais, eu escrevi um blog post sobre predição de séries temporais usando extreme learning machine. Se você quiser conferir:

Time Series Forecasting through Extreme Learning Machine