PostgreSQL 相似人群圈选,人群扩选,向量相似 使用实践 - cube

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: postgresql 数据库 圈人

背景
PostgreSQL 相似插件非常多,插件的功能以及用法如下:

《PostgreSQL 相似搜索插件介绍大汇总 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》

相似人群分析在精准营销,推荐系统中的需求很多。

人的属性可以使用向量来表达,每个值代表一个属性的权重值,通过向量相似,可以得到一群相似的人群。

例如

create table tt (
uid int8 primary key,
att1 float4, -- 属性1 的权重值
att2 float4, -- 属性2 的权重值
att3 float4, -- 属性3 的权重值
...
attn float4 -- 属性n 的权重值
);
使用cube表示属性

create table tt (
uid int8 primary key,
att cube -- 属性
);
使用cube或imgsmlr可以达到类似的目的。

a <-> b float8 Euclidean distance between a and b.
a <#> b float8 Taxicab (L-1 metric) distance between a and b.
a <=> b float8 Chebyshev (L-inf metric) distance between a and b.
但是如果向量很大(比如属性很多),建议使用一些方法抽象出典型的特征值,压缩向量。 类似图层,图片压缩。实际上imgsmlr就是这么做的:

pic

例如256256的像素,压缩成44的像素,存储为特征值。

例子
1、创建插件

create extension cube;
2、创建测试表

create table tt (id int , c1 cube);
3、创建GIST索引

create index idx_tt_1 on tt using gist(c1);
4、创建生成随机CUBE的函数

create or replace function gen_rand_cube(int,int) returns cube as

$$ select ('('||string_agg((random()*$2)::text, ',')||')')::cube from generate_series(1,$1); $$

language sql strict;
5、CUBE最多存100个维度

postgres=# set VERBOSITY verbose

postgres=# select gen_rand_cube(1000,10);

ERROR: 22P02: invalid input syntax for cube
DETAIL: A cube cannot have more than 100 dimensions.
CONTEXT: SQL function "gen_rand_cube" statement 1
LOCATION: cube_yyparse, cubeparse.y:111
6、写入测试数据

insert into tt select id, gen_rand_cube(16, 10) from generate_series(1,10000) t(id);
7、通过单个特征值CUBE查询相似人群,以点搜群

select * from tt order by c1 <-> '(1,2,3,4,5,6,7)' limit x; -- 个体搜群体
8、通过多个特征值CUBE查询相似人群,以群搜群

select * from tt order by c1 <-> '[(1,2,3,4,5,6,7),(1,3,4,5,6,71,3), ...]' limit x; -- 群体搜群体
postgres=# explain select * from tt order by c1 <-> '[(1,2,3),(2,3,4)]' limit 1;

                            QUERY PLAN                                  

Limit (cost=0.11..0.14 rows=1 width=44)
-> Index Scan using idx_tt_1 on tt (cost=0.11..0.16 rows=2 width=44)

     Order By: (c1 <-> '(1, 2, 3),(2, 3, 4)'::cube)  

(3 rows)
9、如果需要再计算压缩前的特征值的相似性,可以使用原始值再计算一遍。

《PostgreSQL 遗传学应用 - 矩阵相似距离计算 (欧式距离,...XX距离)》

select *,
c1 <-> ?1, -- c1表示压缩后的特征值浮点数向量,比如(4*4)
distance_udf(detail_c1,?2) -- deatil_c1 表示原始特征值浮点数向量(比如128*128)
from tt order by c1 <-> ?1 limit xx;
参考
https://www.postgresql.org/docs/devel/static/cube.html

https://github.com/postgrespro/imgsmlr

https://github.com/eulerto/pg_similarity

《PostgreSQL 相似搜索插件介绍大汇总 (cube,rum,pg_trgm,smlar,imgsmlr,pg_similarity) (rum,gin,gist)》

《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 3 - citus 8机128shard (4亿图像)》

《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 2 - 单机分区表 (dblink 异步调用并行) (4亿图像)》

《PostgreSQL 11 相似图像搜索插件 imgsmlr 性能测试与优化 1 - 单机单表 (4亿图像)》
转自阿里云德哥

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
5月前
|
自然语言处理 关系型数据库 数据库
技术经验解读:【转】PostgreSQL的FTI(TSearch)与中文全文索引的实践
技术经验解读:【转】PostgreSQL的FTI(TSearch)与中文全文索引的实践
66 0
|
关系型数据库 定位技术 分布式数据库
沉浸式学习PostgreSQL|PolarDB 18: 通过GIS轨迹相似伴随|时态分析|轨迹驻点识别等技术对拐卖、诱骗场景进行侦查
本文主要教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核.
1304 1
|
6月前
|
弹性计算 关系型数据库 数据库
开源PostgreSQL在倚天ECS上的最佳优化实践
本文基于倚天ECS硬件平台,以自顶向下的方式从上层应用、到基础软件,再到底层芯片硬件,通过应用与芯片的硬件特性的亲和性分析,实现PostgreSQL与倚天芯片软硬协同的深度优化,充分使能倚天硬件性能,帮助开源PostgreSQL应用实现性能提升。
|
6月前
|
SQL 运维 关系型数据库
基于AnalyticDB PostgreSQL的实时物化视图研发实践
AnalyticDB PostgreSQL版提供了实时物化视图功能,相较于普通(非实时)物化视图,实时物化视图无需手动调用刷新命令,即可实现数据更新时自动同步刷新物化视图。当基表发生变化时,构建在基表上的实时物化视图将会自动更新。AnalyticDB PostgreSQL企业数据智能平台是构建数据智能的全流程平台,提供可视化实时任务开发 + 实时数据洞察,让您轻松平移离线任务,使用SQL和简单配置即可完成整个实时数仓的搭建。
143994 8
|
6月前
|
人工智能 关系型数据库 MySQL
一键实现穿衣自由|揭秘淘宝AI试衣间硬核技术:AnalyticDB MySQL向量在线召回
在AI试衣间功能的背后,阿里云瑶池旗下的云原生数据仓库AnalyticDB MySQL提供了高维向量低延时的在线向量召回检索服务,下面将进行介绍。
|
6月前
|
SQL 关系型数据库 MySQL
MySQL【实践 02】MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享(通过bat命令修改mapper文件内的SQL语法)
MySQL【实践 02】MySQL迁移到PostgreSQL数据库的语法调整说明及脚本分享(通过bat命令修改mapper文件内的SQL语法)
257 0
|
关系型数据库 分布式数据库 数据库
沉浸式学习PostgreSQL|PolarDB 21,相似图像搜索
传统数据库不支持图像类型, 图像相似计算函数, 图像相似计算操作服, 相似排序操作符. 所以遇到类似的需求, 需要自行编写应用来解决. PG|PolarDB 通过imgsmlr插件, 可以将图像转换为向量特征值, 使用相似距离计算函数得到相似值, 使用索引加速相似度排序, 快速获得相似图片, 实现以图搜图. 也可以通过pgvector插件来存储图片向量特征值, 结合大模型服务(抠图、图像向量转换), 可以实现从图像转换、基于图像的相似向量检索全流程能力.
898 1
|
关系型数据库 测试技术 分布式数据库
PolarDB | PostgreSQL 高并发队列处理业务的数据库性能优化实践
在电商业务中可能涉及这样的场景, 由于有上下游关系的存在, 1、用户下单后, 上下游厂商会在自己系统中生成一笔订单记录并反馈给对方, 2、在收到反馈订单后, 本地会先缓存反馈的订单记录队列, 3、然后后台再从缓存取出订单并进行处理. 如果是高并发的处理, 因为大家都按一个顺序获取, 容易产生热点, 可能遇到取出队列遇到锁冲突瓶颈、IO扫描浪费、CPU计算浪费的瓶颈. 以及在清除已处理订单后, 索引版本未及时清理导致的回表版本判断带来的IO浪费和CPU运算浪费瓶颈等. 本文将给出“队列处理业务的数据库性能优化”优化方法和demo演示. 性能提升10到20倍.
834 4
|
关系型数据库 分布式数据库 PolarDB
沉浸式学习PostgreSQL|PolarDB 15: 企业ERP软件、网站、分析型业务场景、营销场景人群圈选, 任意字段组合条件数据筛选
本篇文章目标学习如何快速在任意字段组合条件输入搜索到满足条件的数据.
607 0
|
搜索推荐 关系型数据库 数据库
沉浸式学习PostgreSQL|PolarDB 3: 营销场景, 根据用户画像的相似度进行目标人群圈选, 实现精准营销
业务场景1 介绍: 营销场景, 根据用户画像的相似度进行目标人群圈选, 实现精准营销 在营销场景中, 通常会对用户的属性、行为等数据进行统计分析, 生成用户的标签, 也就是常说的用户画像. 标签举例: 男性、女性、年轻人、大学生、90后、司机、白领、健身达人、博士、技术达人、科技产品爱好者、2胎妈妈、老师、浙江省、15天内逛过手机电商店铺、... ... 有了用户画像, 在营销场景中一个重要的营销手段是根据条件选中目标人群, 进行精准营销. 例如圈选出包含这些标签的人群: 白领、科技产品爱好者、浙江省、技术达人、15天内逛过手机电商店铺 .
313 0