如何用SQL实现类似Python merge_asof的两表匹配：取小于日期的最近行

数据表结构 #

Table 1（业务数据表） #

Table 2（价格历史表） #

需求说明 #

为Table 1的每一行匹配Table 2中相同ID、name且price_dt < 当前行date的最近一条记录。由于数据量极大，不能直接全关联后排序去重，必须先缩减Table 2的行数再关联。

Python实现参考 #

已经用Python的pandas.merge_asof实现该逻辑，代码如下：

DF_A['date'] = pd.to_datetime(DF_A['date'], dayfirst=True)
DF_B['price_dt'] = pd.to_datetime(DF_B['price_dt'], dayfirst=True)

out = pd.merge_asof(DF_A.sort_values('date'), 
                   DF_B.sort_values('price_dt'), 
                   left_on='date', 
                   right_on='price_dt',
                   by=['ID','name'],
                   allow_exact_matches=False)

预期结果 #

SQL实现方案 #

方案1：通用SQL（兼容多数数据库） #

先将字符串日期转为数据库原生日期类型，再通过CROSS APPLY/子查询精准匹配最近的价格记录：

WITH formatted_table1 AS (
    SELECT 
        ID, 
        name, 
        qty, 
        -- 日期转换函数按需替换：
        -- MySQL: STR_TO_DATE(date, '%d/%m/%Y')
        -- PostgreSQL: TO_DATE(date, 'DD/MM/YYYY')
        -- SQL Server: CONVERT(DATE, date, 103)
        STR_TO_DATE(date, '%d/%m/%Y') AS date_val
    FROM table1
),
formatted_table2 AS (
    SELECT 
        ID, 
        name, 
        STR_TO_DATE(price_dt, '%d/%m/%Y') AS price_dt_val,
        price
    FROM table2
)
SELECT 
    t1.ID,
    t1.name,
    t1.qty,
    DATE_FORMAT(t1.date_val, '%d/%m/%Y') AS date,
    DATE_FORMAT(t2.price_dt_val, '%d/%m/%Y') AS price_dt,
    t2.price
FROM formatted_table1 t1
CROSS APPLY (
    SELECT TOP 1 price_dt_val, price
    FROM formatted_table2 t2
    WHERE t2.ID = t1.ID 
      AND t2.name = t1.name 
      AND t2.price_dt_val < t1.date_val
    ORDER BY t2.price_dt_val DESC
) t2;

方案2：PostgreSQL专属优化 #

利用LATERAL JOIN结合索引大幅提升性能，适合大数据量场景：

1. 先创建复合索引（必须）：

CREATE INDEX idx_table2_id_name_pricedt ON table2(ID, name, price_dt DESC);

2. 执行查询：

SELECT 
    t1.ID,
    t1.name,
    t1.qty,
    TO_CHAR(t1.date_val, 'DD/MM/YYYY') AS date,
    TO_CHAR(t2.price_dt_val, 'DD/MM/YYYY') AS price_dt,
    t2.price
FROM (
    SELECT 
        ID, 
        name, 
        qty, 
        TO_DATE(date, 'DD/MM/YYYY') AS date_val
    FROM table1
) t1
LATERAL (
    SELECT TO_DATE(price_dt, 'DD/MM/YYYY') AS price_dt_val, price
    FROM table2
    WHERE table2.ID = t1.ID 
      AND table2.name = t1.name 
      AND TO_DATE(price_dt, 'DD/MM/YYYY') < t1.date_val
    ORDER BY price_dt DESC
    LIMIT 1
) t2;

方案3：MySQL 8.0+ 窗口函数优化 #

通过分组取最大日期的方式，避免全表扫描：

WITH formatted_table1 AS (
    SELECT 
        ID, 
        name, 
        qty, 
        STR_TO_DATE(date, '%d/%m/%Y') AS date_val
    FROM table1
),
formatted_table2 AS (
    SELECT 
        ID, 
        name, 
        STR_TO_DATE(price_dt, '%d/%m/%Y') AS price_dt_val,
        price
    FROM table2
),
matched_dates AS (
    SELECT 
        t1.ID,
        t1.name,
        t1.qty,
        t1.date_val,
        MAX(t2.price_dt_val) AS max_price_dt
    FROM formatted_table1 t1
    JOIN formatted_table2 t2 
        ON t1.ID = t2.ID 
        AND t1.name = t2.name 
        AND t2.price_dt_val < t1.date_val
    GROUP BY t1.ID, t1.name, t1.qty, t1.date_val
)
SELECT 
    md.ID,
    md.name,
    md.qty,
    DATE_FORMAT(md.date_val, '%d/%m/%Y') AS date,
    DATE_FORMAT(t2.price_dt_val, '%d/%m/%Y') AS price_dt,
    t2.price
FROM matched_dates md
JOIN formatted_table2 t2 
    ON md.ID = t2.ID 
    AND md.name = t2.name 
    AND md.max_price_dt = t2.price_dt_val;

关键优化提示 #

• 必须为Table 2创建(ID, name, price_dt)复合索引，能直接过滤出每组内的目标范围
• 先转换日期为原生类型，避免字符串比较的性能损耗
• 拒绝全表笛卡尔积关联，只获取每个业务记录所需的单条最近价格行

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