时间戳

首页 > 解决方案 > MySQL为每一行加入外部表中的最新行

mysql - MySQL为每一行加入外部表中的最新行

问题描述

我有 2 个表，其中一个是位置（assetid、时间戳和位置），另一个是状态更改（assitid、时间戳和状态）。我正在尝试选择所有位置，加入“当前状态”，该状态由位置时间戳之前的最新状态更改确定（status.timestamp <= location.timestamp）。

Locations
ID  Asset  Timestamp  Lat/Lon
1   A      1000       Lat/Lon
2   A      2000       Lat/Lon
3   A      3000       Lat/Lon

Status
ID  Asset  Timestamp  Status
1   A      1000       active
2   A      2200       sleep

我在找什么：

LocID  Asset  Timestamp  Lat/Lon  Status
1      A      1000       Lat/Lon  active
2      A      2000       Lat/Lon  active
3      A      3000       Lat/Lon  sleep

我知道“加入最近一行”问题已被问了 1000 次，但我对这个问题的解决方案在这里不起作用。在这种情况下，我不只是在寻找最近的行，我需要相对于位置表中的时间戳的最新状态......这超出了我的能力范围。

MariaDB 10.4.12

在此先感谢您的帮助！

编辑：下面使用 row_number() 的建议确实解决了问题，但效率不高。在我的完整数据集上运行查询，它超时。有没有另一种方法来解决这个问题？

标签： mysqljoin

解决方案

在 MySQL 8.x 中，您可以使用ROW_NUMBER()窗口函数来过滤相关行。

例如：

select loc_id, asset, timestamp
from (
  select
    l.id as loc_id,
    l.asset,
    l.timestamp,
    row_number() over(partition by l.asset order by s.timestamp desc) as rn
  from locations l
  join status s on s.asset = l.asset and s.timestamp <= l.timestamp
) x
where rn = 1

whiteatom 编辑： 为了未来的读者 - 这行得通。我只需要更改分区（重新开始编号；就像分组一样）。一旦我将其更改为“按 l.asset、l.timestamp 分区”，它就会在每个新位置的时间戳重新开始编号，并且编号为 1 的行都是正确的。

工作示例：

select loc_id, lts, asset, location, status
from (
  select
    l.id as loc_id,
    l.asset,
    l.timestamp as lts,
    l.location,
    s.status,
    row_number() over(partition by l.asset, l.timestamp order by s.timestamp desc) as rn
  from location l
  join status s on s.asset = l.asset and s.timestamp <= l.timestamp
) x
where rn = 1

推荐阅读