postgresql 标签分组实战
数据初始化
构建用户标签关系表
drop table IF EXISTS t_user_tag;
create table IF NOT EXISTS t_user_tag(userid int8 not null primary key,tagids int[]);初始化随机长度数据，假设标签范围1-10
-- 生成随机长度的数据数据 ，这样子数据就比较离散点，做查询索引的话，才容易命中，如果都是重复数据，做索引意义不大
do language plpgsql $$
declare
begin
for i in 1..1000000 loop
insert into t_user_tag values (i,
(select array_agg(ceil(random()*10)::int) from generate_series(1,ceil(random()*10)::int))
);
end loop;
end;
$$; 数据写入与变更操作演示
插入1条数据
-- 插入 10000001 用户数据, ARRAY 数组的代表方式
INSERT INTO t_user_tag
VALUES (10000001,
ARRAY[1, 2, 3, 4,5]);
-- 查询该数据
select * from t_user_tag where userid= 10000001;
更新数据
-- 更新数字的第一个元素为null，pg默认下标从1开始，也可以自己自定义
update t_user_tag set tagids[1] = null
where userid = 10000001;
-- 查询该数据
select * from t_user_tag where userid= 10000001;-- 只保留2-4的4位数值
update t_user_tag set tagids = tagids[2:5]
where userid = 10000001;
-- 查询该数据
select * from t_user_tag where userid= 10000001;update t_user_tag set tagids[6:7] = '{6,7}'
where userid = 10000001;
-- 查询该数据
select * from t_user_tag where userid= 10000001;更新时使用函数操作（参考: http://www.postgres.cn/docs/14/functions-array.html）追加写入数据
update t_user_tag set tagids= array_append(tagids,11)
where userid = 10000001;
-- 查询该数据
select * from t_user_tag where userid= 10000001;-- 另外一种追加方式
update t_user_tag set tagids= tagids||22
where userid = 10000001;
-- 查询该数据
select * from t_user_tag where userid= 10000001;删除指定数据
-- 从数组中移除所有等于给定值的所有元素
update t_user_tag set tagids= array_remove(tagids, 2)
where userid = 10000001;
-- 查询该数据
select * from t_user_tag where userid= 10000001;批量修改
-- 将每一个等于给定值的数组元素替换成一个新值
-- 比如说将所有的 6 改成 3
update t_user_tag set tagids = array_replace(tagids,6,3)
where userid = 10000001;
-- 查询该数据
select * from t_user_tag where userid= 10000001;数据查询
假设求取的是都包含了 标签5 和标签6的情况，有哪些
select * from t_user_tag
where tagids @> '{6,5}' limit 10;假设求取的是 包含了 标签5和标签6，并且最多标签为 5 个的情况下有哪些用户
select * from t_user_tag
where tagids @> '{6,5}'
and array_length(tagids,1)<=5
limit 10;假设求取的是只要有 标签7 或 标签9 的情况都筛选出来
select * from t_user_tag
where tagids && '{7,9}'
limit 10;统计加速
构建索引
-- 构建索引 gin索引或 rum索引（支持排序操作）
CREATE INDEX idx_t_user_tag on t_user_tag using gin(tagids);统计查询
-- 耗时90ms，其实内网下应该会更小的耗时
select count(1) from t_user_tag
where tagids @> ARRAY[1,2,3];统计分析
全表统计的情况下
select count(1) from t_user_tag有以上基础后，可以尝试更大数据量的快速检索，例如2000亿数据量的检索如何实时实现
