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.

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.

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 ;