Pandas merge_asof带其他列条件合并及pandasql报错排查

需求是将rules表的actions列合并到原始df中，合并需满足两个条件：

• 数值区间匹配：df.values >= rules.lower 且 df.values < rules.upper
• 日期匹配：df每行的日期，匹配rules中早于该日期、且距离该日期最近的规则日期

测试用的构造数据代码如下：

import pandas as pd
df = pd.DataFrame({"date": ["2022-05-15", "2022-05-20", "2022-05-25", "2022-05-30"],
                   "values": [10, 20, 30, 80]})
df["date"] = pd.to_datetime(df["date"])

rules = pd.DataFrame({"lower": [0, 25, 50, 75, 0],
                      "upper": [25, 50, 75, float("inf"), 25],
                      "actions": [5, 10, 15, 20, 8],
                      "date": ["2022-01-01", "2022-01-01", "2022-01-01", "2022-01-01", "2022-05-18"]})
rules["date"] = pd.to_datetime(rules["date"])

报错核心原因是pandasql底层基于SQLite引擎实现，而你SQL里用的DISTINCT ON (...)是PostgreSQL独有的语法，SQLite原生不支持该语法，因此触发语法错误。

如果要继续用pandasql实现，可以改用窗口函数改写SQL，兼容SQLite语法：

from pandasql import sqldf
sql = """
WITH matched_records AS (
    SELECT 
        df.date AS date,
        df.values,
        rules.actions,
        ROW_NUMBER() OVER (
            PARTITION BY df.date 
            ORDER BY rules.date DESC
        ) AS row_rank
    FROM df
    LEFT JOIN rules
    ON df.date >= rules.date 
       AND df.values >= rules.lower 
       AND df.values < rules.upper
)
SELECT date, values, actions
FROM matched_records
WHERE row_rank = 1
"""
result = sqldf(sql)

对于这类条件合并场景，用pandas原生接口性能远高于pandasql，尤其数据量较大时优势明显，实现逻辑如下：

# 1. 笛卡尔积关联两表，过滤掉不满足数值区间、日期先后条件的记录
matched = df.merge(rules, how="cross", suffixes=("", "_rule"))
matched = matched[
    (matched["values"] >= matched["lower"])
    & (matched["values"] < matched["upper"])
    & (matched["date"] >= matched["date_rule"])
]

# 2. 对每个df的日期行，保留规则日期最新的那条记录，即为匹配结果
result = matched.sort_values(
    by=["date", "date_rule"], 
    ascending=[True, False]
).drop_duplicates(subset=["date"])[["date", "values", "actions"]]

运行后得到的结果和预期完全一致：

优化提示：如果数据量极大，笛卡尔积会产生过多中间数据，可以先用pd.IntervalIndex构建数值区间索引，提前完成数值区间匹配，减少后续计算的数据量。

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