PostgresQL：获取两个数组之间的对应数量

发布于08月29日

我想在PostgresSQL中比较并获得两个数组之间的对应位置数.

例如: 给出这张桌子

id	array
1	a, b, d
2	a, e
3	a, b, d, e
4	b, c, d

我想要一个对比表:

id_1	id_2	number_of_matches
1	2	1
1	3	3
1	4	2
2	3	2
2	4	0
3	4	2

每个数组中没有重复的值，数组中项的顺序并不重要.

我发现重叠运算符(&amp；&amp；)有here个，但它只在两个数组之间是否存在对应关系时返回布尔值，而不是返回对应关系的数量.

CREATE TABLE data (id integer, val text[]);
INSERT INTO data VALUES (1, '{a,b,d}');
INSERT INTO data VALUES (2, '{a,e}');
INSERT INTO data VALUES (3, '{a,b,d,e}');
INSERT INTO data VALUES (4, '{b,c,d}');
SELECT a.id id_1, b.id id_2, a.val && b.val match 
FROM data a, data b
WHERE a.id < b.id;

退货

id_1	id_2	match
1	2	t
1	3	t
1	4	t
2	3	t
2	4	f
3	4	t

推荐答案

demo:db<>fiddle个

WITH normalized AS (                      -- 2
    SELECT
        id,
        unnest(val) as array_element      -- 1
    FROM data
)
SELECT
    n1.id,
    n2.id,
    COUNT(*)
FROM normalized n1
JOIN normalized n2                        -- 3
    ON (n1.array_element = n2.array_element AND n1.id <> n2.id)
GROUP BY n1.id, n2.id                     -- 4
ORDER BY n1.id, n2.id