PostgreSQL中如何统计两个数组间的元素匹配数量？

解决方案1：使用unnest和聚合查询 #

无需自定义函数，直接通过展开数组并匹配统计：

SELECT 
    a.id AS id_1, 
    b.id AS id_2,
    COUNT(elem) AS number_of_matches
FROM data a
CROSS JOIN data b
LEFT JOIN unnest(a.val) elem ON elem = ANY(b.val)
WHERE a.id < b.id
GROUP BY a.id, b.id
ORDER BY a.id, b.id;

说明 #

• CROSS JOIN生成所有id对，a.id < b.id过滤掉重复对比（如避免同时出现1-2和2-1的组合）
• LEFT JOIN unnest(a.val)展开第一个数组的元素，elem = ANY(b.val)判断元素是否存在于第二个数组
• COUNT(elem)自动统计匹配元素数量，无匹配时返回0

解决方案2：自定义数组交集长度函数 #

如果需要更简洁的查询语句，可先定义一个专用函数：

CREATE OR REPLACE FUNCTION array_intersection_length(arr1 text[], arr2 text[])
RETURNS integer AS $$
SELECT COUNT(*)
FROM unnest(arr1) elem
WHERE elem = ANY(arr2);
$$ LANGUAGE sql IMMUTABLE;

然后调用函数完成查询：

SELECT 
    a.id AS id_1, 
    b.id AS id_2,
    array_intersection_length(a.val, b.val) AS number_of_matches
FROM data a
JOIN data b ON a.id < b.id
ORDER BY a.id, b.id;

说明 #

• 函数array_intersection_length接收两个文本数组，展开第一个数组后统计存在于第二个数组中的元素数量
• IMMUTABLE标记表示函数结果仅依赖输入参数，可提升查询优化效率

最终结果 #

执行上述任意方案，都会得到目标结果：

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