Wie berechne ich richtig die SUM-Methode mit einem JOIN?

Question

Also versuche ich, die Anzahl der Teile, die Anzahl der Aufgaben, die Menge in jedem Auftrag und die Zeit, die für die Herstellung jedes Auftrags benötigt wurde, zu zählen, aber ich bekomme einige seltsame Ergebnisse. Wenn ich das ausführe:

SELECT
  j.id, 
    mf.special_instructions,
  count(distinct p.id) as number_of_different_parts,
  count(distinct t.id) as number_of_tasks,
  SUM(distinct j.quantity) as number_of_assemblies,
  SUM(l.time_elapsed) as time_elapsed

FROM
  sugarcrm2.mf_job mf
INNER JOIN ramses.jobs j on
  mf.id = j.mf_job_id
INNER JOIN ramses.parts p on
  j.id = p.job_id
INNER JOIN ramses.tasks t on
  p.id = t.part_id
INNER JOIN ramses.batch_log l on
  t.batch_id = l.batch_id

WHERE 
  mf.job_description                LIKE "%BACKBLAZE%" OR
  mf.customer_name                  LIKE "%BACKBLAZE%" OR
  mf.customer_ref                   LIKE "%BACKBLAZE%" OR
  mf.technical_company_name LIKE "%BACKBLAZE%" OR
  mf.description                        LIKE "%BACKBLAZE%" OR
  mf.name                                   LIKE "%BACKBLAZE%" OR
  mf.enclosure_style                LIKE "%BACKBLAZE%" OR 
    mf.special_instructions     LIKE "%BACKBLAZE%"
Group by j.id

und jetzt erhalte ich genaue Teile- und Aufgabennummern, aber die Summe der verstrichenen Zeit stimmt nicht. Was könnte das Problem sein?

Wenn ich es mit distinct versuche, erhalte ich eine seeehr niedrige Zahl (etwas zwischen 1 und 30, wenn ich etwas näher an 10.000 suche.)

UPDATE: Hier ist der Erstellungscode:

http://pastebin.com/nbhU9rYh

http://pastebin.com/tdmAkNr4

http://pastebin.com/0TFCUaeQ

http://pastebin.com/fugr8C9U

http://pastebin.com/Zq0bKG2L

http://pastebin.com/k5rESUrq

Die Beziehungen sind wie folgt:

• mf_job-Informationen sind mit einem Auftrag verknüpft
• Aufträge haben Teile
• Teile haben Aufgaben
• Aufgaben sind in Chargen
• batch_log ist eine Tabelle mit allen Start- und Stopps für die Chargen von Aufgaben, sie hat eine Startzeit und eine Endzeit und eine verstrichene Zeit.

Ich versuche, alle verstrichene Zeit aus dem batch_log für jedes mf_job mit dem Wort backblaze in einem seiner Felder sowie die Anzahl der Teile, Aufgaben und Baugruppen zu erhalten. Dies muss alles nach job.id oder mf_job.id gruppiert werden.

Answer 1

Versuchen Sie, die Abfrage wie folgt umzuschreiben:

SELECT
  j.id, 
  mf.special_instructions,
  count(p.id) as number_of_different_parts,
  count(t.id) as number_of_tasks,
  SUM(j.quantity) as number_of_assemblies,
  SEC_TO_TIME(SUM(l.seconds_elapsed)) as time_elapsed

FROM
  sugarcrm2.mf_job mf
INNER JOIN ramses.jobs j on
  mf.id = j.mf_job_id
INNER JOIN ramses.parts p on
  j.id = p.job_id
INNER JOIN ramses.tasks t on
  p.id = t.part_id
INNER JOIN (
            SELECT rl.batch_id
                  , SUM(TIME_TO_SEC(rl.time_elapsed)) as seconds_elapsed
            FROM ramses.batch_log rl 
            GROUP BY rl.batch_id
            ) l ON (t.batch_id = l.batch_id)

WHERE 
  mf.job_description                LIKE "%BACKBLAZE%" OR
  mf.customer_name                  LIKE "%BACKBLAZE%" OR
  mf.customer_ref                   LIKE "%BACKBLAZE%" OR
  mf.technical_company_name         LIKE "%BACKBLAZE%" OR
  mf.description                    LIKE "%BACKBLAZE%" OR
  mf.name                           LIKE "%BACKBLAZE%" OR
  mf.enclosure_style                LIKE "%BACKBLAZE%" OR 
  mf.special_instructions           LIKE "%BACKBLAZE%"
GROUP BY j.id WITH ROLLUP

Answer 2

Sie müssen die Abfrage wie folgt ändern:

SELECT
  ...
  SEC_TO_TIME(SUM(TIME_TO_SEC(l.time_elapsed))) as time_elapsed

Außerdem wird die Zeile LIKE '%...' die Abfrage extrem langsam machen, da keine Indizes darauf verwendet werden können.

Wenn Sie MyISAM verwenden können, können Sie einen Volltextindex auf diesen Spalten verwenden und Code wie folgt verwenden:

WHERE MATCH(mf.job_description,mf.customer_name,mf.customer_name,...) 
      AGAINST ('BACKBLAZE' IM NATURAL LANGUAGE MODE)

Siehe:
http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html
http://www.petefreitag.com/item/477.cfm
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_time-to-sec

Answer 3

Es scheint, dass das Problem darin besteht, dass mehrere Aufgaben in derselben Batch sein können und/oder mehrere Teile in derselben Aufgabe sein können. Nehmen wir zum Beispiel an, dass Ihr Job 3 Teile hat und jeder Teil eine Aufgabe hat, und alle 3 Aufgaben sind in derselben Batch. Sie werden die Zeit für diese Batch dreimal hinzufügen. Aber 'distinct' funktioniert auch nicht, da wenn Sie 5 verschiedene Batches haben, die alle 300 Sekunden gedauert haben, sie nicht als unterschiedlich betrachtet werden.

In einer Situation wie dieser ist eine Unterabfrage normalerweise der richtige Weg. Anstatt direkt mit batch_log zu verknüpfen, würden Sie sich mit einer Unterabfrage verknüpfen, die j.id (oder p.job_id), l.batch_id und l.time_elapsed auswählt (das erste ist für die Verknüpfung, das zweite nur für die richtige Berechnung von 'distinct' und das dritte der tatsächliche Wert, der verwendet werden soll). Dann können Sie l.time_elapsed von dort summieren. Auf diese Weise wird jede Batch genau einmal gezählt.

Answer 4

Die Batches(l)-Tabelle hat kein Feld namens time_elapsed Tasks hat es also entweder

SUM(t.time_elapsed) as time_elapsed

-oder-

SUM(l.actual_time) as time_elapsed