over 的 mapping_strategy
困难
23-polars-only Polars 专属
overwindowlisttop-n
给每个员工加一列 top2:他所在部门里工资最高的两个人的名字列表。
- 部门按
dept_id 划分,dept_id 为 null 的员工也作为一个部门。
- 部门内按
salary 从高到低取前两名;工资相同时 emp_id 小的排前面。
top2 是字符串列表,元素顺序就是上面的名次顺序;部门只有一个人时列表只有一个元素。
- 同一部门的每个员工,
top2 都相同。输出的行数与输入相同。
考点:在窗口表达式里算出“每组一个列表”,再把它原样回填到组内每一行,不要先聚合再 join。判题只看结果。
输入
employees(emp_id, name, dept_id, manager_id, hire_date, salary, title, email)
输出
列为 emp_id, name, dept_id, top2,行顺序不限。
输入表 (case base,共 2 个 case:base、ties)
期望输出 (case base,行顺序不限)
| # | emp_idInt64 | nameString | dept_idInt64 | top2List(String) |
| 1 |
1 |
Grace Liu |
1 |
["Grace Liu"] |
| 2 |
2 |
Leo Wang |
2 |
["Leo Wang", "Larry Ren"] |
| 3 |
3 |
Sara Chen |
3 |
["Sara Chen", "Amy Yu"] |
| 4 |
4 |
Lucas Zhang |
4 |
["Bob Pan", "Lucas Zhang"] |
| 5 |
5 |
Mia Zhao |
5 |
["Mia Zhao", "Xin Cai"] |
| 6 |
6 |
Kevin Wu |
3 |
["Sara Chen", "Amy Yu"] |
| 7 |
7 |
Lily Xu |
6 |
["Lily Xu", "Hao Guo"] |
| 8 |
8 |
Tom Huang |
4 |
["Bob Pan", "Lucas Zhang"] |
| 9 |
9 |
Liam Zhou |
4 |
["Bob Pan", "Lucas Zhang"] |
| 10 |
10 |
Nina Yang |
4 |
["Bob Pan", "Lucas Zhang"] |
| 11 |
11 |
Oscar Lin |
4 |
["Bob Pan", "Lucas Zhang"] |
| 12 |
12 |
Linda Ma |
5 |
["Mia Zhao", "Xin Cai"] |
| 13 |
13 |
Eric Sun |
5 |
["Mia Zhao", "Xin Cai"] |
| 14 |
14 |
Ivy He |
5 |
["Mia Zhao", "Xin Cai"] |
| 15 |
15 |
Lei Gao |
5 |
["Mia Zhao", "Xin Cai"] |
| 16 |
16 |
Hao Guo |
6 |
["Lily Xu", "Hao Guo"] |
| 17 |
17 |
Fang Luo |
6 |
["Lily Xu", "Hao Guo"] |
| 18 |
18 |
Jun Liang |
6 |
["Lily Xu", "Hao Guo"] |
| 19 |
19 |
Dana Song |
3 |
["Sara Chen", "Amy Yu"] |
| 20 |
20 |
Victor Tang |
3 |
["Sara Chen", "Amy Yu"] |
| 21 |
21 |
Wen Han |
3 |
["Sara Chen", "Amy Yu"] |
| 22 |
22 |
Lu Feng |
3 |
["Sara Chen", "Amy Yu"] |
| 23 |
23 |
Yan Deng |
3 |
["Sara Chen", "Amy Yu"] |
| 24 |
24 |
Zoe Cao |
7 |
["Zoe Cao"] |
| 25 |
25 |
Ping Peng |
4 |
["Bob Pan", "Lucas Zhang"] |
| 26 |
26 |
Qing Xie |
4 |
["Bob Pan", "Lucas Zhang"] |
| 27 |
27 |
Rui Lu |
null |
["Uma Jiang", "Rui Lu"] |
| 28 |
28 |
Uma Jiang |
null |
["Uma Jiang", "Rui Lu"] |
| 29 |
29 |
Xin Cai |
5 |
["Mia Zhao", "Xin Cai"] |
| 30 |
30 |
Bob Pan |
4 |
["Bob Pan", "Lucas Zhang"] |
| 31 |
31 |
Chen Du |
6 |
["Lily Xu", "Hao Guo"] |
| 32 |
32 |
Amy Yu |
3 |
["Sara Chen", "Amy Yu"] |
| 33 |
33 |
Larry Ren |
2 |
["Leo Wang", "Larry Ren"] |
| 34 |
34 |
Wei Shi |
5 |
["Mia Zhao", "Xin Cai"] |
| 35 |
35 |
Zhang Min |
4 |
["Bob Pan", "Lucas Zhang"] |
| 36 |
36 |
王磊 |
3 |
["Sara Chen", "Amy Yu"] |
| 37 |
37 |
李静 |
5 |
["Mia Zhao", "Xin Cai"] |
| 38 |
38 |
Henry Kong |
6 |
["Lily Xu", "Hao Guo"] |
| 39 |
39 |
Logan Bai |
4 |
["Bob Pan", "Lucas Zhang"] |
| 40 |
40 |
Sophie Fan |
3 |
["Sara Chen", "Amy Yu"] |
共 40 行
参考答案 · over 的 mapping_strategy