'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?

mysqlsql


Solution 1:[1]

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

Solution 2:[2]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Zakaria
Solution 2 sajjad rezaei

Related Questions