Análise de Janela no SQLite

Introdução

Neste laboratório, você explorará as análises de janela (window analytics) do SQLite, com foco no ranqueamento de linhas e no cálculo de totais acumulados (running totals). Você aprenderá como usar funções de janela para realizar cálculos em conjuntos de linhas que estão relacionados à linha atual.

Especificamente, você usará a função ROW_NUMBER() para atribuir um ranking único a cada linha com base no valor das vendas. Você também aprenderá como calcular totais acumulados e particionar dados para análises mais avançadas. Este laboratório oferece uma introdução prática às funções de janela no SQLite.

Criar um Banco de Dados e Tabela de Vendas

Neste primeiro passo, você criará um banco de dados SQLite chamado sales.db e uma tabela chamada sales para armazenar dados de vendas. Esta tabela incluirá colunas para ID do produto, nome do produto e valor das vendas.

Abra seu terminal na VM do LabEx. Seu caminho padrão é /home/labex/project.

Para começar, crie o banco de dados sales.db e abra a ferramenta de linha de comando do SQLite executando o seguinte comando:

sqlite3 sales.db

Este comando cria o arquivo do banco de dados e abre o shell do SQLite, onde você pode executar comandos SQL. Você verá um prompt como este:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Em seguida, crie a tabela sales com as seguintes colunas: product_id, product_name e sales_amount. Insira o seguinte comando SQL no prompt sqlite> e pressione Enter:

CREATE TABLE sales (
    product_id INTEGER,
    product_name TEXT,
    sales_amount INTEGER
);

Este comando configura a tabela sales onde:

• product_id é um inteiro que representa o identificador único para cada produto.
• product_name é um campo de texto que armazena o nome do produto.
• sales_amount é um inteiro que representa o valor das vendas do produto.

Você não verá nenhuma saída se o comando for executado com sucesso.

Inserir Dados de Exemplo na Tabela de Vendas

Agora que você criou a tabela sales, vamos adicionar alguns dados de amostra a ela. Inseriremos seis registros representando diferentes produtos e seus valores de vendas.

Insira os seguintes registros na tabela sales executando estes comandos um por um no prompt sqlite>:

INSERT INTO sales (product_id, product_name, sales_amount) VALUES
(1, 'Laptop', 1200),
(2, 'Keyboard', 75),
(3, 'Mouse', 25),
(4, 'Monitor', 300),
(5, 'Headphones', 100),
(6, 'Webcam', 50);

Esses comandos adicionam seis linhas à tabela sales. Cada linha representa um produto com seu ID, nome e valor de vendas.

• INSERT INTO sales (product_id, product_name, sales_amount) especifica que você está inserindo dados nas colunas product_id, product_name e sales_amount da tabela sales.
• VALUES (1, 'Laptop', 1200) fornece os valores a serem inseridos para cada registro.

Para confirmar se os dados foram adicionados corretamente, execute este comando para visualizar todos os registros na tabela:

SELECT * FROM sales;

Saída Esperada:

1|Laptop|1200
2|Keyboard|75
3|Mouse|25
4|Monitor|300
5|Headphones|100
6|Webcam|50

Esta saída mostra o product_id, product_name e sales_amount para cada registro. O comando SELECT * recupera todas as colunas da tabela especificada.

Classificar Linhas com ROW_NUMBER()

Neste passo, você aprenderá como usar a função de janela ROW_NUMBER() para atribuir uma classificação única a cada linha com base no sales_amount. Isso é útil para identificar os produtos mais vendidos.

A função ROW_NUMBER() atribui um inteiro único a cada linha dentro de uma partição de um conjunto de resultados. A classificação é determinada pela ordem especificada na cláusula ORDER BY.

Execute a seguinte consulta no prompt sqlite>:

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM
    sales;

Esta consulta calcula a classificação de cada produto com base em seu sales_amount em ordem decrescente.

• ROW_NUMBER() OVER (ORDER BY sales_amount DESC) atribui uma classificação a cada linha com base no sales_amount, com o valor de vendas mais alto recebendo a classificação 1.
• ORDER BY sales_amount DESC especifica que a classificação deve ser baseada no sales_amount em ordem decrescente.

Saída Esperada:

Laptop|1200|1
Monitor|300|2
Headphones|100|3
Keyboard|75|4
Webcam|50|5
Mouse|25|6

Como você pode ver, a coluna sales_rank agora contém a classificação de cada produto com base em seu sales_amount, com o valor de vendas mais alto (Laptop) recebendo a classificação 1.

Calcular Totais Acumulados

Neste passo, você aprenderá como calcular totais acumulados (somas cumulativas) usando funções de janela. Totais acumulados são úteis para rastrear a soma de valores ao longo de um período de tempo ou em um conjunto de linhas.

Para calcular totais acumulados, você usa a função SUM() com a cláusula OVER() e uma cláusula ORDER BY para especificar a ordem em que a soma é calculada.

Primeiro, vamos adicionar uma coluna sale_date à tabela sales e preenchê-la com algumas datas de amostra. Execute os seguintes comandos no prompt sqlite>:

ALTER TABLE sales ADD COLUMN sale_date DATE;

UPDATE sales SET sale_date = '2023-01-01' WHERE product_name = 'Laptop';
UPDATE sales SET sale_date = '2023-01-05' WHERE product_name = 'Keyboard';
UPDATE sales SET sale_date = '2023-01-10' WHERE product_name = 'Mouse';
UPDATE sales SET sale_date = '2023-01-15' WHERE product_name = 'Monitor';
UPDATE sales SET sale_date = '2023-01-20' WHERE product_name = 'Headphones';
UPDATE sales SET sale_date = '2023-01-25' WHERE product_name = 'Webcam';

Esses comandos adicionam uma coluna sale_date à tabela sales e atualizam a tabela com datas de amostra para cada produto.

Agora, vamos calcular o total acumulado de sales_amount ao longo do tempo, ordenado por sale_date. Execute a seguinte consulta:

SELECT
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS running_total
FROM
    sales;

Esta consulta calcula o total acumulado de sales_amount ao longo do tempo, ordenado por sale_date.

• SUM(sales_amount) OVER (ORDER BY sale_date) calcula a soma cumulativa de sales_amount até cada sale_date.
• ORDER BY sale_date especifica que o total acumulado deve ser calculado com base na sale_date em ordem crescente.

Saída Esperada:

2023-01-01|Laptop|1200|1200
2023-01-05|Keyboard|75|1275
2023-01-10|Mouse|25|1300
2023-01-15|Monitor|300|1600
2023-01-20|Headphones|100|1700
2023-01-25|Webcam|50|1750

A coluna running_total mostra a soma cumulativa de sales_amount até cada sale_date. Por exemplo, o total acumulado em '2023-01-15' é 1600, que é a soma das vendas de '2023-01-01', '2023-01-05', '2023-01-10' e '2023-01-15'.

Particionar Dados para Análise

Neste passo, você aprenderá como particionar dados usando a cláusula PARTITION BY dentro de funções de janela. O particionamento permite que você divida seus dados em grupos lógicos e, em seguida, execute cálculos dentro de cada grupo de forma independente.

Vamos adicionar uma coluna product_category à nossa tabela sales. Execute os seguintes comandos no prompt sqlite>:

ALTER TABLE sales ADD COLUMN product_category TEXT;

UPDATE sales SET product_category = 'Electronics' WHERE product_name IN ('Laptop', 'Monitor', 'Headphones', 'Webcam');
UPDATE sales SET product_category = 'Accessories' WHERE product_name IN ('Keyboard', 'Mouse');

Esses comandos adicionam uma coluna product_category à tabela sales e atualizam a tabela com categorias para cada produto.

Agora, vamos usar PARTITION BY para calcular o total acumulado de sales_amount dentro de cada product_category. Execute a seguinte consulta:

SELECT
    product_category,
    sale_date,
    product_name,
    sales_amount,
    SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) AS running_total_by_category
FROM
    sales;

Esta consulta calcula o total acumulado de sales_amount dentro de cada product_category, ordenado por sale_date.

• PARTITION BY product_category divide os dados em partições com base na product_category.
• SUM(sales_amount) OVER (PARTITION BY product_category ORDER BY sale_date) calcula a soma cumulativa de sales_amount dentro de cada product_category, ordenada por sale_date.

Saída Esperada:

Accessories|2023-01-05|Keyboard|75|75
Accessories|2023-01-10|Mouse|25|100
Electronics|2023-01-01|Laptop|1200|1200
Electronics|2023-01-15|Monitor|300|1500
Electronics|2023-01-20|Headphones|100|1600
Electronics|2023-01-25|Webcam|50|1650

A coluna running_total_by_category mostra a soma cumulativa de sales_amount dentro de cada product_category, ordenada por sale_date. Observe que o total acumulado reinicia para cada categoria.

Resumo

Neste laboratório, você aprendeu como usar funções de janela no SQLite para realizar análise de dados avançada. Você começou criando uma tabela sales e inserindo dados de amostra. Em seguida, você usou a função ROW_NUMBER() para classificar produtos com base em seu valor de vendas. Você também aprendeu como calcular totais acumulados usando a função SUM() com a cláusula OVER() e como particionar dados usando a cláusula PARTITION BY para realizar cálculos dentro de grupos lógicos. Essas habilidades fornecem uma base para tarefas de análise de dados mais complexas no SQLite.