数据库实验手册系列: 23 数据寻龙点穴-通过数据热度分析寻找商机
digoal约 8 分钟文章开始前推荐2个学习环境:
1、欢迎使用镜像快速体验PostgreSQL/DuckDB强大功能:《最好的PostgreSQL学习镜像》
2、欢迎使用云起实验室: 《免费体验PolarDB开源数据库》
3、PolarDB开源数据库内核、应用等学习图谱: https://www.aliyun.com/database/openpolardb/activity
标签
PostgreSQL , PolarDB , 数据库 , 教学
背景
欢迎数据库应用开发者参与贡献场景, 在此issue回复即可, 共同建设《沉浸式数据库学习教学素材库》, 帮助开发者用好数据库, 提升开发者职业竞争力, 同时为企业降本提效.
系列课程的核心目标是教大家怎么用好数据库, 而不是怎么运维管理数据库、怎么开发数据库内核. 所以面向的对象是数据库的用户、应用开发者、应用架构师、数据库厂商的产品经理、售前售后专家等角色.
本文的实验可以使用永久免费的阿里云云起实验室来完成.
如果你本地有docker环境也可以把镜像拉到本地来做实验:
x86_64机器使用以下docker image:
《amd64 image》
ARM机器使用以下docker image:
《arm64 image》
业务场景1 介绍: 数据寻龙点穴-通过数据热度分析寻找商机
商机和什么有关? 需求
市场规模与什么有关? 消费目标人数, 客单价
投入产出和什么有关? 对精准人群的规模化转化
回答完以上三个问题, 以线下门店选址为例, 我们需要做的是根据目标人群画像, 找到人群聚集位置, 在对应位置开店从而实现规模化转化.
实现和对照
传统数据库GIS能力较弱, 不适合做时空数据分析.
传统方法 设计和实验
无.
PolarDB|PG新方法1 设计和实验
1、创建测试表1, 存储多边形数据, 每个多边形表示小区、商圈、写字楼等AOI数据.
create unlogged table t1 (
gid int, -- 多边形ID（用户定义的小区、商圈、写字楼等）
face box -- 空间信息，实际使用时，可以使用PostGIS的geometry类型
);
生成1万个面测试数据, 其中位置边界为(0,0), (100,100) ,创建GIS索引
insert into t1 select row_number() over(), box (point(x,y),point(x+1,y+1)) from generate_series(0,99) t1(x),generate_series(0,99) t2(y); create index idx_t1_face on t1 using gist(face);
样本如下:
postgres=# select * from t1 limit 10;
gid | face
-----+--------------
1 | (1,1),(0,0)
2 | (1,2),(0,1)
3 | (1,3),(0,2)
4 | (1,4),(0,3)
5 | (1,5),(0,4)
6 | (1,6),(0,5)
7 | (1,7),(0,6)
8 | (1,8),(0,7)
9 | (1,9),(0,8)
10 | (1,10),(0,9)
(10 rows)
2、创建测试表2, 存储对象的位置、轨迹等数据
例如:
打车上下车点
人的轨迹点/线数据
create unlogged table t2 (
uid int, -- 对象ID
pos point, -- 位置，实际使用时，可以使用PostGIS的geometry类型
att text -- 其他属性，可以有更多其他属性
);
生成100万用户的1000万个点测试数据, 其中位置边界为(0,0), (100,100)
insert into t2 select ceil(random()*1000000)::int, point(random()*100, random()*100) from generate_series(1,10000000) t(id);
样本如下:
postgres=# select * from t2 limit 10;
uid | pos | att
--------+-----------------------------------------+-----
624480 | (69.87993638998837,99.62866543892659) |
214726 | (64.99721386704991,30.363436819672884) |
979519 | (12.80339924893319,72.84033470132627) |
760628 | (94.706555334308,64.62020709007525) |
728898 | (93.81539819852414,10.88097702423454) |
891585 | (31.675784937903728,4.798317400419094) |
148367 | (45.395582637999254,73.38459322142725) |
664177 | (3.931702280385707,30.175398899784156) |
152051 | (5.4986871854556085,50.902328906649075) |
326881 | (94.07026426204013,14.140696778935435) |
(10 rows)
3、创建人的标签表, 表示人的特征(例如年龄、消费能力、喜好等)
create unlogged table users (
uid int primary key, -- 用户ID
tags int[] -- 10个标签, 标签ID取值范围 1,1000
);
生成测试数据100万, 并创建标签GIN 索引.
create or replace function gen_rand_arr (int,int) returns int[] as $$
select array(select ceil(random()*$1)::int from generate_series(1,$2) group by 1);
$$ language sql strict; insert into users select generate_series(1,1000000), gen_rand_arr(1000,10);
样本如下:
postgres=# select * from users limit 10;
uid | tags
-----+--------------------------------------------
1 | {695,240,131,308,513,782,311,129,789,678}
2 | {743,175,58,6,11,630,565,53,604,905}
3 | {787,639,690,476,902,306,858,919,961,712}
4 | {443,525,5,396,389,29,872,897,998,82}
5 | {874,305,761,387,210,686,939,533,792,395}
6 | {814,419,972,505,634,892,754,218,631,586}
7 | {775,942,577,785,611,114,11,572,482,327}
8 | {344,676,521,911,577,800,829,631,402,339}
9 | {294,84,249,39,374,970,45,810,135,348}
10 | {222,194,442,470,518,484,697,510,1000,993}
(10 rows)
4、根据人群特征条件, 生成网格式热力图
横竖各100个刻度组成10000个格子的热力图.
用到操作符:
pg_catalog | && | anyarray | anyarray | boolean | overlaps
select
width_bucket(pos[0], 0, 101, 100), -- 该位置落在x轴的哪列bucket
width_bucket(pos[1], 0, 101, 100), -- 该位置落在y轴的哪列bucket
count(*)
from t2 join users using (uid)
where users.tags && array[1,2,3] -- 用户特征为包含1,2,3标签的任意一个.
group by 1,2
order by 1,2; width_bucket | width_bucket | count
--------------+--------------+-------
1 | 1 | 27 -- 表示在格子1,1内符合特征的点数量为27个.
1 | 2 | 44
1 | 3 | 29
1 | 4 | 30
1 | 5 | 29
1 | 6 | 34
1 | 7 | 35
1 | 8 | 32
1 | 9 | 34
...
100 | 66 | 1
100 | 67 | 1
100 | 75 | 1
100 | 78 | 1
100 | 82 | 1
100 | 84 | 1
100 | 92 | 1
100 | 99 | 1
(9848 rows)
用颜色深浅来表示每个格子的count, 即可得到热力图.
5、根据人群特征条件, 生成多边形热力图
行政区块（小区，商圈，写字楼）包含或相交，聚合
用到操作符:
pg_catalog | && | box | box | boolean | overlaps
pg_catalog | @@ | | box | point | center of
创建函数，输入点的值，获得面的值。
create or replace function get_gid(point) returns int as $$
select gid from t1 where face @> box($1,$1) limit 1;
$$ language sql strict immutable parallel safe;
select
get_gid(t2.pos) as boxid, -- 这个点落在哪个多边形内
count(*)
from t2 join users using (uid)
where users.tags && array[1,2,3] -- 用户特征为包含1,2,3标签的任意一个.
group by 1
order by 1; boxid | count
-------+-------
1 | 26
2 | 45
3 | 28
4 | 30
5 | 27
6 | 32
7 | 36
8 | 30
9 | 38
10 | 25
11 | 30
...
9993 | 27
9994 | 37
9995 | 33
9996 | 21
9997 | 31
9998 | 29
9999 | 22
10000 | 28
(10000 rows)
用颜色深浅来表示每个多边形的count, 即可得到热力图.
6、你可以换成geometry类型, 代替本实验的point,box. 再试一试.
对照
无.
知识点
GIS
热力图
空间聚合
Kmeans聚合
width_bucket:
width_bucket(
p1 -- 输入值
p2 -- 边界值（最小，包含）
p3 -- 边界值（最大，不包含）
p4 -- 切割份数
) 当小于最小边界值时，返回0
当大于等于最大边界值时，返回p4+1
空间相交、包含等
思考
1、从 空间关系聚集 到 互联网关系聚集
全球顶级战略家帕拉格•康纳重磅之作《超级版图》副标题: 全球供应链、超级城市与新商业文明的崛起
本书传达的核心理念是: 传统的地理疆界正在消失 ，互联互通全球版图将要形成. 一场布局基础设施、争夺供应链资源的国家角力战已经打响！
《德说-第207期, 《超级版图》 - 人才连接线 & 数据资产》
图数据库? 根据关系来分析聚集人群.
《体验 PostgreSQL|PolarDB 图式数据插件age和WEB产品age-viewer》
数据: 人,关联人数组
分析步骤: 聚集人群, 按聚集后的人群进行标签聚合, 按标签权重排序后得到这些聚集人群的特征(例如消费力、学历、喜好等).
2、从 空间关系聚集 到 互联网关系聚集 到 特征向量聚集
随着AI的发展, 可以将关系再进行无监督学习, 对人群进行特征向量化.
上一个实验已经讲过了, 可以直接使用向量来进行聚集分析:
《沉浸式学习PostgreSQL|PolarDB 22: 用KMeans 数据聚集算法进行无监督学习和数据分类分析》
3、数据在哪里?
消费、社交、本地生活等互联网巨头、银行、运营商 ...
4、终于明白了为什么数据就是资产?
数据是继房地产后的下一代资产. 但是数据可以被复制, 除了管制和法律控制来防止非法复制以外, 得到/采集到数据后如何发挥出其价值才是重中之重. 卖原始数据其实是低端/违法的做法.
5、你能连接|掌控的资源越多, 你的价值就越大, 你的生存能力就越强.
思考一下: 什么样的房子值钱? 为什么? 房子背后连接了哪些公共资源(医疗、教育、商圈、休闲、人群、商机)? 值钱的不是房子, 是资源和与资源的连接.
相信未来在房子背后的资源运营上会出现很多商机和运营型的公司.
连接的重要性:
《德说-第264期, 2023 CCF 中国开源大会 见闻与思考: 数据库全球竞争情况? 我们靠什么超车？学校、学生为什么要以及如何参加开源？》
《德说-第256期, 团队管理和队形布置方法论》
《德说-第227期, 致大学生的入学第一课》
参考
《人分九等，数有阶梯 - PostgreSQL 阶品（颗粒）分析函数width_bucket, kmean应用》
《在PostgreSQL中如何生成测试kmean算法的数据》
《K-Means 数据聚集算法》
《PolarDB 开源版 使用PostGIS 数据寻龙点穴（空间聚集分析）- 大数据与GIS分析解决线下店铺选址问题》
《数据寻龙点穴（空间聚集分析） - 阿里云RDS PostgreSQL最佳实践》
《构建生态与“化气结穴、寻龙点穴”思考》
《PostgreSQL 空间聚合性能 - 行政区、电子围栏 空间聚合 - 时间、空间热力图》
《PostgreSQL 生成空间热力图》
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号:
文章中的参考文档请点击阅读原文获得.