mysql选择具有复杂计数+小时范围条件的列

Question

问题描述：我有以下查询来检索最后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

什么是需要的：，现在我想要修改它来检索具有以下条件的警报：

a.警报(AlmCodes)发生在最后15分钟(原始的Req) 和。

b.只有在过去6小时内，在15分钟的窗口中，每个警报(AlmCodes)的发生时间都不超过三次。

尝试了什么：：--我尝试了以下方法：

1. 在最后15分钟内获取DISTINCT(AlmCodes)。 从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')和OccurTime >= date_sub(现在)，间隔15分钟)；
2. 使用Item-1(上面)作为子查询，并获取每个AlmCode的出现数。 选择Almcode，concat(date(OccurTime)，‘'，HOUR(OccurTime))作为HR，从TB_ALM_HISTORY AlmCode IN中选择计数(*)(从TB_ALM_HISTORY 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’、‘5051’、'5102‘、'5108’、'5136‘、'5150’、'5151‘、'5202’、'5208‘、'5236’、'5250‘、'5251'，'5947'，'5950'，'5952'，'5975'，'5976'，'5977'，'5978')和s.OccurTime >= date_sub(现在()，间隔15分钟)和OccurTime >= date_sub(现在()，间隔15*4*24分钟)由AlmCode，HR；

问题：

1. 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 |
+---------+---------------+----------+

假设此查询是在东部时间下午6点运行的，AlmCode 5202发生在最后6个小时(btwn 12-18小时)，因此在最后15分钟内发生的最后选择查询中不应包含此AlmCode的结果。然而，AlmCode 3236没有在最后6小时内发生，因此必须包括在最后15分钟内发生的针对这个特定AlmCode的所有警报。

1. 如何在一个查询中获得我的最终输出？

最后15分钟获得唯一的AlmCode和OccurTime >=。

对于每一个AlmCode，检查它是否在过去的6小时内发生过三次。

c. (如果不是)，然后用OccurTime >= Last15分钟(如果是的话不包括&简单地跳过)，为这个AlmCode拔出所有警报。

Answer 1

所有警报，在最后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) 

所有警报，已在任何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列上添加索引，将显著减少执行时间()