[SQL] Großes Problem bei Durchnittsberechnung

[Master_T2]

Hi, es geht um folgenden Query:

SELECT
FORMAT(SUM(coalesce(a.betrag_auszaehlung, 0)   
    + coalesce(b.betrag_auszaehlung, 0)
    + coalesce(c.betrag_auszaehlung, 0)
    + coalesce(d.betrag_auszaehlung, 0)) 
    / (COUNT(a.betrag_auszaehlung) 
    + COUNT(b.betrag_auszaehlung)
    + COUNT(c.betrag_auszaehlung)
    + COUNT(d.betrag_auszaehlung)
      ) 
, 2) AS `SUMME`,  
e.anzeigename as `e`, f.anzeigename as `f`
FROM g
LEFT JOIN h ON g.fk_auftragsart = h.oid 
LEFT JOIN i ON g.oid = i.fk_vorgang
LEFT JOIN j ON g.oid = j.fk_vorgang 
LEFT JOIN k ON j.oid = k.fk_bearbeitung
LEFT JOIN l ON j.oid = l.fk_bearbeitung
LEFT JOIN m ON j.oid = m.fk_bearbeitung
LEFT JOIN a ON m.oid = a.fk_bearbeitungsschritt_meldung
LEFT JOIN b ON m.oid = b.fk_bearbeitungsschritt_meldung
LEFT JOIN d ON m.oid = d.fk_bearbeitungsschritt_meldung
LEFT JOIN c ON m.oid = c.fk_bearbeitungsschritt_meldung
LEFT JOIN e ON g.fk_e = e.oid
LEFT JOIN f ON e.fk_f = f.oid
WHERE  g.datum_ausfuehrung <= '2008-03-10 00:00:00' 
AND  i.entsorgung = '1' 
AND  h.kuerzel = 'TEST'
AND  (l.fk_ausfuehrung_art != '3' OR k.oid IS NULL)
AND  f.geloescht = '0'
AND  e.geloescht = '0'
GROUP BY f.anzeigename, e.anzeigename
HAVING SUMME IS NOT NULL

(Die wirklichen Tabellennamen musste ich leider maskieren)

Die Berechnung des Durchschnitts sieht etwas komplizierter aus, da ohne NULL-Werte gerechnet werden soll was mit AVG() nicht funktioniert.

Der Query funktioniert so wie er ist einwandfrei.

Jetzt zum Problem:
Es sollen NUR die LETZTEN 5 Beträge für die Berechnung des Durchschnittswertes herangezogen werden. Bei einer Tabelle würde ich das ja mit nem Subselect hinbekommen à la:

SELECT avg(b.{col}) FROM (SELECT {col} FROM {table} ORDER BY {col} DESC LIMIT 10) as b

Nur ich habe keinen Plan wie ich das bei diesem QWuery umsetzen soll. Hat jemand eine Idee?

[Adromir]

Sortierst die Zeilen nach einer Spalte der maßgeblichen Tabelle und setzt ein Limit..

[Master_T2]

Es sind aber 4 Tabellen die bei der Berechnung des Durchschnitts maßgeblich sind!?

[Adromir]

Naja, aber aus einer Tabelle wird sich doch genau ergeben, welcher Eintrag logisch der erste und welcher der letzte ist. Diese Brauchst du doch nur Absteigend sortieren und mit Limit 0,5 hast du dann immer die letzten 5 Einträge..

Oder hab ich dich da falsch verstanden?

[steffenk]

das wird schwer, das in einer Query hinzubekommen, zumal Du schon ein Monster hast.

Du musst Dir also eine Query mit 5 Subqueries bauen, das den entsprechenden Durchschnitt liefert und Die Hauptquery daraus wieder den Durchschnitt nimmt.

[Master_T2]

Du musst Dir also eine Query mit 5 Subqueries bauen, das den entsprechenden Durchschnitt liefert und Die Hauptquery daraus wieder den Durchschnitt nimmt.

Ja sowas habe ich befürchtet... Danke euch trotzdem. Werde mal nach alternativen schauen, auf 5 Subqueries habe ich definitiv keine Lust, zumal das bei weitem nicht der eizige solcher Monster hier ist...

[steffenk]

lass Dir sowas doch in einer Funktion generieren, dann ist egal wie gross das Monster wird

[Master_T2]

@steffen: könntest du mir denn mal ein Beispiel (ganz einfach im Pseudocode) geben wie du es mit 5 Subqueries machen würdest?

[steffenk]

nurmal als Anregung ein Ansatz:

PHP-Code:

$subqueries = array(
  'table1' => 'field1|field2>0',
  'table2' => 'field2',
  'table3' => 'field3',
);
$subquerySQL = buildSubqueries($subqueries, 5, 'AVG');
echo $subquerySQL;

function buildSubqueries($fieldArray, $limit, $operation) {
    
    
    
    // build query
    $sql = array();
    foreach ($fieldArray as $table => $fields) {
        $field = explode('|',$fields);
        $sql[] = "(SELECT $operation($field[0]) FROM `$table`" . ($field[1] ? " WHERE $field[1]" : '') . " LIMIT $limit)";
    }
    
    switch ($operation) {
        case 'SUM':
            $query = implode(' + ', $sql); 
            break;
        case 'AVG':
            $query = '(' . implode(' + ', $sql) . ')/' . count($sql); 
            break;
    }
    
    
    return $query;
}