基于可信度动态复合键的SQL行关联与产品去重方案

核心逻辑说明 #

这个场景属于带可信度优先级约束的时序实体匹配问题，核心是按扫描时间先后顺序处理记录，严格遵守「高优先级键优先匹配、可信度只升不降」的规则，不需要复杂的图算法，用标准SQL即可落地。
首先统一可信等级量化标准，方便做降级判断：

• 等级3：refk非空（最高可信度）
• 等级2：refk为空、refj非空
• 等级1：refk、refj均为空，仅refi非空（最低可信度）
  所有匹配逻辑都基于这个等级做校验，确保不会出现可信度降级的合并。

匹配规则落地逻辑 #

处理顺序严格按扫描时间t从早到晚逐行处理，每条新记录按以下优先级和已有产品做匹配：

1. 第一优先级匹配refk：找所有已识别产品中，refk非空且和当前记录refk相等、且产品当前最高可信等级 ≤ 当前记录可信等级的对象，命中就合并到该产品
2. 第二优先级匹配refj：如果第一步未命中，找所有已识别产品中，refj非空且和当前记录refj相等、且产品当前最高可信等级 ≤ 当前记录可信等级的对象，命中就合并到该产品
3. 第三优先级匹配refi：如果前两步都未命中，找所有已识别产品中，refi和当前记录refi相等、且产品当前最高可信等级 ≤ 当前记录可信等级的对象，命中就合并到该产品
4. 以上都没命中：当前记录属于新的独立产品

注：匹配时加的「产品最高可信等级 ≤ 当前记录等级」条件，直接实现了规则2的禁止降级要求；从高到低的匹配顺序，天然满足规则1的高优先级键优先、规则3的多候选时选高可信度共有键的要求。

现代SQL实现代码（支持MySQL8+、PostgreSQL、Spark SQL等） #

用递归CTE+LATERAL关联实现逐行匹配，最终输出结果可以直接给每行记录关联唯一产品标识prod_id，统计唯一产品数时直接COUNT(DISTINCT prod_id)即可。

WITH record_base AS (
    SELECT 
        rid, -- 扫描记录表的自增唯一主键
        t,
        refi,
        refj,
        refk,
        -- 计算当前记录自身的可信等级
        CASE 
            WHEN refk IS NOT NULL THEN 3
            WHEN refj IS NOT NULL THEN 2
            ELSE 1
        END AS curr_level,
        -- 按扫描时间生成处理顺序，时间相同可按rid兜底排序
        ROW_NUMBER() OVER(ORDER BY t ASC, rid ASC) AS process_order
    FROM product_scan_record -- 替换成实际的表名
),
recursive_match AS (
    -- 初始化：最早的一条记录作为第一个产品
    SELECT 
        rid, t, refi, refj, refk, curr_level, process_order,
        rid AS prod_id,
        refi AS prod_latest_refi,
        refj AS prod_latest_refj,
        refk AS prod_latest_refk,
        curr_level AS prod_max_level
    FROM record_base 
    WHERE process_order = 1

    UNION ALL

    -- 逐行匹配后续记录
    SELECT 
        curr.rid, curr.t, curr.refi, curr.refj, curr.refk, curr.curr_level, curr.process_order,
        -- 按优先级取匹配到的产品ID，无匹配则生成新产品ID
        COALESCE(match_refk.prod_id, match_refj.prod_id, match_refi.prod_id, curr.rid) AS prod_id,
        -- 更新产品维度的最新ref值，保留已有的高优先级非空ref
        COALESCE(match_refk.prod_latest_refi, match_refj.prod_latest_refi, match_refi.prod_latest_refi, curr.refi) AS prod_latest_refi,
        COALESCE(curr.refj, match_refk.prod_latest_refj, match_refj.prod_latest_refj, match_refi.prod_latest_refj) AS prod_latest_refj,
        COALESCE(curr.refk, match_refk.prod_latest_refk, match_refj.prod_latest_refk, match_refi.prod_latest_refk) AS prod_latest_refk,
        -- 更新产品最高可信等级，只升不降
        GREATEST(
            COALESCE(match_refk.prod_max_level, match_refj.prod_max_level, match_refi.prod_max_level, 0), 
            curr.curr_level
        ) AS prod_max_level
    FROM record_base curr
    -- 第一优先级：匹配refk
    LEFT JOIN LATERAL (
        SELECT prod_id, prod_latest_refi, prod_latest_refj, prod_latest_refk, prod_max_level
        FROM recursive_match prev
        WHERE prev.process_order < curr.process_order
          AND prev.prod_latest_refk IS NOT NULL
          AND prev.prod_latest_refk = curr.refk
          AND prev.prod_max_level <= curr.curr_level
        LIMIT 1
    ) match_refk ON TRUE
    -- 第二优先级：匹配refj（仅当refk未匹配时执行）
    LEFT JOIN LATERAL (
        SELECT prod_id, prod_latest_refi, prod_latest_refj, prod_latest_refk, prod_max_level
        FROM recursive_match prev
        WHERE prev.process_order < curr.process_order
          AND prev.prod_latest_refj IS NOT NULL
          AND prev.prod_latest_refj = curr.refj
          AND prev.prod_max_level <= curr.curr_level
          AND match_refk.prod_id IS NULL
        LIMIT 1
    ) match_refj ON TRUE
    -- 第三优先级：匹配refi（仅当refk、refj都未匹配时执行）
    LEFT JOIN LATERAL (
        SELECT prod_id, prod_latest_refi, prod_latest_refj, prod_latest_refk, prod_max_level
        FROM recursive_match prev
        WHERE prev.process_order < curr.process_order
          AND prev.prod_latest_refi = curr.refi
          AND prev.prod_max_level <= curr.curr_level
          AND match_refk.prod_id IS NULL
          AND match_refj.prod_id IS NULL
        LIMIT 1
    ) match_refi ON TRUE
    WHERE curr.process_order > 1
)
-- 输出结果：每行带唯一产品标识prod_id
SELECT * FROM recursive_match;

规则符合性验证 #

对照给出的4个示例，上述逻辑可以完全满足要求：

• 示例1：三条记录按时间处理，t1生成产品1；t2仅匹配到t1的refi，合并到产品1；t3匹配到产品1的refi，合并到产品1，最终统计为1个产品
• 示例2：t1生成产品1；t2处理时refj和t1相等，优先匹配高优先级的refj，即便refi不同也合并到产品1，最终统计为1个产品
• 示例3：t1生成产品1，最高等级为3；t2自身等级为2，匹配时产品1的最高等级3>2，被降级规则拦截，无法匹配，最终统计为2个产品
• 示例4：t1生成产品1（等级1）；t2未匹配到任何高优先级键，生成产品2（等级2）；t3处理时先匹配refj，命中产品2，直接合并到产品2，不会再走refi匹配逻辑和t1合并，最终统计为2个产品

低版本数据库兼容方案 #

如果使用不支持递归CTE、LATERAL语法的旧版本数据库（比如MySQL 5.x），可以通过存储过程实现相同逻辑：

• 先创建一张临时表，存储已识别产品的ID、最新三个ref值、最高可信等级
• 把所有扫描记录按t升序、rid升序存入游标
• 逐行从游标取记录，按照refk→refj→refi的顺序去临时表查符合等级要求的匹配产品
• 匹配到就更新临时表中对应产品的ref值和最高等级；没匹配到就向临时表插入一条新的产品记录
• 处理完成后，临时表的行数就是唯一产品总数，也可以把临时表的prod_id关联回原扫描记录表给每行打标。

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