V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
turan12
V2EX  ›  MySQL

求一个 MySQL 数据比对的思路

  •  
  •   turan12 · 2017-10-23 16:42:15 +08:00 · 1544 次点击
    这是一个创建于 2572 天前的主题,其中的信息可能已经有所发展或是发生改变。

    最近遇到一个需求,需要进行大量的手机号数据比对工作。大致要求是这样的: 1、每个人大致有 100-500 个联系人号码不等,个别肯能有超过 10000 个联系人。这些联系人都按号码和名称存入一个表里。总数大概有 500-600w 条的样子。 2、我现在需要比对用户通讯录的重复数据。比方说一个新用户注册后,app 会自动把通讯录存到服务器中。这时我就要比对这个用户的通讯录和以前所有用户的通讯录号码重复率,如果重复率超过一定比例,比如说超过 50%,就把该用户单独筛选出来。

    我现在的处理方式是先在 Python 中把用户的号码生成一个列表,然后不断的循环进行列表的比对。但这个速度实在是不可接受。在此请教下 v2 上的各位高人同学看看有没有什么更好的办法。

    13 条回复    2017-10-24 10:59:18 +08:00
    noNOno
        1
    noNOno  
       2017-10-23 17:14:08 +08:00
    你 py 的匹配是新用户号码表依次与每个历史用户号码表匹配么

    sql 的话.可以先找出与新用户通讯录的每个联系人号码相等的历史用户通讯录的联系人号码以及历史用户.然后按历史用户分组,数联系人号码数量获取重复次数,然后计算重复率,找出与新用户联系人相近的历史用户
    如果重复率有量的限制更好,可以先筛选掉用户联系人数量差距大的
    --.懵逼的需求,是要做推荐算法么
    kismetX
        2
    kismetX  
       2017-10-23 17:14:33 +08:00   ❤️ 1
    仅供参考
    phone(
    id,phone
    )
    user(
    id,name
    )
    relation(
    uid,pid,nickname
    )
    取出俩用户 relation 的 pid,取差集,会不会快一些,哈哈
    kismetX
        3
    kismetX  
       2017-10-23 17:15:40 +08:00
    @kismetX 取交集。。。说错了
    WuwuGin
        4
    WuwuGin  
       2017-10-23 17:16:27 +08:00 via Android
    这是在做网贷吗😂
    xeis
        5
    xeis  
       2017-10-23 17:17:53 +08:00 via Android
    通讯录都收集了
    domty
        6
    domty  
       2017-10-23 17:39:44 +08:00   ❤️ 1
    拿缓存数据库做个 set 呗。
    拿到通讯录后遍历手机号是否在这个 set 里,然后标记统计。
    往 mysql 插入新数据的时候把手机号同步到 set 里。
    jason19659
        7
    jason19659  
       2017-10-23 17:45:45 +08:00   ❤️ 1
    通讯录的人给个权重,每出现一次加权重,最后统计权重和?
    zencitta
        8
    zencitta  
       2017-10-23 18:07:44 +08:00   ❤️ 1
    LZ 可以试试下面的 SQL, 不确定行不行

    select user_id, count(*) as contact_count
    from user_contacts
    where contact_people_phone_no in (phone No. list)
    group by user_id
    order by contact_count desc

    给 contact_people_phone_no 做索引
    gamexg
        9
    gamexg  
       2017-10-23 18:10:00 +08:00   ❤️ 1
    给每个用户的通信录弄个很小的 Bloom Filter,对比时先二进制 ^ 下两个 Bloom Filter,如果发现比较吻合在详细对比电话号码?
    不过感觉也就节省不太多。
    asiufasd
        10
    asiufasd  
       2017-10-23 18:21:47 +08:00 via iPhone   ❤️ 1
    给总表的手机号看作数字排序,拿到用户的手机号之后也排个序,然后从头开始在总表里找,找到了的话前面的数据就可以舍弃了,再从那个节点继续往下找,是不是可能能少遍历一点
    rogwan
        11
    rogwan  
       2017-10-23 18:44:32 +08:00
    什么阴暗的 App,还在偷取用户通信录,还要做数据分析 =_=||
    sunchen
        12
    sunchen  
       2017-10-23 19:01:24 +08:00   ❤️ 1
    几百万级表自己 join 一下没什么压力吧,提前再过滤下频率为 1 的联系人,应该没多少数据了

    假设表 contacts 结构:
    user_id 用户 ID,
    phone 联系人电话



    with (
    select user_id
    , phone
    , count(distinct user_id) over (partition by phone) as total_distinct_user
    from contacts
    ) as t

    select a_user_id, b_user_id, count(*) over (partition by a_user_id, b_user_id)
    from
    (
    select a.user_id as a_user_id, b.user_id as b_user_id, phone
    from t as a,
    t as b
    where a.phone = b.phone
    and a.user_id > b.user_id
    and a.total_distinct_user > 1
    );

    上边计算可以算出不同的用户相同联系人数量,因为 mysql 没有 window function,上述过程在 mysql 里可以多分几步计算模拟下
    sunkuku
        13
    sunkuku  
       2017-10-24 10:59:18 +08:00
    这种业务肯定需要用到 redis 之类的缓存,用一个 set 先判断一下就可以了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1129 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 22:55 · PVG 06:55 · LAX 14:55 · JFK 17:55
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.