Funções de Janela MySQL

Introdução

Neste laboratório, você explorará o poder das Funções de Janela (Window Functions) do MySQL. Você aprenderá a realizar cálculos em conjuntos de linhas de tabelas que estão relacionadas à linha atual.

Você começará criando um conjunto de dados de exemplo e, em seguida, usará funções como ROW_NUMBER(), SUM(), AVG() e LAG(), combinadas com a cláusula OVER(), para realizar análises de dados avançadas. Este laboratório fornece exemplos práticos, passo a passo, para ajudá-lo a entender e aplicar essas funções poderosas.

Configurar o Banco de Dados e a Tabela

Antes de usar funções de janela (window functions), você precisa de um banco de dados e uma tabela com dados de exemplo. Nesta etapa, você criará um banco de dados chamado company e uma tabela chamada employees.

Primeiro, abra o terminal a partir da sua área de trabalho.

Conecte-se ao servidor MySQL como o usuário root. Como este é um ambiente de laboratório, você pode usar sudo para se conectar sem senha.

sudo mysql -u root

Uma vez conectado, você verá o prompt do MySQL (mysql>).

Agora, crie o banco de dados company e mude para ele. A cláusula IF NOT EXISTS impede um erro se o banco de dados já existir.

CREATE DATABASE IF NOT EXISTS company;
USE company;

Em seguida, crie a tabela employees. Esta tabela armazenará o ID do funcionário, nome, departamento e salário.

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Agora, insira alguns dados de exemplo na tabela employees.

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Marketing', 55000.00),
(3, 'Charlie', 'Sales', 65000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00)
ON DUPLICATE KEY UPDATE
    employee_name = VALUES(employee_name),
    department = VALUES(department),
    salary = VALUES(salary);

Para verificar se os dados foram inseridos corretamente, você pode visualizar todas as linhas da tabela employees.

SELECT * FROM employees;

A saída deve exibir os cinco registros que você inseriu:

+-------------+---------------+------------+----------+
| employee_id | employee_name | department | salary   |
+-------------+---------------+------------+----------+
|           1 | Alice         | Sales      | 60000.00 |
|           2 | Bob           | Marketing  | 55000.00 |
|           3 | Charlie       | Sales      | 65000.00 |
|           4 | David         | IT         | 70000.00 |
|           5 | Eve           | Marketing  | 62000.00 |
+-------------+---------------+------------+----------+
5 rows in set (0.00 sec)

Com o banco de dados e a tabela prontos, você pode prosseguir para a próxima etapa para aprender sobre funções de janela.

Classificar Linhas com ROW_NUMBER()

A função ROW_NUMBER() atribui um inteiro sequencial único a cada linha dentro de uma partição de um conjunto de resultados. É comumente usada para classificação (ranking) e paginação.

A sintaxe básica é: ROW_NUMBER() OVER (ORDER BY column_name [ASC|DESC])

OVER(): Esta cláusula define a janela (o conjunto de linhas) para a função.
ORDER BY: Esta cláusula, dentro de OVER(), especifica a ordem em que os números das linhas são atribuídos.

Continuando no shell do MySQL, você agora usará ROW_NUMBER() para classificar os funcionários por seus salários em ordem decrescente.

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
    employees;

Esta consulta adiciona uma coluna salary_rank, numerando os funcionários do maior para o menor salário.

+---------------+------------+----------+-------------+
| employee_name | department | salary   | salary_rank |
+---------------+------------+----------+-------------+
| David         | IT         | 70000.00 |           1 |
| Charlie       | Sales      | 65000.00 |           2 |
| Eve           | Marketing  | 62000.00 |           3 |
| Alice         | Sales      | 60000.00 |           4 |
| Bob           | Marketing  | 55000.00 |           5 |
+---------------+------------+----------+-------------+
5 rows in set (0.00 sec)

Como você pode ver, David tem a classificação 1 porque tem o maior salário. Isso demonstra como ROW_NUMBER() pode ser usada para criar uma classificação simples.

Calcular um Total Acumulado com SUM()

Um total acumulado, ou soma cumulativa, é a soma de uma sequência de números que é atualizada à medida que cada novo número é adicionado. Em SQL, você pode calcular isso usando SUM() OVER().

A sintaxe é: SUM(column_name) OVER (ORDER BY column_name [ASC|DESC])

Esta função soma os valores de uma coluna na ordem especificada pela cláusula ORDER BY.

Agora, vamos calcular o total acumulado dos salários, ordenados por employee_id.

SELECT
    employee_name,
    salary,
    SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM
    employees;

O resultado mostrará o salário de cada funcionário e a soma cumulativa até aquela linha.

+---------------+----------+---------------+
| employee_name | salary   | running_total |
+---------------+----------+---------------+
| Alice         | 60000.00 |      60000.00 |
| Bob           | 55000.00 |     115000.00 |
| Charlie       | 65000.00 |     180000.00 |
| David         | 70000.00 |     250000.00 |
| Eve           | 62000.00 |     312000.00 |
+---------------+----------+---------------+
5 rows in set (0.00 sec)

Por exemplo, o running_total para Bob é a soma do salário dele e do salário de Alice (60000.00 + 55000.00 = 115000.00). Isso é útil para acompanhar métricas cumulativas como vendas ou despesas ao longo do tempo.

Agrupar Cálculos com PARTITION BY

A cláusula PARTITION BY divide o conjunto de resultados em partições (grupos) e aplica a função de janela a cada partição independentemente. Isso é útil para realizar cálculos dentro de categorias específicas.

A sintaxe é: function() OVER (PARTITION BY column_name ORDER BY ...)

Vamos usar PARTITION BY para classificar os funcionários dentro de cada departamento com base em seus salários.

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
    employees;

Esta consulta particiona os dados por department e, em seguida, classifica os funcionários em cada departamento por salário.

+---------------+------------+----------+--------------+
| employee_name | department | salary   | rank_in_dept |
+---------------+------------+----------+--------------+
| David         | IT         | 70000.00 |            1 |
| Eve           | Marketing  | 62000.00 |            1 |
| Bob           | Marketing  | 55000.00 |            2 |
| Charlie       | Sales      | 65000.00 |            1 |
| Alice         | Sales      | 60000.00 |            2 |
+---------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Observe que a classificação é reiniciada para cada departamento. Por exemplo, tanto Eve quanto Charlie são classificados como 1, mas dentro de seus respectivos departamentos 'Marketing' e 'Sales'. Isso permite uma análise mais granular em comparação com uma classificação global.

Comparar Linhas com LAG()

A função LAG() fornece acesso a uma linha em um deslocamento físico especificado que vem antes da linha atual. É útil para comparar um valor na linha atual com um valor em uma linha anterior.

A sintaxe é: LAG(expression, offset, default_value) OVER (ORDER BY ...)

expression: A coluna ou expressão a ser recuperada.
offset: O número de linhas a serem retrocedidas (o padrão é 1).
default_value: O valor a ser retornado se o deslocamento estiver fora dos limites (por exemplo, para a primeira linha).

Vamos encontrar o salário do funcionário anterior na lista, ordenado por employee_id.

SELECT
    employee_name,
    salary,
    LAG(salary) OVER (ORDER BY employee_id) AS previous_salary
FROM
    employees;

Esta consulta recupera o salário da linha anterior. Para a primeira linha, onde não há linha anterior, ela retorna NULL.

+---------------+----------+-----------------+
| employee_name | salary   | previous_salary |
+---------------+----------+-----------------+
| Alice         | 60000.00 |            NULL |
| Bob           | 55000.00 |        60000.00 |
| Charlie       | 65000.00 |        55000.00 |
| David         | 70000.00 |        65000.00 |
| Eve           | 62000.00 |        70000.00 |
+---------------+----------+-----------------+
5 rows in set (0.00 sec)

Você pode usar isso para calcular a diferença entre salários consecutivos. Quando o salário anterior for NULL (para a primeira linha), o resultado também será NULL.

SELECT
    employee_name,
    salary,
    salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM
    employees;

Esta consulta calcula a diferença entre o salário do funcionário atual e o anterior.

+---------------+----------+-------------+
| employee_name | salary   | salary_diff |
+---------------+----------+-------------+
| Alice         | 60000.00 |        NULL |
| Bob           | 55000.00 |    -5000.00 |
| Charlie       | 65000.00 |    10000.00 |
| David         | 70000.00 |     5000.00 |
| Eve           | 62000.00 |    -8000.00 |
+---------------+----------+-------------+
5 rows in set (0.00 sec)

Você praticou agora várias funções de janela chave. Você pode sair do shell do MySQL.

exit;

Resumo

Neste laboratório, você explorou o uso de funções de janela do MySQL. Você aprendeu como atribuir números de linha usando ROW_NUMBER(), calcular totais acumulados com SUM() OVER(), realizar cálculos em grupos específicos usando PARTITION BY e acessar dados de linhas anteriores com LAG().

Ao aplicar essas funções a um conjunto de dados de exemplo, você ganhou experiência prática na realização de análises de dados avançadas diretamente em suas consultas SQL. Estas são habilidades valiosas para gerar relatórios e insights complexos a partir de seus dados.

Funções de Janela do MySQL