一、题目
现有用户订单表，请计算用户完成订单数的众数
+----------+-----------+----------------------+| user_id | order_id | order_time |+----------+-----------+----------------------+| 1 | 1001 | 2024-02-25 08:00:00 || 1 | 1002 | 2024-02-25 09:30:00 || 2 | 1003 | 2024-02-25 10:45:00 || 2 | 1004 | 2024-02-25 12:15:00 || 2 | 1005 | 2024-02-25 13:45:00 || 3 | 1006 | 2024-02-25 15:00:00 || 4 | 1007 | 2024-02-25 16:30:00 || 4 | 1008 | 2024-02-25 18:00:00 || 1 | 1009 | 2024-02-26 07:45:00 || 1 | 1010 | 2024-02-26 09:15:00 || 1 | 1011 | 2024-02-26 10:30:00 || 2 | 1012 | 2024-02-26 12:00:00 || 3 | 1013 | 2024-02-26 14:00:00 || 3 | 1014 | 2024-02-26 15:30:00 || 4 | 1015 | 2024-02-26 17:00:00 || 4 | 1016 | 2024-02-26 18:30:00 || 1 | 1017 | 2024-02-27 08:15:00 || 1 | 1018 | 2024-02-27 09:45:00 || 2 | 1019 | 2024-02-27 11:00:00 || 2 | 1020 | 2024-02-27 12:45:00 |+----------+-----------+----------------------+
二、分析
题目要求找出用户完成订单数的众数
1)众数定义
众数（Mode）是统计学中的一个概念，它指的是在一组数据中出现次数最多的数值。在不同的数据集中，众数可能有一个或多个，甚至可能没有众数。以下是众数的一些特点：
单峰众数：如果数据集中只有一个数值出现次数最多，那么这个数值就是众数。例如，在一组数据{1, 2, 2, 3, 4, 4, 4, 5}中，数字4出现的次数最多，所以4是这组数据的众数。 多峰众数：如果数据集中有多个数值出现次数相同且最多，那么这些数值都是众数。例如，在一组数据{1, 2, 2, 3, 3, 4, 4, 5}中，数字2和4都出现了两次，且没有其他数值出现次数更多，所以2和4都是这组数据的众数。 无众数：如果数据集中没有任何数值出现次数明显多于其他数值，那么这组数据没有众数。例如，在一组数据{1, 2, 3, 4, 5}中，每个数值都只出现了一次，所以没有众数。 众数是描述数据集中趋势的一种方式，它特别适用于分类数据和顺序数据。在实际应用中，众数可以帮助我们了解数据的集中趋势，尤其是在数据分布不均匀时。
2）原始题目中给出了订单明细，我们先统计出每个用户的订单数；
3）然后根据订单数据统计出每个订单数对应的人数；
4）对于单峰众数、多峰众数，我们使用rank函数进行排序，序号为1即可；
5）对于无众数情况，需要单独处理，统计rank排序序号最大是否为1，如果最大序号为1说明所有值的个数都一样，则无众数，否则有众数。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1）先统计出每个用户完成订单数，然后根据订单数据统计“完成该单数”的用户数；
selectorder_cnt,count(user_id) as user_cntfrom(select user_id,count(order_id) as order_cntfrom t_order_043group by user_id) tgroup by order_cnt
查询结果
2)根据上面结果，我们发现这个题目中属于是无众数的情况，先试用max()函数进行统计最大的rank_id 如果所有的id都为1这代表所有数据都一致，说明无众数。然后把这个标记放到每一行中，方便后续处理。
with tmp as (selectorder_cnt,count(user_id) as user_cntfrom(select user_id,count(order_id) as order_cntfrom t_order_043group by user_id) tgroup by order_cnt)selectorder_cnt,user_cnt,rank_id,if(max(rank_id)over()=1,0,1) as has_modefrom(selectorder_cnt,user_cnt,rank()over(order by user_cnt desc) as rank_idfrom tmp) t1
查询结果
3）查询出最后结果，即限定rank_id = 1,has_mode=1的数据。去除order_cnt即可
with tmp as (selectorder_cnt,count(user_id) as user_cntfrom(select user_id,count(order_id) as order_cntfrom t_order_043group by user_id) tgroup by order_cnt)selectorder_cntfrom(selectorder_cnt,user_cnt,rank_id,if(max(rank_id)over()=1,0,1) as has_modefrom(selectorder_cnt,user_cnt,rank()over(order by user_cnt desc) as rank_idfrom tmp) t1)tt1where tt1.rank_id =1and tt1.has_mode =1group by order_cnt
查询结果
其他数据场景测试
1.给用户2增加一条记录，如此，则1,2均有7条订单，众数为7;
INSERT INTO t_order_043 VALUES(2, 1021, '2024-02-27 12:47:00');
查询语句
with tmp as (selectorder_cnt,count(user_id) as user_cntfrom(select user_id,count(order_id) as order_cntfrom t_order_043group by user_id) tgroup by order_cnt)selectorder_cntfrom(selectorder_cnt,user_cnt,rank_id,if(max(rank_id)over()=1,0,1) as has_modefrom(selectorder_cnt,user_cnt,rank()over(order by user_cnt desc) as rank_idfrom tmp) t1)tt1where tt1.rank_id =1and tt1.has_mode =1group by order_cnt
查询结果
2.在给3号用户增加一条记录，这样3,4用户均有4笔订单，订单数据为4笔和7笔的分别有2个用户，无众数。
INSERT INTO t_order_043 VALUES(3, 1022, '2024-02-27 12:48:00');
执行上面语句结果
四、建表语句和数据插入
--建表语句CREATE TABLE IF NOT EXISTS t_order_043 (user_id INT,order_id INT,order_time STRING)ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','STORED AS orc;--插入数据INSERT INTO t_order_043 VALUES(1, 1001, '2024-02-25 08:00:00'),(1, 1002, '2024-02-25 09:30:00'),(2, 1003, '2024-02-25 10:45:00'),(2, 1004, '2024-02-25 12:15:00'),(2, 1005, '2024-02-25 13:45:00'),(3, 1006, '2024-02-25 15:00:00'),(4, 1007, '2024-02-25 16:30:00'),(4, 1008, '2024-02-25 18:00:00'),(1, 1009, '2024-02-26 07:45:00'),(1, 1010, '2024-02-26 09:15:00'),(1, 1011, '2024-02-26 10:30:00'),(2, 1012, '2024-02-26 12:00:00'),(3, 1013, '2024-02-26 14:00:00'),(3, 1014, '2024-02-26 15:30:00'),(4, 1015, '2024-02-26 17:00:00'),(4, 1016, '2024-02-26 18:30:00'),(1, 1017, '2024-02-27 08:15:00'),(1, 1018, '2024-02-27 09:45:00'),(2, 1019, '2024-02-27 11:00:00'),(2, 1020, '2024-02-27 12:45:00');
本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文:
有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎扫码联系博主加入
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
