Introdução
Neste laboratório, você aprimorará suas habilidades de escrita de consultas PostgreSQL explorando técnicas avançadas. Você aprenderá a usar subconsultas dentro da cláusula WHERE para filtrar dados com base nos resultados de outra consulta.
O laboratório guia você através da definição e utilização de Expressões de Tabela Comum (CTEs - Common Table Expressions) para melhorar a legibilidade e a modularidade das consultas. Além disso, você aplicará funções de janela como ROW_NUMBER para realizar cálculos em conjuntos de linhas. Finalmente, você dominará o agrupamento e a filtragem de dados usando as cláusulas GROUP BY e HAVING para extrair insights significativos de seus conjuntos de dados.
Escreva uma Subconsulta em uma Cláusula WHERE
Nesta etapa, você aprenderá como usar uma subconsulta dentro da cláusula WHERE de uma consulta SQL. Uma subconsulta, também conhecida como consulta interna ou consulta aninhada, é uma consulta incorporada dentro de outra consulta. As subconsultas são usadas para retornar dados que serão usados na consulta principal como uma condição para restringir ainda mais os dados a serem recuperados.
Entendendo Subconsultas na Cláusula WHERE
Uma subconsulta na cláusula WHERE é tipicamente usada para comparar o valor de uma coluna com o resultado da subconsulta. A subconsulta é executada primeiro, e seu resultado é então usado pela consulta externa.
Sintaxe Básica:
SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);
Aqui, a subconsulta (SELECT column_name FROM another_table WHERE condition) retorna um conjunto de valores. A consulta externa então seleciona linhas de table_name onde column_name está nesse conjunto.
Cenário:
Vamos supor que você tenha duas tabelas: employees (funcionários) e departments (departamentos). A tabela employees contém informações sobre os funcionários, incluindo seus employee_id (ID do funcionário), employee_name (nome do funcionário) e department_id (ID do departamento). A tabela departments contém informações sobre os departamentos, incluindo seus department_id e department_name (nome do departamento).
Queremos encontrar todos os funcionários que trabalham no departamento de 'Vendas'.
Passo 1: Criar as Tabelas e Inserir Dados
Primeiro, conecte-se ao banco de dados PostgreSQL usando o usuário postgres:
sudo -u postgres psql
Em seguida, crie a tabela departments:
CREATE TABLE departments (
department_id SERIAL PRIMARY KEY,
department_name VARCHAR(50)
);
Insira alguns dados de exemplo na tabela departments:
INSERT INTO departments (department_name) VALUES
('Sales'),
('Marketing'),
('Engineering');
Agora, crie a tabela employees:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
employee_name VARCHAR(50),
department_id INTEGER REFERENCES departments(department_id)
);
Insira alguns dados de exemplo na tabela employees:
INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', 2);
Passo 2: Escrever a Subconsulta
Agora, vamos escrever a consulta para encontrar todos os funcionários que trabalham no departamento de 'Vendas' usando uma subconsulta na cláusula WHERE.
SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');
Explicação:
- A subconsulta
(SELECT department_id FROM departments WHERE department_name = 'Sales')seleciona odepartment_idda tabeladepartmentsonde odepartment_nameé 'Sales'. Neste caso, ela retornará1. - A consulta externa então seleciona o
employee_nameda tabelaemployeesonde odepartment_idestá no conjunto retornado pela subconsulta (que é apenas1).
Passo 3: Executar a Consulta e Visualizar os Resultados
Execute a consulta em seu terminal psql. Você deve ver a seguinte saída:
employee_name
---------------
Alice
Charlie
(2 rows)
Isso mostra que Alice e Charlie são os funcionários que trabalham no departamento de 'Vendas'.
Passo 4: Usando EXISTS com Subconsultas
Outra maneira de usar subconsultas na cláusula WHERE é com o operador EXISTS. O operador EXISTS testa a existência de linhas em uma subconsulta. Ele retorna verdadeiro se a subconsulta retornar alguma linha e falso caso contrário.
Aqui está um exemplo de como usar EXISTS para obter o mesmo resultado:
SELECT employee_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM departments d
WHERE d.department_id = e.department_id
AND d.department_name = 'Sales'
);
Esta consulta obtém o mesmo resultado que a anterior, mas usa o operador EXISTS em vez de IN.
Explicação:
- A subconsulta
SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Sales'verifica se existe um departamento com o nome 'Vendas' que tem o mesmodepartment_idque o funcionário atual. - Se a subconsulta retornar alguma linha (significando que existe um departamento de 'Vendas' com o mesmo
department_id), o operadorEXISTSretorna verdadeiro, e o nome do funcionário é selecionado.
Execute a consulta em seu terminal psql. Você deve ver a mesma saída de antes:
employee_name
---------------
Alice
Charlie
(2 rows)
Você agora usou com sucesso uma subconsulta na cláusula WHERE para filtrar dados com base em uma condição em outra tabela. Você também aprendeu como usar o operador EXISTS com uma subconsulta.
Definir e Usar um CTE
Nesta etapa, você aprenderá como definir e usar uma Expressão de Tabela Comum (CTE - Common Table Expression) no PostgreSQL. Uma CTE é um conjunto de resultados temporário nomeado que você pode referenciar dentro de uma única instrução SELECT, INSERT, UPDATE ou DELETE. As CTEs são úteis para dividir consultas complexas em partes mais simples e legíveis.
Entendendo as CTEs
As CTEs são definidas usando a cláusula WITH. Elas existem apenas durante a execução da consulta.
Sintaxe Básica:
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT column1, column2
FROM cte_name
WHERE condition;
Aqui, cte_name é o nome que você dá à CTE. A instrução SELECT dentro dos parênteses define o conjunto de resultados da CTE. A instrução SELECT externa então consulta a CTE como se fosse uma tabela regular.
Cenário:
Continuando com as tabelas employees e departments da etapa anterior, vamos usar uma CTE para encontrar os nomes dos funcionários e seus respectivos nomes de departamentos.
Passo 1: Verificar as Tabelas e Dados
Certifique-se de que as tabelas employees e departments existam e contenham os dados da etapa anterior. Você pode verificar isso executando as seguintes consultas em seu terminal psql:
SELECT * FROM departments;
SELECT * FROM employees;
Se as tabelas ou dados estiverem faltando, consulte a etapa anterior para criá-los e inserir os dados.
Passo 2: Definir uma CTE
Agora, vamos definir uma CTE chamada EmployeeDepartments que junta as tabelas employees e departments para recuperar os nomes dos funcionários e seus nomes de departamentos.
WITH EmployeeDepartments AS (
SELECT
e.employee_name,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
)
SELECT employee_name, department_name
FROM EmployeeDepartments;
Explicação:
- A cláusula
WITH EmployeeDepartments AS (...)define a CTE chamadaEmployeeDepartments. - A instrução
SELECTdentro dos parênteses junta a tabelaemployees(apelidada dee) com a tabeladepartments(apelidada ded) na colunadepartment_id. - A instrução
SELECTexterna então recupera oemployee_namee odepartment_nameda CTEEmployeeDepartments.
Passo 3: Executar a Consulta e Visualizar os Resultados
Execute a consulta em seu terminal psql. Você deve ver a seguinte saída:
employee_name | department_name
---------------+-----------------
Alice | Sales
Bob | Marketing
Charlie | Sales
David | Engineering
Eve | Marketing
(5 rows)
Isso mostra os nomes de todos os funcionários e seus respectivos nomes de departamentos.
Passo 4: Usando CTEs para Filtragem
Você também pode usar CTEs para filtrar dados. Por exemplo, vamos encontrar todos os funcionários que trabalham no departamento de 'Vendas' usando a CTE EmployeeDepartments.
WITH EmployeeDepartments AS (
SELECT
e.employee_name,
d.department_name
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
)
SELECT employee_name
FROM EmployeeDepartments
WHERE department_name = 'Sales';
Explicação:
- Esta consulta é semelhante à anterior, mas adiciona uma cláusula
WHEREà instruçãoSELECTexterna para filtrar os resultados para incluir apenas os funcionários que trabalham no departamento de 'Vendas'.
Execute a consulta em seu terminal psql. Você deve ver a seguinte saída:
employee_name
---------------
Alice
Charlie
(2 rows)
Isso mostra que Alice e Charlie são os funcionários que trabalham no departamento de 'Vendas'.
Você agora definiu e usou com sucesso uma CTE para juntar tabelas e filtrar dados. As CTEs podem melhorar significativamente a legibilidade e a capacidade de manutenção de consultas SQL complexas.
Aplicar Funções de Janela (por exemplo, ROW_NUMBER)
Nesta etapa, você aprenderá como aplicar funções de janela no PostgreSQL. As funções de janela realizam cálculos em um conjunto de linhas de tabela que estão relacionadas à linha atual. Elas são semelhantes às funções agregadas, mas, ao contrário das funções agregadas, as funções de janela não agrupam linhas em uma única linha de saída. Em vez disso, elas fornecem um valor para cada linha no conjunto de resultados.
Entendendo as Funções de Janela
As funções de janela usam a cláusula OVER() para definir a janela de linhas para o cálculo. A cláusula OVER() pode incluir as cláusulas PARTITION BY e ORDER BY para definir ainda mais a janela.
Sintaxe Básica:
SELECT
column1,
column2,
WINDOW_FUNCTION(column3) OVER (
[PARTITION BY column4]
[ORDER BY column5]
) AS alias_name
FROM
table_name;
WINDOW_FUNCTION: O nome da função de janela (por exemplo,ROW_NUMBER,RANK,SUM,AVG).OVER(): Especifica a janela sobre a qual a função opera.PARTITION BY: Divide as linhas em partições, e a função de janela é aplicada a cada partição independentemente.ORDER BY: Define a ordem das linhas dentro de cada partição.alias_name: O alias para o resultado da função de janela calculada.
Cenário:
Continuando com as tabelas employees e departments das etapas anteriores, vamos usar a função de janela ROW_NUMBER() para atribuir uma classificação única a cada funcionário dentro de seus respectivos departamentos com base em seu employee_name.
Passo 1: Aplicar a Função de Janela ROW_NUMBER()
Agora, vamos escrever uma consulta que usa a função de janela ROW_NUMBER() para atribuir uma classificação a cada funcionário dentro de seu departamento.
SELECT
employee_name,
department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
FROM
employees;
Explicação:
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name): Esta é a função de janela.ROW_NUMBER(): Atribui um inteiro sequencial único a cada linha dentro da janela.PARTITION BY department_id: Divide as linhas em partições com base nodepartment_id. Isso significa que a classificação será feita separadamente para cada departamento.ORDER BY employee_name: Especifica a ordem em que as linhas dentro de cada partição são classificadas. Neste caso, os funcionários são classificados alfabeticamente por seuemployee_name.
employee_rank: Este é o alias dado ao resultado da função de janela.
Passo 2: Executar a Consulta e Visualizar os Resultados
Execute a consulta em seu terminal psql. Você deve ver a seguinte saída:
employee_name | department_id | employee_rank
---------------+---------------+---------------
Alice | 1 | 1
Charlie | 1 | 2
Bob | 2 | 1
Eve | 2 | 2
David | 3 | 1
(5 rows)
Isso mostra o nome de cada funcionário, seu ID do departamento e sua classificação dentro de seu departamento. Por exemplo, Alice está classificada em 1 no departamento 1, e Charlie está classificado em 2 no departamento 1.
Passo 3: Usando Funções de Janela com CTEs
Você também pode usar funções de janela dentro de CTEs para tornar suas consultas mais organizadas. Vamos reescrever a consulta anterior usando uma CTE.
WITH RankedEmployees AS (
SELECT
employee_name,
department_id,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
FROM
employees
)
SELECT
employee_name,
department_id,
employee_rank
FROM
RankedEmployees;
Esta consulta produz o mesmo resultado que a anterior, mas usa uma CTE para encapsular o cálculo da função de janela.
Execute a consulta em seu terminal psql. Você deve ver a mesma saída de antes.
Agrupar e Filtrar com GROUP BY e HAVING
Nesta etapa, você aprenderá como usar as cláusulas GROUP BY e HAVING no PostgreSQL para agrupar linhas e filtrar os resultados agrupados. A cláusula GROUP BY agrupa linhas que têm os mesmos valores nas colunas especificadas em linhas de resumo, como encontrar o número de funcionários em cada departamento. A cláusula HAVING é usada para filtrar essas linhas agrupadas com base em uma condição especificada.
Entendendo GROUP BY e HAVING
- GROUP BY: Esta cláusula agrupa linhas com os mesmos valores em uma ou mais colunas em uma linha de resumo. Você normalmente usa funções agregadas (por exemplo,
COUNT,SUM,AVG,MIN,MAX) para calcular valores para cada grupo. - HAVING: Esta cláusula filtra os grupos criados pela cláusula
GROUP BY. É semelhante à cláusulaWHERE, mas opera em grupos em vez de linhas individuais.
Sintaxe Básica:
SELECT
column1,
aggregate_function(column2)
FROM
table_name
WHERE
condition
GROUP BY
column1
HAVING
aggregate_function(column2) condition;
column1: A coluna para agrupar.aggregate_function(column2): Uma função agregada aplicada acolumn2para cada grupo.WHERE: Filtra as linhas antes do agrupamento.GROUP BY: Agrupa as linhas com base nos valores emcolumn1.HAVING: Filtra os grupos após o agrupamento, com base no resultado da função agregada.
Cenário:
Continuando com as tabelas employees e departments, vamos usar GROUP BY e HAVING para encontrar os departamentos que têm mais de um funcionário.
Passo 1: Agrupar por Departamento e Contar Funcionários
Primeiro, vamos escrever uma consulta para agrupar os funcionários por departamento e contar o número de funcionários em cada departamento.
SELECT
d.department_name,
COUNT(e.employee_id) AS employee_count
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
GROUP BY
d.department_name;
Explicação:
SELECT d.department_name, COUNT(e.employee_id) AS employee_count: Isso seleciona o nome do departamento e a contagem de IDs de funcionários para cada departamento.FROM employees e JOIN departments d ON e.department_id = d.department_id: Isso junta as tabelasemployeesedepartmentsna colunadepartment_id.GROUP BY d.department_name: Isso agrupa as linhas por nome do departamento, para que a funçãoCOUNT()conte o número de funcionários em cada departamento.
Passo 2: Executar a Consulta e Visualizar os Resultados
Execute a consulta em seu terminal psql. Você deve ver a seguinte saída:
department_name | employee_count
-----------------+----------------
Engineering | 1
Marketing | 2
Sales | 2
(3 rows)
Isso mostra o número de funcionários em cada departamento.
Passo 3: Filtrar com HAVING
Agora, vamos adicionar uma cláusula HAVING para filtrar os resultados para incluir apenas os departamentos que têm mais de um funcionário.
SELECT
d.department_name,
COUNT(e.employee_id) AS employee_count
FROM
employees e
JOIN
departments d ON e.department_id = d.department_id
GROUP BY
d.department_name
HAVING
COUNT(e.employee_id) > 1;
Explicação:
- Esta consulta é a mesma que a anterior, mas adiciona uma cláusula
HAVING:HAVING COUNT(e.employee_id) > 1: Isso filtra os grupos para incluir apenas os departamentos onde a contagem de IDs de funcionários é maior que 1.
Passo 4: Executar a Consulta e Visualizar os Resultados
Execute a consulta em seu terminal psql. Você deve ver a seguinte saída:
department_name | employee_count
-----------------+----------------
Marketing | 2
Sales | 2
(2 rows)
Isso mostra apenas os departamentos que têm mais de um funcionário (Marketing e Vendas).
Você agora usou com sucesso as cláusulas GROUP BY e HAVING para agrupar linhas e filtrar os resultados agrupados com base em uma condição.
Resumo
Neste laboratório, você aprendeu a escrever consultas avançadas em PostgreSQL. Você começou usando subconsultas dentro da cláusula WHERE para filtrar dados com base nos resultados de outra consulta. Isso envolveu a compreensão da sintaxe e da aplicação de subconsultas para comparar valores de coluna com um conjunto de valores retornados pela consulta interna.
Em seguida, você passou a definir e utilizar Expressões de Tabela Comum (CTEs - Common Table Expressions) para melhorar a legibilidade e a modularidade das consultas. CTEs permitem que você divida consultas complexas em partes mais simples e gerenciáveis.
Em seguida, você aplicou funções de janela como ROW_NUMBER para realizar cálculos em conjuntos de linhas. As funções de janela são semelhantes às funções agregadas, mas fornecem um valor para cada linha no conjunto de resultados, em vez de agrupar linhas em uma única linha de saída.
Finalmente, você dominou o agrupamento e a filtragem de dados usando as cláusulas GROUP BY e HAVING para extrair informações significativas de seus conjuntos de dados. A cláusula GROUP BY agrupa linhas que têm os mesmos valores nas colunas especificadas em linhas de resumo, enquanto a cláusula HAVING filtra essas linhas agrupadas com base em uma condição especificada.
