mysql select columns having complex count + hour-range criteria

Question

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:

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

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.

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)

Could you please provide create table statement (including indexes) and explain separately for 1st, 2nd and complex query? — Vladimir_M, Aug 04 '15 at 20:58
@Vladimir_M added the table structure,output for two-step queries with more detailed expected results — Siva, Aug 04 '15 at 22:02
So if alarm has occurred in any given 15 minutes within last 6 hours thrice or more, should it or shouldn't be included in result? (check description in "issues" paragraph and "what's needed") — Vladimir_M, Aug 04 '15 at 22:12
@Vladimir_M Those alarms **shouldn't be included** if it's seen thrice or more in any 15 minutes of last 6 hours. Sorry that typo is corrected now. — Siva, Aug 05 '15 at 14:18
@Vladimir_M just curious where does this checks for thrice or more occurrence in any of 15 minutes for last hour? — Siva, Aug 05 '15 at 15:12
in inner joins conditions. t1 joind with t2 with following conditions: AlmCode should be same (t2.AlmCode = t1.AlmCode ) , AlmCode_2 should occurre not later then 15 minutes after AlmCode_1 (t2.OccurTime <= date_add(t1.OccurTime, interval 15 minute)) and AlmCode_2 should occurre later then AlmCode_1 (t2.OccurTime > dt1.OccurTime) , so it can not be same occurrence, because OcceTime is different. With same logic t3 joined, which returns thrice occurrence within 15 minutes — Vladimir_M, Aug 05 '15 at 15:24
hmmm excellent... i was always thinking towards making a count of occurrence n then compare with it! :( — Siva, Aug 05 '15 at 15:26
Counting could show wrong result, for example, AlmCode appear 6 times in last 6 hours. It tells you nothing, because they can occur every 1 hour in last 6 hours or every 1 minutes in 6 minutes. — Vladimir_M, Aug 05 '15 at 15:31
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/85220/discussion-between-siva-and-vladimir-m). — Siva, Aug 05 '15 at 16:14

Vladimir_M · Accepted Answer · 2015-08-05T15:27:05.747

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

can you please correct the dt1 typo on both second and final query? — Siva, Aug 05 '15 at 15:23

mysql select columns having complex count + hour-range criteria

1 Answers1