Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Query to group on max event_count?

Tags:

sql

php

mysql

I need to calculate the time difference between groups of events. Each "event group" starts over when the event_count column = 1, and the max event_count can be any number greater than 1.

How can I do this in MySQL?

Here is the table and some test data:

Table

CREATE TABLE `monitoring` ( 
  `event_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
  `hosting_id` char(36) DEFAULT NULL, 
  `event_timestamp` datetime DEFAULT NULL, 
  `event_type` tinyint(3) unsigned DEFAULT NULL, 
  `event_count` int(6) DEFAULT NULL, 
  `session` varchar(36) DEFAULT NULL, 
  PRIMARY KEY (`event_id`) 
) ENGINE=MyISAM AUTO_INCREMENT=1765 DEFAULT CHARSET=utf8;

Test Records:

INSERT INTO `monitoring` (`event_id`, `hosting_id`, `event_timestamp`, `event_type`, `event_count`, `session`)
VALUES
    (1753,'97948a60-2e44-d39c-bb02-506c80c97df4','2013-10-23 20:01:19',1,1,NULL),
    (1711,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:02:20',1,1,NULL),
    (1712,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:02:26',1,2,NULL),
    (1713,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:02:30',1,3,NULL),
    (1714,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:02:33',1,4,NULL),
    (1715,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:02:45',1,5,NULL),
    (1716,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:02:48',1,6,NULL),
    (1717,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:02:51',1,7,NULL),
    (1718,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:29',1,1,NULL),
    (1719,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:31',1,2,NULL),
    (1720,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:33',1,3,NULL),
    (1721,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:35',1,4,NULL),
    (1722,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:38',1,5,NULL),
    (1723,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:40',1,6,NULL),
    (1724,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:43',1,7,NULL),
    (1725,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:46',1,8,NULL),
    (1726,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:48',1,9,NULL),
    (1727,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 16:49:50',1,10,NULL),
    (1754,'988df609-4ae4-3062-6361-4fd2665cfb42','2013-10-24 02:48:32',1,1,NULL),
    (1729,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:02:28',1,1,NULL),
    (1730,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:03:25',1,2,NULL),
    (1731,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:03:30',1,3,NULL),
    (1732,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:05:42',1,4,NULL),
    (1733,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:06:03',1,5,NULL),
    (1758,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-24 12:23:43',1,1,NULL),
    (1757,'d425f99b-0812-5902-37da-4fd266c64545','2013-10-24 08:16:26',1,1,NULL),
    (1756,'20e00e08-459b-b229-d341-4fd266c23df1','2013-10-24 06:32:10',1,1,NULL),
    (1738,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:09:02',1,1,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1739,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:09:04',1,2,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1740,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:09:06',1,3,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1741,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:09:07',1,4,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1742,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:09:08',1,5,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1743,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:09:09',1,6,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1755,'988df609-4ae4-3062-6361-4fd2665cfb42','2013-10-24 06:30:20',1,1,NULL),
    (1745,'a9e17a2a-42d9-979f-0484-4fd2665b487a','2013-10-23 17:09:28',1,1,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1746,'a9e17a2a-42d9-979f-0484-4fd2665b487a','2013-10-23 17:09:38',1,2,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1747,'a9e17a2a-42d9-979f-0484-4fd2665b487a','2013-10-23 17:09:55',1,3,'5c31a1eb481bf9e0753f9a25538b0257'),
    (1748,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:13:54',1,1,NULL),
    (1749,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:13:56',1,2,NULL),
    (1750,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:13:58',1,3,NULL),
    (1751,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-23 17:14:00',1,4,NULL),
    (1752,'ecd5f06a-7b82-4cc8-deb2-4fd266eeec3e','2013-10-23 19:06:42',1,1,NULL),
    (1759,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-24 12:23:48',1,2,NULL),
    (1760,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-24 12:23:51',1,3,NULL),
    (1761,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-24 12:23:53',1,4,NULL),
    (1762,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-24 12:23:56',1,5,NULL),
    (1763,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-24 12:23:58',1,6,NULL),
    (1764,'c47204d5-d682-9ba4-b8b6-4fd26660651a','2013-10-24 12:24:23',1,7,NULL);

What I am aiming for is:

On 10-23-2013 down for 31 seconds 
On 10-23-2013 down for 21 seconds 
On 10-23-2013 down for 215 seconds
    etc...

which I will do once I can get this query figured out properly.

My Query so far is:

SELECT min(date(`event_timestamp`)) as EventGroupDate, (max(`event_timestamp`) - min(`event_timestamp`)) HowLong
FROM (
    SELECT `event_timestamp`, @eventGroup := @eventGroup + (`event_count` = 1) eventGroup
    FROM `monitoring`, (SELECT @eventGroup := 0) init
    WHERE `hosting_id` = 'c47204d5-d682-9ba4-b8b6-4fd26660651a'
    ORDER BY `event_timestamp`
) s
GROUP BY eventGroup

but wrong calculations. Exact calculations are below

and I will post updates to it as I make them.

Each event group starts as event_count = 1, and can end at any number greater than 1. Since this needs to sort by the event_timestamp it can be assumed that all in between could be considered a group. for example: record IDs 1711-1717 would be group 1, 1718-1727 would be group 2, so on and so forth...

Exact results should be:

31
21
215
7
6
40

with the given data

like image 376
Kevin Avatar asked Oct 24 '13 13:10

Kevin


People also ask

What is group by in MySQL?

The MySQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.

How do you get a record with maximum value for each group?

MySQL MAX() function with GROUP BY retrieves maximum value of an expression which has undergone a grouping operation (usually based upon one column or a list of comma-separated columns).

How to optimize group by Query in MySQL?

The most general way to satisfy a GROUP BY clause is to scan the whole table and create a new temporary table where all rows from each group are consecutive, and then use this temporary table to discover groups and apply aggregate functions (if any).


1 Answers

Try this out:

SELECT
  min(date(event_timestamp)) aDay,
  max(UNIX_TIMESTAMP(event_timestamp)) - min(UNIX_TIMESTAMP(event_timestamp)) secondDiff
FROM (
  SELECT event_timestamp, @eventGroup := @eventGroup + (event_count = 1) eventGroup
  FROM monitoring, (SELECT @eventGroup := 0) init
  WHERE hosting_id = 'c47204d5-d682-9ba4-b8b6-4fd26660651a'
  ORDER BY event_timestamp
) s
GROUP BY eventGroup

Output:

|             ADAY | SECONDDIFF |
|------------------|------------|
| October, 23 2013 |         31 |
| October, 23 2013 |         21 |
| October, 23 2013 |        215 |
| October, 23 2013 |          7 |
| October, 23 2013 |          6 |
| October, 24 2013 |         40 |

Fiddle here.

like image 54
Mosty Mostacho Avatar answered Oct 14 '22 04:10

Mosty Mostacho