Return records that total desired value

Question

I have a table in this format, with more N values and N suppliers, approximately 100 thousand records, and I need to do through a query or a plpgsql function a way to get all the release numbers num where the sum of valor_conta total 0 (zero) or where the field records debito and credito totalize the valor_calculo. In the example below would be the nums 4302454, 4304304, 4330098, 4330095. However, these records may not be continuously displayed, and may be in single value. There is no criterion in how they are registered or displayed.

num(pk)   fornecedor      debito    credito   valor_conta saldo   valor_calculo           
4302458 FORNECEDOR1     4.35                4.35        23.47   869.00
4302456 FORNECEDOR1     19.12               19.12       23.47   869.00
4302454 FORNECEDOR1               435.00    -435.00     23.47   869.00
4304304 FORNECEDOR1     460.00              460.00      23.47   869.00
4330098 FORNECEDOR1     409.00              409.00      23.47   869.00
4330095 FORNECEDOR1               434.00    -434.00     23.47   869.00

I have already tried to do several joins, including the form below, which takes the records that add up what adds up the amount of the balance, but does not work properly, when the record is unique and total the amount of the balance.

SELECT 'join'::character(4) as link, a.num, a.debito, a.credito
FROM conta_contabil.staging_livro_razao a 
INNER JOIN (
    SELECT 'join'::character(4) as link, a.num, a.debito, a.credito
    FROM conta_contabil.staging_livro_razao a 
    WHERE a.fornecedor = 'FORNECEDOR1'
    )b ON join' = b.link AND a.fornecedor = 'FORNECEDOR1'
WHERE a.debito + b.debito = 23.47

Someone could help me with this problem?

Answer 1

For what I went through the chat, and using the date to sort the records (yes, if you need the moment you had the balance 0, you need to have the records of the moments in which they happened) I made the following query:

with temp as (
SELECT
row_number() OVER(ORDER BY a.data, a.num) AS i,
a.num, 
coalesce(a.credito,0) as credito,
coalesce(a.debito,0)*-1 as debito,
a.fornecedor
from staging_livro_razao a 
where a.fornecedor = 'FORNECEDOR1'
order by a.data), saldo as
(select 
t.*,
(select sum(x.credito + x.debito) from temp x where x.fornecedor = t.fornecedor and x.i < t.i) as saldo_anterior,
(select sum(x.credito + x.debito) from temp x where x.fornecedor = t.fornecedor and x.i <= t.i) as saldo_atual
from temp t)



select * from saldo;
select * from saldo where saldo_atual = 0;

I put in Sqlfiddle to help: http://sqlfiddle.com/#! 15/ac849/8

Edit:

Your real intention would be to find n records where the sum of these n would be equal to 0. This means that, in 4 records, we would have 4! (Factorial)= 12 possibilities that should be verified. In only 10 records, we would already have 3,628,800 possibilities, which would result in a processing beyond absurd.

Using the informed data, and a few more, I created in SQL Fiddle the example that I believe solves the problem:

http://sqlfiddle.com/#! 15/f813d/1

where all records prior to registration 10 can be discarded without affecting the supplier’s balance.

Answer 2

Surely your problem can be solved by Window Functions.

Here is an example based on your question:

Structure:

CREATE TABLE staging_livro_razao
(
    num bigint,
    fornecedor text,
    debito real,
    credito real,
    valor_conta real,
    saldo real,
    valor_calculo real,
    data date
);

Dice:

INSERT INTO staging_livro_razao ( num, fornecedor, debito, credito, valor_conta, saldo, valor_calculo, data ) VALUES (4302458, 'FORNECEDOR1',  4.35, NULL, 4.35, 23.47, 869.00, current_date  - '6 days'::interval );
INSERT INTO staging_livro_razao ( num, fornecedor, debito, credito, valor_conta, saldo, valor_calculo, data ) VALUES (4302456, 'FORNECEDOR1', 19.12, NULL, 19.12, 23.47, 869.00, current_date  - '5 days'::interval );
INSERT INTO staging_livro_razao ( num, fornecedor, debito, credito, valor_conta, saldo, valor_calculo, data ) VALUES (4302454, 'FORNECEDOR1', NULL, 435.00, -435.00, 23.47, 869.00, current_date  - '4 days'::interval );
INSERT INTO staging_livro_razao ( num, fornecedor, debito, credito, valor_conta, saldo, valor_calculo, data ) VALUES (4304304, 'FORNECEDOR1', 460.00, NULL,  460.00, 23.47, 869.00, current_date  - '3 days'::interval );
INSERT INTO staging_livro_razao ( num, fornecedor, debito, credito, valor_conta, saldo, valor_calculo, data ) VALUES (4330098, 'FORNECEDOR1', 409.00, NULL,  409.00, 23.47, 869.00, current_date  - '2 days'::interval );
INSERT INTO staging_livro_razao ( num, fornecedor, debito, credito, valor_conta, saldo, valor_calculo, data ) VALUES (4330095, 'FORNECEDOR1', NULL, 434.00, -434.00, 23.47, 869.00, current_date  - '1 days'::interval );

Consultation:

SELECT
    tbl.num,
    tbl.valor_conta,
    wfunc.historico,
    CASE WHEN ((wfunc.historico * lag(wfunc.historico,1) OVER (ORDER BY wfunc.data)) > 0) THEN 0 ELSE 1 END AS zero
FROM
    (SELECT num, data, sum(valor_conta) OVER (ORDER BY data) AS historico FROM staging_livro_razao WHERE fornecedor =  'FORNECEDOR1') AS wfunc 
JOIN
    staging_livro_razao AS tbl ON (tbl.num = wfunc.num )
ORDER BY
    tbl.data;

Exit: