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

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?

CodePudding user response：

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.

CodePudding user response：

If you're using a recent version of mysql, you can use lead as follows to compare the value of a row with the value of the next row ordered by timestamp:

with u as 
(select *, lead(value) over(order by timestamp) as nextValue
from table_name)
select sum(case when value <> nextValue then 1 else 0 end)
from u;

Fiddle

CodePudding user response：

Use this query to achieve the output:

SELECT SUM(chang) AS chang FROM 
(SELECT  SUM(
        CASE
        WHEN innerT.price = COALESCE((SELECT price FROM `TableName` WHERE id > innerT.id LIMIT 1) , innerT.price) THEN 0
        ELSE 1
        END
    ) AS chang
FROM `TableName` as innerT GROUP BY id ) outerT