17 de janeiro de 2014

Facilitando a criação de queries complexas no SQL Server

Criar certas queries para um banco de dados pode ser um trabalho bastante intrincado. É o caso de consultas cujos filtros (cláusula WHERE) são baseados em cálculos independentes, bem como atualizações que são vinculadas ao resultado de alguma das funções de agregação (como SUM, MIN ou MAX). O cenário fica ainda pior se a manutenção em questão envolver mais de uma tabela, necessitando acessar outros dados através de joins.

Em geral, estas situações podem ser resolvidas mais facilmente usando-se cursores ou outra técnica de programação similar. Para quem usa o SQL Server, no entanto, a solução pode ser bem simples. Desde a versão 2005, esse gerenciador de Banco de Dados dispõe de um recurso chamado CTE - Common Table Expression ou "Expressão de Tabela Comum".

Basicamente, a CTE constrói uma tabela temporária com os registros resultantes de um comando SELECT especificado. A tabela temporária é, então, disponibilizada para o próximo comando SQL executado na mesma conexão com o banco de dados. Assim, esse segundo comando pode executar qualquer query (SELECTs, UPDATEs, DELETEs, INSERTs) referenciando a CTE como se este fosse uma tabela ordinária do banco de dados. Após esse comando ser executado, o SQL Server remove a tabela temporária criada pela CTE.

A sintaxe para declararmos uma CTE consiste na palavra chave WITH seguida pelo nome da tabela temporária, uma lista opcional para indicar os nomes que os campos assumirão, a palavra chave AS e, entre parênteses, a query propriamente dita. O quadro a seguir retrata a sintaxe descrita acima:
WITH nome-da-expressão [ ( nome-coluna-1 [ ,...nome-coluna-n ] ) ] AS
(
texto-do-SELECT
)
Para exemplificar na prática o uso desse recurso, considere o seguinte cenário: você possui uma tabela para registrar todas as alterações feitas num determinado documento em seu banco de dados. Cada alteração é cadastrada com um novo valor sequencial num campo que é parte da chave dos registros.

Em um determinado momento, você decide fazer uma faxina nos registros, limpando o banco de dados para manter apenas a última alteração registrada de cada documento. Como cada documento possui uma quantidade diferente de alterações registradas, não há um número de sequência que você possa usar como corte. A solução, então, é calcular a maior sequência de cada documento e remover os registros cujas sequências sejam menores que o valor calculado em cada documento diferente.

Veja abaixo uma definição simplificada da tabela e um conjunto hipotético dos registros contidos nela:
NUMERO_DOC TIPO_DOC SEQUENCIA USUARIO XML_ALTERACOES
1 NOTA FISCAL 0 GUSTAVO 0x00012010322032...
1 NOTA FISCAL 1 FULANO 0x00012A10962032...
1 NOTA FISCAL 2 GUSTAVO 0x012018650322027...
2 NOTA FISCAL 0 FULANO 0x012018650322027...
3 NOTA FISCAL 0 FULANO 0x012018650322027...
3 NOTA FISCAL 1 FULANO 0x012018650322027...
1 DUPLICATA CR 0 MANOELA 0x012018650322027...
1 DUPLICATA CR 1 MANOELA 0x012014603546520...
1 PEDIDO 0 JOSE 0x012014603520989...
Primeiro, vamos montar a CTE. Devemos obter um registro para cada documento diferente, calculando junto o maior número de sequência de alteração existente para cada um deles. Conseguimos isso facilmente com uma agregação simples:
WITH sel_docs AS (
SELECT numero_doc, tipo_doc, MAX(sequencia) AS sequencia
FROM docs
GROUP BY numero_doc, tipo_doc
)
Ao declarar esta CTE, omiti o nome das colunas na tabela resultante. Desse modo, os nomes adotados pelo SQL Server correspondem aos nomes na query interna. A coluna contendo o MAX não tem um nome por padrão, razão pela qual usei a cláusla AS para forçar num nome na própria query. No geral, é uma boa prática nomear explicitamente as colunas para evitar conflitos e tornar mais clara a operação como um todo.

A tabela abaixo mostra o conteúdo da nossa CTE, à qual dei o nome de sel_docs:
NUMERO_DOC TIPO_DOC SEQUENCIA
1 NOTA FISCAL 2
2 NOTA FISCAL 0
3 NOTA FISCAL 1
1 DUPLICATA CR 1
1 PEDIDO 0
Agora, a montagem do comando seguinte da conexão SQL considera o quadro acima como uma tabela do nosso banco de dados. Podemos, então, escrever o comando DELETE proposto no exemplo como um JOIN entre a CTE e a tabela original (DOCS):
DELETE docs FROM sel_docs
WHERE docs.numero_doc = sel_docs.numero_doc
AND docs.tipo_doc = sel_docs.tipo_doc
AND docs.sequencia < sel_docs.sequencia
O comando SELECT que compõe uma CTE pode ter cláusula WHERE e dispor de JOINS, se necessário. Poderíamos, portanto, ter restringido o tipo de documento na CTE do nosso exemplo ou até mesmo usar o resultado de algum cálculo envolvendo trechos do texto no campo XML_ALTERACOES para construir uma restrição mais elaborada.

Em tempo: o CTE está previsto na padronização do SQL ANSI-99; por isso, outros gerenciadores de banco de dados também implementaram esse recurso. O Oracle, por exemplo, o introduziu em sua versão 9i release 2, com sintaxe praticamente idêntica à apresentada neste post. Mais detalhes podem ser encontrados no artigo WITH Clause : Subquery Factoring.