Comparing Row Values Based On User MySQL

Question

I thought every MySQL query question would be answered by now, but I have a query that I can't find a good answer to.

I would like to run a query analyzing the differences between rows in one table, based on each SecState column and Timestamp. So loop through all rows, find the next (or previous) reading based on the Timestamp of that SecState and get the difference between the Timestamp, Location, and Days readings.

Input Table Example:

+----------+---------------+-------------+-------------+
| SecState |   Timestamp   |  Location   |    Days     |
+----------+---------------+-------------+-------------+
|        1 | 1574614810000 | 0.030520002 | 0.068209626 |
|        2 | 1574614810000 | 0.000491507 | 0.000124721 |
|        1 | 1574614780000 | 0.030519481 | 0.068209626 |
|        2 | 1574614780000 | 0.000491507 | 0.000124721 |
|        3 | 1574614752000 | 1           | 1           |
|        3 | 1574614731000 | 1           | 1           |
|        1 | 1574614750000 | 0.03051896  | 0.068209626 |
|        2 | 1574614750000 | 0.000491493 | 0.000124721 |
|        1 | 1574614720000 | 0.030518439 | 0.068206906 |
|        2 | 1574614720000 | 0.00049148  | 0.000124721 |
|        1 | 1574614690000 | 0.030517918 | 0.068206906 |
|        2 | 1574614690000 | 0.00049148  | 0.000124721 |
|        3 | 1574614671000 | 1           | 1           |
|        3 | 1574614631000 | 1           | 1           |
|        3 | 1574614571000 | 1           | 1           |
|        1 | 1574614660000 | 0.030517397 | 0.068206906 |
|        2 | 1574614660000 | 0.000491467 | 0.000124721 |
|        1 | 1574614630000 | 0.030516876 | 0.068206906 |
+----------+---------------+-------------+-------------+

Thanks!

Answer 1

If you are running MySQL 8.0, you can use window function lead() to access columns of the next record.

Something like this should be what you want:

select 
    t.*,
    lead(Timestamp) over(partition by SecState order by Timestamp)
        - Timestamp TimestampDiff,
    lead(Location) over(partition by SecState order by Timestamp)
        - Location LocationDiff,
    lead(Days) over(partition by SecState order by Timestamp)
        - Days DaysDiff
from mytable t

In earlier versions, you can self-join the table and use a correlated subquery with a not exists condition to locate the next record, like so:

select
    t.*,
    t1.Timestamp - t.Timestamp TimestampDiff,
    t1.Location - t.Location LocationDiff,
    t1.Days - t.Days DaysDiff
from mytable t
left join mytable t1
    on  t1.SecState = t.SecState
    and t1.Timestamp > t.Timestamp
    and not exists (
        select 1 
        from mytable t2
        where 
            t2.SecState = t.SecState
            and t2.Timestamp > t.Timestamp
            and t2.Timestamp < t1.Timestamp
    )