Count consecutive rows in mysql

Question

I would like to get the count of consecutive rows in MySQL. It is a large database I try to avoiding the joins. Here gadget_id means vehicle and every 20/30 second the vehicle sends the current location. I want to know how long time a vehicle halts in certain location.

Will explain by below table, it is the latest data

id gadget_id location submitted_date 
 --------- -------- ---------- 
1 1 calicut 2012年07月15日 
2 1 calicut 2012年07月14日 
3 1 calicut 2012年07月13日 
4 2 thrissur 2012年07月12日 
5 1 calicut 2012年07月11日
6 1 kannur 2012年07月10日
7 2 thrissur 2012年07月09日
8 1 calicut 2012年07月08日 
... ... ... 

*I want to know how long time gadget_id 1 halts in position calicut.*

Here we can show that the gadget_id = 1 and position=calicut last 4 data's comes from same position calicut. The next data of the gadget_id=1 is from kannur, so we avoid the data from this id. How to get the count 4 when we give the input gadget_id = 1 and position=calicut

Anybody give the suitable query, expect a single query without joins.

• miracle173: I ave no idea what you are talking about. The input of your query is gadget_id = 1 and position='calicut' and the output is 4? This can be achieved by select count(*) from a_table where gadget_id = 1 and position='calicut' but I don't think that is the answer of your question. Can you elaborateyour question?
• Ashok KS: Hello @miracle173 the question is updated, if you have any queries let me know
• Bing: Do a search for "gaps and islands" and you'll find plenty of similar questions and some really good answers. Most of them pretty complex, involving joins. Not sure why you don't want joins, it's like asking a car-wash not to use water :-)
• miracle173: what does the accepted answer return on your sample data? can you supply an sqlfiddle sample
• miracle173: here the sample at sqlfiddle with the wrong query published by @Rohan

Answer 1

Since gadget_id is a vehicle, you need to monitor two things as you look at each row

• when a gadget_id switches location
• when a gadget_id switches to another gadget_id

The solution lies in organizing a set of user variables to monitor that change. Please forgive you are about to see:

First, let's load your data in the test database in a table called gadget_location:

mysql> use test
Database changed
mysql> drop table gadget_location;
Query OK, 0 rows affected (0.07 sec)
mysql> create table gadget_location
 -> (
 -> id int not null auto_increment,
 -> gadget_id int,
 -> location varchar(30),
 -> submitted_date date,
 -> primary key (id)
 -> );
Query OK, 0 rows affected (0.13 sec)
mysql> insert into gadget_location (gadget_id,location,submitted_date) values
 -> (1,'calicut' , '2012-07-15'), (1,'calicut' , '2012-07-14'),
 -> (1,'calicut' , '2012-07-13'), (2,'thrissur', '2012-07-12'),
 -> (1,'calicut' , '2012-07-11'), (1,'kannur' , '2012-07-10'),
 -> (2,'thrissur', '2012-07-09'), (1,'calicut' , '2012-07-08');
Query OK, 8 rows affected (0.05 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> select * from gadget_location;
+----+-----------+----------+----------------+
| id | gadget_id | location | submitted_date |
+----+-----------+----------+----------------+
| 1 | 1 | calicut | 2012年07月15日 |
| 2 | 1 | calicut | 2012年07月14日 |
| 3 | 1 | calicut | 2012年07月13日 |
| 4 | 2 | thrissur | 2012年07月12日 |
| 5 | 1 | calicut | 2012年07月11日 |
| 6 | 1 | kannur | 2012年07月10日 |
| 7 | 2 | thrissur | 2012年07月09日 |
| 8 | 1 | calicut | 2012年07月08日 |
+----+-----------+----------+----------------+
8 rows in set (0.00 sec)
mysql>

OK, here comes the mess:

SET @dupcount = 0;
SET @group_number = 0;
SET @cur_gadget_id = 0;
SET @cur_location = MD5("1");
SET @cur_gadget_location = MD5("1");
SELECT gadget_id,location,GroupNumber,COUNT(1) DaysStopped FROM
(
SELECT
 *,
 @dupcount := IF(@cur_gadget_location=gadget_location,@dupcount+1,1) Dup_Count,
 @group_number := IF(@cur_gadget_location=gadget_location,@group_number,@group_number+1) GroupNumber,
 @cur_gadget_location := gadget_location
FROM
(
 SELECT *,CONCAT(gadget_id,'-',cur_loc) gadget_location FROM
 (
 SELECT *,
 @cur_location := IF(MD5(location)=@cur_location,@cur_location,MD5(location)) cur_loc,
 @cur_gadget_id := IF(gadget_id=@cur_gadget_id,@cur_gadget_id,gadget_id) cur_gadget
 FROM gadget_location
 ) AAA
) AA ) A GROUP BY gadget_id,location,GroupNumber;

Want to see it work ??? Here it goes:

mysql> SET @dupcount = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @group_number = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @cur_gadget_id = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @cur_location = MD5("1");
Query OK, 0 rows affected (0.00 sec)
mysql> SET @cur_gadget_location = MD5("1");
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT gadget_id,location,GroupNumber,COUNT(1) DaysStopped FROM
 -> (
 -> SELECT
 -> *,
 -> @dupcount := IF(@cur_gadget_location=gadget_location,@dupcount+1,1) Dup_Count,
 -> @group_number := IF(@cur_gadget_location=gadget_location,@group_number,@group_number+1) GroupNumber,
 -> @cur_gadget_location := gadget_location
 -> FROM
 -> (
 -> SELECT *,CONCAT(gadget_id,'-',cur_loc) gadget_location FROM
 -> (
 -> SELECT *,
 -> @cur_location := IF(MD5(location)=@cur_location,@cur_location,MD5(location)) cur_loc,
 -> @cur_gadget_id := IF(gadget_id=@cur_gadget_id,@cur_gadget_id,gadget_id) cur_gadget
 -> FROM gadget_location
 -> ) AAA
 -> ) AA ) A GROUP BY gadget_id,location,GroupNumber;
+-----------+----------+-------------+-------------+
| gadget_id | location | GroupNumber | DaysStopped |
+-----------+----------+-------------+-------------+
| 1 | calicut | 1 | 3 |
| 1 | calicut | 3 | 1 |
| 1 | calicut | 6 | 1 |
| 1 | kannur | 4 | 1 |
| 2 | thrissur | 2 | 1 |
| 2 | thrissur | 5 | 1 |
+-----------+----------+-------------+-------------+
6 rows in set (0.02 sec)
mysql>

According to this output, here is what you have:

Gadget 1

• Group 1 : stopped at calicut for 3 days
• Group 3 : left and came back for 1 day
• Group 4 : left calicut and went to kannur for 1 day
• Group 6 : left kannur and went to calicut for 1 day

Gadget 2

• Group 2 : stopped for thrissur for 1 day
• Group 5 : left and came back for 1 day

For some reason, the GroupNumbers came out different in SQLFiddle`. Notwithstanding, the rest of the output is the same.

I hope this is right ...

Answer 2

You could use WHERE NOT EXISTS to be able to see if the subsequent row is the same. This will then give you the boundaries. With an index on the fields of interest it shouldn't have to be costly.

• Ashok KS: Ok thanks for the hint, but how to write the query ????

Answer 3

Not sure I understand what you mean. This uses a derived table to find the row with highest date and location different than 'calicut' and then uses it to count the "previous" rows:

SELECT COUNT(*) AS cnt
FROM gadget_location AS gl
 JOIN
 ( SELECT submitted_date 
 FROM gadget_location
 WHERE gadget_id = 1
 AND location <> 'calicut'
 ORDER BY submitted_date DESC
 LIMIT 1 
 ) AS f
 ON gl.gadget_id = 1
 AND gl.submitted_date > f.submitted_date ;