常见大数据面试SQL-查询前2大和前2小用户并有序拼接
一、题目
有用户账户表，包含年份，用户id和值,请按照年份分组，取出值前两小和前两大对应的用户id,**注意：**需要保持值最小和最大的用户id排首位。
样例数据
+-------+----------+--------+
| year | user_id | value |
+-------+----------+--------+
| 2022 | A | 30 |
| 2022 | B | 10 |
| 2022 | C | 20 |
| 2023 | A | 40 |
| 2023 | B | 50 |
| 2023 | C | 20 |
| 2023 | D | 30 |
+-------+----------+--------+期望结果
+-------+-----------------+-----------------+
| year | max2_user_list | min2_user_list |
+-------+-----------------+-----------------+
| 2022 | A,C | B,C |
| 2023 | B,A | C,D |
+-------+-----------------+-----------------+二、分析
属于取最大最小记录的升级版，最大难点在于拼用户要保证有序。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1.row_number函数根据年份分组，value正排和倒排得到两个序列
使用row_number函数根据年份分组，根据value正排得到 asc_rn用于取出value最小两行记录，根据value倒叙得到desc_rn用于取出最大两行记录
执行SQL
select user_id
, year
, value
, row_number() over (partition by year order by value desc) as desc_rn
, row_number() over (partition by year order by value) as rn
from t12_amount查询结果
+----------+-------+--------+----------+-----+
| user_id | year | value | desc_rn | rn |
+----------+-------+--------+----------+-----+
| B | 2022 | 10 | 3 | 1 |
| C | 2022 | 20 | 2 | 2 |
| A | 2022 | 30 | 1 | 3 |
| C | 2023 | 20 | 4 | 1 |
| D | 2023 | 30 | 3 | 2 |
| A | 2023 | 40 | 2 | 3 |
| B | 2023 | 50 | 1 | 4 |
+----------+-------+--------+----------+-----+2.根据年份分组，取出value最大user_id,第二大user_id,最小user_id,第二小user_id
根据年份分组，取出每年最大、第二大，最小、第二小用户ID。使用if对desc_rn,rn进行判断，对符合条件的数据取出user_id,其他去null,然后使用聚合函数取出结果。
执行SQL
select year,
max(if(desc_rn = 1, user_id, null)) as max1_user_id,
max(if(desc_rn = 2, user_id, null)) as max2_user_id,
max(if(rn = 1, user_id, null)) as min1_user_id,
max(if(rn = 2, user_id, null)) as min2_user_id
from (select user_id
, year
, value
, row_number() over (partition by year order by value desc) as desc_rn
, row_number() over (partition by year order by value) as rn
from t12_amount) t1
group by year查询结果
+-------+---------------+---------------+---------------+---------------+
| year | max1_user_id | max2_user_id | min1_user_id | min2_user_id |
+-------+---------------+---------------+---------------+---------------+
| 2022 | A | C | B | C |
| 2023 | B | A | C | D |
+-------+---------------+---------------+---------------+---------------+3.按照顺序拼接，得到最终结果
按照题目要求，进行字符拼接
- 拼接max1_user_id、max2_user_id为max2_list；
- 拼接min1_user_id、min2_user_id为min2_list;
执行SQL
select year,
concat(max(if(desc_rn = 1, user_id, null)), ',',
max(if(desc_rn = 2, user_id, null))) as max2_user_list,
concat(max(if(rn = 1, user_id, null)), ',',
max(if(rn = 2, user_id, null))) as min2_user_list
from (select user_id
, year
, value
, row_number() over (partition by year order by value desc) as desc_rn
, row_number() over (partition by year order by value) as rn
from t12_amount) t1
group by year查询结果
+-------+-----------------+-----------------+
| year | max2_user_list | min2_user_list |
+-------+-----------------+-----------------+
| 2022 | A,C | B,C |
| 2023 | B,A | C,D |
+-------+-----------------+-----------------+四、建表语句和数据插入
--建表语句
create table if not exists t12_amount
(
year string,
user_id string,
value bigint
);
--插入数据
insert into t12_amount(year, user_id, value)
values ('2022', 'A', 30),
('2022', 'B', 10),
('2022', 'C', 20),
('2023', 'A', 40),
('2023', 'B', 50),
('2023', 'C', 20),
('2023', 'D', 30)本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；