Корректная группировка

185

22 июля 2017, 06:18

Есть запрос, который выводит часы и среднее время обработки заказа за каждый час.

SELECT
    createdhour,
    SEC_TO_TIME(
        sum(
            TIMESTAMPDIFF(
                SECOND,
                createdtime_spec,
                first_status_change
            )
        ) / count(*)
    ) AS 'РФ'
WHERE
    createdtime BETWEEN '2017-07-01'
AND '2017-07-20'
AND department IN ('РФ')
GROUP BY
    createdhour

Вопрос, как в следующей колонке вывести среднее время обработки заказа за каждый час по другому department? Сделал так

SELECT department,
    createdhour,
    case when department = 'РФ' then
SEC_TO_TIME(
        sum(
            TIMESTAMPDIFF(
                SECOND,
                createdtime_spec,
                first_status_change
            )
        ) / count(*)
    )end AS 'РФ',
    case when department = 'ЕС' then
SEC_TO_TIME(
        sum(
            TIMESTAMPDIFF(
                SECOND,
                createdtime_spec,
                first_status_change
            )
        ) / count(*)
    )end AS 'ЕС'
from report_designer
WHERE
    createdtime BETWEEN '2017-07-01'
AND '2017-07-20'
AND department IN ('РФ','ЕС')
GROUP BY
    createdhour,department

Выдает такой:

Данные корректные, но как избавиться от null-значений?

Answer 1

SELECT createdhour,
       max(if(department='РФ',avg_time,NULL)) as `РФ`,
       max(if(department='ЕС',avg_time,NULL)) as `ЕС`
  FROM (
    SELECT createdhour, department,
           SEC_TO_TIME(
              avg(
                  TIMESTAMPDIFF(
                      SECOND,
                      createdtime_spec,
                      first_status_change
                  )
              )
           ) AS avg_time
     from report_designer
    WHERE createdtime BETWEEN '2017-07-01' AND '2017-07-20'
      AND department IN ('РФ','ЕС')
    GROUP BY createdhour, department
   ) X
 GROUP BY createdhour