GPO ( Grupo de Profissionais Oracle )
A maior comunidade Oracle do Brasil !

Pesquisa de Valores Aproximados se não houver Correspondência Exata – Um Caso Dissecado

Pesquisa de Valores Aproximados

Recentemente, num grupo de usuários, recebi uma interessante dúvida de um colega : a necessidade dele era montar uma Consulta aonde o usuário informa um argumento de pesquisa a ser comparado numa coluna numérica (digamos, pra exemplificar, SALÁRIO) e se encontrasse ao menos UM registro na tabela aonde o valor do SALARIO seja exatamente igual a query deveria trazer só esses registros, MAS se não houvesse NENHUM registro obedecendo à condição, aí a query deveria trazer os registros com valores ‘PRÓXIMOS’ ao argumento fornecido…

Além das questões técnicas, o elemento que tornou interessante essa pergunta é que ela serviu de exemplo PERFEITO para demonstrar uma deficiência, um ‘gap’ entre o funcionamento de um SGBD e o raciocínio dos usuários – o fato é, um usuário pode dizer ‘ah, quero valores PRÓXIMOS a esse valor que estou indicando’ mas um SGBD em princípio EXIGE uma definição muito mais precisa do que é ‘PRÓXIMO’ : ‘próximos’ será que são valores que são só um pouquinho maiores ou menores ? Se sim, o QUANTO significa esse ‘pouquinho’ ??

Neste Artigo, eu vou exemplificar como a regra pra definir ‘PROXIMIDADE’ seja : valores que estão 30% acima ou abaixo do argumento de pesquisa, mas como dito CADA usuário pode pensar de maneira diferente, talvez para o usuário ZEZINHO da contabilidade ‘próximos’ seriam valores que estão na mesma ordem de grandeza (tipo, tanto o argumento quanto o valor a comparar na tabela estão na casa dos milhares, digamos, enquanto pro usuário HUGUINHO do financeiro, ‘próximos’ seriam valores que seguem a uma regra completamente diferente…

Faz parte COMPLETAMENTE do nosso trabalho como Especialistas em TI resolver esse GAP, levantando EXATAMENTE o que o usuário quis dizer e o que ele espera obter com a definição dada por eles….

No exemplo abaixo para o resto desse Artigo, eu vou EXEMPLIFICAR a regra de que valores próximos são valores que variam num dado percentual (30%, no meu exemplo) acima ou abaixo do argumento fornecido…

E para fins didáticos, eu VOU ao invés de fornecer a query completa, tentarei DEMONSTRAR a lógica aos pedacinhos – isso VAI ficar um pouco longo, mas é pra demonstrar o raciocínio, que imho é MUITO MAIS INDICADO do que simplesmente mostrar o resultado final – como sempre, ensinar a pescar ao invés de dar o peixe, etc….

Muito bem, primeiro eu tenho os seguintes dados :

SCOTT@xepdb1::CONTAINER=XEPDB1> select empno, ename, sal from emp order by sal;

      EMPNO ENAME             SAL
 ---------- ---------- ----------
       7369 SMITH             800
       7900 JAMES             950
       7876 ADAMS            1100
       7521 WARD             1250
       7654 MARTIN           1250
       7934 MILLER           1300
       7844 TURNER           1500
       7499 ALLEN            1600
       7782 CLARK            2450
       7698 BLAKE            2850
       7566 JONES            2975
       7902 FORD             3000
       7788 SCOTT            3000
       7839 KING             5000
       7935 CHIAPPA          6000

 15 linhas selecionadas.

 SCOTT@xepdb1::CONTAINER=XEPDB1>

=> Vou simular aqui a necessidade de que seja passado um Argumento para a query (via BIND VARIABLE, como recomendado nesses casos) e que SE existirem registros onde a coluna SAL seja exatamente igual ao argumento, apenas esses registros sejam exibidos, E que se não houverem registros com SAL exatamente idêntico, aí serão listados registros com salários PRÓXIMOS ao valor informando, sendo que adotarei uma margem de 30% a mais ou a menos para definir PROXIMIDADE….

Primeiro de tudo, vou criar uma BIND pra receber o argumento em questão – como aqui estou usando sql*plus vou criar uma variável segundo a sintaxe do sql*plus, CADA tool cliente e/ou linguagem de programação que vc estivesse usando no lugar do sql*plus teria as SUAS regras/sintaxes pra isso…

NO meu caso :

SCOTT@xepdb1::CONTAINER=XEPDB1> VARIABLE V_SAL_A_PESQUISAR number;

=> Preencho agora a variável com um valor qualquer – OBVIAMENTE numa aplicação real isso ia ser feito por uma tela de input próprio da aplicação, codificada por um Programador, mas para manter o exemplo simples, vou só botar um valor arbitrário de teste na variável que guarda o argumento de pesquisa:

SCOTT@xepdb1::CONTAINER=XEPDB1> exec :V_SAL_A_PESQUISAR := 3001; 

==> ok.. Agora, antes de criar uma query que traz todos os registros que se encaixam em uma das condições, só pra mostrar o cálculo sendo feito, vou EXIBIR também os valores de 30% a mais e a menos em cima do argumento :

SCOTT@xepdb1::CONTAINER=XEPDB1> select :V_SAL_A_PESQUISAR, (:V_SAL_A_PESQUISAR * 1.30)  as ACIMA_30,  
                                      (:V_SAL_A_PESQUISAR - (:V_SAL_A_PESQUISAR * .30)) as ABAIXO_30 from DUAL;

:V_SAL_A_PESQUISAR   ACIMA_30  ABAIXO_30
------------------ ---------- ----------
              3001     3901,3     2100,7

1 linha selecionada.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> PRONTO, para trazer inicialmente todo mundo eu coloco DUAS condições no WHERE : ou SAL é exatamente igual OU o valor em SAL está ENTRE 30% abaixo ou acima do argumento… Vou aproveitar também para já CALCULAR via Analytics a Contagem de registros com exatamente o mesmo valor em SAL :

SCOTT@xepdb1::CONTAINER=XEPDB1> select empno, ename, sal, sum(CASE WHEN SAL=:V_SAL_A_PESQUISAR then 1 else 0 END) OVER () as QTD_SALS_EXATOS
                                   from emp
                              where sal = :V_SAL_A_PESQUISAR
                                 OR sal BETWEEN (:V_SAL_A_PESQUISAR - (:V_SAL_A_PESQUISAR * .30))
                                        AND (:V_SAL_A_PESQUISAR * 1.30)
                                  order by SAL;


     EMPNO ENAME             SAL QTD_SALS_EXATOS
---------- ---------- ---------- ---------------
      7782 CLARK            2450               0
      7698 BLAKE            2850               0
      7566 JONES            2975               0
      7902 FORD             3000               0
      7788 SCOTT            3000               0

5 linhas selecionadas.

=> Destrinchando a lógica, primeiro eu peço via CASE que pra cada registro seja atribuído um valor 1 se o registro obedece á condição ou 0 se não obedece, aí a SOMA desses zeros ou 1 será a contagem dos registros…

No parágrafo anterior, eu passei um valor de 3001 para o argumento, então realmente a coluna QTD_SAL_EXATOS retornou zero pois NENHUM salário era exatamente igual à 3001… Agora para mostrar que a contagem condicional de registros tá funcionando, passo agora um valor (3000) para o argumento que deveria trazer dois como resultado (seriam os registros do FORD e do SCOTT , vide meus dados) e realmente é assim:

SCOTT@xepdb1::CONTAINER=XEPDB1> exec :V_SAL_A_PESQUISAR := 3000;

Procedimento PL/SQL concluído com sucesso.

SCOTT@xepdb1::CONTAINER=XEPDB1> select empno, ename, sal, sum(CASE WHEN SAL=:V_SAL_A_PESQUISAR then 1 else 0 END) OVER () as QTD_SALS_EXATOS
                                   from emp
                                  where sal = :V_SAL_A_PESQUISAR
                                     OR sal BETWEEN (:V_SAL_A_PESQUISAR - (:V_SAL_A_PESQUISAR * .30))
                                            AND (:V_SAL_A_PESQUISAR * 1.30)
                                      order by SAL;

      EMPNO ENAME             SAL QTD_SALS_EXATOS
 ---------- ---------- ---------- ---------------
       7782 CLARK            2450               2
       7698 BLAKE            2850               2
       7566 JONES            2975               2
       7902 FORD             3000               2
       7788 SCOTT            3000               2

 5 linhas selecionadas.

 SCOTT@xepdb1::CONTAINER=XEPDB1>

==> OK, a query trouxe todos os registros que contém o mesmo valor informado OU que contém valores próximos MAS a coluna de QTD_SALS_EXATOS só me contou os registros com o exato valor de 3000, que eram dois : o registro do FORD e o do SCOTT…

Muito bem , agora fica fácil juntar tudo numa query só, perguntando SE foram encontrados ou não registros que obedecem ao argumento ou se nenhum existir , aí trazer os registros com valores próximos…

SCOTT@xepdb1::CONTAINER=XEPDB1> print V_SAL_A_PESQUISAR

 V_SAL_A_PESQUISAR
 -----------------
              3000


SCOTT@xepdb1::CONTAINER=XEPDB1> SELECT * FROM (
                                                 select empno, ename, sal, sum(CASE WHEN SAL=:V_SAL_A_PESQUISAR then 1 else 0 END) OVER () as QTD_SALS_EXATOS
                                               from emp
                                              where sal = :V_SAL_A_PESQUISAR
                                                 OR sal BETWEEN (:V_SAL_A_PESQUISAR - (:V_SAL_A_PESQUISAR * .30))
                                                            AND (:V_SAL_A_PESQUISAR * 1.30)
                                                )
                                   WHERE (   (sal = :V_SAL_A_PESQUISAR and QTD_SALS_EXATOS > 0)
                                          OR (QTD_SALS_EXATOS = 0)
                                         )
                                    order by SAL;

      EMPNO ENAME             SAL QTD_SALS_EXATOS
 ---------- ---------- ---------- ---------------
       7902 FORD             3000               2
       7788 SCOTT            3000               2

 2 linhas selecionadas.

==>  Vejam que estou com um valor no argumento para o qual EXISTEM salários com exatamente esse valor, só esses foram retornados… Agora vou passar um argumento que NÂO se iguala exatamente a nenhum salário, vou trazer aqueles que estão 30% acima ou abaixo desse argumento:

SCOTT@xepdb1::CONTAINER=XEPDB1> exec :V_SAL_A_PESQUISAR := 3001;

Procedimento PL/SQL concluído com sucesso.

SCOTT@xepdb1::CONTAINER=XEPDB1> SELECT * FROM (
                                                   select empno, ename, sal, sum(CASE WHEN SAL=:V_SAL_A_PESQUISAR then 1 else 0 END) OVER () as QTD_SALS_EXATOS
                                                 from emp
                                                where sal = :V_SAL_A_PESQUISAR
                                                   OR sal BETWEEN (:V_SAL_A_PESQUISAR - (:V_SAL_A_PESQUISAR * .30))
                                                              AND (:V_SAL_A_PESQUISAR * 1.30)
                                               )
                                  WHERE (   (sal = :V_SAL_A_PESQUISAR and QTD_SALS_EXATOS > 0)
                                         OR (QTD_SALS_EXATOS = 0)
                                         )
                            ORDER BY SAL;

      EMPNO ENAME             SAL QTD_SALS_EXATOS
 ---------- ---------- ---------- ---------------
       7782 CLARK            2450               0
       7698 BLAKE            2850               0
       7566 JONES            2975               0
       7902 FORD             3000               0
       7788 SCOTT            3000               0

 5 linhas selecionadas.

 SCOTT@xepdb1::CONTAINER=XEPDB1>

Abraços,

Share

You may also like...

Deixe um comentário

O seu endereço de e-mail não será publicado.