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

一、题目

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

代码语言:javascript
复制
+----------+-----------+----------------------+
| 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)先统计出每个用户完成订单数,然后根据订单数据统计“完成该单数”的用户数;

代码语言:javascript
复制
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这代表所有数据都一致,说明无众数。然后把这个标记放到每一行中,方便后续处理。

代码语言:javascript
复制
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即可

代码语言:javascript
复制
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;

代码语言:javascript
复制
INSERT INTO t_order_043 VALUES 
(2, 1021, '2024-02-27 12:47:00');

查询语句

代码语言:javascript
复制
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个用户,无众数。

代码语言:javascript
复制
INSERT INTO t_order_043 VALUES 
(3, 1022, '2024-02-27 12:48:00');

执行上面语句结果

四、建表语句和数据插入

代码语言:javascript
复制
--建表语句
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');