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.过去 15 分钟内发生的警报(AlmCodes)(原始请求)AND

b.仅当过去 6 小时内任何 15 分钟窗口中每个警报(AlmCodes)发生次数均未超过三次

尝试了什么:我尝试了以下方法:

1. 获取过去 15 分钟内的 DISTINCT(AlmCodes)。

   从TB_ALM中选择distinct(AlmCode)，其中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 分钟) ;

2. 使用 Item-1(上面)作为子查询并获取每个 AlmCode 的出现次数。

   从 TB_ALM_HISTORY 中选择 Almcode,concat(date(OccurTime),' ',HOUR(OccurTime)) as HR,count(*)，其中 AlmCode IN (select unique(s.AlmCode) from TB_ALM_HISTORY s where 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') AND s.OccurTime >= date_sub(NOW(),间隔 15 分钟)) AND OccurTime >= date_sub(NOW(),间隔 15*4*24 分钟) group by 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 小时内(顺便说一句 12-18 小时)，因此此 AlmCode 的结果不应包含在最终选择查询中(发生在过去 15 分钟内)。而 AlmCode 3236 在过去 6 小时内没有发生，因此必须包含在过去 15 分钟内针对该特定 AlmCode 发生的所有警报。

如何在一个查询中获取所有最终输出？

a.获取 OccurTime >= 最近 15 分钟

的唯一 AlmCode

b.对于每个 AlmCode，检查它是否在过去 6 小时内出现了三次

c. 如果否，则拉取此 AlmCode 的所有警报，且 OccurTime >= 最后 15 分钟(如果是，则不包含并直接跳过)

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) 

所有警报，最近6小时内任意15分钟内发生3次(之后将被排除)

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列上添加索引，将显着减少执行时间