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:
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;
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
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.
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).
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).
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With