How to count if the difference of two consecutive rows is greater than 0?

Suppose the following table,

TIMESTAMP                      value
2021-01-27 00:34:05.256000000   1000
2021-01-27 00:34:15.918000000   800
2021-01-27 00:34:46.427000000   1000
2021-01-27 00:25:07.802000000   6300
2021-01-27 00:25:14.651000000   6300
2021-01-27 00:25:31.048000000   150
2021-01-27 00:25:23.264000000   150
2021-01-27 00:26:01.016000000   240
2021-01-27 00:25:38.978000000   450

The final output I am trying to achieve is,

Change
  6

where Change is a counter which increments given the difference between any two consecutive rows (value rows) is not 0. How may I achieve this using mysql?

Answer

LAG() can be used to fetch the preceding row:

mysql> select value, lag(value) over(order by timestamp) as prev_value from mytable;
+-------+------------+
| value | prev_value |
+-------+------------+
|  6300 |       NULL |
|  6300 |       6300 |
|   150 |       6300 |
|   150 |        150 |
|   450 |        150 |
|   240 |        450 |
|  1000 |        240 |
|   800 |       1000 |
|  1000 |        800 |
+-------+------------+
9 rows in set (0.00 sec)

mysql> select sum(value-prev_value <> 0) as `change` from (
  select value, lag(value) over(order by timestamp) as prev_value from mytable
) as t;
+--------+
| change |
+--------+
|      6 |
+--------+
1 row in set (0.01 sec)

LEAD() can give the same result, as shown in another answer. In both cases, either the first row has no previous, or the last row has no next, so they cannot compare to NULL.