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
kkbear
V2EX  ›  MySQL

咨询个 SQL 数据去重的问题

  •  
  •   kkbear · 60 天前 · 2972 次点击
    这是一个创建于 60 天前的主题,其中的信息可能已经有所发展或是发生改变。
    目前有个流水表,大几千万的数据,可以简化如下:
    会员号( member_id), 流水日期( trans_date)

    有个需求,需要找出指定日期段内的会员数量(去重)
    这个日期段时灵活的,可能时本月,本周,最近 7 天,最近 30 天

    有什么方法能优化
    1. 没办法根据日期段做汇总结果的统计表,因为日期段没有任何规律
    2. 数据库为 mysql,目前用的 sum(distinct member_id) where trans_date between
    29 条回复    2024-09-07 00:25:18 +08:00
    wenxueywx
        1
    wenxueywx  
       60 天前
    写错了? 统计数量怎么会用 sum 应该用 count 吧
    没理解根据日期做汇总结果的统计表什么意思
    asAnotherJack
        2
    asAnotherJack  
       60 天前
    怎么是 sum 呢,不该是 count 吗
    kkbear
        3
    kkbear  
    OP
       60 天前
    写错了,是 count
    @wenxueywx 统计表,也可以理解成缓存表就行
    ZZ74
        4
    ZZ74  
       60 天前
    优化 ?现在是碰到什么问题了 查询慢?
    sagaxu
        5
    sagaxu  
       60 天前
    按一个月不超过 300 万数据增量估算,有联合索引(trans_date, member_id),低频词的在从库里查 1 个月也 OK
    8355
        6
    8355  
       60 天前
    我倾向于用其他数据库做 例如 clickhouse 或着其他大数据方向的数据库计算,并不是用关系型数据库。
    如果可以接受不是很精准的话可以用布隆过滤器
    x1n04
        7
    x1n04  
       60 天前
    type 看看,再加点索引呢。
    wenxueywx
        8
    wenxueywx  
       60 天前
    实时查询就行了,没必要将汇总结果缓存吧,主要在 trans_date 字段上设计索引。如果 select 没有其它字段的话就加上 member_id 做个联合索引,直接使用覆盖索引查询。
    NoOneNoBody
        9
    NoOneNoBody  
       60 天前
    我只知道 pandas 做这个甚为简单,sql 就不晓得了
    kkbear
        10
    kkbear  
    OP
       60 天前
    加索引也慢,估计是去重数据太多了
    kkbear
        11
    kkbear  
    OP
       60 天前
    @8355 是吧,我也感觉这项目已经不适用关系型数据库了,底层还有很多爬取同步过来的数据,然后中间一顿清洗整理汇总
    akiakise
        12
    akiakise  
       60 天前 via iPhone
    提供一个思路,离线算出来过去每天的去重会员数保存到一个新表里,查询的时候直接按时间范围 sum 新表就行了
    clf
        13
    clf  
       60 天前
    先 explain 看看怎么走的索引呗。
    xmumiffy
        14
    xmumiffy  
       60 天前 via Android
    建个新表就存每日去重后你要的数据,你说的“简化”,实际搜索时还是被其他列影响了。
    建个日期加会员号的索引
    OliverDD
        15
    OliverDD  
       60 天前
    @kkbear #11 这标准的 ETL 流程,还是上 OLAP 数据库吧,别为难 mysql 了
    8355
        16
    8355  
       60 天前
    @kkbear #11 因为这种需求就是 mysql 做也能做,但是随着数据量的增长只会越来越痛苦,感觉早点换方案更好,到这个数据量了一年加个小几万来解决这种问题感觉是比较超值的,不然在 mysql 凑合最后还是要迁移重做一遍。
    hhhzccc
        17
    hhhzccc  
       60 天前
    上报表系统吧
    rockyliang
        18
    rockyliang  
       60 天前
    @akiakise 你这思路肯定不行啊,求 N 天的去重数量,不能简单的将每天的去重数量加起来
    pkoukk
        19
    pkoukk  
       60 天前
    沟通需求,把查询时间的粒度从秒提升到小时或者天,我相信运营不会看精确到秒的粒度数据。
    然后按粒度把数据统计成二级表,查询从二级表 sum
    Mithril
        20
    Mithril  
       60 天前   ❤️ 1
    主要还是看你的需求到底是什么,是要精确结果,还是一个近似估计。而且也要看数据分布是怎样的,平均查询覆盖的数据量有多少。

    比如说你这几千万数据是好几年的,每天其实都没多少,那就先拆表,再限制查询范围不能跨边界,结果就能好不少。

    OLAP 在这种大规模去重统计上,是用 HyperLogLog 去算近似值的。如果你一定要精确结果,那它也会非常慢的(还是要比你 mysql 快)。

    所以如果你数据量比较集中,只要个有一定精度的近似值,而且以后还有这类统计分析的需求,那就上个 OLAP 数据库。但这不只是增加了系统复杂度,而且数据同步,OLAP 数据库的备份还原等等你都要考虑到。

    如果一定要精确值,而且以后也没这类需求。想办法在现有数据库上改进也可以的。
    ZGame
        21
    ZGame  
       60 天前
    你这就一个表 直接推到 es 里查就很快了吧....
    ZGame
        22
    ZGame  
       60 天前
    1.先搞个从库,然后查询。
    2.如果查询速度不满足效率,先加索引,数据库层面的优化
    3.如果还是不行,把数据同步到 es 或者其他 mpp 并查询。
    hero1874
        23
    hero1874  
       60 天前
    搞 OLAP 数据库吧 StarRocks Doris ClickHouse 都行,不过 ck 运维很麻烦。。
    StarRocks Doris 可以用 catalog ,直接取到 mysql 的数据,然后再计算也还不错
    cnbatch
        24
    cnbatch  
       60 天前
    简单粗暴的解决办法

    建个新的表(以下称之为“日期表”),只需两个 column:
    timestamp 、会员号(member_id)

    然后把流水表的数据逐条拆分写入这个“日期表”,顺便修改下原程序的代码,更新流水表的同时也要写入“日期表”
    chainal7777
        25
    chainal7777  
       60 天前
    按天出汇总啊,楼上说 olap 的,我感觉还不如 mysql 呢
    Biggoldfish
        26
    Biggoldfish  
       60 天前 via Android
    这种量级的数据和简单的需求,任何正常的 OLAP db 都是闭眼出结果吧
    srlp
        27
    srlp  
       60 天前 via iPhone
    直接 doris 闭眼出。

    面试的话,构建 memberid-活跃日期 bitmap 表
    loveaeen
        28
    loveaeen  
       60 天前
    增加中间件也就增加了额外维护成本,按楼上说的。每日汇总数据数量,日期段只查汇总表也挺好的。
    oaix
        29
    oaix  
       59 天前
    如果不用追求 100%准确,并且时间段最小粒度是天。可以每天算一个 hyperloglog ,存起来。查询的时候把对应日期的 hyperloglog 合并起来。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3153 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 19ms · UTC 12:29 · PVG 20:29 · LAX 04:29 · JFK 07:29
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.