注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

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

2011-11-02 22:24:09|  分类: Postgres基础 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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


一 需求演示
--1.1测试表
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 数组含有相同元素的记录,相同的元素越多,我们
           就认为这两个元素越相似,并根据相似度降序排序。
 
--1.2 找出与 id=1 的 phone 数组含有相同的元素的记录 
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模块比较 int4[] 的数组类型

--2.1 安装 intarray 模块
mydb=# create extension intarray;
CREATE EXTENSION   
     
 备注:intarray模块里有个 "&" 函数,可以找到数组元素的相同部分, 具体信息可查阅手册
          http://www.postgresql.org/docs/9.1/static/intarray.html

--2.2 & 操作符使用     
mydb=> select array[1,2,3] & array[1,2];
 ?column?
----------
 {1,2}
(1 row)

--2.3 不支持 int8 类型的数组
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 数组类型。

--2.4 源码 & 操作符代码
CREATE OPERATOR & (
        LEFTARG = _int4,
        RIGHTARG = _int4,
        COMMUTATOR = &,
        PROCEDURE = _int_inter
);
   备注:可以在 $PGHOME/share/extension 目录下查阅 intarray--1.0.sql 文件。


方法二:创建 intersection 函数,对 int8[] 数组类型进行比较
--3.1 create function
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;

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


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

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


四 性能测试
--4.1创建测试表并插入数据
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)


--4.2 查询SQL
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)


--4.3 查看执行计划

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)


--4.4创建 gin 索引
mydb=>  create index concurrently idx_array_test_phone_list on array_test using gin (phone_list);
CREATE INDEX     
     
      
--4.5 再次查看PLAN
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  毫秒左右,效率显著提高。
     

五 参考
http://blog.163.com/digoal@126/blog/static/163877040201192624726272/
http://www.itfingers.com/Question/756871/postgres-function-to-return-the-intersection-of-2-arrays/zh
http://www.postgresql.org/docs/9.1/static/intarray.html
http://www.postgresql.org/docs/9.1/static/indexes-types.html

  评论这张
 
阅读(27120)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016