PostgreSQL: 如何获取一维数组的相同元素并根据相似度排序

今天开发有个需求,表中有一个列为一维数组类型,现在需要找出表中具有相同元素的数据,描述起来可能有点费力,下面举个例子就明白了。

需求演示

测试表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mydb=> /d test_array;  
Table "mydb.test_array"
Column | Type | Modifiers
--------+----------+-----------
id | integer |
phone | bigint[] |

mydb=> select * from test_array;
id | phone
----+-------------
1 | {1,2}
2 | {1,2,3}
3 | {2,3}
4 | {1,2,3,4}
5 | {1,2,3,4,5}
6 | {4,5,6}

备注: 给出一个 id, 然后找出与这个 id 对应的 phone 数组含有相同元素的记录,相同的元素越多,我们就认为这两个元素越相似,并根据相似度降序排序。

找出与 id=1 的 phone 数组含有相同的元素的记录

1
2
3
4
5
6
7
mydb=> select id,phone from test_array where phone && (select phone from test_array where id=1) and id!=1;  
id | phone
----+-------------
2 | {1,2,3}
3 | {2,3}
4 | {1,2,3,4}
5 | {1,2,3,4,5}

备注:上面SQL虽然能成功找出具有相同元素的记录,但是不能根据相似度排序,今天总结了下,有以下方法实现上面功能。

方法一:使用 intarray 模块

使用intarray模块比较 int4[] 的数组类型。

安装 intarray 模块

1
2
mydb=# create extension intarray;  
CREATE EXTENSION

备注:intarray模块里有个 “&” 函数,可以找到数组元素的相同部分, 具体信息可查阅手册 http://www.postgresql.org/docs/9.1/static/intarray.html

& 操作符使用

1
2
3
4
5
mydb=> select array[1,2,3] & array[1,2];  
?column?
----------
{1,2}
(1 row)

2.3 不支持 int8 类型的数组

1
2
3
4
5
mydb=> select array[11111111111,2,3] & array[11111111111,2];  
ERROR: operator does not exist: bigint[] & bigint[]
LINE 1: select array[11111111111,2,3] & array[11111111111,2];
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

备注:intarray 模块虽然能比较并获得数组的相同元素,但仅支持 int4 数组类型。

& 操作符代码

1
2
3
4
5
6
CREATE OPERATOR & (  
LEFTARG = _int4,
RIGHTARG = _int4,
COMMUTATOR = &,
PROCEDURE = _int_inter
);

备注:可以在 $PGHOME/share/extension 目录下查阅intarray–1.0.sql 文件。

方法二:自定义函数

创建 intersection 函数,对 int8[] 数组类型进行比较
创建函数,如下:

1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION intersection(anyarray, anyarray) RETURNS anyarray as $$  
SELECT ARRAY(
SELECT $1[i]
FROM generate_series( array_lower($1, 1), array_upper($1, 1) ) i
WHERE ARRAY[$1[i]] && $2
);
$$ language sql;

备注:这里我们开发组的一名同事找到的,感谢这位同事。

测试

1
2
3
4
5
mydb=> select intersection(array[11111111111,2,3],array[11111111111,2,3]);  
intersection
-------------------
{11111111111,2,3}
(1 row)

备注:这次果然没报错了,这种方法虽然功能实现了,但效率如何呢?下面简单测试下。

性能测试

创建测试表并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mydb=> create table array_test (skyid serial primary key,phone_list int8[]);  
NOTICE: CREATE TABLE will create implicit sequence "array_test_skyid_seq" for serial column "array_test.skyid"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "array_test_pkey" for table "array_test"
CREATE TABLE

mydb=> insert into array_test(phone_list) select regexp_split_to_array(id1||';'||id2||';'||id3||';'||id4,';')::int8[] from phone ;
INSERT 0 100000

mydb=> select * from array_test limit 10;
skyid | phone_list
-------+---------------
1 | {1,2,3,4}
2 | {2,3,4,5}
3 | {3,4,5,6}
4 | {4,5,6,7}
5 | {5,6,7,8}
6 | {6,7,8,9}
7 | {7,8,9,10}
8 | {8,9,10,11}
9 | {9,10,11,12}
10 | {10,11,12,13}
(10 rows)

执行SQL

1
2
3
4
5
6
7
8
9
10
mydb=> select t2.skyid,t2.phone_list, array_length(intersection(t1.phone_list,t2.phone_list),1)  
mydb-> from array_test t1, array_test t2
mydb-> where t1.skyid=1 and t1.skyid!=t2.skyid and t1.phone_list && t2.phone_list
mydb-> ;
skyid | phone_list | array_length
-------+------------+--------------
2 | {2,3,4,5} | 3
3 | {3,4,5,6} | 2
4 | {4,5,6,7} | 1
(3 rows)

查看执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mydb=> explain analyze select t2.skyid,t2.phone_list, array_length(intersection(t1.phone_list,t2.phone_list),1)  
mydb-> from array_test t1, array_test t2
mydb-> where t1.skyid=8 and t1.skyid!=t2.skyid and t1.phone_list && t2.phone_list
mydb-> order by array_length(intersection(t1.phone_list,t2.phone_list),1) desc;
QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------
Sort (cost=3743.94..3745.19 rows=500 width=110) (actual time=1279.393..1279.423 rows=6 loops=1)
Sort Key: (array_length(intersection(t1.phone_list, t2.phone_list), 1))
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=0.00..3721.53 rows=500 width=110) (actual time=0.651..1279.292 rows=6 loops=1)
Join Filter: ((t1.skyid <> t2.skyid) AND (t1.phone_list && t2.phone_list))
-> Index Scan using array_test_pkey on array_test t1 (cost=0.00..8.28 rows=1 width=57) (actual time=0.236..0.275 rows=1 loops=1)
Index Cond: (skyid = 8)
-> Seq Scan on array_test t2 (cost=0.00..2087.00 rows=100000 width=57) (actual time=0.013..608.045 rows=100000 loops=1)
Total runtime: 1279.619 ms
(9 rows)

创建 gin 索引

1
2
mydb=> create index concurrently idx_array_test_phone_list on array_test using gin (phone_list);  
CREATE INDEX

再次查看PLAN

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mydb=> explain analyze select t2.skyid,t2.phone_list, array_length(intersection(t1.phone_list,t2.phone_list),1)  
mydb-> from array_test t1, array_test t2
mydb-> where t1.skyid=7 and t1.skyid!=t2.skyid and t1.phone_list && t2.phone_list
mydb-> order by array_length(intersection(t1.phone_list,t2.phone_list),1) desc;

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------
Sort (cost=1070.18..1071.43 rows=500 width=110) (actual time=1.185..1.215 rows=6 loops=1)
Sort Key: (array_length(intersection(t1.phone_list, t2.phone_list), 1))
Sort Method: quicksort Memory: 17kB
-> Nested Loop (cost=19.88..1047.77 rows=500 width=110) (actual time=0.854..1.117 rows=6 loops=1)
Join Filter: (t1.skyid <> t2.skyid)
-> Index Scan using array_test_pkey on array_test t1 (cost=0.00..8.28 rows=1 width=57) (actual time=0.231..0.239 rows=1 loops=1)
Index Cond: (skyid = 7)
-> Bitmap Heap Scan on array_test t2 (cost=19.88..905.74 rows=500 width=57) (actual time=0.226..0.264 rows=7 loops=1)
Recheck Cond: (t1.phone_list && phone_list)
-> Bitmap Index Scan on idx_array_test_phone_list (cost=0.00..19.75 rows=500 width=0) (actual time=0.123..0.123 rows=7 loops=1)
Index Cond: (t1.phone_list && phone_list)
Total runtime: 1.399 ms
(12 rows)

备注:由于测试是在虚拟机上进行,数据量并不大,但从上面看出上面的SQL在创建了 gin 类型索引后,执行时间在1.3 毫秒左右,效率显著提高。

参考

原创文章,作者:1402239773,如若转载,请注明出处:https://blog.ytso.com/236439.html

(0)
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论