Oblicz wartość wiersza na podstawie poprzednich i rzeczywistych wartości wiersza

Cześć wszystkim i dziękuję za pomoc.
Mam następującą sytuację: tabela zwana wyciągami zawierająca pola id (int), stmnt_date (data), debet (podwójne), kredyt (podwójne) i saldo (podwójne)

Chcę obliczyć saldo zgodnie z następującymi zasadami:

Saldo pierwszego rzędu ( chronologicznie ) = debet - kredyt i dla pozostałych rzędów

bieżące saldo wiersza = chronologicznie poprzednie saldo wiersza + bieżące obciążenie wiersza - bieżące saldo wiersza

Jak widać na powyższym obrazku, wiersze nie są ułożone według daty i dlatego dwukrotnie użyłem tego słowa chronologicznie, aby podkreślić znaczenie wartości stmnt_date.

Bardzo ci dziękuje za pomoc.

Zakładając, że stmnt_datema to UNIQUEograniczenie, byłoby to dość łatwe dzięki funkcjom okna / analizy:

SELECT 
    s.stmnt_date, s.debit, s.credit,
    SUM(s.debit - s.credit) OVER (ORDER BY s.stmnt_date
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
        AS balance
FROM
    statements AS s
ORDER BY
    stmnt_date ;

Niestety MySQL nie (jeszcze) zaimplementował funkcji analitycznych. Możesz rozwiązać problem albo za pomocą ścisłego SQL, przez samodzielne dołączenie do tabeli (która powinna być raczej nieefektywna, chociaż działa w 100%) lub przez użycie określonej funkcji MySQL, zmiennych (które byłyby dość wydajne, ale trzeba by to przetestować podczas uaktualniania mysql, aby upewnić się, że wyniki są nadal poprawne i nie są zniekształcone przez pewne ulepszenia optymalizacji):

SELECT 
    s.stmnt_date, s.debit, s.credit,
    @b := @b + s.debit - s.credit AS balance
FROM
    (SELECT @b := 0.0) AS dummy 
  CROSS JOIN
    statements AS s
ORDER BY
    stmnt_date ;

Twoje dane spowodują:

+------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014-05-15 |  3000 |      0 |    3000 |
| 2014-06-17 | 20000 |      0 |   23000 |
| 2014-07-16 |     0 |   3000 |   20000 |
| 2014-08-14 |     0 |   3000 |   17000 |
| 2015-02-01 |  3000 |      0 |   20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)

Myślę, że możesz spróbować:

set @balance := 0;

SELECT stmnt_date, debit, credit, (@balance := @balance + (debit - credit)) as Balance
FROM statements
ORDER BY stmnt_date;

Odpowiedź ypercube jest dość spektakularna (nigdy nie widziałem tworzenia zmiennej w jednym zapytaniu za pomocą takiego fikcyjnego wyboru), więc oto instrukcja CREATE TABLE dla twojej wygody.

W przypadku obrazów danych tabelarycznych w Wyszukiwarce grafiki Google możesz użyć https://convertio.co/ocr/ lub https://ocr.space/, aby przekonwertować go na dokument tekstowy. Następnie, jeśli OCR nie wykrył kolumn poprawnie, a masz komputer Mac, użyj TextWrangler z wciśniętym klawiszem opcji, aby wykonać prostokątny wybór i przesuwać kolumny. Połączenie edytora SQL, takiego jak Sequel Pro , TextWrangler i arkusza kalkulacyjnego, takiego jak Dokumenty Google, sprawia, że ​​radzenie sobie z danymi tabelarycznymi oddzielonymi tabulatorami jest niezwykle wydajne.

Gdybym mógł umieścić to wszystko w komentarzu, zrobiłbym to, więc proszę nie głosować za odpowiedzią.

-- DROP TABLE statements;

CREATE TABLE IF NOT EXISTS statements (
  id integer NOT NULL AUTO_INCREMENT,
  stmnt_date date,
  debit integer not null default 0,
  credit integer not null default 0,
  PRIMARY KEY (id)
);

INSERT INTO statements
(stmnt_date  , debit, credit) VALUES
('2014-06-17', 20000, 0     ),
('2014-08-14', 0    , 3000  ),
('2014-07-16', 0    , 3000  ),
('2015-02-01', 3000 , 0     ),
('2014-05-15', 3000 , 0     );

-- this is slightly modified from ypercube's (@b := 0 vs @b := 0.0)
SELECT 
    s.stmnt_date, s.debit, s.credit,
    @b := @b + s.debit - s.credit AS balance
FROM
    (SELECT @b := 0) AS dummy 
CROSS JOIN
    statements AS s
ORDER BY
    stmnt_date ASC;

/* result
+------------+-------+--------+---------+
| stmnt_date | debit | credit | balance |
+------------+-------+--------+---------+
| 2014-05-15 |  3000 |      0 |    3000 |
| 2014-06-17 | 20000 |      0 |   23000 |
| 2014-07-16 |     0 |   3000 |   20000 |
| 2014-08-14 |     0 |   3000 |   17000 |
| 2015-02-01 |  3000 |      0 |   20000 |
+------------+-------+--------+---------+
5 rows in set (0.00 sec)
*/

Stoły do ​​samodzielnego łączenia nie są bardzo szybkie na dużych stołach. Dlatego radząc sobie z tym zadaniem na PostgreSQL postanowiłem użyć funkcji wyzwalacza do obliczenia przechowywanego pola „balance”. Wszystkie obliczenia odbywają się tylko raz dla każdego wiersza.

DROP TABLE IF EXISTS statements;

CREATE TABLE IF NOT EXISTS statements (
  id BIGSERIAL,
  stmnt_date TIMESTAMP,
  debit NUMERIC(18,2) not null default 0,
  credit NUMERIC(18,2) not null default 0,
  balance NUMERIC(18,2)
);

CREATE OR REPLACE FUNCTION public.tr_fn_statements_balance()
RETURNS trigger AS
$BODY$
BEGIN

    UPDATE statements SET
    balance=(SELECT SUM(a.debit)-SUM(a.credit) FROM statements a WHERE a.stmnt_date<=statements.stmnt_date)
    WHERE stmnt_date>=NEW.stmnt_date;

RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER tr_statements_after_update
  AFTER INSERT OR UPDATE OF debit, credit
  ON public.statements
  FOR EACH ROW
  EXECUTE PROCEDURE public.tr_fn_statements_balance();


INSERT INTO statements
(stmnt_date  , debit, credit) VALUES
('2014-06-17', 20000, 0     ),
('2014-08-14', 0    , 3000  ),
('2014-07-16', 0    , 3000  ),
('2015-02-01', 3000 , 0     ),
('2014-05-15', 3000 , 0     );


select * from statements order by stmnt_date;

Na przykład w MSSQL:

Użyj instrukcji with (), aby wygenerować CTE. Jest to zasadniczo tymczasowy zestaw wyników, który pokaże wartość każdego wiersza. Możesz użyć matematyki w instrukcji with, aby utworzyć kolumnę na końcu, używając matematyki, aby pokazać sumę wiersza to DEBIT-KREDYT. W instrukcji with musisz przypisać numery wierszy do każdego wiersza, użyj klauzuli OVER z WITH (), aby uporządkować do stmnt_date.

Następnie rekurencyjnie połącz tabelę z sobą, używając a.ROWNUMBER = b.ROWNUMBER-1 lub +1, co pozwoli ci odwołać się do a.total + b.total = suma tego i poprzedniego wiersza.

Rozumiem, że nie dostarczam kodu, ale jest to praktyczna metoda na osiągnięcie tego. Na żądanie mogę podać kod :)