CTE

CTE

CTE significa "Common Table Expression"" e pode ser considerado um conjunto de resultados temporário, recuperado no início e reutilizado mais tarde quantas vezes for necessário. O conjunto de resultados funciona como uma tabela temporária, que é necessário declarar explicitamente e dura apenas enquanto o comando estiver em execução. Um CTE é geralmente considerado mais legível que uma subconsulta. Um CTE é mais poderoso que uma subconsulta pois também pode ser auto-referiado ou mesmo referenciado várias vezes na mesma consulta. Esta funcionalidade permite executar queries hierarquicos, que desta forma ficam disponíveis nos SGBDs que não os suportavam com sintaxe própria. Neste módulo vamos ver:

CTE simples
Usar CTE para evitar subselect
Usar CTE para query hierarquico

CTE simples

Um exemplo simples de CTE, não muito útil, mas suficiente para mostrar o conceito.

WITH MyCTE AS (  
      SELECT employee_id, first_name, last_name, manager_id
      FROM employees
      WHERE manager_id IS NULL 
)
SELECT *
FROM MyCTE;

EMPLOYEE_ID|FIRST_NAME|LAST_NAME|MANAGER_ID|
-----------|----------|---------|----------|
        100|Steven    |King     |          |

Um exemplo mais elaborado: vamos procurar funcionários que têm um salário superior à média do departamento em que trabalham:

with average_salary as ( 
   select department_id, avg(salary) as avg_sal 
   from employees
   group by department_id
)
select e.first_name, e.last_name, e.salary, y.department_name, a.avg_sal
from employees e
inner join average_salary a on e.department_id=a.department_id
inner join departments y on e.department_id=y.department_id
where e.salary > a.avg_sal
order by 1;

FIRST_NAME|LAST_NAME|SALARY|DEPARTMENT_NAME|AVG_SAL                                  |
----------|---------|------|---------------|-----------------------------------------|
Adam      |Fripp    |  8200|Shipping       |3475.555555555555555555555555555555555556|
Alberto   |Errazuriz| 12000|Sales          |8955.882352941176470588235294117647058824|
Alexander |Hunold   |  9000|IT             |                                     5760|
Alexis    |Bull     |  4100|Shipping       |3475.555555555555555555555555555555555556|
Allan     |McEwen   |  9000|Sales          |8955.882352941176470588235294117647058824|
Britney   |Everett  |  3900|Shipping       |3475.555555555555555555555555555555555556|
Bruce     |Ernst    |  6000|IT             |                                     5760|
Clara     |Vishney  | 10500|Sales          |8955.882352941176470588235294117647058824|
Daniel    |Faviet   |  9000|Finance        |8601.333333333333333333333333333333333333|
Danielle  |Greene   |  9500|Sales          |8955.882352941176470588235294117647058824|
David     |Bernstein|  9500|Sales          |8955.882352941176470588235294117647058824|
Den       |Raphaely | 11000|Purchasing     |                                     4150|
Eleni     |Zlotkey  | 10500|Sales          |8955.882352941176470588235294117647058824|
Ellen     |Abel     | 11000|Sales          |8955.882352941176470588235294117647058824|
Gerald    |Cambrault| 11000|Sales          |8955.882352941176470588235294117647058824|
Harrison  |Bloom    | 10000|Sales          |8955.882352941176470588235294117647058824|
Janette   |King     | 10000|Sales          |8955.882352941176470588235294117647058824|
Jennifer  |Dilly    |  3600|Shipping       |3475.555555555555555555555555555555555556|
John      |Russell  | 14000|Sales          |8955.882352941176470588235294117647058824|
Karen     |Partners | 13500|Sales          |8955.882352941176470588235294117647058824|
Kelly     |Chung    |  3800|Shipping       |3475.555555555555555555555555555555555556|
Kevin     |Mourgos  |  5800|Shipping       |3475.555555555555555555555555555555555556|
Lisa      |Ozer     | 11500|Sales          |8955.882352941176470588235294117647058824|
Matthew   |Weiss    |  8000|Shipping       |3475.555555555555555555555555555555555556|
Michael   |Hartstein| 13000|Marketing      |                                     9500|
Nancy     |Greenberg| 12008|Finance        |8601.333333333333333333333333333333333333|
Nandita   |Sarchand |  4200|Shipping       |3475.555555555555555555555555555555555556|
Patrick   |Sully    |  9500|Sales          |8955.882352941176470588235294117647058824|
Payam     |Kaufling |  7900|Shipping       |3475.555555555555555555555555555555555556|
Peter     |Hall     |  9000|Sales          |8955.882352941176470588235294117647058824|
Peter     |Tucker   | 10000|Sales          |8955.882352941176470588235294117647058824|
Renske    |Ladwig   |  3600|Shipping       |3475.555555555555555555555555555555555556|
Sarah     |Bell     |  4000|Shipping       |3475.555555555555555555555555555555555556|
Shanta    |Vollman  |  6500|Shipping       |3475.555555555555555555555555555555555556|
Shelley   |Higgins  | 12008|Accounting     |                                    10154|
Steven    |King     | 24000|Executive      | 19333.3333333333333333333333333333333333|
Tayler    |Fox      |  9600|Sales          |8955.882352941176470588235294117647058824|
Trenna    |Rajs     |  3500|Shipping       |3475.555555555555555555555555555555555556|

O query dentro do CTE determina a média de salários para cada departamento;
O query fora do CTE faz uma junção entre a tabela dos empregados e o query anterior (média dos salários) para comparar o salário de cada funcionário com a média do departamento;

Topo

Usar CTE para evitar subselect

Vamos procurar os funcionários que recebem o menor salário em cada departamento. Propomos duas consultas possíveis:

select first_name,salary,job_id
from employees
where (job_id, salary) in (select job_id, min(salary) 
                           from employees
                           group by job_id)
order by job_id, salary;
-- ---------------------------------------------------------------------

WITH MyCTE (job, min_sal)
AS (  select job_id, min(salary) 
      from employees
      group by job_id)
select e.first_name, e.salary, e.job_id
from employees e
inner join MyCTE on MyCTE.job=e.job_id and MyCTE.min_sal=e.salary;

FIRST_NAME|SALARY|JOB_ID    |
----------|------|----------|
William   |  8300|AC_ACCOUNT|
Shelley   | 12008|AC_MGR    |
Jennifer  |  4400|AD_ASST   |
Steven    | 24000|AD_PRES   |
Lex       | 17000|AD_VP     |
Neena     | 17000|AD_VP     |
Luis      |  6900|FI_ACCOUNT|
Nancy     | 12008|FI_MGR    |
Susan     |  6500|HR_REP    |
Diana     |  4200|IT_PROG   |
Michael   | 13000|MK_MAN    |
Pat       |  6000|MK_REP    |
Hermann   | 10000|PR_REP    |
Karen     |  2500|PU_CLERK  |
Den       | 11000|PU_MAN    |
Eleni     | 10500|SA_MAN    |
Sundita   |  6100|SA_REP    |
Martha    |  2500|SH_CLERK  |
Randall   |  2500|SH_CLERK  |
TJ        |  2100|ST_CLERK  |
Kevin     |  5800|ST_MAN    |

Ambos produzem o mesmo resultado, embora as linhas não venham na mesma ordem porque seguem planos de execução diferentes;
O query com CTE substitui o operador IN por uma junção, o que do ponto de vista de desempenho é melhor. Quando o IN tem muitos resultados para pesquisar torna-se muito lento;
Se comparar os planos de execução dos dois queries vai confirmar que o segundo tem menor custo total e movimenta menos bystes

O exemplo a seguir deve ser executado no schema SQLTuning e mostra duas consultas possíveis para encontrar clientes que fizeram pedidos com o maior número de itens. Novamente, usamos uma solução com subselect e outra com CTE:

select *
from customer c
inner join sales_order so on c.customer_id = so.customer_id
where so.order_id in (	select i1.order_id
				      	from item i1
				      	group by i1.order_id
				      	having count(*) = (select max(count(*))
				                           from item i2
				                           group by i2.order_id
				                          )
                     );
-- --------------------------------------------------      
with MyCTE1 (order_id, item_count) as (
      select so2.order_id, count(*) 
      from sales_order so2
      inner join item i1 on i1.order_id=so2.order_id
      group by so2.order_id 
), MyCTE2 (max_count) as ( 
      select max(a.item_count)
      from MyCTE1 a
)
select *
from customer c
inner join sales_order so on c.customer_id = so.customer_id
inner join MyCTE1 on MyCTE1.order_id=so.order_id
inner join MyCTE2 on MyCTE1.item_count = MyCTE2.max_count;

CUSTOMER_ID|NAME        |ADDRESS      |CITY       |STATE|ZIP_CODE|AREA_CODE|PHONE_NUMBER|SALESPERSON_ID|CREDIT_LIMIT|ORDER_ID|ORDER_DATE         |CUSTOMER_ID|SHIP_DATE          |TOTAL|OBS                                               |ORDER_ID|ITEM_COUNT|MAX_COUNT|
-----------|------------|-------------|-----------|-----|--------|---------|------------|--------------|------------|--------|-------------------|-----------|-------------------|-----|--------------------------------------------------|--------|----------|---------|
        105|K + T SPORTS|3476 EL PASEO|SANTA CLARA|CA   |91003   |      408|     3769966|          7844|        5000|     617|2009-02-05 00:00:00|        105|2009-03-03 00:00:00|46370|aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa|     617|        10|       10|

Neste exemplo temos o mesmo problema de performance que no exemplo anterior, mas como as tabelas têm maior dimensão, os planos de execução apresentam diferenças mais significativas, sendo mais evidente a melhoria com o CTE, que evita o IN;

Este query mostra os salários de cada empregado comparando-os com a média do departamento e da função. Como se trata de um subquery correlacionado vamos tentar arranjar uma solução mais eficiente com subselect simples:

select
   e1.ename,
   e1.sal,
   e1.job,
   (select round(avg(e2.sal)) from emp e2 where e2.job=e1.job) "Avg_Sal_Job",
   e1.deptno,
   (select round(avg(e3.sal)) from emp e3 where e3.deptno=e1.deptno) "Avg_Sal_Deptno"
from emp e1;

WITH w_avg_sal_job AS (
	SELECT job, avg(sal) AS Avg_Sal_Job
	FROM emp
	GROUP BY job
),
w_avg_sal_deptno AS (
	SELECT deptno, avg(sal) AS Avg_Sal_Deptno
	FROM emp
	GROUP BY deptno
)
SELECT
   e1.ename,
   e1.sal,
   e1.job,
   j.Avg_Sal_Job,
   e1.deptno,
   d.Avg_Sal_Deptno
FROM emp e1
INNER JOIN w_avg_sal_job j ON j.job=e1.job
INNER JOIN w_avg_sal_deptno d ON d.deptno=e1.deptno;

Topo

Usar CTE para query hierarquico

Alguns SGBDR não disponibilizam o query hierarquico do Oracle, não sendo possível resolver problemas deste tipo. O CTE permite recursividade, que com o auxilio das funções analíticas permitem resolver este problema:

WITH MyCTE (empno, ename, mgr, hierachy_level, myorder) AS (
	SELECT  boss.empno,
		boss.ename,
		boss.mgr,
		1 as hierachy_level,
		CAST(ROW_NUMBER() OVER (ORDER BY boss.empno) as VARCHAR2(4000)) as myorder
	FROM emp boss
	WHERE boss.mgr IS NULL 
	UNION ALL
	SELECT  emp.empno,
		emp.ename,
		emp.mgr,
		MyCTE.hierachy_level+1,
		MyCTE.myorder || '.' || CAST(ROW_NUMBER() OVER (PARTITION BY emp.mgr ORDER BY emp.empno) as VARCHAR2(4000)) as myorder
	FROM emp
	INNER JOIN MyCTE ON emp.mgr = MyCTE.empno
	WHERE emp.mgr IS NOT NULL
)
SELECT myc.*,
	  lpad(' ', (myc.hierachy_level-1)*6) || '* ' || ename as hierarchy
FROM MyCTE myc 
order by myc.myorder;

EMPNO|ENAME |MGR |HIERACHY_LEVEL|MYORDER|HIERARCHY                |
-----|------|----|--------------|-------|-------------------------|
 7839|KING  |    |             1|1      |* KING                   |
 7566|JONES |7839|             2|1.1    |      * JONES            |
 7788|SCOTT |7566|             3|1.1.1  |            * SCOTT      |
 7876|ADAMS |7788|             4|1.1.1.1|                  * ADAMS|
 7902|FORD  |7566|             3|1.1.2  |            * FORD       |
 7369|SMITH |7902|             4|1.1.2.1|                  * SMITH|
 7698|BLAKE |7839|             2|1.2    |      * BLAKE            |
 7499|ALLEN |7698|             3|1.2.1  |            * ALLEN      |
 7521|WARD  |7698|             3|1.2.2  |            * WARD       |
 7654|MARTIN|7698|             3|1.2.3  |            * MARTIN     |
 7844|TURNER|7698|             3|1.2.4  |            * TURNER     |
 7900|JAMES |7698|             3|1.2.5  |            * JAMES      |
 7782|CLARK |7839|             2|1.3    |      * CLARK            |
 7934|MILLER|7782|             3|1.3.1  |            * MILLER     |

Podemos obter a hierarquia da empresa usando uma consulta CTE recursiva. Esta consulta mostra os níveis hierárquicos de forma semelhante ao query hierarquico. Um CTE recursivo requer quatro elementos para funcionar corretamente:

Consulta de nível zero: executa uma vez e retorna o elemento de nível superior;
Consulta recursiva: executa várias vezes e é o critério para os restantes resultados;
Instrução UNION ALL para juntar os resultados da consulta de nível 0 e recursiva;
Instrução INNER JOIN para juntar a consulta recursiva aos resultados do CTE;

Observe a coluna hierachy_level que tira partido da propriedade recursiva e aumenta em 1 unidade cada vez que a consulta vai um nível mais abaixo. Usamos esse valor para produzir, na última coluna, um output visual que mostra a hierarquia entre os funcionários.

Também precisamos da função analítica para atribuir um número a cada linha e usar esse número para classificar o output. Sem essa coluna, a ordem de escrita estaria incorreta.

Topo

Realizado por Turtle Learning ®. Última alteração em 2019-12-08