Как я могу получить SUM для правильного вычисления соединения?

Итак, я пытаюсь подсчитать количество деталей, количество заданий, количество в каждой задаче и время, затрачиваемое на производство каждой работы, но я получаю некоторые фанковые результаты. Если я запустил это:

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

и теперь я получаю точные номера деталей и задач, но неверная сумма времени не верна. В чем проблема?

Когда я пытаюсь сделать это с помощью distinct, я получаю небольшое количество veeeeery (например, что-то между 1 и 30, когда я ищу что-то ближе к 10 000.)

UPDATE: вот код создания:

http://pastebin.com/nbhU9rYh

http://pastebin.com/tdmAkNr4

http://pastebin.com/0TFCUaeQ

http://pastebin.com/fugr8C9U

http://pastebin.com/Zq0bKG2L

http://pastebin.com/k5rESUrq

Взаимоотношения таковы:

  • Информация о mf_job связана с заданием
  • задания имеют части
  • части имеют задачи
  • задачи в пакетах
  • batch_log - это таблица со всеми запусками и остановками для партий задач, она имеет start_time и stop_time и time_elapsed.

Я пытаюсь получить все time_elapsed из batch_log для каждого mf_job со словом backblaze в одном из полей, а также количеством частей, задач и сборок. Все это нужно сгруппировать по job.id или mf_job.id

Ответы

Ответ 1

Попробуйте переписать запрос:

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

Ответ 2

Вам нужно изменить запрос:

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

Кроме того, строка LIKE '%...' сделает запрос uber медленным, потому что никакие индексы на нем не могут быть использованы.

Если вы можете использовать MyISAM, вы можете использовать полнотекстовый индекс для этих столбцов и использовать код, например:

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

См:
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

Ответ 3

Похоже, проблема заключается в том, что несколько задач могут находиться в одной партии, и/или несколько частей могут находиться в одной и той же задаче. Скажем, например, что ваша работа состоит из трех частей, и каждая часть имеет задачу, и все 3 задания находятся в одной партии. Вы добавите время для этой партии три раза. Но разные не будут работать, так как если у вас есть 5 разных партий, которые заняли 300 секунд, они не будут считаться отличными.

В подобной ситуации подзапрос обычно является способом выхода. Вместо того, чтобы напрямую соединяться с batch_log, вы должны присоединиться к подзапросу, который выбрал различные j.id (или p.job_id), l.batch_id и l.time_elapsed (первый для объединения, второй - только для правильного вычисления и третье - фактическое значение для использования). Тогда вы можете суммировать l.time_elapsed оттуда. Таким образом, каждая партия подсчитывается ровно один раз.

Ответ 4

партии (l) таблица не имеет поля, называемого time_elapsed задачи делают это так:

SUM(t.time_elapsed) as time_elapsed

-or-

SUM(l.actual_time) as time_elapsed