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
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| |
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 | |
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| |
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; |
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:
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.
Realizado por Turtle Learning ®. Última alteração em 2019-12-08