如何将New_Price表价格匹配Actual_Price表最近值获取对应产品描述
双表最近价格匹配实现方案
核心逻辑:对New_Price表的每条记录,计算与Actual_Price表所有价格的差值绝对值,取差值最小的记录关联Product_Desc字段即可,若存在多条差值相同的记录,可自定义排序规则取首条。
注:你给出的预期输出中X对应的
ABG为笔误,正确匹配结果应为ABC,2.0与1.2的差值为0.8,远小于与3.4的差值1.4。
SQL 实现(兼容MySQL 8.0+/PostgreSQL/SQL Server等主流支持窗口函数的数据库)
WITH price_diff_calc AS ( SELECT np.Product, np.Price, ap.Product_Desc, -- 按差值升序排序,同差值时按实际价格升序取第一条 ROW_NUMBER() OVER ( PARTITION BY np.Product ORDER BY ABS(np.Price - ap.Price) ASC, ap.Price ASC ) AS match_rank FROM New_Price np CROSS JOIN Actual_Price ap -- 数据量大时可加范围过滤减少计算量,比如仅匹配浮动范围在1以内的价格 -- WHERE ap.Price BETWEEN np.Price - 1 AND np.Price + 1 ) SELECT Product, Price, Product_Desc AS `Product Desc` FROM price_diff_calc WHERE match_rank = 1;
Python Pandas 实现
import pandas as pd # 构造示例表数据 new_price_df = pd.DataFrame({ "Product": ["X", "Z"], "Price": [2.0, 3.0] }) actual_price_df = pd.DataFrame({ "Product_Desc": ["ABC", "EFG"], "Price": [1.2, 3.4] }) # 定义最近值匹配函数 def get_closest_desc(row): diff_series = abs(actual_price_df["Price"] - row["Price"]) closest_idx = diff_series.idxmin() return actual_price_df.loc[closest_idx, "Product_Desc"] # 生成匹配结果 new_price_df["Product Desc"] = new_price_df.apply(get_closest_desc, axis=1) # 输出结果 print(new_price_df)
内容的提问来源于stack exchange,提问作者Margeary Gg
相关产品推荐
相关产品推荐

