mysql如何查询一年中下单天数最多的用户？

Question

如题，就一张订单表有user_id，订单表100w+,用户表10w+，查询去年下单天数最多的用户，并按坚持天数列一个降序的排行榜（前200名）

Answer 1

select count(*) as "下单天数" 
from 
    订单表 
where 
    订单表.date between xxx and yyy 
group by 
    订单表.user_id, 订单表.date
order by 
    "下单天数" desc 
limit 0 offset 200    

在这个基础上随便再加上左连接就可以了。以上写法不考虑性能问题，有性能问题的请自己填坑。100w数据的话，如果索引到位了应该也不会有性能问题

Answer 2

我试着来回答一下这个问题

首先，从题目描述来看，反映出需求的两个特点：

第一、时效性低，因为查询的只有去年一年的排行榜，并不是一小时、半小时
第二、数据量大，因为查询的是去年一整年的下单坚持天数，通过订单表明细统计坚持天数，一整年仅仅包含双十一的统计量就是不小的数量级，这个量级像淘宝，可能达到千万，何况是统计一整年的明细，一整年可能过亿

所以，使用MySQL去做这样的统计和排序不是特别合理，就光统计就会耗费大量时间，因为MySQL8.0以下count函数在innodb中的性能是非常差的。

对订单明细做用户级的天数统计的做法，建议将订单表中的订单明细同步到HBase，通过HBase，按user_id分组聚合用户下单的时间，得到每个用户去年一整年下单的总天数，然后，将统计结果同步给MySQL存下来，作为一个统计表，最后，我们直接通过下面的SQL对这张存下来的统计表中的总天数字段排序，得到前200名用户即可。

毕竟这样大数据量的统计可以离线做（无需实时），同时，用HBse统计会更快一些。

select user_id from user_order_statistics order by buy_day_num desc limit 200

其中，统计表结构包含id，_id，buy_day_num这几个字段。

---

至于count为什么慢，此处我就不赘述了，可以参考文章《分页count越来越慢，怎么办？》

Answer 3

SELECT *
FROM user as u
         INNER JOIN (SELECT user_id, COUNT(order_id) as order_nums
                    FROM order
                    GROUP BY user_id
 ORDER BY order_nums desc
 LIMIT 200) as o on u.user_id = o.user_id

试试？

Answer 4

SELECT 
    `user_id`, COUNT(1) AS `cnt`
FROM
    (SELECT 
        `user_id`, `order_date`
    FROM
        `user_order`
    GROUP BY `user_id` , `order_date`) a
GROUP BY `user_id`
ORDER BY `cnt` DESC
LIMIT 200;