开发者社区> 德哥> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

PostgreSQL 多重含义数组检索与条件过滤 (标签1:属性, 标签n:属性) - 包括UPSERT操作如何修改数组、追加数组元素

简介:
+关注继续查看

标签

PostgreSQL , 多重函数数组 , UDF索引 , 过滤 , 文本处理


背景

PG的数组类型,被广泛应用于 画像系统 , 标签系统。

在一些业务重建中,对数组内容的定义往往包含了多重含义,例如即包含了标签本身,又包含了标签的属性(例如 标签值:权值,时间 等)。

那么如何能高效的进行标签的检索,同时又过滤出符合标签加权值的记录呢?

例子

1、建表

create table tbl(id int, info text[]);  

2、写入测试数据

insert into tbl values (1, array['a:100', 'b:10']);  
  
insert into tbl values (2, array['a:15', 'b:20', 'c:99']);  
  
insert into tbl values (3, array['c:78', 'b:100']);  
  
postgres=# select * from tbl;  
 id |       info         
----+------------------  
  1 | {a:100,b:10}  
  2 | {a:15,b:20,c:99}  
  3 | {c:78,b:100}  
(3 rows)  

3、创建UDF1,提取出要查询的标签值(用到了正则匹配)

create or replace function get_label(text[]) returns text[] as $$  
  select array(select substring(unnest($1), '(.*):'));  
$$ language sql strict immutable;  
  
postgres=# select get_label(info) from tbl;  
 get_label   
-----------  
 {a,b}  
 {a,b,c}  
 {c,b}  
(3 rows)  

4、创建UDF1索引

create index idx_tbl1 on tbl using gin (get_label(info));  
  
postgres=# explain select * from tbl where get_label(info) @> array['a'];  
                              QUERY PLAN                                 
-----------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=2.40..3.86 rows=1 width=36)  
   Recheck Cond: (get_label(info) @> '{a}'::text[])  
   ->  Bitmap Index Scan on idx_tbl1  (cost=0.00..2.40 rows=1 width=0)  
         Index Cond: (get_label(info) @> '{a}'::text[])  
(4 rows)  

5、创建UDF2,提取指定标签的加权值(用到了正则匹配,数组下标计算,数组按位置取元素等操作)

create or replace function get_weight(text[], text) returns text as $$  
  select substring($1[array_position(get_label($1), $2)], ':(.*)');  
$$ language sql strict immutable;  
  
  
postgres=# select info, get_weight(info, 'a') from tbl;  
       info       | get_weight   
------------------+------------  
 {a:100,b:10}     | 100  
 {a:15,b:20,c:99} | 15  
 {c:78,b:100}     |   
(3 rows)  

6、查询SQL如下

查询包含标签a,同时权值大于20的记录。

postgres=# select * from tbl where get_label(info) @> array['a'] and get_weight(info, 'a')::float8 >20;  
 id |     info       
----+--------------  
  1 | {a:100,b:10}  
(1 row)  
  
postgres=# explain select * from tbl where get_label(info) @> array['a'] and get_weight(info, 'a')::float8 >20;  
                                      QUERY PLAN                                        
--------------------------------------------------------------------------------------  
 Bitmap Heap Scan on tbl  (cost=2.40..4.12 rows=1 width=36)  
   Recheck Cond: (get_label(info) @> '{a}'::text[])  
   Filter: ((get_weight(info, 'a'::text))::double precision > '20'::double precision)  
   ->  Bitmap Index Scan on idx_tbl1  (cost=0.00..2.40 rows=1 width=0)  
         Index Cond: (get_label(info) @> '{a}'::text[])  
(5 rows)  

UDF功能是不是很赞呢?

UPSERT时,如何修改数组、追加数组元素

https://www.postgresql.org/docs/10/static/functions-array.html

1、追加元素

array_append(anyarray, anyelement)  
  
array_cat(anyarray, anyarray)  
  
array_fill(anyelement, int[], [, int[]])  
  
array_prepend(anyelement, anyarray)  

2、修改元素

array_replace(anyarray, anyelement, anyelement)  

3、删除元素

array_remove(anyarray, anyelement)  

用法举例

insert into tbl values (1, ?) on conflict (id) do update set info=func(tbl.info,?);    
create table tbl1(id int primary key, info int[]);  
  
postgres=# insert into tbl1 values (1, array[1,2,3]) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;   
 id |  info     
----+---------  
  1 | {1,2,3}  
(1 row)  
  
INSERT 0 1  
postgres=# insert into tbl1 values (1, array[1,2,3]) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;   
 id |    info       
----+-------------  
  1 | {1,2,3,100}  
(1 row)  
  
INSERT 0 1  
postgres=# insert into tbl1 values (1, null) on conflict (id) do update set info=array_append(tbl1.info, 100) returning *;   
 id |      info         
----+-----------------  
  1 | {1,2,3,100,100}  
(1 row)  
  
INSERT 0 1  

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
#私藏项目实操分享#【React工作记录八】如何限制ant design的input只能输入数字
#私藏项目实操分享#【React工作记录八】如何限制ant design的input只能输入数字
42 0
Uma
DTCC 2019 | 阿里云TSDB: 教你解锁时序时空数据库的种种黑科技
摘要:阿里云TSDB是阿里自研的一种高性能,低成本,稳定可靠的在线时序时空数据库产品。该产品统一了阿里巴巴集团90%以上的APM数据和事件型数据的存储和计算,并在广泛应用于外部的物联网,工业制造,电力,化工以及IT运维等行业。
2884 0
杨格门锁 YGSLockSDK V3.3酒管软件接口_99v
链接:https://pan.baidu.com/s/1HP7FEWdA7sTmHzW1OTbGfg 密码:om1f   LockSDK 智能门锁管理系统开发包说明文档 请仔细阅读下面的每一条说明, 这样对接就能事半功倍  !!!!!!!!!!   一、重要说明 本接口支持各种开发语言, 包括:C#、VC、Delphi7、Delphi2000、PB9.0、PB10、VB.net、VB6.0等(每种语言一个文件夹),请选择自己常用的语言的演示程序(Demo)进行测试和开发。
1355 0
【WPF】【UWP】借鉴 asp.net core 管道处理模型打造图片缓存控件 ImageEx
原文:【WPF】【UWP】借鉴 asp.net core 管道处理模型打造图片缓存控件 ImageEx 在 Web 开发中,img 标签用来呈现图片,而且一般来说,浏览器是会对这些图片进行缓存的。 比如访问百度,我们可以发现,图片、脚本这种都是从缓存(内存缓存/磁盘缓存)中加载的,而不是再去访问一次百度的服务器,这样一方面改善了响应速度,另一方面也减轻了服务端的压力。
1194 0
【Cocos2d-x for WP8 学习整理】(3)CCScrollView 实现捕鱼达人一样的场景选择界面
原文:【Cocos2d-x for WP8 学习整理】(3)CCScrollView 实现捕鱼达人一样的场景选择界面 UI 界面一般是游戏里比较独立的地方,因为游戏引擎一般都比较注意基础的功能封装,很少会关注UI,但是 UI 确是玩家第一眼看到的效果,因此能否实现一个美观的UI对于提升游戏的整体美观有着很大的帮助。
892 0
《BREW进阶与精通——3G移动增值业务的运营、定制与开发》连载之49---BREW SDK 九大功能之多媒体系统
版权声明:本文为半吊子子全栈工匠(wireless_com,同公众号)原创文章,未经允许不得转载。
644 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
让用户数据永远在线,让数据无缝的自由流动
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载