SQL Server多条件INNER JOIN查询同地点价差5%重复产品问题

业务背景 #

• 每个产品都带有LatLng属性，业务运行过程中可能产生难以识别的重复产品，仅价格或图片存在小幅差异。
• 需求：按地点分组，对比同地点内产品的ProductType和Price，将匹配结果标注在地图上，以便更轻松地发现重复产品。
• 查询规则：先计算每个产品价格的5%作为价格浮动阈值，其他产品价格在该阈值的加减范围内即可视为粗略匹配的重复产品。

Products表结构及样例数据 #

预期结果规则 #

• ABC、DEF、MNO、RST需返回：均为Red Widget、处于同一地点，且价格完全相等或差值在彼此价格的5%以内。
• UVW不予返回：价格超出阈值范围，大概率不属于重复产品。
• JKL和XYZ不予返回：虽均为Blue Widget且价格相同，但所处地点不同。

问题排查与解决方案 #

问题根因 #

1. 不存在跨地点匹配问题：原SQL的JOIN条件中已经明确关联了经纬度相等的记录，不会出现跨地点匹配的情况。
2. 价格匹配逻辑漏洞：原逻辑仅校验了B产品价格是否在A产品的价格浮动范围内，未校验A产品价格是否也在B产品的价格浮动范围内，不符合“差值在彼此价格的5%以内”的规则，会导致大量不符合要求的弱匹配记录被返回。
3. 阈值判断为开区间：原逻辑使用<和>做价格边界判断，刚好等于阈值上限/下限的合法记录会被漏判，建议改为闭区间。

修正后的SQL #

WITH cte AS
(
    SELECT 
        p.ProductID,
        p.ProductType,
        p.Price,
        ROUND(p.Latitude, 3) AS Latitude,
        ROUND(p.Longitude, 3) AS Longitude
    FROM 
        Products p
    WHERE
       p.Latitude IS NOT NULL AND p.Longitude IS NOT NULL 
)
SELECT 
    DISTINCT a.ProductID,
    a.Price,
    a.Latitude, 
    a.Longitude
FROM 
    cte a
INNER JOIN cte b 
    ON a.ProductID <> b.ProductID
    AND a.Latitude = b.Latitude 
    AND a.Longitude = b.Longitude
    AND a.ProductType = b.ProductType
    -- 双向校验价格在彼此的5%浮动范围内
    AND b.Price BETWEEN a.Price * 0.95 AND a.Price * 1.05
    AND a.Price BETWEEN b.Price * 0.95 AND b.Price * 1.05

验证效果 #

针对样例数据执行修正后的SQL，仅会返回ABC、DEF、MNO、RST四条符合要求的记录，符合预期结果。

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