Aprenda PL/SQL

Neste post, vamos mostrar como utilizar funções analíticas para fazer o rateio de um valor em diversas linhas de uma tabela.

Primeiro, vamos criar a tabela de teste:

CREATE TABLE glufke_teste
(nro_nota  NUMBER, item NUMBER, valor NUMBER);
INSERT INTO glufke_teste VALUES ( 50, 1, 1.45);
INSERT INTO glufke_teste VALUES ( 50, 2, 3.91);
INSERT INTO glufke_teste VALUES ( 50, 3, 5.04);
SQL> SELECT * FROM glufke_teste;

  NRO_NOTA       ITEM      VALOR
---------- ---------- ----------
        50          1       1,45
        50          2       3,91
        50          3       5,04

SQL>

Nosso objetivo é ratear um valor proporcionalmente nessas linhas. Como exemplo prático, vamos distribuir o valor R$ 49,30!

Sabemos que a soma das 3 linhas é 10,40:

SQL> SELECT SUM(VALOR) FROM GLUFKE_TESTE;

SUM(VALOR)
----------
      10,4

SQL>

Então, através de uma simples regra de 3, vemos o percentual de cada linha! Ou seja, temos que aplicar o valor de 49,30 conforme esses percentuais!

SQL> SELECT VALOR, VALOR/10.4*100 PERC FROM GLUFKE_TESTE;

     VALOR       PERC
---------- ----------
      1,45 13,9423076
      3,91 37,5961538
      5,04 48,4615384

SQL>

Com isso, basta colocar o valor 49.30 ali que temos distribuído! Repare que a soma do valor rateado é exatamente os 49,30 ! Ou seja, está distribuído conforme nossa necessidade!

SQL> select valor, valor/10.4* 49.3 vlr_rateado from glufke_teste;

     VALOR VLR_RATEADO
---------- -----------
      1,45 6,873557692
      3,91 18,53490384
      5,04 23,89153846

SQL>

Usando funções analíticas pra obter esse resultado!

Agora, usando a função analitica SUM, pra buscar “em tempo de execução” a soma da coluna valor (abaixo, a coluna TOTAL), e fazer o cálculo todo numa única tacada!

SELECT
  nro_nota
, item
, valor
, sum(valor) over (order by nro_nota) total
, valor / sum(valor) over (order by nro_nota) * 49.3 vlr_rateado
FROM GLUFKE_teste
WHERE nro_nota = 50
/
  NRO_NOTA       ITEM      VALOR      TOTAL VLR_RATEADO
---------- ---------- ---------- ---------- -----------
        50          1       1,45       10,4 6,873557692
        50          2       3,91       10,4 18,53490384
        50          3       5,04       10,4 23,89153846

SQL>

Vamos adicionar mais um campo chamado VLR_ARR, que arredonda pra 2 casas decimais o valor rateado:

SELECT
  nro_nota
, item
, valor
, sum(valor) over (order by nro_nota) total
, valor / sum(valor) over (order by nro_nota) * 50 vlr_rateado
, ROUND(   valor / sum(valor) over (order by nro_nota) * 49.3 ,2 ) vlr_arr
FROM glufke_teste
WHERE nro_nota = 50
/
  NRO_NOTA       ITEM      VALOR      TOTAL VLR_RATEADO VLR_ARR
---------- ---------- ---------- ---------- ----------- -----------
        50          1       1,45       10,4 6,971153846        6,87
        50          2       3,91       10,4 18,79807692       18,53
        50          3       5,04       10,4 24,23076923       23,89

SQL>

PROBLEMA A VISTA!
Quando fazemos rateios e arredondamos o valor, a chance de dar uma diferença de 1 centavo ou mais é muito grande! Se somarmos o valor rateado não fecha com os 49.30!

Veja: 6.87 + 18.53 + 23.89 = 49.29 !!! (diferença de 1 centavo!)

Resolvendo problema do centavo

Vamos usar funções analíticas pra jogar essa diferença de 1 centavo para uma das linhas da nota! (no caso, vamos jogar para a linha de maior valor).

Primeiro, vamos descobrir se realmente existe uma diferença de 1 centavo ou mais:

SELECT
  VLR_RATEADO
, ROUND(VLR_RATEADO,2)                        VLR_ARR
, COUNT(1) OVER (ORDER BY 1)                  QTD_LIN
, ROW_NUMBER()    OVER (ORDER BY VLR_RATEADO) LIN
, SUM(VLR_RATEADO) OVER (ORDER BY 1)          SOMA
, SUM(ROUND(VLR_RATEADO,2)) OVER (ORDER BY 1) SOMA_ARR
FROM
(
 SELECT
   nro_nota
 , item
 , valor
 , valor / SUM(valor) OVER (ORDER BY nro_nota) * 49.3 vlr_rateado
 FROM glufke_teste
 WHERE nro_nota = 50
 )
/
VLR_RATEADO    VLR_ARR    QTD_LIN        LIN       SOMA   SOMA_ARR
----------- ---------- ---------- ---------- ---------- ----------
6,873557692       6,87          3          1       49,3      49,29
18,53490384      18,53          3          2       49,3      49,29
23,89153846      23,89          3          3       49,3      49,29

SQL>

Acima, as colunas QTD_LIN e LIN vão nos ser úteis pra aplicar a diferença apenas na última linha, ou seja, quando QTD_LIN = LIN (no caso 3=3).
A coluna SOMA, contém o valor total.
A coluna SOMA_ARR é a soma do valor arredondado! Repare que tem 1 centavo a menos!

Então, basta fazer um DECODE que aplica essa diferença (49.3 – 49.2 –> SOMA – SOMA_ARR )
Veja:

SELECT
  VLR_RATEADO
, ROUND(VLR_RATEADO,2) VLR_RATEADO_ARR
, DECODE( COUNT( 1) OVER (ORDER BY 1)
       , ROW_NUMBER()    OVER (ORDER BY VLR_RATEADO)
       , SUM( VLR_RATEADO) OVER (ORDER BY 1) - SUM( ROUND(VLR_RATEADO,2)) OVER (ORDER BY 1)+ ROUND(VLR_RATEADO,2)
       , ROUND(VLR_RATEADO,2)
       )  VLR_COM_DIF
FROM
(
SELECT
  nro_nota
, item
, valor
, (valor) / SUM(valor) OVER (ORDER BY nro_nota) * 49.3 vlr_rateado
FROM GLUFKE_teste
WHERE nro_nota = 50
)
/
VLR_RATEADO VLR_RATEADO_ARR VLR_COM_DIF
----------- --------------- -----------
6,873557692            6,87        6,87
18,53490384           18,53       18,53
23,89153846           23,89       23,90

SQL>

O select acima, aplica a diferença de 1 centavo apenas na ultima linha. Dessa forma, a diferença de centavo vai pra uma das linhas e o total rateado sempre fecha corretamente!
A soma do VLR_RATEADO está errada! ( 6,87 + 18,53 + 23,89 = 49,29)
Já o campo VLR_COM_DIF está com a soma correta, fechando os 49,30 !

Dessa forma, com apenas 1 select foi possível realizar o rateio e ainda aplicar a diferença do arredondamento em uma das linhas, graças as funções analíticas!

Outro Exemplo:

INSERT INTO glufke_teste VALUES ( 51, 1, 5);
INSERT INTO glufke_teste VALUES ( 51, 2, 5);
INSERT INTO glufke_teste VALUES ( 51, 3, 5);
INSERT INTO glufke_teste VALUES ( 51, 4, 7.5);
INSERT INTO glufke_teste VALUES ( 51, 5, 7.5);

Vamos aplicar o valor 10 nesses valores:

SELECT
  NRO_NOTA
, ITEM
, VALOR
, ROUND(VLR_RATEADO,2) VLR_RATEADO_ARR
, DECODE( COUNT( 1) OVER (ORDER BY 1)
       , ROW_NUMBER()    OVER (ORDER BY VLR_RATEADO)
       , SUM( VLR_RATEADO) OVER (ORDER BY 1)
       - SUM( ROUND(VLR_RATEADO,2)) OVER (ORDER BY 1)
       + ROUND(VLR_RATEADO,2)
       , ROUND(VLR_RATEADO,2)
       )  VLR_COM_DIF
FROM
(
SELECT
  nro_nota
, item
, valor
, (valor) / SUM(valor) OVER (ORDER BY nro_nota) * 10 vlr_rateado
FROM GLUFKE_teste
WHERE nro_nota = 51
)
  NRO_NOTA       ITEM      VALOR VLR_RATEADO_ARR VLR_COM_DIF
---------- ---------- ---------- --------------- -----------
        51          1          5            1,67        1,67
        51          2          5            1,67        1,67
        51          3          5            1,67        1,67
        51          4        7,5            2,50        2,50
        51          5        7,5            2,50        2,49     -- < descontou 1 centavo aqui!

SQL>

Para terminar a saga, vamos adicionar a palavra-chave PARTITION BY, e dessa forma, podemos fazer o calculo acima para todas as notas! No exemplo abaixo, vamos ratear o valor 10 pra cada nota. Ou seja, no total, a soma vai dar 20, pois temos apenas 2 notas no exemplo:

SELECT
  NRO_NOTA
, ITEM
, VALOR
, ROUND(VLR_RATEADO,2) VLR_RATEADO_ARR
, DECODE( COUNT( 1) OVER (partition by nro_nota  ORDER BY 1)
       , ROW_NUMBER()    OVER (partition by nro_nota ORDER BY VLR_RATEADO)
       , SUM( VLR_RATEADO) OVER (partition by nro_nota ORDER BY 1)
       - SUM( ROUND(VLR_RATEADO,2)) OVER (partition by nro_nota ORDER BY 1)
       + ROUND(VLR_RATEADO,2)
       , ROUND(VLR_RATEADO,2)
       )  VLR_COM_DIF
FROM
(
SELECT
  nro_nota
, item
, valor
, (valor) / SUM(valor) OVER (partition by nro_nota ORDER BY nro_nota) * 10 vlr_rateado
FROM GLUFKE_teste
)
  NRO_NOTA       ITEM      VALOR VLR_RATEADO_ARR VLR_COM_DIF
---------- ---------- ---------- --------------- -----------
        50          1       1,45            1,39        1,39
        50          2       3,91            3,76        3,76
        50          3       5,04            4,85        4,85
        51          1       5,00            1,67        1,67
        51          3       5,00            1,67        1,67
        51          2       5,00            1,67        1,67
        51          5       7,50            2,50        2,50
        51          4       7,50            2,50        2,49

8 rows selected

SQL>

Observe que a soma da coluna VLR_COM_DIF fecha exatamente os 20. Ja a coluna VLR_RATEADO_ARR dá diferença de centavo!

Neste LINK tem diversos exemplos muito bons! Até mais!

Comments are closed.