PostgreSQL如何判断两个数组存在至少2个共同匹配元素

PostgreSQL 没有直接实现「共同元素数量大于1返回TRUE」的内置函数，可通过现有基础函数组合实现，以下是两种常用实现方式：

方式1：intarray扩展实现（仅支持整数数组，性能更高） #

需要先开启扩展：

CREATE EXTENSION IF NOT EXISTS intarray;

直接通过交集运算符计算重合元素数判断：

-- 封装为函数方便调用
CREATE OR REPLACE FUNCTION array_multi_match_int(arr1 int[], arr2 int[])
RETURNS boolean AS $$
SELECT COALESCE(array_length(arr1 & arr2, 1), 0) > 1;
$$ LANGUAGE sql IMMUTABLE;

方式2：通用实现（无扩展依赖，支持所有可比较类型数组） #

适用于text、int等所有支持等值比较的数组类型，适用范围最广：

-- 通用多类型匹配函数
CREATE OR REPLACE FUNCTION array_multi_match(arr1 anyarray, arr2 anyarray)
RETURNS boolean AS $$
SELECT COALESCE(COUNT(*), 0) > 1
FROM unnest(arr1) AS elem1
INNER JOIN unnest(arr2) AS elem2 ON elem1 = elem2;
$$ LANGUAGE sql IMMUTABLE;

规则验证 #

所有测试用例均符合给出的匹配规则：

• array_multi_match(ARRAY['a','b','c','d'], ARRAY['a']) → 返回 FALSE
• array_multi_match(ARRAY['a','b','c','d'], ARRAY['a','b']) → 返回 TRUE
• array_multi_match(ARRAY['a','b','c','d'], ARRAY['a','b','c']) → 返回 TRUE
• array_multi_match(ARRAY['a','b','c','d'], ARRAY['a','d']) → 返回 TRUE
• array_multi_match(ARRAY['a','b','c','d'], ARRAY['a','c','e']) → 返回 TRUE
• array_multi_match(ARRAY['a','b','c','d'], ARRAY['a','b','c','d']) → 返回 TRUE
• array_multi_match(ARRAY['a','b','c','d'], ARRAY['a','b','e','d','f']) → 返回 TRUE
• array_multi_match(ARRAY['a','b','c','d'], ARRAY['a','z','e','f','g','h']) → 返回 FALSE

注意事项 #

如果数组内存在重复元素，需要先对数组去重再计算的话，可以在unnest前先对数组做去重处理，示例：

-- 带去重的通用匹配函数
CREATE OR REPLACE FUNCTION array_multi_match_distinct(arr1 anyarray, arr2 anyarray)
RETURNS boolean AS $$
SELECT COALESCE(COUNT(*), 0) > 1
FROM (SELECT DISTINCT unnest(arr1)) AS elem1
INNER JOIN (SELECT DISTINCT unnest(arr2)) AS elem2 ON elem1 = elem2;
$$ LANGUAGE sql IMMUTABLE;

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