如何关联更新周期不同的测量表与元数据表？

首先明确：你设想的“按时间精度从高到低递进匹配”的思路既不符合业务逻辑，也没法在SQL中实现，原因如下：

• SQL的JOIN条件是一次性判定的，不存在“先试A条件不满足再试B条件”的逻辑，强行把多个精度条件混在一起会导致一条测量数据匹配到多条元数据，结果完全混乱。
• 你的逻辑误解了元数据的生效规则：元数据是从更新时刻起生效，直到下一次更新前，不是找和测量时间精度对齐的元数据。比如元数据在2023-03-22 10:00:00更新，下一次在2023-03-29 14:30:00更新，那这期间所有测量数据都应该绑定第一条元数据，和分钟/小时/天精度无关。

正确实现方案（以MySQL为例） #

方法1：子查询匹配最近生效的元数据 #

这是最直观的实现，对每条测量数据，找到元数据表中时间戳≤当前测量时间的最大元数据时间戳，对应的元数据就是当前生效的：

SELECT 
    m.timestamp,
    m.value,
    meta.*
FROM 
    Measurements m
LEFT JOIN 
    MetaData meta ON meta.timestamp = (
        SELECT MAX(timestamp) 
        FROM MetaData 
        WHERE timestamp <= m.timestamp
    )

方法2：窗口函数划分生效区间（MySQL 8.0+支持） #

先给每条元数据计算出下一次更新的时间，再把测量数据匹配到对应的时间区间里：

WITH MetaWithRange AS (
    SELECT 
        *,
        LEAD(timestamp) OVER (ORDER BY timestamp) AS next_timestamp
    FROM MetaData
)
SELECT 
    m.timestamp,
    m.value,
    meta.*
FROM 
    Measurements m
LEFT JOIN 
    MetaWithRange meta 
    ON m.timestamp >= meta.timestamp 
    AND (m.timestamp < meta.next_timestamp OR meta.next_timestamp IS NULL)

最后一条元数据没有下一次更新，所以用meta.next_timestamp IS NULL匹配所有后续的测量数据。

注意事项 #

• 必须给元数据表的timestamp字段创建索引，否则面对大量测量数据时，查询性能会急剧下降。
• 如果元数据表为空，LEFT JOIN会返回元数据相关字段为NULL，可根据业务需求调整为INNER JOIN或添加NULL值处理逻辑。

内容的提问来源于stack exchange，提问作者Aida_Aida