如何使用SQL JOIN将不同频率的传感器数据按时间戳对齐？-SQL-PHP中文网

直接用on sensor_a.ts = sensor_b.ts会丢数据，因传感器采样频率不同导致时间戳几乎不等；应改用最近邻匹配（如lateral+interval范围查询）并统一时间精度、确保时区一致、添加索引。

如何使用sql join将不同频率的传感器数据按时间戳对齐？

为什么直接用 `ON sensor_a.ts = sensor_b.ts` 会丢数据

传感器采样频率不同（比如温度每秒1次、加速度每毫秒10次），时间戳几乎不可能完全相等。用等值连接会把绝大多数记录过滤掉，结果集远小于任一表行数。

实际要的是“找离目标时间最近的那条记录”，不是“找完全相等的时间”。这本质是范围关联，不是等值关联。

别用 =，改用 BETWEEN 或窗口函数 + LAG/LEAD
先统一时间精度：把毫秒级转成秒级（FLOOR(UNIX_TIMESTAMP(ts) * 1000) / 1000）再对齐，否则索引失效
注意时区：确保所有表的 ts 字段是同一时区的 DATETIME 或 TIMESTAMP，别混用

用 `LEFT JOIN + LATERAL` 实现最近邻匹配（PostgreSQL / MySQL 8.0+）

LATERAL 允许右侧子查询引用左侧表字段，天然适合“为每一行找最近的另一条记录”这种场景。

示例：把 10Hz 的加速度数据按秒对齐到 1Hz 的温湿度数据

Synthesys

Synthesys是一款提供 AI 配音、数字人视频和图像生成的内容创作平台。

下载

SELECT t.ts AS temp_ts, t.temp, a.ax, a.ay
FROM temp_sensor t
LEFT JOIN LATERAL (
  SELECT ax, ay 
  FROM acc_sensor a 
  WHERE a.ts BETWEEN t.ts - INTERVAL '0.5 SECOND' AND t.ts + INTERVAL '0.5 SECOND'
  ORDER BY ABS(TIMESTAMPDIFF(MICROSECOND, t.ts, a.ts))
  LIMIT 1
) a ON TRUE;

INTERVAL '0.5 SECOND' 是搜索窗口，宽度取决于两个传感器最大可能偏移（比如采样不同步导致 ±300ms 偏差）
TIMESTAMPDIFF(MICROSECOND, ...) 算微秒级误差，比 ABS(a.ts - t.ts) 更可靠（避免 datetime 减法溢出）
必须给 a.ts 加索引：CREATE INDEX idx_acc_ts ON acc_sensor(ts);

在 SQLite 或旧版 MySQL 中用自连接模拟最近邻

没有 LATERAL 时，得靠相关子查询或自连接，性能差但兼容性好。

关键点是避免全表扫描：用 WHERE 限制范围后再排序取 Top 1

SELECT t.ts, t.temp,
  (SELECT a.ax 
   FROM acc_sensor a 
   WHERE a.ts BETWEEN datetime(t.ts, '-0.5 seconds') AND datetime(t.ts, '+0.5 seconds')
   ORDER BY abs(strftime('%s', a.ts) - strftime('%s', t.ts)) 
   LIMIT 1) AS ax
FROM temp_sensor t;

SQLite 的 strftime('%s', ...) 返回秒级 Unix 时间戳，别用 datetime 直接减——它返回字符串
MySQL 5.7 可用 UNIX_TIMESTAMP(a.ts) - UNIX_TIMESTAMP(t.ts)，但务必确保字段类型是 DATETIME，不是 VARCHAR
如果某秒没找到加速度数据，该字段为 NULL，不是报错