zeilenweise Berechnung

[Doommortar]

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

 

[akretschmer]

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:

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.

 

[ukulele]

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

SELECT    *,
        menge - entnahme AS offen
FROM    doom

 

[Doommortar]

Hallo

Vielen Dank für die Antworten.

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

SELECT    *,
        menge - entnahme AS offen
FROM    doom

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.

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

Doommortar

 

[akretschmer]

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.

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

 

[Doommortar]

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

Was ist PG?

 

[akretschmer]

Was ist PG?

PostgreSQL. Meiner Meinung nach um Welten besser als MySQL.

 

[Doommortar]

Also mit PG sollte sich das machen lassen?

Was sind denn die Vorteile gegenüber MySQL?

 

[akretschmer]

Also mit PG sollte sich das machen lassen?

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

Was sind denn die Vorteile gegenüber MySQL?

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
• ...

 

[ukulele]

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.

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

 

[akretschmer]

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

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

 

[ukulele]

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

Zu tiefsinnig für einen Samstag im Büro

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

 

[akretschmer]

Zu tiefsinnig für einen Samstag im Büro

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, ...

 

[ukulele]

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

 

[akretschmer]

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:

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.