Блог

У меня есть таблица с 6-значными номерами событий; я пытаюсь подсчитать номер одного события (скажем, 600189), произошедшего за определенный период времени, и разделить его на количество второго номера события (скажем, 600122) за тот же период времени.

приведенный ниже запрос возвращает мои два значения, но я хочу изменить запрос для выполнения вычисления (count of 600189 / count of 600122) * 100 :

 select count (messageno) 
from event
where timestamp > '2019-03-14' and timestamp < '2019-03-15' and
messageno in ('600122','600189')
group by messageno
  

Я бы просто использовал условную агрегацию:

 select sum(case when messageno = '600122' then 1 else 0 end) as cnt_1,
       sum(case when messageno = '600189' then 1 end) as cnt_2,
       ( sum(case when messageno = '600122' then 1 else 0 end) /
         sum(case when messageno = '600189' then 1 end)
       ) as ratio            
from event
where timestamp > '2019-03-14' and
      timestamp < '2019-03-15' and
      messageno in ('600122', '600189');
  

Обратите внимание, что else в знаменателе удалено. Это сделано намеренно. Если нет сообщений с этим номером, то это возвращает NULL ошибку деления на ноль.

Есть пара шаблонов запросов.

Одним из подходов является условное агрегирование. Мы можем вернуть значение 1, если условие истинно, в противном случае вернуть 0, и сложить единицы и 0 с суммой aggregate, чтобы получить количество. Рассмотрим:

Для MySQL мы можем сделать что-то вроде этого:

 SELECT SUM( IF(e.messageno='600122',1,0) )   AS cnt_600122
     , SUM( IF(e.messageno='600189',1,0) )   AS cnt_600189
     , ( 100.0
       * SUM( IF(e.messageno='600122',1,0) ) 
       / SUM( IF(e.messageno='600189',1,0) )
       ) AS pct
   FROM event e
  WHERE e.timestamp > '2019-03-14'
    AND e.timestamp < '2019-03-15'
    AND e.messageno IN ('600122','600189')
  

Более переносимый эквивалент, совместимый со стандартами ANSI, будет работать для Microsoft SQL Server и др.:

 SELECT SUM( CASE e.messageno WHEN '600122' THEN 1 ELSE 0 END )  AS cnt_600122
     , SUM( CASE e.messageno WHEN '600189' THEN 1 ELSE 0 END )  AS cnt_600189
     , ( 100.0
       * SUM( CASE e.messageno WHEN '600122' THEN 1 ELSE 0 END ) 
       / SUM( CASE e.messageno WHEN '600122' THEN 1 ELSE 0 END )
       ) AS pct
   FROM event e
  WHERE e.timestamp > '2019-03-14'
    AND e.timestamp < '2019-03-15'
    AND e.messageno IN ('600122','600189')
  

Другие подходы заключались бы в выполнении агрегации во встроенном представлении (представлениях) или использовании подзапросов в списке ВЫБОРА:

   SELECT 100.0
       * ( SELECT SUM(1) 
             FROM event e
            WHERE e.timestamp  > '2019-03-14'
              AND e.timestamp  < '2019-03-15'
              AND e.messageno IN ('600122')
         )
       / ( SELECT SUM(1) 
             FROM event e
            WHERE e.timestamp  > '2019-03-14'
              AND e.timestamp  < '2019-03-15'
              AND e.messageno IN ('600189')
          ) 
            AS pct