Common Table Expressions - CTE

Common Table Expressions (CTE) são visões localmente definidas dentro de uma query que permitem a construção de selects incrivelmente complexos e úteis.

Sem este recurso, o trabalho para cria um relatório de referência cruzada, por exemplo, seria bastante trabalhoso. Felizmente muitos bancos de dados suportam o uso de CTE. CTE é parte do ANSI SQL-99 standard.

O exemplo exibido logo abaixo, é um SQL desenvolvido por mim, para um relatório de comissões de vendedores que mostra as comissões totalizadas para cada mês de um ano.

O exemplo faz parte do ERP PoliGestor - Gestão Empresarial da empresa POLIDADOS INFORMÁTICA TOTAL. O banco de dados utilizado foi o "Firebird 2.5".

/*
  Lista comissões dos vendedores por mês
  
  Autor: Everton de Vargas Agilar
  Data: 06/08/2009

 */ 
with comissoes(idvendedor, mes, ano, vlr_vendas, valor_comissao)
as
(
  select tbl.idvendedor,
         extract(month from tbl.data) mes,
         extract(year from tbl.data) ano,
         sum(tbl.vlr_total) vlr_vendas,
         sum(tbl.comissao_valor) comissao_venda
  from orcamentos tbl
  where tbl.filial = :filial and tbl.cancelado = 0 and
        extract(year from tbl.data) = :ano
  group by tbl.idvendedor, 2, 3
  having sum(tbl.comissao_valor) > 0
)
select
  id, nome_vendedor,
  comissao_jan, comissao_fev,
  comissao_mar, comissao_abr,
  comissao_mai, comissao_jun,
  comissao_jul, comissao_ago,
  comissao_set, comissao_out,
  comissao_nov, comissao_dez
from (
    select
       v.id,
       v.nome nome_vendedor,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 1) as comissao_jan,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 2) as comissao_fev,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 3) as comissao_mar,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 4) as comissao_abr,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 5) as comissao_mai,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 6) as comissao_jun,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 7) as comissao_jul,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 8) as comissao_ago,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 9) as comissao_set,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 10) as comissao_out,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 11) as comissao_nov,
       (select coalesce(c.valor_comissao, 0) from comissoes c
        where c.idvendedor = v.id and c.mes = 12) as comissao_dez
    from vendedores v 
    where v.filial = :filial
    union all
    select -1,
       'Totais por mês ==>',
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 1) as comissao_jan,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 2) as comissao_fev,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 3) as comissao_mar,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 4) as comissao_abr,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 5) as comissao_mai,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 6) as comissao_jun,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 7) as comissao_jul,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 8) as comissao_ago,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 9) as comissao_set,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 10) as comissao_out,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 11) as comissao_nov,
       (select sum(c.valor_comissao) from comissoes c
        where c.mes = 12) as comissao_dez
    from rdb$database
)
order by nome_vendedor nulls last;

Conclusão: Common Table Expressions (CTE) é um recurso que merece ser explorado, principalmente porque permite a construção de selects complexos e muito performático, se comparado a outras alternativas. O mesmo relatório poderia ser desenvolvido utilizando outras alternativas, como views, procedures armazenadas no banco ou mesmo desenvolvido usando uma linguagem de programação tradicional.

Sintáxe para o CTE:

WITH [RECURSIVE]
 CTE_A [(a1, a2, …)] 
 AS ( SELECT … ),

 CTE_B [(b1, b2, …)]
 AS ( SELECT … ),
...
SELECT ...
  FROM CTE_A, CTE_B, TAB1, TAB2 ...
 WHERE ...