Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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? –  miracle173 Jan 2 '13 at 7:34
    
Hello @miracle173 the question is updated, if you have any queries let me know –  Ashok KS Jan 2 '13 at 8:44
2  
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 :-) –  Bing Jan 2 '13 at 8:54
    
what does the accepted answer return on your sample data? can you supply an sqlfiddle sample –  miracle173 Jan 2 '13 at 12:34
    
here the sample at sqlfiddle with the wrong query published by @Rohan –  miracle173 Jan 2 '13 at 13:19

3 Answers 3

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 ...

share|improve this answer

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.

share|improve this answer
    
Ok thanks for the hint, but how to write the query ???? –  Ashok KS Jan 2 '13 at 5:07

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 ;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.