MySQL count changes in sequence

Question

I'm trying to find a way to count the number of times a value changes in an ordered list. Given a sequence such as A, B, A, A, B, C, C, there would be 4 changes, ignoring the first one.

What I need is something like a COUNT(GROUP BY x) command to be applied after an ORDER BY y, but this isn't valid syntax. Is there a simple way to accomplish this in pure MySQL, or am I better off iterating through with Python?

The table would be sorted by a date field. For example:

+------------+-------+
| Date       | Value |
+------------+-------+
| 2015-09-01 | A     |
| 2015-09-02 | B     | (change)
| 2015-09-03 | A     | (change)
| 2015-09-05 | A     |
| 2015-09-06 | B     | (change)
| 2015-09-07 | C     | (change)
| 2015-09-08 | C     |
+------------+-------+

Answer 1

plan

• order by Date
• calculate lag variable over the ordering
• calculate changes ( comparing to lag variable )
• take max(changes) and subtract one for ( for the first transition.. )

query

set @chgs := 0;
set @lag  := null;

select max(chgs) - 1 as num_changes
from
(
  select `Date`, `Value`,
  @chgs := if(@lag = `Value`, @chgs, 
                if(@lag := `Value`, @chgs + 1, @chgs + 1)) as chgs
  from sequence
  order by `Date`
) calc
;

output

+-------------+
| num_changes |
+-------------+
|           4 |
+-------------+

sqlfiddle