Self join und Abs()

[semo]

Ich möchte Geldflüsse nachvollziehen.
Ein Beispiel: am 01.10. wurden 100 € von einem Konto überwiesen und am 03.10 sind diese auf einem anderen Konto eingegangen und dort wiederum weiter überwiesen worden.
Nun muss ich ja hier den Betrag 100 € und -100 € abfragen und auch den Zeitraum der Buchungen berücksichtigen. Als Beispiel habe ich mal 7 Tage angesetzt.

Aufbau meiner Tabelle "Buchungen", die alle Buchungen aller Konten enthält:

Code:

ID
KontoID
Buchungstag
Geldbetrag
Verwendungszweck
Kontonummer
Bankleitzahl
Bemerkungen

Mein bisheriger Lösungansatz:

Code:

select 
b1.id, 
b1.KontoID, 
b1.Buchungstag,
b1.geldbetrag, 
Abs(b1.geldbetrag) as b1absgeldbetrag, 
b1.Verwendungszweck,
b1.Kontonr,
b1.BLZ,
b1.Bemerkung,
b2.id, 
b2.Buchungstag,
b2.Geldbetrag, 
Abs(b2.Geldbetrag) as b2absgeldbetrag 
FROM Buchungen b1, Buchungen b2
WHERE
b1absgeldbetrag = b2absgeldbetrag
and b1.id <> b2.id 
and b1.buchungstag between b2.Buchungstag - 7 and b2.buchungstag

Die Abfrage läuft ewig. Ist der join zweier Tabellen per berechnetem Wert (Abs()) erlaubt?
Hat jemand Verbesserungsvorschläge für mich?

[Sir Rufo]

Schreib doch mal auf

a) was in den Tabellen stehen wird (die Daten)
Und
b) was die Abfrage liefern soll

[FredlFesl]

Zitat von semo:

Die Abfrage läuft ewig. Ist der join zweier Tabellen per berechnetem Wert (Abs()) erlaubt?

Erlaubt ist alles, was nicht verboten ist. Wieso sollte das verboten sein?

Aber SQLite ist nicht gerade Performanceweltmeister.

Falls ID monoton steigend ist, würde ich die Abfrage zusätzlich mit "a.ID > b.ID" einschränken.
Welche Spalten sind mit einem Index versehen?

Wenn Du ausgehende (<0) mit eingehenden (>0) Buchungen korrelieren willst, würde ich nicht mit ABS arbeiten, sondern die Buchungswerte direkt vergleichen. Das ist genauer, denn deine Abfrage liefert auch die Buchungen, bei denen ein Geldbetrag X auf ein Konto Y eingegangen ist, und der gleiche Betrag bis zu 7 Tage später irgendwo abgegangen ist => Blödsinn.

SQL-Code:

Select * from
  buchungen as Ausgehend join 
  buchungen as Eingehend 
    on Ausgehend.Geldbetrag = -Eingehend.Geldbetrag -- Index kann nicht verwendet werden!

where Eingehend.ID>Ausgehend.ID 
  and Ausgehend.Geldbetrag < 0
  and Eingehend.Buchungsdatum between Ausgehend.Buchungsdatum - 7 and Ausgehend.Buchungsdatum

Du könntest auch den Geldbetrag als Absolutwert speichern und ein zusätzliches Feld "BuchungsArt" einführen. Dort steht dann "Überweisung", "Einzahlung" etc. drin.
Darüber (und über den Geldbetrag) legst Du dann einen Index und verwendest diese Spalten als JOIN-Verknüpfung. Dann kann die DB die Indexe verwenden. So ist das nicht möglich und daher wird die Abfrage recht langsam sein.

SQL-Code:

Select * from
  buchungen as Ausgehend join 
  buchungen as Eingehend 
    on Ausgehend.Geldbetrag = Eingehend.Geldbetrag -- Index kann verwendet werden!

where Eingehend.ID>Ausgehend.ID  
  and Ausgehend.Buchungsart = Ueberweisung  
  and Eingehend.Buchungsart = Einzahlung    
  and Eingehend.Buchungsdatum between Ausgehend.Buchungsdatum - 7 and Ausgehend.Buchungsdatum

Oder machst es noch anders:
Du führst zwei Spalten für den Geldbetrag: Ausgehend, Eingehend. Darüber legst Du jeweils einen Index und vergleichest dann Ausgehend mit Eingehend.

SQL-Code:

Select * from
  buchungen as Ausgehend join 
  buchungen as Eingehend 
    on Ausgehend.AusgehenderGeldbetrag = Eingehend.EingehenderGeldbetrag -- Index kann verwendet werden

where Eingehend.ID>Ausgehend.ID 
  and Ausgehend.AusgehenderGeldbetrag < 0
  and Eingehend.Buchungsdatum between Ausgehend.Buchungsdatum - 7 and Ausgehend.Buchungsdatum

[Jumpy]

Da ich alles andere als ein Banker bin, mal naiv gefragt. Würde es nicht Sinn machen auch den "Zweck" in den Vergleich mit aufzunehmen, da der doch bei der Abgehenden und Ankommenden Buchun gleich sein müsste, oder?

[semo]

Es geht darum Geldschiebereien über mehrere Konten hinweg nachzuvollziehen und nicht nur die korrespondierenden Eingänge auf einem Konto nach einer Überweisung von einem anderen Konto anzuzeigen.

Angezeigt werden sollen alle Geldflüsse gruppiert nach Betrag, so dass interessante Konten dabei zum Vorschein kommen.

[jobo]

-Du solltest zunächst einen sauberen Join hinbekommen.
Andernfalls permutieren die Mengen und Du erhältst keinen Geldstrom, sondern eine wunderbare Geldvermehrung.
- Dann wäre zu klären, ob es ein geschlossenes Kontensystem ist oder nicht und wie Du dann die Einzelbuchungen "nach innen" und "nach außen" bearbeitest und bewertest.
- Und was ist ein "Geldstrom"? Deine Abfrage stellt scheinbar einfach ein Buchungspaar nebeneinander.

Der "größer" Vergleich und der Betragsjoin hinken ziemlich. Was ist mit identischen Beträgen im Betrachtungszeitraum? Die werden bei dem Statement alle (jeder mit jedem ) gejoint! Hast Du Kontrolle über das Verfahren, mit dem die Buchungen erzeugt werden? Das würde evtl. einen Ansatzpunkt über die ID liefern. Wenn die Buchungsoperation 100% atomar ist und eine ordentliche sequenz verwendet wird und später niemand in den Daten "rumfummelt", müsstest Du Buchungspaare an aufeinanderfolgenden ID erkennen und joinen können. Auch der Verwendungszweck könnte ein Kriterium sein. Der sollte zumindest für ein Buchungspaar identisch sein, eindeutiges Joinkriterium wäre er aber auch nicht.

Wenn der Join exakt ist (nicht mehr permutiert) wirst Du schon einen Performance Unterschied bemerken. Dann kannst Du mit Tuning beginnen: Indizes, Queryumstellung, Optimizerregeln (gibts die unter SQLite?)

Grundsätzlich wäre noch die Frage, ob Du nur auswerten willst/kannst oder ob Du auch Einfluss auf das Datenmodel hast.

OT: Monopoly gibt es schon als Computerspiel

[semo]

Ich habe nun erst einmal alle Buchungen nach dem absoluten Betrag gruppiert:

Code:

select * FROM Buchungen as b1
where 
b1.Geldbetrag <> 0
and
Abs(b1.Geldbetrag) in 
(
SELECT Abs(b2.Geldbetrag) as b2absbetrag
FROM Buchungen b2
where b2absbetrag <> 0
GROUP BY b2absbetrag
HAVING count(b2absbetrag) > 1
)
order by Abs(b1.Geldbetrag) desc, b1.Buchungstag asc

Dies geht recht fix und man hat nun zumindest mal eine Grundlage für die manuelle Auswertung.
Jetzt fehlt "nur" noch die Abfrage der Buchungen innerhalb eines Zeitraums von zum Beispiel 7 Tagen.