Expressões de Tabela Comuns (CTEs) no MySQL

Introdução

Neste laboratório, você aprenderá a usar Common Table Expressions (CTEs) no MySQL. CTEs ajudam a simplificar consultas complexas, dividindo-as em etapas lógicas e legíveis. Uma CTE cria um conjunto de resultados temporário e nomeado que você pode referenciar dentro de uma única instrução SQL.

Você começará configurando um banco de dados e criando uma CTE simples para filtrar dados de funcionários. Em seguida, explorará CTEs recursivas para lidar com dados hierárquicos, como um organograma. Finalmente, você aprenderá a juntar uma CTE com uma tabela para realizar análises de dados mais avançadas.

Configurar o Banco de Dados e uma CTE Simples

Nesta primeira etapa, você configurará o ambiente do banco de dados e definirá uma Common Table Expression (CTE) simples. CTEs são definidas usando a cláusula WITH e atuam como uma view temporária que existe apenas durante a duração de uma única consulta. Isso torna consultas complexas mais fáceis de ler e gerenciar.

Primeiro, abra o terminal a partir do seu desktop.

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

sudo mysql -u root

Uma vez conectado, você verá o prompt do MySQL (mysql>). Agora, crie um banco de dados chamado labex_db e mude para ele.

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;

Em seguida, crie uma tabela employees e insira alguns dados de exemplo. Esta tabela armazenará informações básicas sobre funcionários, incluindo seu departamento e salário.

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

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);

Agora que a tabela está pronta, vamos definir uma CTE simples para selecionar apenas os funcionários do departamento 'Sales'.

WITH SalesEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;

Vamos detalhar esta consulta:

• WITH SalesEmployees AS (...): Isso define uma CTE chamada SalesEmployees.
• A consulta dentro dos parênteses seleciona funcionários da tabela employees onde o department é 'Sales'.
• SELECT * FROM SalesEmployees;: Esta é a consulta principal que recupera todos os dados do nosso conjunto de resultados temporário SalesEmployees.

Você deverá ver a seguinte saída, listando apenas os funcionários do departamento de Vendas:

+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary   |
+-------------+------------+-----------+----------+
|           1 | John       | Doe       | 60000.00 |
|           3 | Robert     | Jones     | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)

Isso confirma que sua primeira CTE está funcionando corretamente. Por favor, permaneça no shell do MySQL para a próxima etapa.

Criar uma CTE Recursiva para Dados Hierárquicos

CTEs recursivas são um recurso poderoso para consultar dados hierárquicos, como organogramas ou categorias aninhadas. Uma CTE recursiva se refere a si mesma para processar uma estrutura semelhante a uma árvore nível por nível. Ela consiste em um "membro âncora" (o ponto de partida) e um "membro recursivo" (a iteração).

Nesta etapa, você criará uma tabela representando uma hierarquia de funcionários e usará uma CTE recursiva para exibi-la.

Primeiro, ainda no shell do MySQL, crie a tabela employee_hierarchy. Esta tabela inclui um manager_id que aponta para o employee_id de outro funcionário, criando a hierarquia.

CREATE TABLE IF NOT EXISTS employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);

Nesta estrutura, 'David' é o gerente de nível superior porque seu manager_id é NULL.

Agora, escreva uma CTE recursiva para percorrer essa hierarquia. A palavra-chave RECURSIVE é necessária.

WITH RECURSIVE EmployeeHierarchyCTE AS (
    -- Membro âncora: seleciona o gerente de nível superior
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE manager_id IS NULL

    UNION ALL

    -- Membro recursivo: junta-se a si mesmo para encontrar subordinados
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employee_hierarchy e
    INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;

Vamos analisar esta consulta:

• Membro Âncora: A primeira instrução SELECT encontra a raiz da hierarquia (onde manager_id é NULL) e atribui a ela o level 0.
• Membro Recursivo: A segunda instrução SELECT junta employee_hierarchy com a própria CTE (EmployeeHierarchyCTE). Ela encontra todos os funcionários cujo manager_id corresponde a um employee_id já presente na CTE e incrementa o level.
• UNION ALL: Este operador combina os resultados dos membros âncora e recursivo.

A consulta produzirá a seguinte saída, mostrando o organograma completo com os níveis:

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | David         |       NULL |     0 |
|           2 | Emily         |          1 |     1 |
|           3 | Frank         |          1 |     1 |
|           4 | Grace         |          2 |     2 |
|           5 | Henry         |          2 |     2 |
|           6 | Ivy           |          3 |     2 |
|           7 | Jack          |          3 |     2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)

Você usou com sucesso uma CTE recursiva para consultar dados hierárquicos. Por favor, permaneça no shell do MySQL para a etapa final.

Juntar uma CTE com uma Tabela

CTEs podem ser juntadas com outras tabelas como tabelas regulares. Isso é útil para combinar dados agregados de uma CTE com dados detalhados de outra tabela. Nesta etapa, você criará uma CTE para calcular o salário médio por departamento e, em seguida, a juntará de volta à tabela employees.

Ainda no shell do MySQL, execute a seguinte consulta. Ela define uma CTE chamada AvgSalaryByDepartment e, em seguida, a junta com a tabela employees.

WITH AvgSalaryByDepartment AS (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.employee_id,
    e.first_name,
    e.department,
    e.salary,
    a.avg_salary
FROM
    employees e
JOIN
    AvgSalaryByDepartment a ON e.department = a.department;

Aqui está a análise:

• A CTE AvgSalaryByDepartment calcula o salário médio para cada departamento.
• A consulta principal então junta a tabela employees (apelidada de e) com esta CTE (apelidada de a) na coluna department.
• Isso permite que você exiba o salário de cada funcionário ao lado do salário médio de seu departamento.

A saída esperada será:

+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary   | avg_salary   |
+-------------+------------+------------+----------+--------------+
|           1 | John       | Sales      | 60000.00 | 57500.000000 |
|           2 | Jane       | Marketing  | 75000.00 | 72500.000000 |
|           3 | Robert     | Sales      | 55000.00 | 57500.000000 |
|           4 | Emily      | IT         | 90000.00 | 90000.000000 |
|           5 | Michael    | Marketing  | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Você pode verificar os resultados manualmente. Por exemplo, o salário médio para o departamento de 'Sales' é (60000 + 55000) / 2 = 57500, o que corresponde à saída. Isso confirma que sua consulta está funcionando corretamente.

Você juntou com sucesso uma CTE com uma tabela. Agora você pode sair do shell do MySQL.

exit;

Resumo

Neste laboratório, você aprendeu a usar efetivamente Expressões de Tabela Comuns (CTEs) no MySQL. Você começou configurando um banco de dados e tabelas, e depois progrediu na criação de diferentes tipos de CTEs.

Você aprendeu a:

• Definir uma CTE simples usando a cláusula WITH para filtrar dados e melhorar a legibilidade da consulta.
• Criar uma CTE recursiva para navegar e exibir dados hierárquicos de uma tabela.
• Juntar uma CTE com uma tabela para combinar resultados agregados com dados detalhados em nível de linha para análises mais complexas.

Essas habilidades são fundamentais para escrever consultas SQL limpas, manteníveis e poderosas, especialmente ao lidar com lógica ou estruturas de dados complexas.