zeilenweise Berechnung

Hallo

Ich habe folgende verkürzte Tabellenstruktur.

Das ist ein Beispiel für ein Produkt.

ID | EntnahmeMenge | geplanteEntnahmeMenge | OffeneMenge
1 | 3 | 12 | 9
2 | 1 | 12 | 8
3 | 2 | 12 | 6
4 | 1 | 12 | 5

In der Tabelle sollen Entnahme Mengen gespeichert werden.
Jetzt suche ich eine Lösung wie ich in einer Abfrage mit MySQL die Spalte OffeneMenge berechnen kann.

Kann mir jemand helfen? Vielen Dank.

Doommortar

 

Ich hab eine Gute und eine Schlechte Nachricht, zuerst die Gute:

Code:

test=*# select * from doom;
 id | entnahme | menge
----+----------+-------
  1 |        3 |    12
  2 |        1 |    12
  3 |        2 |    12
  4 |        1 |    12
(4 rows)

Time: 0,158 ms
test=*# select *, menge - sum(entnahme) over (order by id) as offen from doom ;
 id | entnahme | menge | offen
----+----------+-------+-------
  1 |        3 |    12 |     9
  2 |        1 |    12 |     8
  3 |        2 |    12 |     6
  4 |        1 |    12 |     5
(4 rows)

Die Schlechte: MySQL kann das glaube nicht.

 

Gibt es denn überhaupt mehrere Einträge in der Tabelle für eine ID? Oder tuts schon

Code:

SELECT    *,
        menge - entnahme AS offen
FROM    doom

 

Hallo

Vielen Dank für die Antworten.

Wenn ich jetzt kein Denkfehler habe müßte ich mit der Abfrage aber folgendes Ergebnis bekommen.
Er soll aber die entnommene Menge aus den vohergehenden Zeilen mit abrechnen.

Code:

id | entnahme | menge | offen
----+----------+-------+-------
  1 |        3 |    12 |    9
  2 |        1 |    12 |    11
  3 |        2 |    12 |    10
  4 |        1 |    12 |    11
(4 rows)

Doommortar

 

Jupps. Mir fällt aber auch nix ein, was mit MySQL gehen könnte. An sich ist es aber so, daß Du diese letzte Spalte nicht brauchst und das bei der Ausgabe durch die Applikation mit ausrechnen kannst.
Oder halt auf PG wechseln ;-)

Andreas

 

Ok, schade. Die letzte Spalte sollte auch nur zum ausgeben sein.

Was ist PG?

 

PostgreSQL. Meiner Meinung nach um Welten besser als MySQL.

 

Also mit PG sollte sich das machen lassen?

Was sind denn die Vorteile gegenüber MySQL?

 

Hab ich doch oben gezeigt, mein Beispiel ist mit PostgreSQL gemacht und es macht genau das, was Du willst.

Neben einer deutlich freieren Lizenz (BSD, MySQL gehört zu Oraggle):

• Window-Funktionen wie oben gezeigt
• weit mehr Datentypen
• funktionale und partielle Indexe
• CHECK-Constraints funktionieren im Gegensatz zu MySQL
• ein cooler Planer, detailiertes EXPLAIN
• zig interne Programmiersprachen
• ...

 

Genau das war meine Frage, stand so nicht explizit im Anfangspost.

 

Jein, es stand so in den gezeigten Daten ;-)

 

Zu tiefsinnig für einen Samstag im Büro

Code:

SELECT    t1.ID,
        t1.EntnahmeMenge,
        t1.geplanteEntnahmeMenge,
        t1.geplanteEntnahmeMenge - (    SELECT    sum(t2.EntnahmeMenge)
                                        FROM    tabelle t2
                                        WHERE    t2.ID >= t1.ID ) AS OffeneMenge
FROM    tabelle t1

 

Könnte gehen, aber je Zeile ein extra Subselect mit Aggregation, ...

 

Das ist wahr, bei größeren Tabellen würde ich dann auch eher mit Trigger arbeiten und eben eine fixe Spalte berechnen. Da es ja auch eine geplanteEntnahmeMenge als Spalte gibt ist das DB Design sowieso nicht so ganz ideal

 

Hab mir mal EXPLAIN angeschaut:

Code:

test=*# explain analyse select *, menge - sum(entnahme) over (order by id) as offen from doom ;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=1.08..1.16 rows=4 width=12) (actual time=0.046..0.051 rows=4 loops=1)
   ->  Sort  (cost=1.08..1.09 rows=4 width=12) (actual time=0.033..0.036 rows=4 loops=1)
         Sort Key: id
         Sort Method: quicksort  Memory: 17kB
         ->  Seq Scan on doom  (cost=0.00..1.04 rows=4 width=12) (actual time=0.007..0.008 rows=4 loops=1)
 Total runtime: 0.086 ms
(6 rows)

Time: 0,365 ms
test=*# explain analyse select *, menge - (select sum(t2.entnahme) from doom t2 where t2.id<=doom.id) from doom ;
                                                  QUERY PLAN
---------------------------------------------------------------------------------------------------------------
 Seq Scan on doom  (cost=0.00..5.30 rows=4 width=12) (actual time=0.024..0.045 rows=4 loops=1)
   SubPlan 1
     ->  Aggregate  (cost=1.05..1.06 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=4)
           ->  Seq Scan on doom t2  (cost=0.00..1.05 rows=1 width=4) (actual time=0.001..0.003 rows=2 loops=4)
                 Filter: (id <= doom.id)
                 Rows Removed by Filter: 2
 Total runtime: 0.079 ms
(7 rows)

Deine Lösung braucht einen Seq. Scan außen rum und dann 4 mal innen (bei 4 Zeilen), da kommst dann halt schon auf die Kosten von über 5. Mit wachsender Anzahl Zeilen geht das natürlich mächtig in die Hose.