SQL MySQL: Kumulative Summe in sortierter View erstellen

KarlGa · 30. August 2025

Vielleicht kann jemand helfen.

Ich möchte ein View erstellen, mit einer kumulativen Summe über alle w's und nach typ,datum sortiert.

Das Problem ist, das sub-select kennt typ nicht, und die kumulative Summe ist in der falschen Reihenfolge.

Mein Versuch:

SQL:

DROP VIEW book2_w_all;

CREATE VIEW book2_w_all AS
SELECT b2.*, COALESCE((SELECT SUM(book2.eur)
              FROM book2
              WHERE book2.id <= b2.id   # hier wird wohl das Problem sein
              AND typ='w'
              ORDER BY datum ASC), 0) AS eur_sum
FROM book2 AS b2
WHERE typ!='b'
ORDER BY datum ASC;

Beispiel:

Gegeben

| id | datum | eur | typ |
| --- | ----- | --- | --- |
| 1 | 15 | 10 | d |
| 2 | 16 | 20 | d |
| 3 | 1 | 30 | w |
| 4 | 30 | 50 | w |
| 5 | 17 | 40 | w |

Ist

| id | datum | eur | typ | sum |
| -- | ----- | --- | --- | --- |
| 3 | 1 | 30 | w | 30 |
| 1 | 15 | 10 | d | 30 |
| 2 | 16 | 20 | d | 30 |
| 5 | 17 | 40 | w | 120 |
| 4 | 30 | 50 | w | 80 |

Soll

| id | datum | eur | typ | sum |
| -- | ----- | --- | --- | --- |
| 3 | 1 | 30 | w | 30 |
| 1 | 15 | 10 | d | 0 |
| 2 | 16 | 20 | d | 0 |
| 5 | 17 | 40 | w | 70 |
| 4 | 30 | 50 | w | 120 |

floq0r · 30. August 2025

Wenn du nach datum sortierst wirst du das in der where clause deines subselects auch berücksichtigen müssen. Du siehst doch, dass du für die Summe im subselect doch alle Einträge mit einer ID kleiner der Referenz-ID selektierst und das Datum unberücksichtigt lässt.
Hast du das überhaupt selbst geschrieben?

KarlGa · 30. August 2025

floq0r schrieb:
wirst du das in der where clause deines subselects auch berücksichtigen müssen.

Leider dann gleiches Fehlverhalten

KarlGa · 30. August 2025

Es hat noch nicht ganz funktioniert, denn das ORDER BY in der sub-select query wird ignoriert:

SQL:

DROP VIEW book2_w_all;

CREATE VIEW book2_w_all AS
SELECT b2.*, COALESCE((SELECT
              SUM(IF(book2.typ='w', book2.eur, 0))
              FROM book2
              WHERE book2.datum <= b2.datum
              AND typ != 'b'
              ORDER BY typ ASC, datum ASC), 0) AS eur_sum
FROM book2 AS b2
WHERE typ != 'b'
ORDER BY typ ASC, datum ASC;

KarlGa · 30. August 2025

Jetzt geht's

SQL:

DROP VIEW book2_w_all;

CREATE VIEW book2_w_all AS
SELECT   t1.*,
         SUM(IF(t1.typ='w' AND t2.typ='w', t2.eur, 0)) AS eur_sum
FROM     book2 t1
         LEFT JOIN book2 t2 ON t2.datum <= t1.datum
GROUP BY id
ORDER BY typ ASC, datum ASC;

KarlGa · 31. August 2025

Performant ist so ein full (left) join aber auch nicht, oder? Also, wenn man annimmt, es gäbe 1000de Zeilen... was bei mir aber nicht der Fall ist... es geht nur um ein Haushaltsbuch.

Vielleicht sollte ich die Tabelle auch mal normalisieren, weil zurzeit stehen die Typen 'd', 'w' und 'b' in nur einer Tabelle (Einzahlungen, Auszahlungen und Balances)... Drei Tabellen wären vielleicht sinnvoller.

dms · 31. August 2025

Tipp - irgendeine SQL-Fiddel mit Daten lädt mehr zum grübeln ein

Leider fehlt ja das DDL der Tabelle book2 - warum sollte man die auch zeigen gel?
Gibts denn sinnvolle Indexe?

KarlGa · 31. August 2025

dms schrieb:
warum sollte man die auch zeigen gel?

https://sqlfiddle.com/mariadb/online-compiler?id=d677e973-1b46-47a9-8204-4e12c4791dba

SQL:

CREATE TABLE `book2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datum` datetime NOT NULL,
  `eur` decimal(12,2) NOT NULL,
  `typ` enum('b','d','w') NOT NULL DEFAULT 'b',
  PRIMARY KEY (`id`)
);

INSERT INTO `book2` (`datum`, `eur`, `typ`) VALUES
  ("2025-08-15", 10, 'd'),
  ("2025-08-16", 20, 'd'),
  ("2025-08-01", 30, 'w'),
  ("2025-08-30", 40, 'w'),
  ("2025-08-17", 50, 'w');

# falsch:
SELECT b2.*, COALESCE((SELECT SUM(book2.eur)
              FROM book2
              WHERE book2.id <= b2.id
              AND typ='w'
              ORDER BY datum ASC), 0) AS eur_sum
FROM book2 AS b2
WHERE typ!='b'
ORDER BY datum ASC;

# falsch:
SELECT b2.*, COALESCE((SELECT
              SUM(IF(book2.typ='w', book2.eur, 0))
              FROM book2
              WHERE book2.datum <= b2.datum
              AND typ != 'b'
              ORDER BY typ ASC, datum ASC), 0) AS eur_sum
FROM book2 AS b2
WHERE typ != 'b'
ORDER BY typ ASC, datum ASC;

# richtig:
SELECT   t1.*,
         SUM(IF(t1.typ='w' AND t2.typ='w', t2.eur, 0)) AS eur_sum
FROM     book2 t1
         LEFT JOIN book2 t2 ON t2.datum <= t1.datum
GROUP BY id
ORDER BY typ ASC, datum ASC;

dms schrieb:
SQL-Fiddel mit Daten

Mein anderer Beitrag muss noch freigeschaltet werden, bis dahin sollte genügen:

SQL:

CREATE TABLE `book2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datum` datetime NOT NULL,
  `eur` decimal(12,2) NOT NULL,
  `typ` enum('b','d','w') NOT NULL DEFAULT 'b',
  PRIMARY KEY (`id`)
);

INSERT INTO `book2` (`datum`, `eur`, `typ`) VALUES
  ("2025-08-15", 10, 'd'),
  ("2025-08-16", 20, 'd'),
  ("2025-08-01", 30, 'w'),
  ("2025-08-30", 40, 'w'),
  ("2025-08-17", 50, 'w');

# falsch:
SELECT b2.*, COALESCE((SELECT SUM(book2.eur)
              FROM book2
              WHERE book2.id <= b2.id
              AND typ='w'
              ORDER BY datum ASC), 0) AS eur_sum
FROM book2 AS b2
WHERE typ!='b'
ORDER BY datum ASC;

# falsch:
SELECT b2.*, COALESCE((SELECT
              SUM(IF(book2.typ='w', book2.eur, 0))
              FROM book2
              WHERE book2.datum <= b2.datum
              AND typ != 'b'
              ORDER BY typ ASC, datum ASC), 0) AS eur_sum
FROM book2 AS b2
WHERE typ != 'b'
ORDER BY typ ASC, datum ASC;

# richtig:
SELECT   t1.*,
         SUM(IF(t1.typ='w' AND t2.typ='w', t2.eur, 0)) AS eur_sum
FROM     book2 t1
         LEFT JOIN book2 t2 ON t2.datum <= t1.datum
GROUP BY id
ORDER BY typ ASC, datum ASC;

Im Allgemeinen würde ich einen freundlichen Umgangston bevorzugen. (Nur so als Hinweis)

dms · 31. August 2025

KarlGa schrieb:
Im Allgemeinen würde ich einen freundlichen Umgangston bevorzugen. (Nur so als Hinweis)

anstelle

dms schrieb:
Gibts denn sinnvolle Indexe?

Servus, vorweg dir einen schönen Sonntag - habe auch gerade nixx anderes zu tun.

A) Aus welchem Grund vermeidest du denn (ist ja nun nach meinen Anfrage zu sehen)
die Indexierung der Spalte datum?

B) in deiner Sollliste schreibts du in der Spalte DATUM nur eine Zahl - was suchst du denn nun?
Alle Summen zum Datum oder zum Tag des Monats - da werde ich mit deinem SQL und der Liste
nicht schlau

C) um wieviele Records geht es denn?

D) es geht nur im ein Buch - ich vermisse ansonster Hr. Codd und bin ob des Joins verunsichert

Ansonsten einen extra schönen Sonntag (habe nun andere Pläne)

KarlGa · 31. August 2025

a) der Tag ist nicht unique, es kann durchaus zwei Ein- oder Auszahlungen am gleichen Tag geben

b) eigentlich suche ich nur ein Datum, als Stellvertreter für einen Tag; zurzeit verwende ich für alle Ein- und Auszahlungen einfach denselben Monat... gut, int hätte es auch getan, aber dann ist der Wertebereich nicht nur auf Monatstage beschränkt

c) hmmm, ich würde sagen, zwischen 20 und 100 Zeilen; mehr regelmäßige Transaktionen habe ich nicht pro Monat

d) die Frage wäre, ob es auch einfacher/kürzer/schneller/übersichtlicher ginge; sprich, was ist best (or common) practice für diesen use case und für den mysql bzw. mariadb SQL Dialekt?

Einige dieser Fragen könnte sicherlich auch schon ChatGPT beantworten, aber dessen Antworten müssen dann auch auf "Stimmigkeit" hin von einem Experten in diesem Thema gegengeprüft werden; das ist in gewisser Hinsicht ein wenig der Nachteil an KIs.

Schönen Abend dir

Suche

SQL MySQL: Kumulative Summe in sortierter View erstellen

KarlGa

Banned

floq0r

Captain

KarlGa

Banned

KarlGa

Banned

KarlGa

Banned

KarlGa

Banned

dms

Commander

KarlGa

Banned

dms

Commander

KarlGa

Banned