Running total of every increment

Question

I have a table events as follows:

f_id   leg   
1       1
2       1
3       1
4       2
5       2
6       3
7       1
8       1
9       2

I want a running total of every time the leg changes. Expected output:

f_id   leg     total_legs   
1       1         1
2       1         1
3       1         1
4       2         2
5       2         2
6       3         3
7       1         4
8       1         4
9       2         5

Not sure how to go about this.

SELECT *, @leg_var:=IF(@current_leg=leg, leg) as total_legs FROM `events`

This is clearly wrong.

Answer 1

WITH cte AS ( SELECT *, CASE WHEN LAG(leg) OVER (ORDER BY f_id) = leg 
                             THEN 0 
                             ELSE 1 
                             END lag_leg
              FROM test )
SELECT f_id, leg, SUM(lag_leg) OVER (ORDER BY f_id) total_legs
FROM cte;

fiddle

Answer 2

This is a kind of gaps-and-islands problem. In MySQL 8.0, you can use lag() and a cumulative sum():

select fid, leg, sum(not leg <=> lag_leg) over(order by f_id) total_legs
from (
    select e.*, lag(leg) over(order by f_id) lag_leg
    from events e
) e

Answer 3

The problem can be solved without window functions using variables:

SET @leg_var:=null;
SET @total:=0;
SELECT 
    f_id, 
    @leg_var prev_leg, 
    @total:=@total+if(@leg_var is null or @leg_var<>leg, 1, 0) as total,
    @leg_var:=leg as leg
FROM events
ORDER BY f_id;

fiddle here