本文首发: 常见大数据面试SQL-查询前2大和前2小用户并有序拼接 数据仓库技术.
一、题目
有用户账户表,包含年份,用户id和值,请按照年份分组,取出值前两小和前两大对应的用户id,**注意:**需要保持值最小和最大的用户id排首位。
样例数据
期望结果
二、分析
属于取最大最小记录的升级版,最大难点在于拼用户要保证有序。
维度 | 评分 |
---|---|
题目难度 | ⭐️⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1.row_number函数根据年份分组,value正排和倒排得到两个序列
使用row_number函数根据年份分组,根据value正排得到 asc_rn用于取出value最小两行记录,根据value倒叙得到desc_rn用于取出最大两行记录
执行SQL
查询结果
2.根据年份分组,取出value最大user_id,第二大user_id,最小user_id,第二小user_id
根据年份分组,取出每年最大、第二大,最小、第二小用户ID。使用if对desc_rn,rn进行判断,对符合条件的数据取出user_id,其他去null,然后使用聚合函数取出结果。
执行SQL
查询结果
3.按照顺序拼接,得到最终结果
按照题目要求,进行字符拼接
- 拼接max1_user_id、max2_user_id为max2_list;
- 拼接min1_user_id、min2_user_id为min2_list;
执行SQL
查询结果