mysql - MySQL计数变化

我想用MySQL计算按Id分组的列值中的更改数。
源表：

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 );

可视化：

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

预期产量：

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

我想问一下在SQL中是否有这样做的方法。

最佳答案

这不是一个非常有效或优雅的解决方案，
不过，为了展示使用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