5 Exemplos práticos de SQL's COM Cláusula

Estes cinco casos de uso prático farão com que você queira usar a cláusula SQL COM APLICAÇÃO DIÁRIA.

A cláusula WITH pode ajudá-lo a escrever consultas SQL legíveis e quebrar cálculos complexos em etapas lógicas. Ela foi adicionada ao SQL para simplificar as longas e complicadas consultas. Neste artigo, mostraremos 5 exemplos práticos da cláusula WITH e explicaremos como usá-la torna as consultas SQL mais legíveis.

A cláusula WITH também é chamada de Expressão de Tabela Comum (CTE). Aprender como trabalhar com ela não é um conhecimento básico de SQL, por isso recomendamos praticar COMO através de um curso interativo estruturado, como nosso Consultas Recursivas curso. Em mais de 100 exercícios, você aprenderá a sintaxe básica dos CTEs, assim como os conceitos avançados de CTEs aninhados e recursivos.

Qual é a cláusula com o SQL?

O outro nome da cláusula WITH, Common Table Expression, dá uma dica do que ela faz. Uma cláusula WITH permite criar uma declaração SELECT que retorna um resultado temporário; você pode nomear este resultado e referenciá-lo em outra consulta. Basicamente, é uma subconsulta nomeada, mas pode ser recursiva. Aqui está como uma cláusula COM uma subconsulta se compara a uma subconsulta.

O CTE não pode funcionar sem a consulta principal, portanto, deve ser seguido por uma consulta regular. Esta consulta é normalmente também uma declaração SELECT, mas pode ser INSERT, UPDATE, ou DELETE.

Pense em um CTE como uma tabela temporária com uma consulta que sempre tem que ser executada para que a tabela seja utilizada. Como é um tipo de tabela, você pode fazer referência ao CTE na cláusula FROM como uma tabela regular.

Sintaxe básica com a cláusula

O que explicamos acima pode ser mostrado como um código SQL:

WITH cte AS (
  SELECT …
  FROM table
)

SELECT …
FROM cte;

Vamos dar uma olhada nas partes chave da sintaxe. O CTE é sempre, sem exceção, iniciado pela cláusula WITH. O nome do CTE segue então, que é cte no exemplo genérico acima. Após o nome CTE vem a palavra-chave AS. O que se segue entre parênteses é a definição de CTE. Em um exemplo muito simples, esta é apenas uma declaração SELECT. Finalmente, há a consulta principal (como nas subconsultas regulares) que faz referência ao CTE.

Lembre-se, esta é uma sintaxe básica. Vamos revisitá-la nos exemplos. Ao longo do caminho, você também verá como esta sintaxe básica pode ser estendida escrevendo CTEs múltiplos ou aninhados.

Exemplos 1 & 2: Informações sobre salários

Dados de exemplo

Vamos usar a tabela employees nos dois primeiros exemplos. Ela tem as seguintes colunas:

• id - A identificação do funcionário.
• first_name - O primeiro nome do funcionário.
• last_name - O sobrenome do funcionário.
• salary - O salário do empregado.
• department - O departamento do funcionário.

Aqui estão os dados:

E aqui está um link para uma consulta que você pode executar para criar a tabela:

Exemplo 1: Mostrar como o salário de cada funcionário se compara ao salário médio da empresa

Para resolver este problema, você precisa mostrar todos os dados da tabela employees. Além disso, é preciso mostrar o salário médio da empresa e depois a diferença para o salário de cada funcionário.

Aqui está a solução:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
)
	
SELECT id,
	 first_name,
	 last_name,
	 salary,
	 department,
	 average_company_salary,
	 salary - average_company_salary  AS salary_difference
FROM employees, avg_total_salary;

Primeiro, iniciar o CTE usando a cláusula WITH. O nome do CTE ('avg_total_salary') vem depois disso. Abra os parênteses após AS, e escreva a declaração regular SELECT. Ele calcula o salário médio da empresa.

Para usar a saída do CTE, escreva outra declaração SELECT como a principal consulta. Observe que o CTE e a consulta principal são separados apenas por parênteses. A nova linha só existe para a legibilidade. A consulta principal seleciona todas as colunas tanto da tabela quanto do CTE. O CTE e a tabela employees Além disso, há a coluna calculada salary_difference.

A consulta retorna isto:

Podemos ver todos os dados dos funcionários e quanto seu salário está acima ou abaixo da média da empresa. Por exemplo, o salário de Melissa Allman é 293,48 mais alto do que a média. O salário de Nina Longhetti é 993,20 abaixo da média.

Exemplo 2: Mostrar os dados dos funcionários junto com a média do Departamento e o salário médio da empresa

Vamos um pouco mais longe do exemplo anterior. Desta vez, não é preciso calcular a diferença entre os salários. Mas você precisa mostrar os dados analíticos de todos os funcionários e depois mostrar o salário médio do departamento e da empresa.

Você pode fazer isso escrevendo dois CTEs ao invés de um:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
),
	
avg_dpt_salary AS (
	SELECT department,
		 AVG(salary) AS average_department_salary
	FROM employees
	GROUP BY department
)
	
SELECT e.id,
	   e.first_name,
	   e.last_name,
	   e.salary,
	   average_department_salary,
	   average_company_salary
FROM employees e JOIN avg_dpt_salary ads ON e.department = ads.department, avg_total_salary;

Lembre-se de que mencionamos que a sintaxe básica poderia ser estendida. Aqui está um exemplo de como fazer isso. Sim, você pode escrever vários CTEs, um após o outro, e depois referenciá-los na consulta principal.

Vamos ver como fazer isso. Tudo é usual com o primeiro CTE: COM cláusula, o nome CTE, AS, e a definição CTE entre parênteses. Este CTE calcula o salário médio da empresa.

O segundo CTE calcula o salário médio por departamento. Mas há algo diferente aqui! Observe duas coisas principais: não há outra cláusula COM A cláusula, e os CTEs são separados por uma vírgula.

É assim que se escrevem múltiplos CTEs: a cláusula COM CTE é escrita somente antes da primeira consulta (não deve aparecer antes de qualquer outro CTE!), e uma vírgula deve separar os CTEs.

Não importa o número de CTEs, eles devem ser sempre seguidos pela consulta principal. E não há vírgula entre o último CTE e a consulta principal!

A consulta principal junta-se à tabela com os dois CTEs e seleciona os dados relevantes. A adesão é feita da mesma forma que com as tabelas regulares: especifique o tipo de adesão e a coluna em que as tabelas serão unidas.

Aqui está a saída:

Agora você tem todos os dados em um só lugar: salários individuais, média do departamento e média da empresa. A partir daqui, você pode ir mais longe em sua análise de dados.

Exemplos 3 & 4: Receita

Dados de exemplo

Nos próximos dois exemplos, vamos utilizar a tabela revenue. Criamo-lo com a consulta CREATE TABLE encontrada aqui. Ela tem as seguintes colunas:

• id - A identificação do registro de receita.
• year - O ano da receita.
• quarter - O trimestre da receita.
• revenue_amount - O valor da receita.

Familiarize-se com os dados mostrados abaixo:

Exemplo 3: Mostrar cada ano com a receita anual correspondente e a receita total

Use os dados acima para mostrar todos os anos disponíveis. Junto com cada ano, mostre a receita anual desse ano e a receita total da empresa em todos os anos.

Esta tarefa é semelhante ao Exemplo 1, mas usaremos uma função agregada diferente:

WITH total_revenue AS (
	SELECT SUM(revenue_amount) AS total_company_revenue
	FROM revenue
)
	
SELECT year,
	 SUM (revenue_amount) AS annual_revenue,
	 total_company_revenue
FROM revenue, total_revenue
GROUP BY year, total_company_revenue
ORDER BY year;

O CTE usa a função agregada SUM() para calcular a receita total da empresa.

A consulta principal junta-se ao CTE com a tabela revenue. Usamos esta declaração SELECT para mostrar o ano, depois calculamos a receita anual para cada ano e mostramos a receita total.

Esta é a saída da consulta:

O resultado mostra que a receita anual em 2019 foi de 10.489.632,27. Em 2020, foi de 20.919.087,62, e assim por diante. Se todos os quatro anos forem somados, a receita total será de 80.812.015,89.

Exemplo 4: Mostrar cada trimestre junto com a maior e a menor receita trimestral para aquele ano e o total

Você precisa listar todos os anos e trimestres com a receita correspondente. Até agora, tudo bem. Então você precisa mostrar a menor receita trimestral para aquele ano e a menor receita trimestral para todos os anos. A seguir, você faz o mesmo para a maior receita.

Aqui está a solução:

WITH yearly_min_max_quarter AS (
	SELECT year,
		   MIN(revenue_amount) AS minimum_quarterly_revenue_annual,
		   MAX(revenue_amount) AS maximum_quarterly_revenue_annual
FROM revenue
GROUP BY year),

min_max_overall AS (
	SELECT MIN(revenue_amount) AS overall_min_revenue,
	       MAX(revenue_amount) AS overall_max_revenue
FROM revenue)

SELECT r.year,
	   quarter,
	   revenue_amount,
	   minimum_quarterly_revenue_annual,
	   overall_min_revenue,
	   maximum_quarterly_revenue_annual, 
	   overall_max_revenue
FROM revenue r 
JOIN yearly_min_max_quarter ymmq 
ON r.year = ymmq.year, min_max_overall
ORDER BY year, quarter ASC;

Esta solução novamente requer dois CTEs. Você provavelmente já sabe como escrever isto, mas vamos explicar cada passo.

O primeiro CTE encontra a menor e a maior receita trimestral para cada ano. Para conseguir isso, use as funções MIN() e MAX() e agrupe os dados por ano.

Em seguida, escreva o segundo CTE sem a cláusula WITH e separe-o por uma vírgula do primeiro. Este CTE retorna a menor e a maior receita trimestral em todos os anos.

A consulta principal junta-se à tabela revenue com o primeiro e depois com o segundo CTE. Ele mostrará os dados da tabela e do CTE como uma só tabela.

Dê uma olhada no resultado:

Junto com a receita de cada trimestre, você tem agora algumas outras informações importantes. Você sabe que a receita mais baixa em 2019 foi de 984.157,15, e você pode ver que isso aconteceu no terceiro trimestre. A receita mais baixa em 2020 (2.497.441,68) foi nos três primeiros meses do ano. Você pode analisar todos os outros anos de forma semelhante olhando para a coluna minimum_quarterly_revenue_annual.

O valor na coluna overall_min_revenue representa a receita mais baixa de todos os tempos. É o mesmo em todas as linhas e corresponde a 2019T3. As duas colunas seguintes são semelhantes, mas mostram a receita mais alta em vez da mais baixa. Em outras palavras, a maior receita em 2019 foi de 5.417.884,15, que é do quarto trimestre. A maior receita de todos os tempos é de 12.478.945,47, o que foi realizado em 2021T1.

Exemplo 5: Horas Trabalhadas

Dados de exemplo

A tabela para o último exemplo é chamada folha de horas_de_trabalho. Ele registra o horário de trabalho dos funcionários. Suas colunas são auto-explicativas, portanto, vamos apenas olhar para os dados:

Exemplo 5: Mostrar as horas de trabalho médias mais baixas e mais altas

Este exemplo quer que você primeiro encontre a média de horas de trabalho por funcionário e depois só produza a média mais baixa e a mais alta.

Aqui está o código para resolver este problema:

WITH login_times AS (
SELECT 
id,
employee_id,
start_time,
end_time,
end_time - start_time AS working_hours
FROM employee_timesheet),

avg_login AS (
	SELECT 
employee_id,
AVG(working_hours) AS average_working_hours
FROM login_times
GROUP BY employee_id)

SELECT MIN(average_working_hours) AS min_average_working_hours,
	 MAX(average_working_hours) AS max_average_working_hours
FROM avg_login;

No início, isto poderia parecer como qualquer consulta com dois CTEs. Olhe mais de perto, e você verá que não é! Sim, há dois CTEs. Mas a diferença é que o segundo CTE faz referência ao primeiro, o que não era o caso nos Exemplos 2 e 4.

Isto é chamado de um CTE aninhado. O primeiro CTE é usado para obter a diferença entre o início e o fim do login; é assim que você obtém as horas de trabalho para cada sessão.

Um funcionário tem várias sessões, então precisamos encontrar a duração média da sessão, ou seja, a média de horas trabalhadas. O segundo CTE é usado para esse fim. Em termos de sintaxe, nada é novo, exceto que o CTE faz referência ao primeiro CTE na cláusula FROM.

Em seguida, na consulta principal, fazemos algo chamado agregação multinível. Pegamos a média de horas de trabalho por empregado (agregação de primeiro nível) e encontramos o mínimo e o máximo desses valores (agregação de segundo nível).

O resultado é o seguinte:

O resultado nos diz que a média mais baixa de horas de trabalho por funcionário é de 5 horas, 57 minutos e 4 segundos. A média mais alta é de 13:24:29.

Benefícios do SQL COM a Cláusula

Os cinco exemplos que mostramos foram cuidadosamente selecionados para mostrar o uso e os benefícios típicos da cláusula WITH.

O primeiro benefício torna-se claro quando você tem várias etapas de cálculo, como vimos nos exemplos acima. Usando a cláusula WITH, você pode organizar bem o código e dividi-lo em partes lógicas.

Quando os cálculos se tornam mais complicados, o comprimento e a complexidade do código também aumentam. O uso da cláusula WITH é ótimo para manter isso sob controle. Embora os códigos nos exemplos acima possam parecer longos, eles ficariam significativamente mais longos (e menos legíveis) se utilizássemos subconsultas em vez da cláusula WITH. E como você viu no último exemplo, o uso da cláusula WITH permite calcular facilmente as agregações multiníveis.

Um outro benefício é que a cláusula WITH lhe permite escrever consultas recursivas em SQL, o que abre um mundo totalmente novo de possibilidades.

Aprender a cláusula WITH às vezes pode ser esmagador, por isso preparamos um guia que o ajudará a estruturar sua abordagem. Feliz aprendizado, e sabemos que a cláusula SQL WITH irá mais do que recompensar seus esforços!