Schwierige SQL-Abfrage

[dobberph]

also:
Voraussetzung:
Tabelle menge (id int, ...)
Tabelle menge_fortschritt (
menge_id int foreign_key menge.id,
datum date
fortschritt int (max 100)

Beispielinhalt menge_fortschritt:
1 , 1.10.2014 , 10
1 , 2.10.2014 , 30
1 , 10.10.2014 , 100
2 , 2.10.2014 , 50
2 , 5.10.2014 , 70
zu Menge 3 gibt es keinen Eintrag (also keinen Baufortschritt)

Die Eingabe wäre ein Array von menge_id ' s. (In diesem Fall [1,2,3])

Erwartete Ausgabe in der Form datum , fortschritt (max 100).
Beispiel für erwartete Ausgabe (zu obigem Beispiel):
1.10.2014 , (10 + 0 + 0)/3
2.10.2014 , (30 + 50 + 0)/3
5.10.2014 , (30 + 70 + 0)/3
10.10.2014 , (100 + 70 + 0)/3
(die zweite Spalte natürlich ausgerechneter Weise, nur damit ihr wisst, was ich meine.

@Distrilec: Also menge_id 's brauche ich in der Auswertung nicht mehr. Dort werden die ja alle zusammen aggregiert.

 

[Distrilec]

@dobberph Wie, du brauchst keine mengen_ids ? Woher weißt du dann was aggregiert wird ?
Also gibst du quasi eine Anzahl mengen_ids vor... Und dafür soll dann diese Auswertung laufen?
Beginnend mit dem ersten fortschritts-datum (irgend einer mengen_id, die du vorgegeben hast) und endend mit dem letzten ?

Ich hoffe ich habe dich richtig verstanden?

 

[dobberph]

Ja, genau.
Die menge_id ' s geben mir Anfangs- und Enddatum (und natürlich die Fortschritte), aber am Ende brauche ich eine aggregierte Kurve über den Fortschritt aller dieser Mengen zusammen. Deshalb brauche ich die ids nicht mehr.

 

[dobberph]

Vielleicht noch etwas genauer: Das Anfangsdatum ist das Minimum aller Datumseinträge zu den mengen_ids, das Enddatum dementsprechend das Maximum

 

[ukulele]

Achso jetzt verstehe ich

-- CTE Tabellen zu Testzwecken
WITH menge(id) AS (
    SELECT    1
    UNION ALL
    SELECT    2
    UNION ALL
    SELECT    3
    ),
    menge_fortschritt(fk_id,datum,fortschritt) AS (
    SELECT    1,'2014-10-01 00:00:00.000',10
    UNION ALL
    SELECT    1,'2014-10-02 00:00:00.000',30
    UNION ALL
    SELECT    1,'2014-10-10 00:00:00.000',100
    UNION ALL
    SELECT    2,'2014-10-02 00:00:00.000',50
    UNION ALL
    SELECT    2,'2014-10-05 00:00:00.000',70
    )
-- Ende CTE Tabellen

SELECT    t.datum,
        sum(t.fortschritt) / count(t.id) AS fortschritt
FROM    (
-- Hilfstabelle t
SELECT    raster.datum,
        m.id,
        (    SELECT    max(fortschritt)
            FROM    menge_fortschritt
            WHERE    fk_id = m.id
            AND        datum <= raster.datum ) AS fortschritt
FROM    (
-- Rastertabelle mit allen Datumswerten
SELECT    DISTINCT
        datum
FROM    menge_fortschritt
WHERE    datum IS NOT NULL
AND        fk_id IN ( 1,2,3 )
-- Ende Rastertabelle
) raster, (
-- Alle Mengen IDs zum Projekt holen
SELECT    id
FROM    menge
WHERE    id IN ( 1,2,3 )
-- Ende Mengen IDs
) m
-- Ende Hilfstabelle
) t
GROUP BY t.datum

Der Anfang mit den CTE Tabellen ist nur zum testen auf anderen DBs (MSSQL bei mir), den kannst du unter MySQL nicht ausführen. Statt dessen muss es die Tabellen menge und menge_fortschritt wirklich geben. Die Testdaten entsprechen dem, was du bereit gestellt hast.

Alles danach sollte sich so ausführen lassen, ich habe das jetzt sehr abstrakt formuliert. Ich denke mal du brauchst den Bezug auf die menge-Tabelle um nicht alle Mengen auszuwählen sondern alle Mengen die z.B. einem Projekt zugeordnet sind. Die Bedingung

AND        fk_id IN ( 1,2,3 )

müsste also an beiden Stellen getautscht werden, damit das richtig Sinn macht.

Die Berechnung des Gesamtfortschritts aller betroffenen IDs zusammen rundet jetzt noch auf ganze Zahlen, das läßt sich aber durch Konvertierung von INT in z.B. FLOAT verbessern.

 

[dobberph]

Wow. Das nenn ich mal hilfsbereit!
Funktioniert perfekt (und ich verstehs sogar ^^).
Vielen, vielen Dank.
Und schöne Weihnachtszeit für euch.

 

[ukulele]

Wenn sehr viele Datensätze in menge_fortschritt sind sollte ein Index auf fk_id,datum eventuell Sinn machen.

 

[dobberph]

Hab ich schon. Danke trotzdem ;D

 

[Distrilec]

Wenn ich mir das so ansehe... Vermisse ich doch sehr das Windowing in MySQL ^^