时间戳

首页 > 解决方案 > 每组前两名之间的 Sql 差异

mysql - 每组前两名之间的 Sql 差异

问题描述

我在 mysql 数据库中有一个数据表，其中包含时间序列数据

**Table Sensor Data**  
ID    uuid  server_time
1      a    2021-07-29 11:36:15
2      b    2021-07-29 11:36:15
3      a    2021-07-29 12:36:15
4      b    2021-07-29 11:39:15
5      a    2021-07-29 13:36:15

我想找到每个 uuid 的两个最新数据的 server_time 之间的时间差（以分钟为单位）。因此，对于 a 而言，它将在 2021-07-29 13:36:15、2021-07-29 12:36:15 之间，对于 b 而言，它将在 2021-07-29 11:39:15、2021-07- 之间29 时 11 分 36 分 15 秒。查询应该足够高效，因为表中有超过 200 万条记录。我愿意添加索引以提高性能。

标签： mysqlsql

解决方案

您可以使用row_number()和聚合：

select uuid,
       max(server_time) as latest_server_time,
       min(server_time) as penultimate_server_time,
       timestampdiff(second, min(server_time), max(server_time)) as diff_seconds
from (select t.*,
             row_number() over (partition by uuid order by server_time desc) as seqnum
      from t
     ) t
where seqnum <= 2
group by uuid
having count(*) >= 2;

您可能需要在(uuid, server_time desc).

推荐阅读