Re: [despammed] Re: Frage zu SQL

Hi,

ich habe eine Tabelle:

test=# \d konten
Table "public.konten"
Column | Type | Modifiers
--------+-----------------------------+---------------
kunde | bigint | not null
ts | timestamp without time zone | default now()
typ | character(1) | not null
wert | numeric(10,2) | not null
text | character varying |
Indexes:
"idx_konten_kunde" btree (kunde)
"idx_konten_ts" btree (ts)
Check constraints:
"konten_typ_check" CHECK (typ ~ '[KD]'::text)
Foreign-key constraints:
"konten_kunde_fkey" FOREIGN KEY (kunde) REFERENCES kunden(id)

Dort will ich Zu/Abbuchungen speichern, mit typ 'K' für zubuchen, 'D'
abbuchen.

select a.kunde, case when a.typ = 'K' then a.wert when a.typ = 'D' then NULL END as kredit,
case when a.typ = 'K' then NULL when a.typ = 'D' then a.wert END as debit,
a.ts,
sum(get_saldo(a.typ, a.wert))
from konten a, konten b
where b.ts <= a.ts
group by a.kunde, kredit, debit, a.ts
order by a.ts;

Das get_saldo(a.typ, a.wert) ist ne kleine Hilfsfunktion, die den wert
negiert, wenn typ='D' ist.

Ich hab mal ein paar Testdaten reingeworfen und obiges SELECT gemacht:

kunde | kredit | debit | ts | sum
-------+---------+---------+----------------------------+------------
1 | | 2378.61 | 2002-10-13 16:08:44.342253 | -2378.61
1 | | 7785.63 | 2003-01-31 15:08:44.342253 | -15571.26
1 | | 8442.49 | 2004-02-14 15:08:44.342253 | -25327.47
1 | 6599.12 | | 2004-03-01 15:08:44.342253 | 26396.48
1 | 8478.31 | | 2004-06-16 16:08:44.342253 | 42391.55
1 | 1865.92 | | 2004-07-09 16:08:44.342253 | 11195.52
1 | 3271.92 | | 2004-08-09 16:08:44.342253 | 22903.44
1 | 695.64 | | 2004-11-20 15:08:44.342253 | 5565.12
1 | | 2547.23 | 2005-01-14 15:08:44.342253 | -22925.07
1 | 4203.80 | | 2005-02-28 15:08:44.342253 | 42038.00
1 | | 3477.18 | 2005-03-09 15:08:44.342253 | -38248.98
1 | | 8650.23 | 2005-03-23 15:08:44.342253 | -103802.76
1 | | 8460.50 | 2005-03-25 15:08:44.342253 | -109986.50

Klappt. Aber, bei einigen tausen Datensätzen, dauert es *EWIG*. (12000
Sätze, Abbruch nach ca. 1 Stunde warten...)

Wie könnte man das geschickter formulieren, oder ist es generell eine
schlechte Idee, weil ich ja in der letzten Spalte immer das 'davor'
beachten muß?

Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Andreas Kretschmer wrote:
> select a.kunde, case when a.typ = 'K' then a.wert when a.typ = 'D'
> then NULL END as kredit, case when a.typ = 'K' then NULL when a.typ =
> 'D' then a.wert END as debit, a.ts,
> sum(get_saldo(a.typ, a.wert))
> from konten a, konten b
> where b.ts <= a.ts
> group by a.kunde, kredit, debit, a.ts
> order by a.ts;

> Klappt. Aber, bei einigen tausen Datensätzen, dauert es *EWIG*.
> (12000 Sätze, Abbruch nach ca. 1 Stunde warten...)

Ich verstehe die Bedeutung dieser Join-Bedingung b.ts <= a.ts nicht. Das
ergibt, und jetzt lehn ich mich mathematisch mal aus dem Fenster, glaub
ich N*(N+1)/2 Datensätze, also hier rund 72 Millionen, was bei deiner
Tabellenstruktur mindestens 5 GB Speicher entspricht, die dann schön
sortiert und gruppiert werden müssen. Das würde ich also nochmal
überdenken.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

am 04.07.2005, um 16:27:10 +0200 mailte Peter Eisentraut folgendes:
> Andreas Kretschmer wrote:
> > select a.kunde, case when a.typ = 'K' then a.wert when a.typ = 'D'
> > then NULL END as kredit, case when a.typ = 'K' then NULL when a.typ =
> > 'D' then a.wert END as debit, a.ts,
> > sum(get_saldo(a.typ, a.wert))
> > from konten a, konten b
> > where b.ts <= a.ts
> > group by a.kunde, kredit, debit, a.ts
> > order by a.ts;
>
> > Klappt. Aber, bei einigen tausen Datensätzen, dauert es *EWIG*.
> > (12000 Sätze, Abbruch nach ca. 1 Stunde warten...)
>
> Ich verstehe die Bedeutung dieser Join-Bedingung b.ts <= a.ts nicht. Das
> ergibt, und jetzt lehn ich mich mathematisch mal aus dem Fenster, glaub
> ich N*(N+1)/2 Datensätze, also hier rund 72 Millionen, was bei deiner

ja, kann hinkommen, der explain sagte auch was in dieser Größenordnung.

Also, ich wollte ja die summen bis dahin erfassen, also grob so:

id | wert | summe
---------------------
1 | 5 | 5 (0+5)
2 | 10 | 15 (5+10)
3 | -2 | 13 (15-2)

> Tabellenstruktur mindestens 5 GB Speicher entspricht, die dann schön
> sortiert und gruppiert werden müssen. Das würde ich also nochmal
> überdenken.

mache ich. Mmmh, ich werd dann mal
www.varlena.com/varlena/GeneralBits/archive.php absuchen...

Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Andreas Kretschmer wrote:
> Also, ich wollte ja die summen bis dahin erfassen, also grob so:
>
> id | wert | summe
> ---------------------
> 1 | 5 | 5 (0+5)
> 2 | 10 | 15 (5+10)
> 3 | -2 | 13 (15-2)

Das geht mit SQL meines Wissens nach nicht, weil du ja, zur Optimierung,
die Zwischenergebnisse zwischenspeichern müsstest. Ich denke es geht
mit einer PL/pgSQL-Funktion, die das alles im Speicher macht. Wenn das
nicht geht, dann wohl eher im Client.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Andreas Kretschmer schrob:

> am 04.07.2005, um 16:27:10 +0200 mailte Peter Eisentraut folgendes:
>> Ich verstehe die Bedeutung dieser Join-Bedingung b.ts <= a.ts nicht. Das
>> ergibt, und jetzt lehn ich mich mathematisch mal aus dem Fenster, glaub
>> ich N*(N+1)/2 Datensätze, also hier rund 72 Millionen, was bei deiner
>
> ja, kann hinkommen, der explain sagte auch was in dieser Größenordnung.
>
> Also, ich wollte ja die summen bis dahin erfassen, also grob so:
>
> id | wert | summe
> ---------------------
> 1 | 5 | 5 (0+5)
> 2 | 10 | 15 (5+10)
> 3 | -2 | 13 (15-2)

Deine Lösung liegt zwar - wie Peter festgestellt hat - in O(n^2), das
Problem jedoch in O(n). Ich denke aber nicht, daß man mit SQL alleine
da 'ran kommt, da man sich die Summe der letzten Zeile merken müßte.
In Verbindung mit pltcl (oder PL/FOO, wenn's dort ebenfalls Persistenz
gibt) ist es jedoch kein Problem:

--8<---------------cut here---------------start------------->8---
scratch=> create or replace function sum_upto_here(numeric) returns numeric as '
scratch'> upvar GD sum
scratch'>
scratch'> if {[string equal "" $1]} {
scratch'> set sum 0
scratch'> } elseif {![info exists sum] || ![string length $sum]} {
scratch'> set sum $1
scratch'> } else {
scratch'> set sum [expr {$sum + $1}]
scratch'> }
scratch'> return $sum
scratch'> ' language 'pltcl' volatile;
CREATE FUNCTION
scratch=> create table foo (bar int);
CREATE TABLE
scratch=> \copy foo from stdin
1
2
3
\.
scratch=> select bar, sum_upto_here(bar) from foo;
bar | sum_upto_here
-----+---------------
1 | 1
2 | 3
3 | 6
(3 Zeilen)
--8<---------------cut here---------------end--------------->8---

Der Zähler wird hier mit sum_upto_here(NULL) zurückgesetzt, vielleicht
geht das auch noch eleganter?

HTH
Andreas

In article <200507041939(dot)44229(dot)peter_e(at)gmx(dot)net>,
Peter Eisentraut <peter_e(at)gmx(dot)net> writes:

> Andreas Kretschmer wrote:
>> Also, ich wollte ja die summen bis dahin erfassen, also grob so:
>>
>> id | wert | summe
>> ---------------------
>> 1 | 5 | 5 (0+5)
>> 2 | 10 | 15 (5+10)
>> 3 | -2 | 13 (15-2)

> Das geht mit SQL meines Wissens nach nicht, weil du ja, zur Optimierung,
> die Zwischenergebnisse zwischenspeichern müsstest. Ich denke es geht
> mit einer PL/pgSQL-Funktion, die das alles im Speicher macht. Wenn das
> nicht geht, dann wohl eher im Client.

Die Frage ist, ob man das überhaupt serverseitig machen _sollte_. Ich
finde, ein DB-Server sollte lediglich die nötigen Daten zurückliefern
(evtl. sortiert), während die Aufbereitung dieser Daten mindestens
ebensogut der Client erledigen kann. Eine laufende Summe scheint mir
da client-seitig besser aufgehoben.

Andreas Seltenreich <andreas+pg(at)gate450(dot)dyndns(dot)org> schrieb:
> Deine Lösung liegt zwar - wie Peter festgestellt hat - in O(n^2), das
> Problem jedoch in O(n). Ich denke aber nicht, daß man mit SQL alleine
> da 'ran kommt, da man sich die Summe der letzten Zeile merken müßte.
> In Verbindung mit pltcl (oder PL/FOO, wenn's dort ebenfalls Persistenz

Ich bedanke mich bei Dir und PeterE. Mmmhhh, ich teste das morgen mal
aus.

Das ist jetzt auch nicht wirklich lebenswichtig für mich. Wie schon
angedeutet, werde ich evtl. zum LinuxInfoTag in DD einen Vortrag¹ halten,
wo ich so ein paar schöne Dinge zu PG erklären möchte, also so Dinge wie
Trigger, Views, Stored Procedures, Transaktionen und so, und bin halt
grad dabei, mir ein Grundkonzept aufzubauen.
Ich bin nicht wirklich der SQL-Profi, mache aber @work einiges und finde
PG, im Vergleich zu anderen freien System genial und im Vergleich zu
kommerziellen Systemen ebenbürtig, insbesondere wenn ich sehe, daß
$Softwarebude unserer $Firme eine ORACLE-Lösung verhöckert hat, wo nicht
einmal referentielle Integrität in den Kundenstammdaten gewährleistet
ist *kotz* *würg*, Idioten diese.
Das sind für mich Dinge, die ein RDMS bieten soll und muß und die man
nutzen soll und muß - das ist so etwa mein Fahrplan - vorstellen von
dem, was PG kann und zeigen, daß man das nutzen *sollte*.

PS.: auch wenn solche Vorträge 'lästig' sind im Sinne davon, daß man
selbst viel Zeit investiert - hey, ich beschäftige mich damit und lerne
_für mich_ dabei. Und dafür bin ich mir mit 38 noch immer nicht zu
alt...

¹ Peter: falls es dazu kommt, ich würde auch Werbematerial auslegen
und/oder mit den Debian-Leuten reden, daß ich mit an deren Stand
a bissl was zu PG sage. Werbematerial wäre da willkommen...

Andreas
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)

Harald Fuchs <hf0614x(at)protecting(dot)net> schrieb:
> ebensogut der Client erledigen kann. Eine laufende Summe scheint mir
> da client-seitig besser aufgehoben.

Hey - die Idee hat was...

Andreas
--
Diese Message wurde erstellt mit freundlicher Unterstützung eines freilau-
fenden Pinguins aus artgerechter Freilandhaltung. Er ist garantiert frei
von Micro$oft'schen Viren. (#97922 http://counter.li.org) GPG 7F4584DA
Was, Sie wissen nicht, wo Kaufbach ist? Hier: N 51.05082°, E 13.56889° ;-)

Kretschmer Andreas wrote:
> ¹ Peter: falls es dazu kommt, ich würde auch Werbematerial auslegen
> und/oder mit den Debian-Leuten reden, daß ich mit an deren Stand
> a bissl was zu PG sage. Werbematerial wäre da willkommen...

Geht klar. Ich werde wohl auch kommen. Zumindest habe ich mich bei den
Organisatoren schon großspurig angekündigt. :)

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Harald Fuchs schrob:

> Die Frage ist, ob man das überhaupt serverseitig machen _sollte_. Ich
> finde, ein DB-Server sollte lediglich die nötigen Daten zurückliefern
> (evtl. sortiert), während die Aufbereitung dieser Daten mindestens
> ebensogut der Client erledigen kann. Eine laufende Summe scheint mir
> da client-seitig besser aufgehoben.

Das ist natürlich ein gutes Argument. Wenn Andreas aber eine Bank
aufmachen wollte, bliebe es bestimmt nicht bei einem Client, der diese
Funktionalität bräuchte. Da wär' die Software für den Bankschalter,
den Kontoauszugsdrucker, Homebanking via HTTPS/CGI, 'ne
HBCI-Schnittstelle, die Überwachungsmöglichkeiten für den Statt, etc.
Da wäre es mir lieber, wenn man sich nur an einer einzigen Stelle um
Überläufe und Rundungsfehler kümmern muß, und die dutzend Anwendungen
möglichst wenig mit den Geldbeträgen 'rumrechnen würden.

Gruß
Andreas
--

am 04.07.2005, um 20:45:12 +0200 mailte Peter Eisentraut folgendes:
> Kretschmer Andreas wrote:
> > ¹ Peter: falls es dazu kommt, ich würde auch Werbematerial auslegen
> > und/oder mit den Debian-Leuten reden, daß ich mit an deren Stand
> > a bissl was zu PG sage. Werbematerial wäre da willkommen...
>
> Geht klar. Ich werde wohl auch kommen. Zumindest habe ich mich bei den

Fein.

> Organisatoren schon großspurig angekündigt. :)

Staun. Hab ich nix von vernommen...

Andreas, Mitglied des Vereins...
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

am 04.07.2005, um 19:41:30 +0200 mailte Andreas Seltenreich folgendes:
> da 'ran kommt, da man sich die Summe der letzten Zeile merken müßte.
> In Verbindung mit pltcl (oder PL/FOO, wenn's dort ebenfalls Persistenz
> gibt) ist es jedoch kein Problem:

Danke, ich hab mal etwas auf http://www.varlena.com/varlena/GeneralBits/
geschaut und dort auch gleich ein Beispiel mit plperl gefunden. Ich
bastle grad damit etwas rum...

Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===

Am Dienstag, 5. Juli 2005 07:23 schrieb Andreas Kretschmer:
> > Organisatoren schon großspurig angekündigt. :)
>
> Staun. Hab ich nix von vernommen...

Naja, nur bei denen, die auf dem LinuxTag präsent waren.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/