(SELECT) Spalten aufaddieren bis bestimmter Wert erreicht ist

Hallo Leute ich brauch mal nen Query oder nen Anhaltspunkt.

Ich habe gerade heute angefangen mich das erste mal mit Postgres zu beschäftigen, weil ich eine Anwendung (ein Planspiel Börse) schreibe, für die ich die RETURNING Anweisung benötige (Update und Select in einem Query gekoppelt). Komme ansonsten von nem MySQL Background, wo ich zwar auch nicht so der Held drin bin, aber schon ganz gut auch kompliziertere Sachen hinkrieg.

Nun aber zu meinem eigentlichen Problem, zu dem ich auch in MySQL keinen Ansatz wüsste.

Man stelle sich vor, ich habe eine Tabelle mit n Musikstücken, mit unterschiedlichsten Attributen und vor allem immer unterschiedlichen Längen.

Ich möchte jetzt ein Query haben was mir zu bestimmten Attributen soviele Musikstücke raus sucht, bis eine CD voll ist. Um es erstmal einfach zu machen würde ich jetzt ein Query mit der WHERE Klausel auf das gewünschte Attribut machen und dieses dann der Länge nach sortieren. Das haut ja noch hin, aber wie bekomme ich jetzt in einem Query die WHERE Klausel, die Sortierung und das Aufaddieren der Stücklängen bis zur vollen CD hin?

Das Problem ist von dem Planspiel abstrahiert. Da das ganze bischen Zeitkritisch ist, muss es tatsächlich in einem Query untergebracht werden. Gibt es überhaupt derartige Abfragen die aufaddieren?

Schnellschuß und keine vollständige Lösung: PG 8.4 und seine CTE-Funktionen:

test=*# select * from musik;
 id | laenge
----+--------
  1 |     10
  2 |     12
  3 |      8
  4 |     16
  5 |      9
  6 |     13
  7 |     14
  8 |      8
  9 |     12
 10 |     10
 11 |     13
 12 |      7
(12 rows)

Nun mal der Länge nach sortiert und aufsummiert:

test=*# select id, laenge, sum(laenge) over (order by laenge desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as spielzeit from musik; id | laenge | spielzeit
----+--------+-----------
  4 |     16 |        16
  7 |     14 |        30
 11 |     13 |        43
  6 |     13 |        56
  2 |     12 |        68
  9 |     12 |        80
  1 |     10 |        90
 10 |     10 |       100
  5 |      9 |       109
  3 |      8 |       117
  8 |      8 |       125
 12 |      7 |       132
(12 rows)

Und nun so, daß die Spielzeit unter z.B. 80 bleibt:

test=*# select * from (select id, laenge, sum(laenge) over (order by laenge desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as spielzeit from musik) foo where spielzeit < 80;
 id | laenge | spielzeit
----+--------+-----------
  4 |     16 |        16
  7 |     14 |        30
 11 |     13 |        43
  6 |     13 |        56
  2 |     12 |        68
(5 rows)

Ja, ich weiß, da hätten nun noch andere Titel mit Platz. Da fällt mir grad nix ein, zumindest nix triviales. Man könnte z.B. mittels plpgsql aber da fortsetzen und aus dem verbleibenden Pool alle löschen, die nun zu groß sind, und dann das Spiel wiederholen, bis man durch ist.


Andreas

Andreas, ich liebe Dich!

Sauber, das ist genau was ich wollte. Das ganze so zu optimieren, dass die CD möglichst voll würde haut nicht hin, hierzu müsste man die Potenzmenge errechnen und das geht bis maximal 40-50 Titel gut, darüberhinaus wäre der Rechenaufwand nicht mehr zu meistern.

Das ganze CD Beispiel ja aber nur fürs Planspielbörse abstrahiert ist und ich eigentlich zu einer Menge an Aktien alle Möglichen Kaufangebote, die reinpassen würden ausgeben möchte (und diese Gleichzeitig blocken, deshalb das ganze mit update returning) wird die Summe der Kaufangebote warscheinlich weit weit über die 40-50 hinaus gehen.

Noch eine kleine Frage, werds nachher erst testen, geht das erst ab Postgres 8.4? Weil hab noch 8.3 unter Lenny. Muss sonst mal sehen, wie ich da nen update hinkrieg.

Gruß

Andre

Zu spät, bin verheiratet.

Noch eine kleine Frage, werds nachher erst testen, geht das erst ab Postgres 8.4? Weil hab noch 8.3 unter Lenny. Muss sonst mal sehen, wie ich da nen update hinkrieg.

Gruß

Andre

Ja, ab 8.4, eher nicht.

Andreas

Hm, mistig, war nen bischen voreilig. Unter 8.3.7 gehts natürlich nicht. Schade. Hinzukommt, dass die 8.4 bei Lenny nur als experimental zu haben ist, ich müsste also ein unstables System fahren (zumindest für die Datenbank).

Kann sich natürlich nur noch um Monate handeln, bis das dann in Debian 5.1 (oder 6.0) mit auftaucht. Du schreibst noch was von plpgsql. Das ist absolutes Neuland für mich, werd auch gleich dazu erstmal das Tutorial durchlaufen. Hast Du dennoch nen Ansatz, nach dem ich suchen kann? Ist ja im Grunde wie ne Schleife zu verstehen. Hole mir solnage neue Zeilen, bis das Fass voll ist.

du kannst die 8.4 ruhig benutzen, unabhaenig davon, ob das als unstable markiert ist oder nicht. Im gegenteil, du kannst dich freuen, dass Debian das zumindest im Paketsystem drin hat. weder in gentoo, archlinux, … ist es enthalten. seufz

Hab ich schon gesehen, mein Gentoo hat bis auf 8.0 sogar noch alles maskiert und bei sabayon sieht es auch nicht besser aus.

Da der Server ne vserverumgebung fährt, werde ich es jetzt wohl so machen, dass ich die Datenbank auf einen unstable Gast auslagere, die dann eben nur über das interne Netz erreichbar sein wird.

Trotzdem nochmal ne Frage zu PL/pgSQL. Das ist doch ne Prozedurale erweiterung des SQL Standarts, oder nicht?

Ist dort theoretisch etwas wie folge möglich:

Locke Tabelle für weitere SQL Verbindungen
Hole mir alle Zeilen, die noch in einen Bestimmten Wert reinpassen (das aufaddieren)
markiere alle benutzten Zeile
Nehme Lock wieder raus

Man könnte das ganze natürlich auch über PHP Abfackeln. Also erst alle Zeilen holen, auf addieren und sich die IDs der benutzen merken, dann diese wieder Updaten. Da das ganze aber Zeitkritisch ist, habe ich mit Pech eine Zeile zweimal verwendet. Und genau das will ich vermeiden.



Sowas ist moeglich.

PostgreSQL: Documentation: Manuals: PostgreSQL 8.4: PL/pgSQL - SQL Procedural Language