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

Estudo de caso em desenvolvimento de lógica – Programação SQL para Cálculo de Lucro Líquido

Programação SQL para Cálculo

Neste artigo, ao invés de simplesmente mostrar o resultado final pronto, o objetivo é demonstrar passo a passo a linha de pensamento usada no desenvolvimento, e as causas/razões de cada técnica aplicada até chegarmos à resposta final.

Cenário

A Empresa só revende produtos que compra, então o lucro bruto num período de tempo é a soma do preço de venda em todas as vendas no período substraído do preço de compra dos produtos vendidos. Haverá também uma lista de despesas, a soma das despesas da empresa será o custo no período, o lucro líquido num período de tempo será o lucro bruto do período substraído da soma das despesas havidas no período, e será necessário mostrar tanto o valor do lucro líquido como o percentual de lucratividade, que será o percentual de diferença entre o lucro bruto e o lucro líquido.

Criação das tabelas e inserção de dados

Antes da parte técnicado artigo, criaremos as tabelas e teremos os dados, para servirem de exemplo:

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> create table COMPRAS (ID_COMPRA         number,
                                                          COD_PRODUTO       number,
                                                          QTD_COMPRADA      number,
                                                          PRECO_UNIT_COMPRA number,
                                                          DT_COMPRA         date);

Tabela criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> create table VENDAS (NF_VENDA           number,
                                                         COD_PRODUTO        number,
                                                         QTD_VENDIDA        number,
                                                         PRECO_UNIT_VENDA   number,
                                                         DT_VENDA           date);

Tabela criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> create table DESPESAS (ID_DESPESA    number,
                                                           DESCR_DESPESA varchar2(80),
                                                           VALOR_DESPESA number,
                                                           DATA_DESPESA  date
                                                          );

Tabela criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into DESPESAS values(1, 'Conta de Luz Setembro', 500, to_date('10/09/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into DESPESAS values(2, 'Conta de Luz Outubro', 800, to_date('10/10/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into DESPESAS values(3, 'Folha de pagamento Outubro', 20000, to_date('10/10/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into COMPRAS values (1, 10, 1000, 100, to_date('01/09/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into COMPRAS values (2, 10, 1500, 90, to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into COMPRAS values (3, 20, 2000, 90, to_date('02/10/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into VENDAS values(10000, 10, 5000, 140, to_date('05/09/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into VENDAS values(10130, 10, 4500, 140, to_date('04/10/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into VENDAS values(10131, 10, 3200, 140, to_date('05/10/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> insert into VENDAS values(10132, 20, 1500, 120, to_date('06/10/2021', 'dd/mm/yyyy hh24:mi:ss') );

1 linha criada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> commit;

Commit concluído.

Passo 1

Obter soma dos valores de despesas de compras no período a analisar (será usado no resto do artigo período do mês de Outubro/2021):

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> select sum(valor_despesa) as DESPESAS_NO_PERIODO
                                      from despesas
                                     where data_despesa between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                            and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss');

     DESPESAS_NO_PERIODO
------------------------
                  20.800

1 linha selecionada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> select sum(QTD_COMPRADA * PRECO_UNIT_COMPRA) as COMPRAS_NO_PERIODO
                                      from compras
                                     where DT_COMPRA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                         and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss');

      COMPRAS_NO_PERIODO
------------------------
                 315.000

1 linha selecionada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> select sum(QTD_VENDIDA * PRECO_UNIT_VENDA) as VENDAS_NO_PERIODO
                                      from VENDAS where DT_VENDA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                                     and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss');
SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> /

               RESULTADO
------------------------
               1.258.000

1 linha selecionada.

** IMPORTANTE **

Na lógica em questão, era desejado somar todas as linhas que atenderam à condição de filtro, SEM especificar um valor a agrupar – se fosse desejado obter a soma de CADA PRODUTO, o CODPRODUTO serviria de chave pro agrupamento, seria necessário então um GROUP BY , assim:

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> select 
                                       COD_PRODUTO, sum(QTD_VENDIDA * PRECO_UNIT_VENDA) as resultado
                                      from VENDAS where DT_VENDA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                                     and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                     GROUP BY COD_PRODUTO;

COD_PRODUTO                RESULTADO
----------- ------------------------
         10                1.078.000
         20                  180.000

2 linhas selecionadas.

Isso não será preciso na lógica sendo desenvolvida, mas fica a Explicação, de quando é ou não preciso usar GROUP BY ao trabalhar com funções de Agrupamento de dados…

Passo 2

Efetuar as contas matemáticas necessárias – para isso, o comando da linguagem SQL que nos permite exibir dados , sendo que esses dados TEM que provir de uma tabela.

Como os dados provém de tabelas DIFERENTES no exemplo, isto poderia ser feito através de JOIN mas como no exemplo não há colunas comuns entre as tabelas para se fazer o JOIN, E, além disso, as consultas retornam apenas UM valor (que é a soma desejada), vou usar o recurso da query colunar, sendo que no RDBMS ORACLE para se fazer uma query sem referenciar uma coluna, é necessário usar a pseudo-tabela DUAL:

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> SELECT 
     (
      select sum(valor_despesa)
        from despesas
       where data_despesa between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                              and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
     ) as DESPESAS_NO_PERIODO,
     --
     (
      select sum(QTD_COMPRADA * PRECO_UNIT_COMPRA)
        from compras
       where DT_COMPRA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                           and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
     ) as COMPRAS_NO_PERIODO,
     --
     (
      select sum(QTD_VENDIDA * PRECO_UNIT_VENDA) as VENDAS_NO_PERIODO
        from VENDAS where DT_VENDA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                       and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
     ) as LUCRO_BRUTO
     --
FROM DUAL;

DESPESAS_NO_PERIODO COMPRAS_NO_PERIODO LUCRO_BRUTO
------------------- ------------------ -----------
              20800             315000     1258000

1 linha selecionada.

Aí, para somar as colunas , caímos numa restrição do Oracle. Numa query Oracle, só podem ser usadas em cálculos, condições no WHERE, etc, colunas que EXISTAM na fonte de dados citadas no FROM – como Não É o caso dessas colunas , portanto não é possível esta forma:

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> SELECT
       (
         select sum(valor_despesa)
           from despesas
          where data_despesa between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                 and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
        ) as DESPESAS_NO_PERIODO,
        --
        (
         select sum(QTD_COMPRADA * PRECO_UNIT_COMPRA)
           from compras
          where DT_COMPRA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                              and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
        ) as COMPRAS_NO_PERIODO,
        --
        (
         select sum(QTD_VENDIDA * PRECO_UNIT_VENDA) as VENDAS_NO_PERIODO
           from VENDAS where DT_VENDA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                          and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
        ) as LUCRO_BRUTO
        --
       , LUCRO_BRUTO - (COMPRAS_NO_PERIODO + DESPESAS_NO_PERIODO) as LUCRO_LIQUIDO
   FROM DUAL;


      , LUCRO_BRUTO - (COMPRAS_NO_PERIODO + DESPESAS_NO_PERIODO) as LUCRO_LIQUIDO
                                            *
ERRO na linha 21:
ORA-00904: "DESPESAS_NO_PERIODO": identificador inválido

A solução é SIMPLES, basta usar o conceito de INNER QUERY, aonde a fonte de dados (cláusula FROM) é demarcada como sendo Uma Outra Query, que traz as colunas calculadas, assim:

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> SELECT A.LUCRO_BRUTO - (A.COMPRAS_NO_PERIODO + A.DESPESAS_NO_PERIODO) as LUCRO_LIQUIDO
                                      FROM (
                                            Select (
                                                    select sum(valor_despesa)
                                                      from despesas
                                                     where data_despesa between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                                            and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                   ) as DESPESAS_NO_PERIODO,
                                                   --
                                                   (
                                                    select sum(QTD_COMPRADA * PRECO_UNIT_COMPRA)
                                                      from compras
                                                     where DT_COMPRA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                                         and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                   ) as COMPRAS_NO_PERIODO,
                                                   --
                                                   (
                                                    select sum(QTD_VENDIDA * PRECO_UNIT_VENDA) as VENDAS_NO_PERIODO
                                                      from VENDAS where DT_VENDA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                                                     and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                   ) as LUCRO_BRUTO
                                              From Dual
                                           ) A;

LUCRO_LIQUIDO
-------------
       922200

1 linha selecionada.

Passo 3

Percentual de Lucratividade : como a fórmula do percentual de lucratividade envolve a coluna LUCRO_LIQUIDO, que é uma coluna Calculada (que cfrme dito antes, não está contida no FROM) , Poderíamos aplicar novamente a opçõa de INNER QUERY, criando ainda mais um nível de consultas, mas optaremos por simplesmente repetir a fórmula do cálculo de LUCRO LÍQUIDO na coluna que calcula o percentual:

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> SELECT 
                                         A.LUCRO_BRUTO - (A.COMPRAS_NO_PERIODO + A.DESPESAS_NO_PERIODO) as LUCRO_LIQUIDO,
                                         --
                                         ROUND ( (LUCRO_BRUTO - (A.LUCRO_BRUTO - (A.COMPRAS_NO_PERIODO + A.DESPESAS_NO_PERIODO)) ) * 100 / LUCRO_BRUTO, 2 ) PERC_LUCRO
                                      FROM (
                                            Select (
                                                    select sum(valor_despesa)
                                                      from despesas
                                                     where data_despesa between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                                            and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                   ) as DESPESAS_NO_PERIODO,
                                                   --
                                                   (
                                                    select sum(QTD_COMPRADA * PRECO_UNIT_COMPRA)
                                                      from compras
                                                     where DT_COMPRA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                                         and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                   ) as COMPRAS_NO_PERIODO,
                                                   --
                                                   (
                                                    select sum(QTD_VENDIDA * PRECO_UNIT_VENDA) as VENDAS_NO_PERIODO
                                                      from VENDAS where DT_VENDA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                                                     and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                   ) as LUCRO_BRUTO
                                              From Dual
                                           ) A;

LUCRO_LIQUIDO PERC_LUCRO
------------- ----------
       922200      26,69

1 linha selecionada.

Essa é a resposta… Apenas para fins de DEBUG/demonstração, exibirei os valores usados nos cálculos:

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB> SELECT
            A.LUCRO_BRUTO, A.COMPRAS_NO_PERIODO ,  A.DESPESAS_NO_PERIODO,
            A.COMPRAS_NO_PERIODO + A.DESPESAS_NO_PERIODO                   as CUSTOS_NO_PERIODO,
            A.LUCRO_BRUTO - (A.COMPRAS_NO_PERIODO + A.DESPESAS_NO_PERIODO) as LUCRO_LIQUIDO,
            --
            ROUND(( LUCRO_BRUTO - (A.LUCRO_BRUTO - (A.COMPRAS_NO_PERIODO + A.DESPESAS_NO_PERIODO)) ) * 100 / LUCRO_BRUTO, 2 ) PERC_LUCRO
       FROM (
             Select (
                     select sum(valor_despesa)
                       from despesas
                      where data_despesa between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                             and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                    ) as DESPESAS_NO_PERIODO,
                    --
                    (
                     select sum(QTD_COMPRADA * PRECO_UNIT_COMPRA)
                       from compras
                      where DT_COMPRA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                          and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                    ) as COMPRAS_NO_PERIODO,
                    --
                    (
                     select sum(QTD_VENDIDA * PRECO_UNIT_VENDA) as VENDAS_NO_PERIODO
                       from VENDAS where DT_VENDA between to_date('01/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                                                      and to_date('31/10/2021', 'dd/mm/yyyy hh24:mi:ss')
                    ) as LUCRO_BRUTO
               From Dual
            ) A;

LUCRO_BRUTO COMPRAS_NO_PERIODO DESPESAS_NO_PERIODO CUSTOS_NO_PERIODO LUCRO_LIQUIDO PERC_LUCRO
----------- ------------------ ------------------- ----------------- ------------- ----------
    1258000             315000               20800            335800        922200      26,69

1 linha selecionada.

SCOTT@ORCL19CPDB::CNTNR=ORCL19CPDB>
Share

You may also like...

Deixe um comentário

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