Função de grupo para multiplicar, SUM -> MULT

Fala pessoal,

Recebi um email de um leitor solicitando uma solução para que fosse desenvolvida uma função semelhante ao SUM, já nativo do banco de dados Oracle, só que fizesse multiplicação e não soma, como o SUM faz atualmente!

Por exemplo:

Tabela: FATORIAL
Campo: VALOR

Valor
5
6
3
2
3

Portanto, se usarmos o SUM, teriamos um resultado assim: 5 + 6 + 3 + 2 + 3 = 19
O pretendido é: 5 * 6 * 3 * 2 * 3 = 540

A sugestão que dei foi: Criar uma função própria que faça a multiplicação dos valores. A implementação/testes ficou assim:


SQL> --Cria tabela
SQL> create table fatorial(campo1 number, valor number);
Tabela criada.
SQL> --Insere valores
SQL> insert into fatorial values (1,2);
1 linha criada.
SQL> insert into fatorial values (1,3);
1 linha criada.
SQL> insert into fatorial values (2,4);
1 linha criada.
SQL> insert into fatorial values (2,4);
1 linha criada.
SQL>

Em seguida criamos a seguinte function:


SQL> create or replace function mult_vals(p_filtro number) return number is
2 v_retorno number;
3 begin
4 v_retorno := 0;
5 for i in (select valor from fatorial where campo1 = p_filtro)
6 loop
7 if (v_retorno = 0) then
8 v_retorno := 1;
9 end if;
10 v_retorno := v_retorno*i.valor;
11 end loop;
12 return v_retorno;
13 end;
14 /
FunþÒo criada.
SQL>

E depois é só testar:


SQL> select campo1, mult_vals(campo1)
2 from fatorial
3 group by campo1;
CAMPO1 MULT_VALS(CAMPO1)
---------- -----------------
1 6
2 16
SQL>

Funcionou como uma luva não?

É isso, o que não tem nativo a gente faz!
Neste caso fiz algo bem específico, só pra atender a necessidade que nosso leitor precisava, mas podemos pensar em algo mais genérico, se necessário! Usando SQL Dinâmico, passando nome do campo a ser multiplicado e nome da tabela! Fica a sugestão!

Agradecendo ao nosso leitor Rodrigo Vieira pela solicitação!!

Grande abraço, espero que gostem.

5 ideias sobre “Função de grupo para multiplicar, SUM -> MULT

  1. Renan da Costa

    Ou pode fazer assim mais simples:

    SELECT (vlr_base_pis*1.65/100) FROM x08_itens_merc WHERE vlr_base_pis = ‘596.92’

    Ou

    UPDATE x08_itens_merc SET vlr_pis = (VLR_CONTAB_ITEM*1.65/100)
    WHERE COD_EMPRESA = ‘001’
    AND COD_ESTAB = ‘001’
    AND DATA_FISCAL = ‘01.04.2011’
    AND VLR_PIS IS NULL
    AND NUM_DOCFIS = ‘5790’;

    Responder
  2. rogenaro

    Como a * b * c = e^( ln(a) + ln(b) + ln(c) ), é possível chegar no mesmo resultado com o comando sum:

    select campo1, round
    ( exp
    ( sum
    ( ln
    ( decode( valor, 0, .000000000000000001, valor ) )
    )
    )
    , 2
    ) mult
    from
    ( select 1 campo1, 2 valor from dual union all
    select 1 campo1, 3 valor from dual union all
    select 2 campo1, 4 valor from dual union all
    select 2 campo1, 4 valor from dual
    ) fatorial
    group by campo1

    A única observação é tratar o valor 0, e lembrar de usar o round no resultado final para o número de casas desejado (devido a erros de arredondamento, precisões de ordem mais elevadas acabam sendo perdidas com esta solução, após a 20ª casa decimal)

    Responder
    1. oracle Autor do post

      Beleza rogenaro.

      Muito obrigado pela sua contribuição.
      Não testei, mas, fica aí o registro de mais uma forma de fazer.

      Abraços!

      Responder

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *