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álidoA 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>