5.6 Subqueries e CTE

As subqueries (ou subconsultas) são instruções SELECT utilizadas dentro de outras consultas SELECT. Se inicialmente podem parecer confusas, entendê-las é essencial para criar análises complexas em SQL. Como implicado no seu nome, as subqueries contém uma ou mais consultas dentro de si mesmas. Elas podem ser usadas dentro de diferentes predicados SQL como SELECT, FROM, WHERE ou HAVING e também podem retornar tanto um valor único como uma lista de valores.

Para facilitar o entendimento, podemos separar as subqueries em diferentes consultas e considerar que as consultas internas são sempre executadas antes das externas. Por exemplo, vamos supor que o gerente comercial da Northwind quer saber quais pedidos tiveram um ticket médio acima da média. Primeiro, o subtotal de cada pedido precisa ser calcular a partir da tabela de Pedido Item (OrderDetail):

%%sql
SELECT OrderId, SUM(UnitPrice * Quantity) AS subtotal
FROM 'OrderDetail'
GROUP BY OrderId
LIMIT 5;

OrderId	subtotal
10248	440.0
10249	1863.4
10250	1813.0
10251	670.8
10252	3730.0

Para calcular o ticket médio dos pedidos, precisamos então computar a média do subtotal, utilizando a consulta acima como uma subquery:

%%sql

SELECT AVG(subtotal) AS ticket_medio 
FROM (
 	SELECT OrderId, SUM(UnitPrice * Quantity) AS subtotal
 	FROM 'OrderDetail'
 	GROUP BY OrderId
);

ticket_medio
1631.877819277109

Por fim, precisamos usar a subquery que computa o ticket médio dentro de outra consulta como parte do filtro, afinal queremos saber os pedidos que tiveram valor acima do ticket médio. Neste caso, três subqueries são necessárias: a query para calcular o subtotal dos pedidos é usada duas vezes e outra subquery é usada para calcular a média dos pedidos:

%%sql
SELECT OrderId, subtotal FROM (
SELECT OrderId, SUM(UnitPrice * Quantity) AS subtotal
 FROM 'OrderDetail' 
 GROUP BY OrderId
) 
WHERE subtotal > (
 SELECT AVG(subtotal) AS ticket_medio FROM (
 	SELECT OrderId, SUM(UnitPrice * Quantity) AS subtotal
 	FROM 'OrderDetail'
 	GROUP BY OrderId
 ))
 LIMIT 5

OrderId	subtotal
10249	1863.4
10250	1813.0
10252	3730.0
10255	2490.5
10258	2018.6

Mesmo neste exemplo simples já é possível notar que o uso das subqueries pode facilmente se tornar de difícil entendimento. Uma nova subquery poderia ser usada por exemplo para calcular a quantidade de pedidos acima do ticket médio, adicionando mais um nível na consulta. Para facilitar a criação de consultas complexas, alguns bancos de dados permitem o uso das chamadas CTEs (Common Table Expressions), que são uma forma de “salvar” resultados de consultas para serem usados posteriormente dentro de outra consulta. A estrutura básica de uma CTE é a seguinte:

WITH nome_da_cte AS (
  (Consulta SELECT)
)

Para executar uma CTE precisamos chamá-la em uma ou mais consultas SELECT como se fosse uma tabela:

SELECT * FROM nome_da_cte

Também é possível criar diversas CTE’s em sequência. No exemplo anterior podemos usar algumas CTEs para salvar as nossas subqueries e facilitar o entendimento da consulta. Nota-se como uma CTE pode ser usada tanto como parte de outra (FROM) como na consulta final, por sua vez muito mais clara que na versão com subqueries:

O conhecimento de subqueries e CTEs dá a capacidade de criar consultas complexas em SQL e gerar análises avançadas para a tomada de decisão ou transformar dados com robustez. Embora extremamente flexíveis, ambas também podem se tornar muito ineficientes. Na prática, as CTEs são geralmente preferíveis às subqueries devido a sua maior facilidade de entendimento, principalmente em projetos em equipe. Para os fins de análise de dados, o custo em horas de trabalho para entender uma consulta complexa feita por outra pessoa é na grande maioria dos casos maior que a perda de alguns segundos ou minutos em uma consulta ineficiente. SQL é uma linguagem rica e com enormes possibilidades. Dominá-lo permite ao analytics engineer se diferenciar na sua capacidade de analisar e transformar dados. O objetivo deste capítulo foi dar as bases para esse aprendizado. Na parte 3 vamos colocar esse conhecimento em prática na transformação de dados.