Komplexe Select Abfrage

[dave_christopher]

Hallo zusammen,

ich habe folgende Ausgangstabelle:

ID | Aktion | Datum | Betrag |
-----------------------------------------
1   |   1217   | 15.01.17 | 10
2   |   1217   | 17.02.17  | 10
3   |   1217   | 14.03.17  | 10
4   |   1216   | 14.03.16  | 10
5   |   1216   | 14.04.16 | 10
6   |   1216   | 15.04.16 | 40
7   |   1216   | 16.04.16 | 20
8   |   1216   | 17.04.16 | 10
9   |   1317   | 15.01.17 | 10
10 |   1317   | 17.02.17  | 10
11  |   1317   | 14.03.17  | 10
12  |   1316   | 14.03.17  | 10
13  |   1316   | 14.04.16 | 10
14  |   1316   | 15.04.16 | 40
15  |   1316   | 16.04.16 | 20
16  |   1316   | 17.04.16 | 10

Und ich möchte folgende Ergebnistabelle erhalten:

 Aktion | Anzahl | Betrag |
-----------------------------------------
1217   | 3 | 30
1216   | 3 | 60
1317   | 4 | 40
1316   | 4 | 80

Ich benötige also ein MySQL-Statement, welches die Anzahl der aktuellen Betragsbuchungen pro Aktion im aktuellen Jahr kalkuliert, dies als Referenzwert für die jeweilige Aktion (12*, 13* etc.) nimmt und die Beträge aus den Vorjahren eben nur bis zu dieser Anzahl kumuliert.

Ist das überhaupt möglich?
Vielleicht hat jemand eine Idee oder Lösung?

Herzlichen Dank für jede Hilfe.

 

[akretschmer]

für Aktion 1216 sehe ich 5 Einträge (alle Jahr 2016) mit Summe 90. Erkläre bitte, wie Du auf 3 und 60 kommst.

 

[dave_christopher]

Die Anzahl 3 ergibt sich aus diesen Buchungen (Anzahl Buchungsdatum):
1 | 1217 | 15.01.17 | 10
2 | 1217 | 17.02.17 | 10
3 | 1217 | 14.03.17 | 10

Nur eben diese Anzahl darf für 1216 herangezogen werden (auch wenn es mehr Buchungstage gibt):
4 | 1216 | 14.03.16 | 10
5 | 1216 | 14.04.16 | 10
6 | 1216 | 15.04.16 | 40

Ergibt die Summe: 60

Eben das ist das komplizierte...

 

[ukulele]

Das ergibt immer noch keinen logischen Zusammenhang für mich. Du hast gruppiert:

Aktion | Anzahl | Betrag |
-----------------------------------------
1217 | 3 | 30
1216 | 5 | 90
1317 | 3 | 30
1316 | 5 | 90

Zeige uns anhand dieser Datensätze wie du auf dein Ergebnis kommst.

 

[akretschmer]

Wenn ich Dich richtig interpretiere, hat das Aktionsfeld 2 Bedeutungen: die ersten 2 Stellen eine Nummer, die letzten 2 Stellen das Jahr, oder?

Warum dann aber:

12 | 1316 | 14.03.17 | 10
13 | 1316 | 14.04.16 | 10

 

[dave_christopher]

Gut erkannt! Das war ein Tippfehler von mir.
So ist es korrekt:

ID | Aktion | Datum | Betrag |
-----------------------------------------
1 | 1217 | 15.01.17 | 10
2 | 1217 | 17.02.17 | 10
3 | 1217 | 14.03.17 | 10
4 | 1216 | 14.03.16 | 10
5 | 1216 | 14.04.16 | 10
6 | 1216 | 15.04.16 | 40
7 | 1216 | 16.04.16 | 20
8 | 1216 | 17.04.16 | 10
9 | 1317 | 15.01.17 | 10
10 | 1317 | 17.02.17 | 10
11 | 1317 | 14.03.17 | 10
12 | 1317 | 14.03.17 | 10
13 | 1316 | 14.04.16 | 10
14 | 1316 | 15.04.16 | 40
15 | 1316 | 16.04.16 | 20
16 | 1316 | 17.04.16 | 10

also:
12 | 1317 | 14.03.17 | 10
13 | 1316 | 14.04.16 | 10

Die von Dir interpretierte Logik ist somit richtig!

 

[akretschmer]

dann sollten das die Werte für 2016 sein:

test=*# with anzahl as (select aktion-1 as aktion2, count(datum) from dave where extract(year from datum) = 2017 and aktion::text ~ '17$' group by aktion), j2016 as (select *, row_number() over (partition by aktion order by datum) as zeile from dave where extract(year from datum) = 2016), foo as (select * from j2016 left join anzahl on j2016.aktion=anzahl.aktion2) select aktion2, count(1), sum(betrag) from foo where zeile <= count and extract (year from datum) = 2016 group by aktion2 ;
 aktion2 | count | sum
---------+-------+-----
  1216 |  3 |  60
  1316 |  4 |  80
(2 Zeilen)

 

[dave_christopher]

Wie kann ich dieses Statement testen?
Kann ich es als einfache Query ausführen?

Aber die Ergebnisse scheinen korrekt.
Würde das auch dynamisch über eine größere Tabelle mit vielen Aktionen und Buchungstagen funktionieren?
Als 2017 müsste eben stattdessen die aktuelle berechnete Jahreszahl herangezogen werden.

 

[akretschmer]

Ja, das ist ein einfaches Statement. Allerdings nutze ich PostgreSQL. Eine ganze Reihe von Features, die ich nutze, kann MySQL nicht. Ich schließe es eigentlich fast aus, daß Du mit MySQL eine funktionierende Lösung bekommst. Aber vielleicht findet sich ja jemand, der noch eine Idee hat.

Die Abfrage ließe sich ganz sicher auch erweitern, und mit passenden Indexen (z.B. funktionalen Indexen, die MySQL auch nicht hat) würde das auch schnell gehen.

Davon abgesehen ist Dein Tabellendesign für den Eimer, insbesndere das Feld Aktion mit seinen 2 Bedeutungen.

 

[dave_christopher]

Dachte ich mir fast, dass es mit MySQL keine Möglichkeit gibt.
Vielleicht hat ja aber jemand noch einen Einfall?

Vielen Dank aber für Deine Mühe/Hilfe!
Zumindest scheint es ja dann in PostgreSQL prinzipiell zu funktionieren.

Bzgl. Tabellendesign:
Das ist ja auch nur ein sehr vereinfachtes Beispiel um das Problem zu beschreiben.

 

[dave_christopher]

Angenommen ich hätte nun folgende Tabelle vorliegen:
ID | Aktion | Datum | Betrag | AnzKum | BetragKum
--------------------------------------------------------
1 | 1217 | 15.01.17 | 10 | 1 | 10
2 | 1217 | 17.02.17 | 10 | 2 | 20
3 | 1217 | 14.03.17 | 10 | 3 | 30
4 | 1216 | 14.03.16 | 10 | 1 | 10
5 | 1216 | 14.04.16 | 10 | 2 | 20
6 | 1216 | 15.04.16 | 40 | 3 | 60
7 | 1216 | 16.04.16 | 20 | 4 | 80
8 | 1216 | 17.04.16 | 10 | 5 | 90
9 | 1317 | 15.01.17 | 10 | 1 | 10
10 | 1317 | 17.02.17 | 10 | 2 | 20
11 | 1317 | 14.03.17 | 10 | 3 | 30
12 | 1317 | 14.03.17 | 10 | 4 | 40
13 | 1316 | 14.04.16 | 10 | 1 | 10
14 | 1316 | 15.04.16 | 40 | 2 | 50
15 | 1316 | 16.04.16 | 20 | 3 | 70
16 | 1316 | 17.04.16 | 10 | 4 | 80

Und ich möchte folgende Ergebnistabelle:

Aktion | Datum | Betrag | AnzKum |
-------------------------------------------
1217 | 14.03.17 | 30 | 3
1216 | 15.04.16 | 60 | 3
1317 | 14.03.17 | 40 | 4
1316 | 17.04.16 | 80 | 4

Also es soll jeweils (pro Aktion) vom aktuellen Jahr der aktuellste Datensatz als Referenz genommen werden.
Jeweils dort, wo in den Vorjahren AnzKum den gleichen wert hat, wie im aktuellen Jahr, soll der entsprechende Datensatz mit in die Ergebnistabelle aufgenommen werden.
Hierbei soll zusätzlich zwischen den einzelnen Aktionen unterschieden werden (pro Aktion ein Referenzwert, AnzKum).
Sollte es für eine ältere Aktion nur einen AnzKum-Wert < als der aktuelle AnzKum-Wert existieren, wird der Datensatz mit dem höchsten AnzKum wert der älteren Aktion ausgegeben.

Siehst du dafür irgendeine Möglichkeit?

 

[akretschmer]

Siehst du dafür irgendeine Möglichkeit?

Pauschal ja. Aber das wäre eh wieder eine PostgreSQL-Lösung - für Dich also nicht hilfreich.

 

[dave_christopher]

Also in Mysql keine chance.

 

[dave_christopher]

Oder noch eine andere Frage:
Gibt es evtl. ein Update Statement (oder mehrere hintereinander) bei dem ich die Tabelle folgendermaßen befüllen kann:
Ausgangstabelle:
ID | Aktion | Datum | Betrag | AnzKum | BetragKum |Anz_ref
--------------------------------------------------------
1 | 1217 | 15.01.17 | 10 | 1 | 10 |
2 | 1217 | 17.02.17 | 10 | 2 | 20 |
3 | 1217 | 14.03.17 | 10 | 3 | 30 |
4 | 1216 | 14.03.16 | 10 | 1 | 10 |
5 | 1216 | 14.04.16 | 10 | 2 | 20 |
6 | 1216 | 15.04.16 | 40 | 3 | 60 |
7 | 1216 | 16.04.16 | 20 | 4 | 80 |
8 | 1216 | 17.04.16 | 10 | 5 | 90 |
9 | 1317 | 15.01.17 | 10 | 1 | 10 |
10 | 1317 | 17.02.17 | 10 | 2 | 20 |
11 | 1317 | 14.03.17 | 10 | 3 | 30 |
12 | 1317 | 14.03.17 | 10 | 4 | 40 |
13 | 1316 | 14.04.16 | 10 | 1 | 10 |
14 | 1316 | 15.04.16 | 40 | 2 | 50 |
15 | 1316 | 16.04.16 | 20 | 3 | 70 |
16 | 1316 | 17.04.16 | 10 | 4 | 80 |
17 | 1517 | 15.01.17 | 10 | 1 | 10 |
17 | 1517 | 16.01.17 | 10 | 2 | 20 |
17 | 1516 | 16.01.16 | 10 | 1 | 10 |

Ergebnistabelle:
ID | Aktion | Datum | Betrag | AnzKum | BetragKum |Anz_ref
--------------------------------------------------------
1 | 1217 | 15.01.17 | 10 | 1 | 10 | 3
2 | 1217 | 17.02.17 | 10 | 2 | 20 | 3
3 | 1217 | 14.03.17 | 10 | 3 | 30 | 3
4 | 1216 | 14.03.16 | 10 | 1 | 10 | 3
5 | 1216 | 14.04.16 | 10 | 2 | 20 | 3
6 | 1216 | 15.04.16 | 40 | 3 | 60 | 3
7 | 1216 | 16.04.16 | 20 | 4 | 80 | 3
8 | 1216 | 17.04.16 | 10 | 5 | 90 | 3
9 | 1317 | 15.01.17 | 10 | 1 | 10 | 4
10 | 1317 | 17.02.17 | 10 | 2 | 20 | 4
11 | 1317 | 14.03.17 | 10 | 3 | 30 | 4
12 | 1317 | 14.03.17 | 10 | 4 | 40 | 4
13 | 1316 | 14.04.16 | 10 | 1 | 10 | 4
14 | 1316 | 15.04.16 | 40 | 2 | 50 |4
15 | 1316 | 16.04.16 | 20 | 3 | 70 | 4
16 | 1316 | 17.04.16 | 10 | 4 | 80 | 4
17 | 1517 | 15.01.17 | 10 | 1 | 10 | 1
18 | 1517 | 16.01.17 | 10 | 2 | 20 | 1
19 | 1516 | 16.01.16 | 10 | 1 | 10 | 1

In die Spalte "Anz_Ref" sollten also die Referenzwerte (Maxiumwert von AnzKum für entsprechende Aktion) eingetragen werden, die für die älteren Aktionen herangezogen werden sollen.
Führend ist dabei die aktuelle Aktion. Sollte bei einer aktuellen Aktion ein höherer Wert als bei einer älteren Aktion existieren, dann soll der nächst höhere mögliche wert einer älteren Aktion herangezogen werden (siehe IDs 17,18,19).
Wäre das evtl. in MYSQL möglich? Evtl. auch über mehrere Statements hintereinander?

 

[akretschmer]

Du könntest Dir MariaDB anschauen, das ist ähnlich zu MySQL, kann aber WITH-Abfragen. Vielleicht kommst Du damit ans Ziel.