Return records totaling desired value

Question

Return records totaling desired value

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I have a table in this format, with more N values and N vendors, approximately 100,000 records, and I need to do through a query or a plpgsql function a way to get all the launch numbers num where the sum of valor_conta is 0 (zero) or where the records of the debito and credito fields total valor_calculo . In the example below would be the nums 4302454 , 4304304 , 4330098 , 4330095 . However, these records may not be displayed continuously, 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've tried to do several joins, including the form below, which takes the records that add up to what adds up to the balance amount, but does not work properly when the record is unique and total the balance amount. / p>

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

Would anyone help me with this problem?

sql postgresql plpgsql

asked by anonymous 26.06.2017 / 17:52

2 answers

TypeScript supports an equivalent implementation of trait? Short date submission not working abbreviated asp.net mvc

score 1 · Answer 1

So you have been through the chat, and using the date to sort the records (yes, if you need the moment when you had the balance 0, you need to have the records of the times when they happened) I made the following query: p>

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 it in SQLFiddle to help: link

Edit:

Your real intention would be to find% with% records where the sum of these n would equal 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 an absurd rendering.

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

link

where, all records prior to record 10, can be discarded without affecting the vendor balance.

score 0 · Answer 2

Certainly your problem can be solved by using Window Functions .

Here's 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
);

Data:

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 );

Inquiry:

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;

Output: