PostgreSQL相似人群圈選，人群擴選，向量相似使用實踐-cube

背景
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億影像)》
轉自阿里雲德哥