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

MySQL 如何查询每一天的用户注册数量(近 7 日、15 日、30 日...)?

  •  
  •   NowTime ·
    PrintNow · 2019-04-25 15:43:18 +08:00 · 9820 次点击
    这是一个创建于 1799 天前的主题,其中的信息可能已经有所发展或是发生改变。

    SegmentFault: https://segmentfault.com/q/1010000018986285

    已经使用 Google 搜索过,没找到合适的答案(有些看不懂)

    有以下表:

    表字段: id 是用户唯一 id reg_time 是用户注册时间戳

    mysql> SELECT id,reg_time FROM `apps_user` ;
    +----+------------+
    | id | reg_time   |
    +----+------------+
    |  1 | 1524322845 |
    |  2 | 1524322945 |
    |  3 | 1524323277 |
    |  4 | 1524325325 |
    |  5 | 1524325451 |
    |  6 | 1524325517 |
    |  7 | 1524340390 |
    |  8 | 1524346779 |
    |  9 | 1524346875 |
    | 10 | 1524419896 |
    | 11 | 1524456557 |
    | 12 | 1524456564 |
    | 13 | 1524456663 |
    | 14 | 1524499581 |
    | 15 | 1524504637 |
    | 16 | 1524504718 |
    | 17 | 1524523875 |
    | 18 | 1524528715 |
    | 19 | 1524542755 |
    | 20 | 1524543480 |
    +----+------------+
    20 rows in set (0.00 sec)
    

    我想要获取近 7 天、自定义时间范围内中的 每一天用户注册量

    例如,我想要近 7 天中每一天的用户注册量,预想返回结果如下(这里的预想返回结果与开头表的数据无关联):

    +-------------+-------+
    |    date     | total |
    +-------------+-------+
    |  2019-04-19 |   3   |
    |  2019-04-20 |   0   |
    |  2019-04-21 |   0   |
    |  2019-04-22 |   8   |
    |  2019-04-23 |   0   |
    |  2019-04-24 |   9   |
    |  2019-04-25 |   2   |
    

    请大神解答,先谢谢了!

    第 1 条附言  ·  2019-04-26 13:22:27 +08:00

    在这里统一回复下,我应该算是个业余开发(或者根本不算吧=.=),凭自己兴趣写了个小项目,写后台时,需要用折线图展现出近 7 日等某个时间范围内的每天的注册人数。

    最开始想到是一天天去 count,但我个人觉得对性能有一些影响; 然后想着有没有办法直接查询一次就能获取到指定时间范围内每一天的用户注册量,接着去 Google 搜,翻了几页,没有找到合适的答案,然后就在 SegmentFault 上提问,然后又发到 v2ex

    总结,我还是太年轻,也向各位大佬学习了🙏

    36 条回复    2019-07-11 15:53:01 +08:00
    lihongjie0209
        1
    lihongjie0209  
       2019-04-25 15:45:53 +08:00
    你应该不是开发吧
    shuax
        2
    shuax  
       2019-04-25 15:46:07 +08:00
    先搞个任务,把每一天的注册人数写一个表里面,然后 7 天什么的都是小意思。
    glacer
        3
    glacer  
       2019-04-25 15:50:31 +08:00
    时间戳转成 date,然后 group by
    kisshere
        4
    kisshere  
       2019-04-25 15:51:30 +08:00 via Android
    保持队形,你应该不是开发吧
    luozic
        5
    luozic  
       2019-04-25 15:53:03 +08:00   ❤️ 8
    mysql 按时间段统计(年,季度,月,天,时)
    按年汇总,统计:

    select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y');

    按月汇总,统计:

    select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y-%m');

    按季度汇总,统计:

    select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

    select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by concat(date_format(col, '%Y'),FLOOR((date_format(col, '%m')+2)/3));

    按小时:

    select sum(mymoney) as totalmoney,count(*) as sheets from mytable group by date_format(col, '%Y-%m-%d %H ');

    查询 本年度的数据:

    SELECT * FROM mytable WHERE year(FROM_UNIXTIME(my_time)) = year(curdate())

    查询数据附带季度数:

    SELECT id, quarter(FROM_UNIXTIME(my_time)) FROM mytable;

    查询 本季度的数据:

    SELECT * FROM mytable WHERE quarter(FROM_UNIXTIME(my_time)) = quarter(curdate());

    本月统计:

    select * from mytable where month(my_time1) = month(curdate()) and year(my_time2) = year(curdate())

    本周统计:

    select * from mytable where month(my_time1) = month(curdate()) and week(my_time2) = week(curdate())

    N 天内记录:

    WHERE TO_DAYS(NOW())-TO_DAYS(时间字段)<=N

    本篇文章来源于 Linux 公社网站(www.linuxidc.com) 原文链接: http://www.linuxidc.com/Linux/2012-11/74145.htm
    dovme
        6
    dovme  
       2019-04-25 15:53:16 +08:00
    用 date_format(date, format) 将注册时间转化为 2019-04-19 这样的格式,然后 groupby
    CallMeReznov
        7
    CallMeReznov  
       2019-04-25 15:54:59 +08:00
    想当年我运维入职,老板让我上线上 SQLSERVER 服务器查东西.手抖的一批...
    不过还好我知道有个 count()
    cjlmwcy
        8
    cjlmwcy  
       2019-04-25 15:56:06 +08:00
    你应该不是开发吧
    goodbye1014ac
        9
    goodbye1014ac  
       2019-04-25 16:03:44 +08:00
    你应该不是开发吧
    misaka19000
        10
    misaka19000  
       2019-04-25 16:16:56 +08:00
    在写数据的时候把 年、年-月、年-月-日 这种数据结构写到数据库中,之后可以直接使用 group 来进行搜索
    kifile
        11
    kifile  
       2019-04-25 16:18:56 +08:00
    你应该不是开发吧
    cjlmwcy
        12
    cjlmwcy  
       2019-04-25 16:24:37 +08:00   ❤️ 1
    SELECT
    COUNT(u.id) AS total,
    DATE_FORMAT(
    FROM_UNIXTIME(u.reg_time),
    '%Y-%m-%d'
    ) AS date
    FROM
    apps_user AS u
    WHERE
    FROM_UNIXTIME(u.reg_time) >= DATE_ADD(NOW(), INTERVAL - 7 DAY)
    GROUP BY
    date
    richChou
        13
    richChou  
       2019-04-25 16:30:17 +08:00
    甚至可能都不是资深产品经理
    thisisgpy
        14
    thisisgpy  
       2019-04-25 16:35:10 +08:00
    你这问题吓得我一度怀疑自己到底会不会做开发
    liuzhaowei55
        15
    liuzhaowei55  
       2019-04-25 16:48:22 +08:00   ❤️ 2
    楼上那些讽刺楼主的都是臭傻逼,有一个算一个。
    二楼的做法是一个解决方案,主要问题就是如果某一天没有用户注册,直接用 SQL 是无法查询出来这天的统计数据的,所以需要结合逻辑解决问题,不能直接通过 SQL 获得最终结果。
    liuzhaowei55
        17
    liuzhaowei55  
       2019-04-25 17:00:15 +08:00
    @junan0708 首先表示感谢,其次就是希望下次不会遇到有人问统计半年内结果怎么办的问题了。
    liprais
        18
    liprais  
       2019-04-25 17:04:45 +08:00
    @liuzhaowei55 一个比较简单的做法是先做个日期表,然后用注册表左关联之后就可以补零了
    另外讽刺楼主不对,张嘴就骂人也不对,与诸君共勉了
    liuzhaowei55
        19
    liuzhaowei55  
       2019-04-25 17:09:45 +08:00   ❤️ 2
    @liprais 就是看不惯讽刺的这些人,自己没有真实的处理过这种问题,只看楼主描述不懂难点问题在哪里可以理解,你不要讲话就好了,自然会有明白的人出来解决问题比如二楼,现在我就想那个讽刺楼主的人,自己好好想想这个问题怎么解决吧,自己真实遇到这个问题了是不是能很好的解决。
    Patrick95
        20
    Patrick95  
       2019-04-25 17:11:46 +08:00
    还腆着脸嘲讽楼主,楼主起码有开源项目。
    你们呢,就一张嘴吗
    neoblackcap
        21
    neoblackcap  
       2019-04-25 17:27:37 +08:00
    用 SQL 实现不是不行,不过太复杂了,而且不好维护。像 12 楼的写法,过一个月之后再来看看没有注释的版本,大家觉得可以一眼就看出是怎么回事?
    老实建汇总表,那么天天跑个定时任务脚本就可以了,简单易维护。
    yjxjn
        22
    yjxjn  
       2019-04-25 17:29:12 +08:00
    @lihongjie0209
    @kisshere
    @thisisgpy
    @zhoujunjie221
    @kifile
    @goodbye1014ac
    @cjlmwcy 都舔着脸嘲讽 LZ,键盘侠!
    fangxing204
        23
    fangxing204  
       2019-04-25 17:40:34 +08:00 via Android
    可以直接 group by 按范围的,https://stackoverflow.com/q/21421973/5615038
    mamahaha
        24
    mamahaha  
       2019-04-25 18:18:15 +08:00
    如果用 laravel,这个很方便,可以查询介于时间戳 xxxxxxxx 和 yyyyyyyyy 之间生成的用户
    DB::table('users')->whereBetween('created_at',[ xxxxxxxxx,yyyyyyyyyy])->get();
    victrec
        25
    victrec  
       2019-04-25 18:57:45 +08:00
    最直接的时间戳转成时间再按情况取年月日进行 GROUP BY。不过具体到用,还得看数据多不多,多的话这么搞要炸,楼上说的对,写定时任务记下每天的数量,数据多了每小时统计一把。
    Raymon111111
        26
    Raymon111111  
       2019-04-25 19:04:01 +08:00
    如果只需要数量的话就缓存是最简单的

    每天一个 key, 注册一个 +1

    然后要什么 7 天 30 天的数据就直接在缓存里找就行了

    如果需要更快捷的就再每天 0 点的时候算一个统计的数量
    funky
        27
    funky  
       2019-04-25 19:06:03 +08:00
    redis bitmap
    lihongjie0209
        28
    lihongjie0209  
       2019-04-25 20:23:47 +08:00
    @yjxjn 我就是说了一下 楼主应该不是开发 这就是嘲讽了?有多玻璃心啊

    这种问题本来就是一个应用开发应该知道的:

    1. 要么数据库查询的时候先转化成 date 类型, 再 groupBy, 数据量大的话可能会有性能问题
    2. 要么数据库查询的时候直接用 时间戳 范围, 再在代码里面汇总。
    3. 至于说定时任务和汇总表, 其实原理和上面的一样, 不过就是加了一个缓存而已。

    这个问题的本质就是时间戳和日期的转化, 作为一个应用开发不知道时间戳?我怀疑楼主不是开发而可能是其他岗位临时使用数据库而已。

    屁大点事,就 JJYY 的。
    bwn123
        29
    bwn123  
       2019-04-25 22:12:37 +08:00
    不说动手能力,你应该连了解都没了解过开发流程吧
    jessun1990
        30
    jessun1990  
       2019-04-26 09:31:28 +08:00 via iPhone
    你应该是产品!
    ccl945
        31
    ccl945  
       2019-04-26 10:30:18 +08:00
    别解释了,你就是一个前台客服!
    jswh
        32
    jswh  
       2019-04-26 11:48:17 +08:00
    让开发下载下来然后 excel 处理
    jswh
        33
    jswh  
       2019-04-26 11:48:30 +08:00
    @jswh 手动狗头
    NowTime
        34
    NowTime  
    OP
       2019-04-26 13:03:05 +08:00
    @cjlmwcy 感谢
    NowTime
        35
    NowTime  
    OP
       2019-04-26 13:12:45 +08:00
    @shuax
    @liuzhaowei55
    @neoblackcap
    @Raymon111111
    感谢各位建议,我算是业余的开发吧...我只是凭自己兴趣写的一个小小的项目,我以为能够直接使用 SQL 语句查询到每天的返回会结果,结果我还是太年轻,向各位大佬学习了!
    GreatLiXin
        36
    GreatLiXin  
       2019-07-11 15:53:01 +08:00
    @NowTime
    我有一个比较省力的解法,今天我和你遇到了同样的问题
    我们要统计每一天的 testCase Fail 的数量,同样,有时候会没有 Fail 的 case,这时候就会发现当天的日期直接在查询结果中消失。
    在想了一下午后,我写出来了如下查询语句。很快捷也很简单。
    ```
    SELECT
    count(IF(status = false,true,null)) AS "Fail"
    FROM function_test
    GROUP BY date_time
    ```
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3042 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 31ms · UTC 12:47 · PVG 20:47 · LAX 05:47 · JFK 08:47
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.