Construindo Uma Consulta Sql Passo-a-passo

[Micheus]

Este "artigo" ficou meio longo, mas para quem não tem muita afinidade com a construção de um SQL mais complexo, talvez vala a pena lê-lo até o final.

A performance de consultas deste tipo dependerá de vários fatores, dentre eles: "tamanho" da base, existência de índices pelos campos envolvidos nas consultas, banco de dados utilizado. Uma pequena avaliação comparativa entre o método utilizado aqui e uma segunda alternativa a este tipo de consulta pode ser baixado para avaliação: Estatistica de resultado de uma consulta SQL

Eu vou utilizar como exemplo o banco de dado EMPLOYEE que acompanha o FireBird (o qual, se não me engano, também vem com o INTERBASE). É uma base pequeníssima, mas que servirá para o propósito.

Ao final do artigo estão anexos a base de dados e o script da consulta completa.

Inicialmente devo considerar que o Firebird passou a dar suporte a sub-selects, no formato proposto, apenas com o lançamento da versão 2.0. Assim, para versões anteriores, haveria a alternativa de criar uma procedure para retornar os resultados que seriam obtidos com o sub-select - este recurso (consultar no resultado de uma procedure) já estaria disponível na versão anterior.

Eu não saberia dizer em que versão do Interbase este recurso foi implementado, mas sendo o Firebird (sua primeira versão) derivado da versão 6, até esta versão, com certeza não foi implementado.

Das tabelas utilizadas

- EMPLOYEE (empregado) - nos interessam os campos *EMP_NO e FIRST_NAME;

- SALES (vendas) - nos enteressam os campos *SALES_REP, SHIP_DATE e TOTAL_VALUE.

Objetivo da consulta

- Totalizar, no período de um ano (de Jan a Dez), as vendas mês-a-mês realizadas e agrupadas por empregados.

Considerações sobre o problema

- Precisamos "enumerar" todos os empregados que realizaram alguma venda no período;

- Para cada empregado, devemos totalizar as vendas para cada mês no período;

- Desejamos obter o resultado no formato de planilha: Linha (Empregado), Coluna (Mês no período) e Intercessão (Valores totais).

Construindo a consulta SQL

1) Obtenção da relação dos empregados que efetuaram vendas

Como queremos apenas os empregados que realizaram vendas num período, deveremos "buscar" esta informação na tabela SALES. Mas, como sabemos que esta é uma tabela onde a relação é N, ou seja, existem vários registros para um mesmo empregado, então precisaremos fazer a consulta de modo a obtermos apenas uma ocorrência (linha resultado) para cada empregado.

Este efeito pode ser obtido através do uso da palavra reservada DISTINCT. Assim, já temos como partida a seguinte consulta:

SELECT DISTINCT SALES_REP

FROM SALES

Precisaremos ainda restringir o resultado para apenas os empregados que fizeram a vendas no período desejado (início e fim). Para isto, utilizaremos a cláusula WHERE:

SELECT DISTINCT SALES_REP

FROM SALES

WHERE SHIP_DATE BETWEEN '1993-01-01' AND '1993-12-31'

Agora já temos uma consulta que retorna os empregados que realizaram vendas num período desejado. A título de referência, vamos chamá-la de CnsEmpregadoVenda.

2) Obtenção do valor total vendido por um determinado vendedor

Para montar nossa consulta final, precisaremos avaliar como será montada a consulta para obtermos os totais das vendas, num mês, para cada empregado. Estaremos novamente fazendo uso da tabela SALES.

Esta totalização é obtida através da combinação de funções de agregação (tais como SUM, COUNT e MAX) e uso da cláusula GROUP BY, já que desejamos fazer um somatório agrupando o resultado por empregado. Assim, nossa consulta ficaria inicialmente como segue:

SELECT SALES_REP, SUM(TOTAL_VALUE)

FROM SALES

GROUP BY SALES_REP

OBS: Na cláusula GROUP BY devem ser adicionados todos os campos definidos na cláusula SELECT e que não seja uma função de agregação.

Porém, da mesma forma como na etapa anterior, precisamos restringir o período a ser avaliado o resultado. Como nosso objetivo é uma totalização mês-a-mês, a cláusula WHERE deverá restringir o resutado de acordo com o mês e ano informados e para tanto temos duas formas de fazermos isto:

a - utilizando BETWEEN e indicando dia inicial e final do mês em questão;

b - utilizando uma função que extraia o número do mês e do ano a partir da data da venda - esta função é EXTRACT.

Eu acredito que a forma mais simples seja a opção "b", assim não teremos nehuma preocupação com relação a ano bissexto ou se o mês termina com 30 ou 31.

Alterando nossa consulta para utilizarmos o filtro, ela ficará assim:

SELECT SALES_REP_<MES_NOME>, SUM(TOTAL_VALUE) AS TOTAL_VALUE_<MES_NOME>

FROM SALES

WHERE EXTRACT(MONT FROM SHIP_DATE) = <MES>

AND EXTRACT(YEAR FROM SHIP_DATE) = EXTRACT(YEAR FROM <DAT_INICIAL>)

GROUP BY SALES_REP

Observe que utilizei <MES> na consulta para indicar que deveremos alí colocar o número referente ao mês que queremos obter o resultado e, também, utilizei <DAT_INICIAL> como referência para obtenção do ano. Como nossa proposta é avaliar os doze meses do ano, isto significa que em nossa consulta final, teremos que utilizar 12 consultas como esta para obter o resulltado esperado - uma coluna para cada mês do ano.

A título de referência, vou chamar esta consulta de CnsVenda<MES_NOME> (p. e. CnsVendaJAN, CnsVendaFEV, ...).

Observe também, que estamos apenas totalizando as vendas por mês. Mas, em nosso resultado final, queremos o total de vendas por ano para cada empregado. Então, precisaremos fazer a combinação de ambas consultas CnsEmpregadoVenda e CnsVenda<MES_NOME>.

Montando uma consulta na forma "esquemática"

Vamos começar a juntar tudo, montando a consulta para obtenção das informações do mês de janeiro apenas.

Incialmente vamos entender que queremos ligar (filtrar) as consultas CnsVenda<MES_NOME> (vendas no mês) com CnsEmpregadoVenda (empregados que venderam no período). Sabemos que:

- CnsEmpregadoVenda, nos retorna a coluna SALES_REP;

- CnsVenda<MES_NOME>, nos retorna as colunas SALES_REP e TOTAL_VALUE.

Também, queremos mostrar o nome do empregado, então buscaremos o campo FIRST_NAME da tabela EMPLOYEE, referenciando o campo EMP_NO. Isto posto poderíamos montar a seguinte consulta esquemática:

SELECT SALES_REP, FIRST_NAME, TOTAL_VALUE_<MES_NOME>

from CnsEmpregadoVenda

left outer join CnsVenda<MES_NOME> ON (CnsVenda<MES_NOME>.SALES_REP_<MES_NOME> = CnsEmpregadoVenda.SALES_REP)

left join EMPLOYEE E ON (E.EMP_NO = CnsEmpregadoVenda.SALES_REP)

seguindo este esquema, a consulta para Janeiro, ficaria:

SELECT SALES_REP, FIRST_NAME, TOTAL_VALUE_JAN

from CnsEmpregadoVenda

left outer join CnsVendaJAN on (CnsVendaJAN.SALES_REP_JAN = CnsEmpregadoVenda.SALES_REP)

left join EMPLOYEE E ON (E.EMP_NO = CnsEmpregadoVenda.SALES_REP)

continuando, para os meses Janeiro e Fevereiro:

SELECT SALES_REP, FIRST_NAME, TOTAL_VALUE_JAN, TOTAL_VALUE_FEV

from CnsEmpregadoVenda

left outer join CnsVendaJAN on (CnsVendaJAN.SALES_REP_JAN = CnsEmpregadoVenda.SALES_REP)

left outer join CnsVendaFEV on (CnsVendaFEV.SALES_REP_FEV = CnsEmpregadoVenda.SALES_REP)

left join EMPLOYEE E ON (E.EMP_NO = CnsEmpregadoVenda.SALES_REP)

e esta analogia se segue até o mês de Dezembro.

Transformando a consulta "esquemática" em uma consulta prática

Para efetivamente construirmos a consulta final, vamos apenas substituir as consultas SQL onde aparecem as "consultas de referência", mas mantendo o nome de referência sob a forma de ALIASES (necessário para identificar as consultas no relacionamento), começando pela consulta para os meses de janeiro e fevereiro:

SELECT SALES_REP, FIRST_NAME, TOTAL_VALUE_JAN, TOTAL_VALUE_FEV
FROM (SELECT DISTINCT S.SALES_REP
        FROM SALES S
        WHERE S.SHIP_DATE BETWEEN '1993-01-01' AND '1993-12-31') AS CnsEmpregadoVenda
  LEFT OUTER JOIN (SELECT SALES_REP AS SALES_REP_JAN, SUM(TOTAL_VALUE) AS TOTAL_JAN
                       FROM SALES
                       WHERE EXTRAC(MONTH FROM SHIP_DATE) = 1  /* Mês de Janeiro apenas, do ano de 1993 */
                           AND EXTRAC(YEAR FROM SHIP_DATE) = EXTRAC(YEAR FROM CAST('1993-01-01' AS DATE))
                       GROUP BY SALES_REP) AS CnsVendaJAN ON (CnsVendaJAN.SALES_REP_JAN = CnsEmpregadoVenda.SALES_REP)
  LEFT OUTER JOIN (SELECT SALES_REP AS SALES_REP_FEV, SUM(TOTAL_VALUE) AS TOTAL_FEV
                       FROM SALES
                       WHERE EXTRAC(MONTH FROM SHIP_DATE) = 2  /* Mês de Fevereiro apenas, do ano de 1993 */
                           AND EXTRAC(YEAR FROM SHIP_DATE) = EXTRAC(YEAR FROM CAST('1993-01-01' AS DATE))
                       GROUP BY SALES_REP) AS CnsVendaFEV ON (CnsVendaJAN.SALES_REP_FEV = CnsEmpregadoVenda.SALES_REP)
  LEFT JOIN EMPLOYEE E ON (E.EMP_NO = CnsEmpregadoVenda.SALES_REP)

e procedimento segue-se para os demais meses. Definindo a consulta final para ser utilizada no Delphi Após testada a consulta, precisamos ajustá-la para uso na aplicação Delphi. A melhor forma é parametrizá-la:

SELECT SALES_REP, E.FIRST_NAME, TOTAL_JAN, TOTAL_FEV, ..., TOTAL_DEZ
 FROM (SELECT DISTINCT S.SALES_REP
       FROM SALES S
       WHERE S.SHIP_DATE BETWEEN :DAT_INICIO AND :DAT_FIM) AS CNSVENDEDORES
   LEFT OUTER JOIN (SELECT SALES_REP AS SALES_REP_JAN, SUM(TOTAL_VALUE) TOTAL_JAN
              FROM SALES
              WHERE EXTRACT(MONTH FROM SHIP_DATE) = 1
                AND EXTRACT(YEAR FROM SHIP_DATE) = EXTRACT(YEAR FROM :DAT_INICIO)
              GROUP BY SALES_REP) AS CnsVendaJAN ON (CnsVendaJAN.SALES_REP_JAN = CNSVENDEDORES.SALES_REP)
   LEFT OUTER JOIN (SELECT SALES_REP AS SALES_REP_FEV, SUM(TOTAL_VALUE) TOTAL_FEV
              FROM SALES
              WHERE EXTRACT(MONTH FROM SHIP_DATE) = 2
                AND EXTRACT(YEAR FROM SHIP_DATE) = EXTRACT(YEAR FROM :DAT_INICIO)
              GROUP BY SALES_REP) AS CnsVendaFEV ON (CnsVendaFEV.SALES_REP_FEV = CNSVENDEDORES.SALES_REP)
  :
  :
  :
   LEFT OUTER JOIN (SELECT SALES_REP AS SALES_REP_DEZ, SUM(TOTAL_VALUE) TOTAL_DEZ
              FROM SALES
              WHERE EXTRACT(MONTH FROM SHIP_DATE) = 12
                AND EXTRACT(YEAR FROM SHIP_DATE) = EXTRACT(YEAR FROM :DAT_INICIO)
              GROUP BY SALES_REP) AS CnsVendaDEZ ON (CnsVendaDEZ.SALES_REP_DEZ = CNSVENDEDORES.SALES_REP)

   LEFT JOIN EMPLOYEE E ON (E.EMP_NO = CNSVENDEDORES.SALES_REP)

Adicionamos esta consulta à propriedade SQL de um componente do tipo Query, adicionamos todos os fields ao componente e depois ao executar a query basta passar o parâmetros DAT_INICIO e DAT_FINAL. A consulta está pronta para ser apresentada num DBGrid ou em um relatório.

Querendo buscar o total do período para cada empregado, basta substituir a parte inicial da consulta:

SELECT SALES_REP, E.FIRST_NAME, TOTAL_GERAL, TOTAL_JAN, TOTAL_FEV, ..., TOTAL_DEZ

FROM (SELECT S.SALES_REP, SUM(TOTAL_VALUE) AS TOTAL_GERAL

FROM SALES S

WHERE S.SHIP_DATE BETWEEN :DAT_INICIO AND :DAT_FIM

GROUP BY S.SALES_REP) AS CNSVENDEDORES

Bom, era "só" isso por hora. Espero que seja útil e qualquer comentário ou sugestão pode ser me enviado por MP.

Abraços

EMPLOYEE.zip

Cosulta_Completa.txt