常见大数据面试SQL-查询前2大和前2小用户并有序拼接

本文首发“数据仓库技术”
常见大数据面试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 (<