mysql выбирает столбцы со сложными критериями подсчета + диапазона часов

Описание проблемы. У меня есть следующий запрос для получения последних предупреждений за последние 15 минут.

SELECT
   AlmCode,OccurTime,ClearTime....columnN 
FROM 
   TB_ALM 
WHERE 
   AlmCode IN ('3236',....'5978') AND 
   OccurTime >= date_sub(NOW(),interval 15 minute);

Структура таблицы:

CREATE TABLE `TB_ALM` (
  `Col1` smallint(2) DEFAULT NULL,
  `Col2` int(4) DEFAULT NULL,
  `Col3` int(2) DEFAULT NULL,
  `Col4` int(10) DEFAULT NULL,
  `Col5` int(10) unsigned DEFAULT NULL,
  `Col6` int(2) DEFAULT NULL,
  `Col7` int(2) DEFAULT NULL,
  `Col8` int(10) DEFAULT NULL,
  `Col9` int(10) unsigned DEFAULT NULL,
  `AlmCode` int(10) unsigned DEFAULT NULL,
  `Col10` int(2) NOT NULL,
  `Col11` int(10) unsigned DEFAULT NULL,
  `Col12` char(12) DEFAULT NULL,
  `Col13` int(2) unsigned DEFAULT NULL,
  `Col14` int(10) unsigned DEFAULT NULL,
  `Col15` int(10) unsigned DEFAULT NULL,
  `Col16` int(10) unsigned DEFAULT NULL,
  `OccurTime` datetime NOT NULL,
  `ClearTime` datetime DEFAULT NULL,
  `AlmDesc` varchar(500) DEFAULT NULL,
  `Col20` int(1) DEFAULT '0',
  `Col21` bigint(20) DEFAULT NULL,
  `Col22` char(120) DEFAULT NULL,
  `Col23` int(10) DEFAULT NULL,
  KEY `TB_ALM_IDX2` (`Col1`,`Col2`,`Col3`,`Col6`,`Col7`,`Col11`,`AlmCode`,`Col9`,`Col4`,`Col8`,`ClearTime`) USING BTREE,
  KEY `TB_ALM_IDX1` (`Col1`,`Col2`,`Col3`,`Col6`,`Col7`,`Col11`,`AlmCode`,`Col5`,`Col21`),
  KEY `TB_ALM_IDX3` (`Col1`,`Col2`,`Col3`,`Col5`) USING BTREE,
  KEY `TB_ALM_IDX4` (`Col1`,`Col2`,`Col3`,`OccurTime`,`ClearTime`,`Col21`) USING BTREE,
  KEY `TB_ALM_IDX5` (`Col23`),
  KEY `TB_ALM_IDX6` (`Col1`,`Col2`,`Col3`,`Col6`,`Col7`,`AlmCode`,`Col11`,`ClearTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Что нужно: Теперь я хочу изменить это, чтобы получать сигналы тревоги со следующими критериями:

а. Тревоги (AlmCodes) возникли за последние 15 минут (исходное требование) И

б. ТОЛЬКО если каждый из аварийных сигналов (кодов аварийных сигналов) не возникал более трех раз в любом из 15-минутных окон в течение последних шести часов.

Что пробовали. Я попробовал следующий подход:

Получите DISTINCT(AlmCodes) за последние 15 минут.

выберите отдельный (AlmCode) из TB_ALM, где AlmCode IN ('3236','4002','4008','4036','4050','4051','4102','4108','4136','4150' ,'4151','4202','4208','4236','4250','4251','4801','4802','4836','4848','4850','4851',' 4902", "4936", "4950", "4951", "5002", "5008", "5036", "5050", "5051", "5102", "5108", "5136", "5150" ,'5151','5202','5208','5236','5250','5251','5947','5950','5952','5975','5976','5977',' 5978') AND OccurTime >= date_sub(NOW(),интервал 15 минут);
Используйте Item-1 (выше) в качестве подзапроса и получите количество вхождений для каждого AlmCode.

выберите Almcode,concat(date(OccurTime),' ',HOUR(OccurTime)) как HR,count(*) из TB_ALM_HISTORY, где AlmCode IN (выберите отдельный(s.AlmCode) из TB_ALM_HISTORY s, где s.AlmCode IN ('3236' ,'4002','4008','4036','4050','4051','4102','4108','4136','4150','4151','4202','4208',' 4236", "4250", "4251", "4801", "4802", "4836", "4848", "4850", "4851", "4902", "4936", "4950", "4951" ,'5002','5008','5036','5050','5051','5102','5108','5136','5150','5151','5202','5208',' 5236','5250','5251','5947','5950','5952','5975','5976','5977','5978') И s.OccurTime >= date_sub(NOW() ,интервал 15 минут) ) AND OccurTime >= date_sub(NOW(),интервал 15*4*24 минуты) группа по AlmCode,HR;

Проблемы:

Запрос Items-2 продолжает выполняться всегда с (подзапросом), где, как если бы я запускал их как два отдельных запроса, он мгновенно возвращается, как показано ниже. Чего здесь не хватает?

Запрос-1: Получить уникальные сигналы тревоги

select distinct(AlmCode)
from TB_ALM_HISTORY 
where AlmCode IN ('3236','4002','4008','4036','4050','4051','4102','4108','4136','4150','4151','4202','4208','4236','4250','4251','4801','4802','4836','4848','4850','4851','4902','4936','4950','4951','5002','5008','5036','5050','5051','5102','5108','5136','5150','5151','5202','5208','5236','5250','5251','5947','5950','5952','5975','5976','5977','5978') 
AND OccurTime >= date_sub(NOW(),interval 15 minute) ;

    +---------+
    | AlmCode |
    +---------+
    |    3236 |
    |    5202 |
    |    5236 |
    +---------+

Запрос-2: получить количество для каждого уникального будильника за последние 6 часов.

select Almcode,concat(date(OccurTime),' ',LPAD(HOUR(OccurTime),2,'0')) as HR,count(*) from TB_ALM_HISTORY where AlmCode IN ('3236','5202','5236') AND OccurTime >= date_sub(NOW(),interval 15*4*7 minute) group by AlmCode,HR;
+---------+---------------+----------+
| Almcode | HR            | count(*) |
+---------+---------------+----------+
|    3236 | 2015-08-04 11 |        2 |
|    5202 | 2015-08-04 13 |        6 |
|    5202 | 2015-08-04 14 |        4 |
|    5202 | 2015-08-04 15 |        2 |
|    5202 | 2015-08-04 16 |        1 |
|    5202 | 2015-08-04 17 |        2 |
+---------+---------------+----------+

Если предположить, что этот запрос был запущен в 18:00 по восточному стандартному времени, код AlmCode 5202 возник за последние 6 часов (между 12–18 часами), и, следовательно, результаты для этого кода AlmCode не должны включаться в окончательный запрос на выборку (или за последние 15 минут). в то время как код AlmCode 3236 не возникал в течение последних 6 часов, и, следовательно, должны быть включены все аварийные сигналы, возникшие за последние 15 минут для данного конкретного кода AlmCode.

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

а. Получите уникальный AlmCode с OccurTime >= Last 15 Minutes

б. Для каждого из этих AlmCode проверьте, произошло ли это трижды за последние 6 часов.

в. Если НЕТ, извлеките все сигналы тревоги для этого AlmCode с OccurTime >= Last 15 Minutes (если ДА, не включайте и просто пропустите)

Siva 04.08.2015 источник

comment

Не могли бы вы предоставить оператор создания таблицы (включая индексы) и объяснить отдельно для 1-го, 2-го и сложного запроса? - Vladimir_M 04.08.2015

comment

@Vladimir_M добавил структуру таблицы, вывод для двухэтапных запросов с более подробными ожидаемыми результатами. - Siva 05.08.2015

comment

Таким образом, если тревога возникла в течение любых заданных 15 минут в течение последних 6 часов три или более раз, должна она или не должна быть включена в результат? (проверьте описание в разделе проблем и что необходимо) - Vladimir_M 05.08.2015

comment

@Vladimir_M Эти будильники не должны включаться, если они срабатывали три или более раз в течение любых 15 минут из последних 6 часов. Извините, что опечатка исправлена сейчас. - Siva 05.08.2015

comment

Я отредактировал свой ответ, теперь он должен соответствовать - Vladimir_M 05.08.2015

comment

@Vladimir_M просто любопытно, где это проверяет три или более вхождения в любую из 15 минут за последний час? - Siva 05.08.2015

comment

в условиях внутреннего соединения. t1 соединяется с t2 со следующими условиями: AlmCode должен быть таким же (t2.AlmCode = t1.AlmCode), AlmCode_2 должен произойти не позднее, чем через 15 минут после AlmCode_1 (t2.OccurTime ‹= date_add(t1.OccurTime, интервал 15 минут)) и AlmCode_2 должен произойти позже, чем AlmCode_1 (t2.OccurTime › dt1.OccurTime), поэтому это не может быть одно и то же событие, потому что OcceTime отличается. С той же логикой соединился t3, который возвращает три вхождения в течение 15 минут. - Vladimir_M 05.08.2015

comment

хм, отлично... я всегда думал о том, чтобы подсчитать количество вхождений, а затем сравнить с ним! :( - Siva 05.08.2015

comment

Подсчет может показать неправильный результат, например, AlmCode появляется 6 раз за последние 6 часов. Это ничего вам не говорит, потому что они могут происходить каждый 1 час за последние 6 часов или каждую 1 минуту за 6 минут. - Vladimir_M 05.08.2015

comment

Давайте продолжим обсуждение в чате. - Siva 05.08.2015

Ответы (1)

arrow_upward
1
arrow_downward

Все тревоги, созданные за последние 15 минут (по вашему запросу).

select distinct(AlmCode) 
from TB_ALM 
where AlmCode IN ('3236','4002','4008','4036','4050','4051','4102','4108','4136','4150','4151','4202','4208','4236','4250','4251','4801','4802','4836','4848','4850','4851','4902','4936','4950','4951','5002','5008','5036','5050','5051','5102','5108','5136','5150','5151','5202','5208','5236','5250','5251','5947','5950','5952','5975','5976','5977','5978') 
AND OccurTime >= date_sub(NOW(),interval 15 minute)

Все тревоги, HAS произошли трижды за любые 15 минут за последние 6 часов (будут исключены после)

select distinct t1.AlmCode
from TB_ALM t1
inner join TB_ALM t2 on t2.AlmCode = t1.AlmCode 
    and t2.OccurTime <= date_add(t1.OccurTime, interval 15 minute)
    and t2.OccurTime > t1.OccurTime
inner join TB_ALM t3 on t3.AlmCode = t1.AlmCode 
    and t3.OccurTime <= date_add(t1.OccurTime, interval 15 minute)
    and t3.OccurTime > t2.OccurTime
WHERE true
  AND t1.OccurTime >= date_sub(now(), interval 6 hour)
  AND t1.AlmCode IN ('3236','4002','4008','4036','4050','4051','4102','4108','4136','4150','4151','4202','4208','4236','4250','4251','4801','4802','4836','4848','4850','4851','4902','4936','4950','4951','5002','5008','5036','5050','5051','5102','5108','5136','5150','5151','5202','5208','5236','5250','5251','5947','5950','5952','5975','5976','5977','5978')

Таким образом, окончательный запрос

select distinct(AlmCode) 
from TB_ALM 
where true
  AND OccurTime >= date_sub(NOW(),interval 15 minute) 
  AND AlmCode IN ('3236','4002','4008','4036','4050','4051','4102','4108','4136','4150','4151','4202','4208','4236','4250','4251','4801','4802','4836','4848','4850','4851','4902','4936','4950','4951','5002','5008','5036','5050','5051','5102','5108','5136','5150','5151','5202','5208','5236','5250','5251','5947','5950','5952','5975','5976','5977','5978')
  AND AlmCode NOT IN (select distinct t1.AlmCode
from TB_ALM t1
inner join TB_ALM t2 on t2.AlmCode = t1.AlmCode 
    and t2.OccurTime <= date_add(t1.OccurTime, interval 15 minute)
    and t2.OccurTime > t1.OccurTime
inner join TB_ALM t3 on t3.AlmCode = t1.AlmCode 
    and t3.OccurTime <= date_add(t1.OccurTime, interval 15 minute)
    and t3.OccurTime > t2.OccurTime
WHERE true
      AND t1.OccurTime >= date_sub(now(), interval 6 hour)
      AND t1.AlmCode IN ('3236','4002','4008','4036','4050','4051','4102','4108','4136','4150','4151','4202','4208','4236','4250','4251','4801','4802','4836','4848','4850','4851','4902','4936','4950','4951','5002','5008','5036','5050','5051','5102','5108','5136','5150','5151','5202','5208','5236','5250','5251','5947','5950','5952','5975','5976','5977','5978')
  )

Добавьте индекс в столбец AlmCode, это значительно сократит время выполнения

Vladimir_M 04.08.2015

comment

не могли бы вы исправить опечатку dt1 как во втором, так и в последнем запросе? - Siva; 05.08.2015

comment

Спасибо, что заметили, исправлено - Vladimir_M; 05.08.2015

mysql выбирает столбцы со сложными критериями подсчета + диапазона часов

Ответы (1)

Похожие вопросы