MySQL 计数变化

Question

我想使用 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中做到这一点。

Answer 1

这不是非常有效或优雅的解决方案，但只是为了展示一些您可以使用 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