mysql - 基于用户 MySQL 比较行值

Question

我以为现在每个 MySQL 查询问题都会得到解答，但我有一个查询找不到好的答案。

我想运行一个查询，根据每个 SecState 列和时间戳来分析一个表中行之间的差异。因此，循环遍历所有行，根据该 SecState 的时间戳查找下一个(或上一个)读数，并获取时间戳、位置和天数读数之间的差异。

输入表示例:

+----------+---------------+-------------+-------------+
| 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 |
+----------+---------------+-------------+-------------+

谢谢!

Answer 1

如果您运行的是 MySQL 8.0，则可以使用窗口函数 lead() 访问下一条记录的列。

这样的东西应该是你想要的:

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

在早期版本中，您可以自联接表并使用带有不存在条件的相关子查询来定位下一条记录，如下所示:

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
    )