如何在PostgreSQL中实现JSONB列的匹配百分比计算？

1. 先明确核心匹配逻辑 #

对应你给出的示例，核心规则可以拆解为：

• 从雇主的JSONB中提取值为"1"的所有键作为employer_keys
• 从候选人的JSONB中提取值为"1"的所有键作为candidate_keys
• 计算雇主独有键的数量：即employer_keys中不在candidate_keys里的键的个数
• 匹配度公式：1 - (独有键数 / 雇主总键数)（额外处理雇主无有效技能的边界情况，避免除以0）

2. 创建可复用的自定义SQL函数 #

我们可以用PostgreSQL的PL/pgSQL写一个函数封装这个逻辑，方便后续查询调用：

CREATE OR REPLACE FUNCTION compare_json(employer jsonb, candidate jsonb)
RETURNS numeric AS $$
DECLARE
    employer_keys text[];
    candidate_keys text[];
    employer_key_count integer;
    remainder_count integer;
BEGIN
    -- 提取雇主JSONB中值为'1'的键数组
    SELECT array_agg(key) INTO employer_keys
    FROM jsonb_each_text(employer)
    WHERE value = '1';
    
    -- 提取候选人JSONB中值为'1'的键数组
    SELECT array_agg(key) INTO candidate_keys
    FROM jsonb_each_text(candidate)
    WHERE value = '1';
    
    employer_key_count = cardinality(employer_keys);
    
    -- 处理雇主无有效技能的边界情况（避免除以0）
    IF employer_key_count = 0 THEN
        RETURN 0.0;
    END IF;
    
    -- 计算雇主独有键的数量
    remainder_count = cardinality(array(SELECT unnest(employer_keys) EXCEPT SELECT unnest(candidate_keys)));
    
    -- 计算匹配度并保留两位小数（可根据需求调整精度）
    RETURN round((1 - (remainder_count::numeric / employer_key_count)) * 100, 2) / 100;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

3. 扩展查询输出所需字段 #

假设你的表结构是：

• employers表：包含id（雇主ID）、skills（存储技能的JSONB字段）
• candidates表：包含id（候选者ID）、skills（存储技能的JSONB字段）

如果要查询指定雇主（比如ID=1）和所有候选者的匹配情况，输出候选者ID、匹配度、匹配技能列表，可以用下面的SQL：

SELECT
    c.id AS candidate_id,
    compare_json(e.skills, c.skills) AS match_score,
    -- 生成双方匹配的技能列表（都有且值为1的键）
    array(
        SELECT unnest(employer_keys)
        INTERSECT
        SELECT unnest(candidate_keys)
    ) AS skills_match
FROM
    employers e
CROSS JOIN
    candidates c
WHERE
    e.id = 1; -- 替换为你要匹配的雇主ID

4. 性能优化建议 #

• 若数据量较大，给JSONB字段创建GIN索引可以加速键的筛选操作：

  CREATE INDEX idx_employers_skills_gin ON employers USING GIN (skills);
  CREATE INDEX idx_candidates_skills_gin ON candidates USING GIN (skills);
  

• 可以根据业务需求调整匹配度的精度，比如去掉round函数直接保留原始小数，或者调整保留位数。
• 边界逻辑可自定义：比如雇主无有效技能时，函数当前返回0.0，你可以改成返回NULL或者其他符合业务预期的值。

验证示例 #

用你给出的测试数据调用函数：

SELECT compare_json(
    '{"autism": "1", "social": "1", "dementia": "0", "domestic": "1"}'::jsonb,
    '{"autism": "0", "social": "1", "dementia": "0", "domestic": "1"}'::jsonb
);

返回结果为0.67（对应66.67%，四舍五入后），和你的示例逻辑完全一致。

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