CODEWITHSUNDEEP
mysqlsqldatejoinwindow-functions
Alex
Alex

Reputation: 1583

Comparing Row Values Based On User MySQL

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!

Upvotes: 0

Views: 24

Answers (1)

GMB
GMB

Reputation: 222582

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
    )

Upvotes: 1

Related Questions