常熟市住房建设局网站,怎么运营,做创意ppt网站,什么网站可以做数据图摘要#xff1a; 标签 PostgreSQL , 多重函数数组 , UDF索引 , 过滤 , 文本处理 背景 PG的数组类型#xff0c;被广泛应用于 画像系统 #xff0c; 标签系统。 在一些业务重建中#xff0c;对数组内容的定义往往包含了多重含义#xff0c;例如即包含了标签本身#xff0c… 摘要 标签 PostgreSQL , 多重函数数组 , UDF索引 , 过滤 , 文本处理 背景 PG的数组类型被广泛应用于 画像系统 标签系统。 在一些业务重建中对数组内容的定义往往包含了多重含义例如即包含了标签本身又包含了标签的属性例如 标签值:权值,时间 等。 点此查看原文 标签 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), #039;(.*):#039;));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 (cost2.40..3.86 rows1 width36) Recheck Cond: (get_label(info) ‘{a}’::text[]) - Bitmap Index Scan on idx_tbl1 (cost0.00..2.40 rows1 width0) 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)], #039;:(.*)#039;);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 (cost2.40..4.12 rows1 width36) Recheck Cond: (get_label(info) ‘{a}’::text[]) Filter: ((get_weight(info, ‘a’::text))::double precision ‘20’::double precision) - Bitmap Index Scan on idx_tbl1 (cost0.00..2.40 rows1 width0) 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 infofunc(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 infoarray_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 infoarray_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 infoarray_append(tbl1.info, 100) returning *; id | info —-—————– 1 | {1,2,3,100,100} (1 row) INSERT 0 1 扫描二维码获取更多消息
