Search code examples
mysqlsubqueryhourin-subquery

mysql select columns having complex count + hour-range criteria

Issue Description: I have the following query to retrieve the latest alarms in last 15 minutes.

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

Table Structure:

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

What's Needed: Now I want to have this modified to retrieve the alarms with following criteria:

a. Alarms(AlmCodes) occurred in last 15 minutes(Original Req) AND

b. ONLY If each of Alarms(AlmCodes) has not occurred more than thrice in any of the 15 minute window during last six hours

What's Tried: I tried the following approach:

  1. Get the DISTINCT(AlmCodes) during last 15 minutes.

    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) ;

  2. Use Item-1(above) as subquery and get the count of occurrence for each AlmCode.

    select Almcode,concat(date(OccurTime),' ',HOUR(OccurTime)) as HR,count(*) from TB_ALM_HISTORY where AlmCode IN ( select distinct(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(),interval 15 minute) ) AND OccurTime >= date_sub(NOW(),interval 15*4*24 minute) group by AlmCode,HR;

Issues:

  1. Items-2 Query keeps executing for ever with (subquery) where as if i run them as two separate queries, it returns instantly as below. Whats missing here?

Query-1: Get unique alarms

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

Query-2: Get the count for each of unique alarms for last 6 hours

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

Assuming this query was run at 6PM EST, AlmCode 5202 has occurred in last 6 hours(btwn 12-18Hours) and hence results for this AlmCode should not be included in the final select query(of occurred in last 15 minutes). whereas AlmCode 3236 didn't occur in last 6 hours and hence all the alarms that occured in last 15 minutes for this particular AlmCode have to be included.

  1. How to get my end output all in one query?

a. Get the unique AlmCode with OccurTime >= Last 15 Minutes

b. For each of these AlmCode, check if it has occurred thrice in last 6 hours

c. If NO then pull all the alarms for this AlmCode with OccurTime >= Last 15 Minutes (If YES don't include & simply skip)

Solution

  • All alarms, created in last 15 minutes (your query).

    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)

    All alarms, HAS occurred thrice in any 15 minutes in last 6 hours (it will be excluded after)

    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')

    So the final query is

    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')
  )

    Add index on AlmCode column, it will significantly decrease execution time