烧脑数据库算法题 Postgresql 找出数组中重复的手机号

备注:此算法也适用于人脉搜索(比如共同的好友等)

so 思想很重要,工具不重要,思想错了干得再多也是白干.有了思想你也可以无限扩展
搜索人脉关系比查重复的手机号快的多,重复的手机号毕竟要全表比较,人脉搜索关系只需要扫描相关的数据即可,估计应该可以在100毫秒这个级别,我没有样本数据所以没法测量.

示例数据采用Postgresql,有兴趣的朋友可以试试,博主测试环境500W去重大约用时53秒.
使用到的数组函数点PostgreSQL 数组函数扩展
现实生活中一个人可能会有多个手机号,因此设计系统时必须考虑用户和手机号的唯一性.
因为实际应用比本案例复杂,主从表实践下来的效果不太满意,因此改用数组存储电话号码,以下为脚本.

1.创建表

/****************************************************************************************
    用户手机信息表
drop table if exists umobiles;
delete from umobiles
****************************************************************************************/
create table umobiles(
    objectid bigint not null,                       --用户唯一编号
    mobiles text[] not null,                        --用户电话号码
    constraint pk_umobiles_objectid primary key(objectid) with (fillfactor=80)  
) with (fillfactor=80);
create index idx_umobiles_mobiles on umobiles using gin(mobiles);

2.创建相关函数

/****************************************************************************************
    创建随机生成手机号函数
drop function if exists mobiles_rand();
****************************************************************************************/
create or replace function mobiles_rand()
    returns text[]
as $$
	select array_agg('1'|| ((random()*(999999999-100000000)+0)::integer)) from generate_series(1,(random()*(3-1)+1)::bigint);
$$ language sql strict;

/****************************************************************************************
    添加数据至用户手机信息表函数,每执行一次插入1000行
drop function if exists umobiles_add();
****************************************************************************************/
create or replace function umobiles_add(bigint)
  returns bigint
as $$
	with cte as(
		insert into umobiles(objectid,mobiles)
			select id as objectid,rand_mobiles() as mobiles from generate_series($1,$1 + 999) as id
				returning objectid
	)select max(objectid) from cte;	
$$ language sql strict;

3.导入测试数据

/****************************************************************************************
    递归插入数据,再烧脑,直接烧死你们,哈哈
    每次插入1000行,直到插入5000000数据
****************************************************************************************/
with recursive inserts(id) as (
    values(1::bigint)
    union all
    select umobiles_add(id)+1 from inserts as p
            where id<=5000000
)select * from inserts;
--Time: 203754.293 ms (03:23.754)
select count(*) from umobiles;

3.找出重复的手机号

因为是随机的,所以每个人的结果都是不同的.

/****************************************************************************************
    计算是否有重复的手机号,按数据分片原则一次检查$2行数据
        $1:用户编号
        $2:每次检查的行数
    返回null表示手机号没有重复,否则返回记录唯一编号
drop function if exists umobiles_repeat(bigint,integer);
****************************************************************************************/
create or replace function umobiles_repeat(iuserid bigint,ilimit integer) 
    returns table(objectids bigint[]) 
as $$
	with cte as(
		select objectid from umobiles where objectid>=$1 order by objectid limit $2
	),mobiles as(
		select objectid,unnest(mobiles) as mobile from umobiles where objectid = any(select objectid from cte)
	),counts as(
			select array_agg(objectid) as objectids,count(*) as c from mobiles as ta group by mobile
	)select array_repeat(objectids) from counts where c>1
$$ language sql;
--按数据分片原则一次检查一万行,直到所有数据处理完成.
select umobiles_repeat(1,10000)

批量一次性处理自己动动脑,多动脑有利于预防老年痴呆,博主测试环境一次性完成500W去重大约用时53秒.