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,