MySQL内连接中匹配最接近时间的实现方法

要实现将production表的每条记录与crypto表中对应加密货币的最接近时间的价格记录关联，我们需要先处理货币代码映射（比如production里的SIA对应crypto里的SC），再匹配最近时间的价格记录，下面提供两种可行的方案：

方法一：使用窗口函数（推荐，MySQL 8.0+版本适用） #

窗口函数可以高效地对每条production记录的匹配结果进行排序，直接取时间最近的那条：

WITH mapped_production AS (
    -- 统一货币代码，将SIA转为SC，同时过滤crypto中不存在的币种（如CMM）
    SELECT 
        id,
        CASE currency 
            WHEN 'SIA' THEN 'SC'
            ELSE currency
        END AS crypto_code,
        date_hour,
        bal_conf
    FROM production
    WHERE currency IN ('ETH', 'SIA', 'BTM', 'BTC')
),
ranked_crypto AS (
    -- 关联币种，按时间差绝对值排序，标记每条production对应的最近价格记录
    SELECT 
        mp.id AS production_id,
        mp.crypto_code,
        mp.date_hour AS production_date,
        mp.bal_conf,
        c.id AS crypto_id,
        c.date_hour AS crypto_date,
        c.price_usd,
        -- 按时间差从小到大排名，排名1的就是最近的记录
        ROW_NUMBER() OVER (
            PARTITION BY mp.id 
            ORDER BY ABS(TIMESTAMPDIFF(SECOND, mp.date_hour, c.date_hour)) ASC
        ) AS rn
    FROM mapped_production mp
    JOIN crypto c ON mp.crypto_code = c.crypto_code
)
-- 筛选出每个production对应的最近价格记录
SELECT 
    production_id,
    crypto_code,
    production_date,
    bal_conf,
    crypto_id,
    crypto_date,
    price_usd
FROM ranked_crypto
WHERE rn = 1;

代码说明： #

1. mapped_production CTE：先统一货币代码格式，同时过滤掉crypto表中没有对应数据的币种（比如CMM），避免无效关联。
2. ranked_crypto CTE：将处理后的production表与crypto表按币种关联，用ROW_NUMBER()窗口函数对每条production的所有匹配结果按时间差绝对值排序，排名第一的就是时间最接近的价格记录。
3. 最后筛选排名为1的记录，得到最终关联结果。

方法二：使用关联子查询（兼容低版本MySQL） #

如果你的MySQL版本低于8.0，不支持窗口函数，可以用关联子查询来找到每条production记录对应的最小时间差：

SELECT 
    p.id AS production_id,
    CASE p.currency 
        WHEN 'SIA' THEN 'SC'
        ELSE p.currency
    END AS crypto_code,
    p.date_hour AS production_date,
    p.bal_conf,
    c.id AS crypto_id,
    c.date_hour AS crypto_date,
    c.price_usd
FROM production p
JOIN crypto c ON 
    CASE p.currency 
        WHEN 'SIA' THEN 'SC'
        ELSE p.currency
    END = c.crypto_code
WHERE 
    p.currency IN ('ETH', 'SIA', 'BTM', 'BTC')
    -- 匹配当前production记录对应的最小时间差的crypto记录
    AND ABS(TIMESTAMPDIFF(SECOND, p.date_hour, c.date_hour)) = (
        SELECT MIN(ABS(TIMESTAMPDIFF(SECOND, p_inner.date_hour, c_inner.date_hour)))
        FROM production p_inner
        JOIN crypto c_inner ON 
            CASE p_inner.currency 
                WHEN 'SIA' THEN 'SC'
                ELSE p_inner.currency
            END = c_inner.crypto_code
        WHERE p_inner.id = p.id
    );

代码说明： #

• 同样先处理货币代码映射和无效币种过滤。
• 关联子查询会计算当前production记录对应币种的所有crypto记录的最小时间差，主查询则匹配这个最小时间差的记录。
• 注意：如果存在两条crypto记录与production的时间差完全相同，这个查询会返回多条结果；如果要避免这种情况，可以在子查询的MIN后增加额外的排序条件（比如优先取最新的时间）。

额外注意事项 #

• 时间差计算用了TIMESTAMPDIFF(SECOND, ...)，可以精确到秒；如果不需要这么精确，也可以改为MINUTE单位。
• 内连接会自动排除production表中没有对应crypto数据的记录（比如CMM），符合需求。
• 如果需要保留所有production记录（包括无匹配的），可以将JOIN改为LEFT JOIN，但结果中无匹配的价格字段会显示NULL。

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