Schwierige SQL-Abfrage

[dobberph]

Hallo ihr,

ich dachte eigentlich ich wäre recht erfahren in der Datenbankprogrammierung, habe hier aber ein Problem, an dem ich scheitere. Folgende Tabellen habe ich:

menge (id, weitere Attribute)
menge_fortschritt (menge_id, fortschritt, datum)

Es wird hier in der Tabelle menge_fortschritt der Fortschritt von Mengen eingetragen. Das bedeutet z.B.
- am 1.1.2010 wird menge_1 auf 10% fortschritt gesetzt
- am 10.1.2010 wird menge_1 auf 90% fortschritt gesetzt
- am 2.1.2010 wird menge_2 auf 30% fortschritt gesetzt
- am 10.1.2010 wird menge_2 auf 60% fortschritt gesetzt
- menge_3 ist bei fortschritt 0, hat also keinen Eintrag in der Tabelle menge_fortschritt

Jetzt möchte ich Verläufe abrufen, die sich auf mehrere Mengen beziehen.

Aus obiger Tabelle würde sich für den Verlauf von menge_1, menge_2 und menge_3 das Ergebnis
datum, tagesgesamtfortschritt (das ist der Average der 3 Mengen an dem Tag)
1.1.2010 3,33 (menge_1: 10, menge_2:0, menge_3:0)
2.1.2010 13,33 (menge_1: 10, menge_2:30, menge_3:0)
10.1.2010 50,00 (menge_1: 90, menge_2:60, menge_3:0)

Das Problem ist, dass nicht für jede Menge an jedem Tag ein Eintrag existiert. Für manche Mengen (hier z.B. menge_3 existiert gar kein Eintrag)
Mein erster Ansatz war:
SELECT datum, fortschritt/(SELECT count(*) ...) as tagesgesamtfortschritt
FROM menge_fortschritt
WHERE id IN (...)
GROUP BY datum

Dadurch habe ich aber genau das oben beschriebene Problem, dass zu dem aktuellen Datum ja nicht alle Tage "vorhanden" sind.

Vielleicht hat ja jemand von euch eine Idee.

Vielen Dank im Voraus,
dobberph

 

[akretschmer]

Dadurch habe ich aber genau das oben beschriebene Problem, dass zu dem aktuellen Datum ja nicht alle Tage "vorhanden" sind.

Dann mußt Du Dir eine Hilfstabelle basteln, die alle Datumswerte enthält. In PostgreSQL ginge das ganz einfach mit generate_series(), MySQL kann das nicht.

 

[Distrilec]

Oracle lösung... Sollte aber auch mit MySQL funktionieren:

create table t (menge_id Varchar2(10),
                                fortschritt Number,
                                datum  Date);
insert into t values('menge_1', 10, to_date('01.01.2010', 'DD.MM.YYYY'));
insert into t values('menge_1', 90, to_date('10.01.2010', 'DD.MM.YYYY'));
insert into t values('menge_2', 30, to_date('02.01.2010', 'DD.MM.YYYY'));
insert into t values('menge_2', 60, to_date('10.01.2010', 'DD.MM.YYYY'));
commit;

With daten As
(Select 'menge_1' menge From dual
  Union All
  Select 'menge_2' From dual
  Union All
  Select 'menge_3' From dual)

Select distinct t1.datum,
       t2.menge,
       nvl((Select t.fortschritt From t Where t.datum = t1.datum And t.menge_id = t2.menge), 0) fortschritt
From  t t1
Left Join daten t2
On   t1.datum = t1.datum

kartesische Produkte sind schon was schönes

 

[Hony%]

@Distrilec

Das könnte funktionieren wenn MySQL CTE beherrschen würde. Tut es nur leider nicht. Allerdings sollte sich die CTE in diesem Fall auch als Subselect formulieren lassen.

 

[ukulele]

Ich denke mal das es keinen Sinn macht alle Datumswerte in ein Raster zu bringen da die Fortschritte sicherlich zeitlich nicht alle in der selben Woche oder im selben Monat liegen, sonst ginge es auch mit Pivot. MySQL ist hier tatsächlich schlecht aufgestellt ohne CTE oder Rekursion.

Wieviele mögliche Schritte gibt es denn bis zur Fertigstellung, mehr als 10? Sonst kannst du auch mit mehreren Joins arbeiten.

 

[Distrilec]

@Hony% CTE nur weil ich keine Lust hatte die zweite Tabelle mit allen mengen_ids zu erstellen
Ich hoffe mal so eine Tabelle wird es geben, oder ?

Select distinct t1.datum,
       t2.menge,
       nvl((Select t.fortschritt From t Where t.datum = t1.datum And t.menge_id = t2.menge), 0) fortschritt
From  t t1
Left Join TABELLE MIT ALLEN MENGEN_IDS t2
On   t1.datum = t1.datum

 

[dobberph]

Hallo ihr,
erstmal danke für die schnellen, vielen Antworten.
Zur Sachlage noch: Es geht um Auswertungen, die auch Jahre umfassen können und die Mengen können und werden 10.000 de sein.
Es geht um den Bau von Hochhäusern etc. Die Mengen sind sehr kleinteilige Elemente wie Wandanstrich für eine bestimmte Wand, SteckdoseXY usw. Dementsprechend könnt ihr euch die Dimensionen vorstellen ;D

@Distrilec: Es gibt die Tabelle menge_fortschritt und die Tabelle menge (dort sind alle mengen-ids drin)

Ihr meint also es führt kein Weg an temporären Tabellen vorbei?
- kann man vielleicht eine View bauen, die alle Datumswerte enthält? (damit man nicht jedes Mal eine temporäre Tabelle erstellen muss)
- kann man, wenn man keine view erstellen kann, die temporäre Tabelle mit einer Stored Procedure vielleicht on the fly erstellen?

Danke nochmals,
dobberph

 

[akretschmer]

Ihr meint also es führt kein Weg an temporären Tabellen vorbei?

Wäre ich Du würde ich eine richtige Datenbank nehmen und nicht MySQL.

 

[dobberph]

Wie du vielleicht festgestellt hast ist das kein Heimprojekt, sondern ein Firmenprojekt. Solche Entscheidungen wurden bereits bei Beginn des Projekts festgelegt und sind nicht mehr so leicht änderbar. Ich brauche also wirklich eine Lösung für MySQL ;D

 

[akretschmer]

Wie du vielleicht festgestellt hast ist das kein Heimprojekt, sondern ein Firmenprojekt.

Na dann erst recht! ;-)

Solche Entscheidungen wurden bereits bei Beginn des Projekts festgelegt und sind nicht mehr so leicht änderbar.

Wir haben uns verfahren, fahren aber dennoch geradeaus weiter, weil die Erde ist ja rund.

Ich brauche also wirklich eine Lösung für MySQL ;D

Na egal, ich wünsch Dir Glück.

 

[dobberph]

generate_series() gibt es zwar nicht in MySQL, aber so was hilft sicherlich:
select date_format(adddate('2011-1-1',@num:=@num+1),'%Y-%m-%d') date
from any_table,(select@num:=-1) num
limit
365

Weiß aber nicht, wie es sich damit lösen lässt.
Stehe wirklich auf dem Schlauch...

 

[akretschmer]

Weiß aber nicht, wie es sich damit lösen lässt.
Stehe wirklich auf dem Schlauch...

http://www.postgresql.org/message-i...LO9PiyFM0bXdSvU3emAH2pM4BE+evA@mail.gmail.com

ungefähr Dein Problem, oder?

 

[ukulele]

Selbst wenn sich alle Datumswerte erzeugen lassen hilft dir das nicht weiter.

Du kannst jetzt sagen ich mache für jedes Datum eine Spalte aber das wird bei entsprechend vielen Werten nicht funktionieren. Es bleibt also nur entweder eine feste Anzahl Spalten mit variablen Datumsangaben, also z.B.:
Spalte menge_id | 10% | 20% | ...
menge1 | A | B | ...
menge1 | X | Y | ...
Oder ein Textfeld das alle Stände verkettet:
Spalte menge_id | verlauf
menge1 | 10% an Datum A, 20% an Datum B
menge2 | 10% an Datum X, 20% an Datum Y

Letzteres läßt sich mit MySQL mangels CTE / Rekursion nicht live berechnen (auch nicht per View) sondern nur über Schleifen als temporäre Tabelle zusammen "kleben". Nicht sehr elegant.

 

[dobberph]

Vielleicht kann ich eine TEMP-View einrichten, die zu jeder menge_id und jedem Datum einen Eintrag erstellt. Im Fortschritt könnte ich eine Funktion einsetzen, die mir für die Menge den aktuellen Stand berechnet:
menge_id , datum , fortschritt
123 , 2014-10-10 , get_fortschritt(123,2014-10-10)

get_fortschritt wäre dann letztlich sowas wie:
SELECT fortschritt FROM menge_fortschritt WHERE menge_id=123 AND datum<="2014-10-10" ORDER BY datum DESC LIMIT 1

Dann bliebe nur die Frage, wie ich die Temp.View "on the fly" erstellen kann. Vermutlich ist der Ausgangspunkt eine Aufgerufene Stored Procedure, die als Eingabe alle menge_ids bekommt. Und genau bei der bräuchte ich noch etwas Hilfe bitte.

 

[Distrilec]

Vllt. nocheinmal die eigentlichen Kriterien aufzählen ?
Irgendwie hab ich den Überblick von dem, was du eig. willst verloren...

Eine wichtige Frage... Wann willst du eine mengen_id anzeigen lassen ?
Soll eine mengen_id die erst seit 09.12.2014 existiert auch schon in der Auswertung am 01.01.2007 auftauchen ?