PostgreSQL 相似人群圈选实践 - cube
站长
· 阅读数 24
相似人群分析在精准营销，推荐系统中的需求很多。
以音乐系统为例：根据用户对歌曲的行为进行打分
用户对歌曲的分数使用向量来表达，每个值代表一个歌曲的权重值，通过向量相似，可以得到一群相似的人群。
收集数据
根据用户行为给歌曲打分
| 单曲循环 | 分享 | 收藏 | 搜索 | 听完 | 没听过 | 跳过 |
|---|---|---|---|---|---|---|
| 5 | 4 | 3 | 2 | 1 | 0 | -1 |
假设收集了以下数据
| 夜曲 | 七里香 | 简单爱 | 樱花草 | 安静了 | 记得要忘记 | 淘汰 | 烟花易冷 | 借口 | 江南 | |
|---|---|---|---|---|---|---|---|---|---|---|
| 我自己 | 5 | 3 | 3 | 0 | -1 | 2 | 5 | 4 | 1 | -1 |
| 小明 | 4 | 5 | 2 | 1 | 0 | 3 | 2 | 0 | 1 | 1 |
| 小王 | 1 | 0 | 5 | 5 | -1 | 5 | 0 | 0 | 1 | 2 |
| 小红 | 3 | 0 | 0 | 3 | 0 | 2 | 0 | 4 | -1 | -1 |
| 小白 | 0 | 0 | 0 | -1 | 5 | -1 | 5 | 0 | 4 | 1 |
欧式距离
欧氏距离（也被称为欧几里得距离）是空间中两点之间的直线距离，也称为直线距离或直线段距离。在二维平面上，欧氏距离可以使用勾股定理计算。在三维或更高维的空间中，它的计算方式也类似。
对于两个点(x1,x2)和(y1,y2)在二维空间上的欧氏距离计算公式为：
d=√(x1-y1)^2 +(x2-y2)^2
其中，√ 表示平方根。在更高维度的情况下，公式可以类似地扩展，例如在三维空间中 (x1,x2,x3)与(y1,y2,y3)的距离为:
d=√(x1-y1)^2 +(x2-y2)^2 +(x3-y3)^2
更多维度以此类推
根据欧式距离计算距离
我自己: (5,3,3,0,-1,2,5,4,1,-1)
小明: (4,5,2,1,0,3,2,0,1,1)
小王:(1,0,5,5,-1,5,0,0,0,1)
小红:(3,0,0,3,0,2,0,4,-1,-1)
小白:(0,0,0,-1,5,-1,5,0,4,1)
根据上面欧式距离公式，我与小明、小王、小红、小白的欧式距离分别是
- √38
- √117
- √61
- √118
值越小表示距离越近，即相似度更高
实践-使用cube插件
-- 创建插件
create extension cube;
-- 创建数据表
create table t_user_cube (
uid int PRIMARY KEY,
name varchar(20),
c1 cube
);
--创建gist索引
create index idx_1 on t_user_cube using gist(c1);
写入数据
insert into t_user_cube(uid,name,c1) values
(1,'我自己','(5,3,3,0,-1,2,5,4,1,-1)'),
(2,'小明','(4,5,2,1,0,3,2,0,1,1)'),
(3,'小王','(1,0,5,5,-1,5,0,0,0,1)'),
(4,'小红','(3,0,0,3,0,2,0,4,-1,-1)'),
(5,'小白','(0,0,0,-1,5,-1,5,0,4,1)');
单个特征搜索
通过单个特征值CUBE查询相似人群，以点搜群
-- 根据我自己的特征值匹配最相似的人
select * from t_user_cube order by c1 <-> '(5,3,3,0,-1,2,5,4,1,-1)';
uid|name|c1 |
---+----+--------------------------------+
1|我自己 |(5, 3, 3, 0, -1, 2, 5, 4, 1, -1)|
2|小明 |(4, 5, 2, 1, 0, 3, 2, 0, 1, 1) |
4|小红 |(3, 0, 0, 3, 0, 2, 0, 4, -1, -1)|
3|小王 |(1, 0, 5, 5, -1, 5, 0, 0, 0, 1) |
5|小白 |(0, 0, 0, -1, 5, -1, 5, 0, 4, 1)|
--剔除自己
select * from t_user_cube where uid !=1 order by c1 <-> '(5,3,3,0,-1,2,5,4,1,-1)';
select * from t_user_cube order by c1 <-> '(5,3,3,0,-1,2,5,4,1,-1)';
uid|name|c1 |
---+----+--------------------------------+
2|小明 |(4, 5, 2, 1, 0, 3, 2, 0, 1, 1) |
4|小红 |(3, 0, 0, 3, 0, 2, 0, 4, -1, -1)|
3|小王 |(1, 0, 5, 5, -1, 5, 0, 0, 0, 1) |
5|小白 |(0, 0, 0, -1, 5, -1, 5, 0, 4, 1)|
多个特征搜索
通过多个特征值CUBE查询相似人群，以群搜群
-- 自己+小白的特征值
select * from t_user_cube order by c1 <-> '[(5,3,3,0,-1,2,5,4,1,-1),(0,0,0,-1,5,-1,5,0,4,1)]';
uid|name|c1 |
---+----+--------------------------------+
1|我自己 |(5, 3, 3, 0, -1, 2, 5, 4, 1, -1)|
5|小白 |(0, 0, 0, -1, 5, -1, 5, 0, 4, 1)|
2|小明 |(4, 5, 2, 1, 0, 3, 2, 0, 1, 1) |
4|小红 |(3, 0, 0, 3, 0, 2, 0, 4, -1, -1)|
3|小王 |(1, 0, 5, 5, -1, 5, 0, 0, 0, 1) |
cube最多支持100个维度
-- 创建生成随机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;
-- 尝试生成1000个维度
select gen_rand_cube(1000,10);
SQL 错误 [22P02]: ERROR: invalid input syntax for cube
Detail: A cube cannot have more than 100 dimensions.
Where: SQL function "gen_rand_cube" statement 1
上面的例子以歌曲为例，根据向量匹配相似人群，cube只支持100个维度，如果歌曲超过100首怎么办呢？
可以换个思路，根据用户不同属性来打分，属性控制在100个以内，难点在于如何设计属性以及权重的变化，这个需要大家根据实际业务情况去思考了。