MySQL count changes

Question

I would like to count number of changes in column Value grouped by Id using MySQL.

Source Table:

create table sequence
(
   `Id` int,
   `Date` date,
   `Value` int not null,
   PRIMARY KEY (`Id`,`Date`)
);

insert into sequence
    ( `Id`,`Date`, `Value` )
  values
    (1, '2016-01-01' , 0  ),
    (1, '2016-01-02' , 10 ),
    (1, '2016-01-03' , 0  ),
    (1, '2016-01-05' , 0  ),
    (1, '2016-01-06' , 10 ),
    (1, '2016-01-07' , 15 ),
    (2, '2016-01-08' , 15 );

Visualization:

+------------+-------+-------+ 
| Date       |  ID   | Value |
+------------+-------+-------+
| 2016-01-01 |   1   |   0   |  
| 2016-01-02 |   1   |   10  | (change)
| 2016-01-03 |   1   |   0   | (change)
| 2016-01-05 |   1   |   0   |
| 2016-01-06 |   1   |   10  | (change)
| 2016-01-07 |   1   |   15  | (change)
| 2016-01-08 |   2   |   15  |
+------------+-------+-------+

Expected output:

+-------+-------+ 
|  ID   | Value |
+-------+-------+
|   1   |   4   |  
|   2   |   0   | 
+-------+-------+

I would like to ask if there is a way how to do this in SQL.

Yes. Many ways. You can use window functions especially Lag. Window functions are not available on all SQL flavors, so a quick and dirty way is to add a row number and then inner join the table (ordered by date,id) to itself with on a.rownumber=b.rownumber+1. Search for Lag/row number over and you'll find plenty good sources — Zahiro Mor, Feb 04 '16 at 19:47
http://www.mysqltutorial.org/mysql-row_number/ shows 2 ways to emulate row numbers in MySQL. MS has it built-in — Zahiro Mor, Feb 04 '16 at 19:54
Check out http://stackoverflow.com/questions/11303532/simulate-lag-function-in-mysql. — Zahiro Mor, Feb 04 '16 at 19:57

score 0 · Answer 1 · answered Feb 04 '16 at 20:37

This is not the very efficient or elegant solution, but just to show some goals that you can achieve using mysql :-)

http://sqlfiddle.com/#!9/1db14/6

SELECT t1.id, MAX(t1.changes)
FROM (SELECT t.*, 
   IF (@i IS NULL,@i:=0,IF(@lastId <> id,@i:=0,IF (@lastV <> `value`, @i:=@i+1, @i:=@i))) as changes,
   @lastV := `value`,
   @lastId := `id`

FROM (
  SELECT *
  FROM sequence
  ORDER BY id, date) t
) t1
GROUP BY t1.id

MySQL count changes

1 Answers1