大数据面试SQL043 计算出完成订单数的众数

一、题目

现有用户订单表，请计算用户完成订单数的众数

+----------+-----------+----------------------+
| 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. 单峰众数：如果数据集中只有一个数值出现次数最多，那么这个数值就是众数。例如，在一组数据{1, 2, 2, 3, 4, 4, 4, 5}中，数字4出现的次数最多，所以4是这组数据的众数。
2. 多峰众数：如果数据集中有多个数值出现次数相同且最多，那么这些数值都是众数。例如，在一组数据{1, 2, 2, 3, 3, 4, 4, 5}中，数字2和4都出现了两次，且没有其他数值出现次数更多，所以2和4都是这组数据的众数。
3. 无众数：如果数据集中没有任何数值出现次数明显多于其他数值，那么这组数据没有众数。例如，在一组数据{1, 2, 3, 4, 5}中，每个数值都只出现了一次，所以没有众数。

众数是描述数据集中趋势的一种方式，它特别适用于分类数据和顺序数据。在实际应用中，众数可以帮助我们了解数据的集中趋势，尤其是在数据分布不均匀时。

2）原始题目中给出了订单明细，我们先统计出每个用户的订单数；

3）然后根据订单数据统计出每个订单数对应的人数；

4）对于单峰众数、多峰众数，我们使用rank函数进行排序，序号为1即可；

5）对于无众数情况，需要单独处理，统计rank排序序号最大是否为1，如果最大序号为1说明所有值的个数都一样，则无众数，否则有众数。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

三、SQL

1）先统计出每个用户完成订单数，然后根据订单数据统计“完成该单数”的用户数；

select 
order_cnt,count(user_id) as user_cnt
from
(select user_id,count(order_id) as order_cnt
from t_order_043
group by user_id
) t
group by order_cnt

查询结果

2)根据上面结果，我们发现这个题目中属于是无众数的情况，先试用max()函数进行统计最大的rank_id 如果所有的id都为1这代表所有数据都一致，说明无众数。然后把这个标记放到每一行中，方便后续处理。

with tmp as (select 
order_cnt,count(user_id) as user_cnt
from
(select user_id,count(order_id) as order_cnt
from t_order_043
group by user_id
) t
group by order_cnt)
select
order_cnt,
user_cnt,
rank_id,
if(max(rank_id)over()=1,0,1) as has_mode
from(
select
order_cnt,
user_cnt,
rank()over(order by user_cnt desc) as rank_id
from tmp
) t1

查询结果

3）查询出最后结果，即限定rank_id = 1,has_mode=1的数据。去除order_cnt即可

with tmp as (select 
order_cnt,count(user_id) as user_cnt
from
(select user_id,count(order_id) as order_cnt
from t_order_043
group by user_id
) t
group by order_cnt)
select
order_cnt
from
(select
order_cnt,
user_cnt,
rank_id,
if(max(rank_id)over()=1,0,1) as has_mode
from(
select
order_cnt,
user_cnt,
rank()over(order by user_cnt desc) as rank_id
from tmp
) t1
)tt1
where tt1.rank_id =1
and tt1.has_mode =1
group 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 (select 
order_cnt,count(user_id) as user_cnt
from
(select user_id,count(order_id) as order_cnt
from t_order_043
group by user_id
) t
group by order_cnt)
select
order_cnt
from
(select
order_cnt,
user_cnt,
rank_id,
if(max(rank_id)over()=1,0,1) as has_mode
from(
select
order_cnt,
user_cnt,
rank()over(order by user_cnt desc) as rank_id
from tmp
) t1
)tt1
where tt1.rank_id =1
and tt1.has_mode =1
group 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 DELIMITED
FIELDS 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');

本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文:www.dwsql.com

同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎你的加入，社群免费。