程序设计习题 第 9 章 分类别
9.1 分组与汇总
1. 请统计字符串”esProc SPL is a smart desktop data analysis tool”中每个字母出现的次数（忽略大小写，a与A是同一个字母）
2.
（1）编程生成一张学生成绩表，效果如下图。学生id取值为1到50，其中1到25号Class为”one”，25号以后为”Two”。Subject为"English"，"Math"，"PE"，"Science"和"Arts"，Score取100以内的随机数。
（2）计算每名学生的总分和平均分和分数最高的科目
（3）计算每个班级的各科平均分，最高分和最低分
（4）将每个班的各科成绩分别降序排列，并保存为多张Excel，命名格式Class_Subject
（5）统计每个班各科的得分中位数和不及格（低于 60 分）人数
（6）筛选出每个班各科成绩90分以上的人
3. 随机生成一系列邮箱地址，里面包含重复值，例如
（1）去掉重复的邮箱地址
（2）找出重复的邮箱地址
4. 某游戏平台一些玩家的行为活动表如下
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。
（1）查询每位玩家第一次登录平台的日期。
结果示例：
（2）查询每位玩家都用了那些设备登陆
结果示例：
（3）计算在首次登录的第二天再次登录的玩家的比率
5. 有员工表
id 是此表的主键（具有唯一值的列）。
该表的每一行表示雇的名字、他们的部门和他们的经理的id。
如果managerId为空，则该员工没有经理。
没有员工会成为自己的管理者。
编写程序，找出至少有五个直接下属的经理名字
9.2 枚举与对齐
1. 有员工薪资表如下，统计A、B、C部门的平均工资
id |
name |
department |
salary |
101 |
John |
A |
7000 |
102 |
Dan |
B |
11000 |
103 |
James |
A |
9000 |
104 |
Amy |
B |
7000 |
105 |
Anne |
B |
16000 |
106 |
Ron |
A |
8000 |
107 |
Van |
C |
20000 |
108 |
Mike |
C |
7500 |
109 |
Jack |
D |
18000 |
110 |
Kelly |
D |
9500 |
111 |
Bonnie |
C |
12000 |
112 |
Adam |
C |
8500 |
2. 根据订单表，顺序列出2013年每月的订单总数
3. 根据发帖记录表，按标签（Label）将帖子分组，并统计各个标签出现频数按照降序排列
ID |
Title |
Author |
Label |
1 |
Easy analysis of Excel |
John |
Excel,ETL,Import,Export |
2 |
Early commute: Easy to pivot excel |
Dan |
Excel,Pivot,Python |
3 |
Initial experience of SPL |
James |
Basics,Introduction |
4 |
Talking about set and reference |
Amy |
Set,Reference,Dispersed,SQL |
5 |
Early commute: Better weapon than Python |
John |
Python,Contrast,Install |
6 |
… |
… |
… |
结果示例：
Label |
Count |
SPL |
7 |
SQL |
6 |
Basics |
5 |
… |
… |
4. 根据员工薪资表，按工资 8000 以下、8000~12000 和 12000 以上分为[L,M,H]三组组，并统计各组的人数。（用枚举和序号分组两种方式分别实现）
id |
name |
department |
salary |
101 |
John |
A |
7000 |
102 |
Dan |
B |
11000 |
103 |
James |
A |
9000 |
104 |
Amy |
B |
7000 |
105 |
Anne |
B |
16000 |
106 |
Ron |
A |
8000 |
107 |
Van |
C |
20000 |
108 |
Mike |
C |
7500 |
109 |
Jack |
D |
18000 |
110 |
Kelly |
D |
9500 |
111 |
Bonnie |
C |
12000 |
112 |
Adam |
C |
8500 |
结果示例：
5. 根据员工薪资表，按年龄条件 [小于 35 岁, 小于 45 岁] 将员工分组（可重复分组），统计平均工资，不满足条件的分到新组。
id |
name |
birthday |
salary |
101 |
John |
1974/11/20 |
7000 |
102 |
Dan |
1980/7/19 |
11000 |
103 |
James |
1970/12/17 |
9000 |
104 |
Amy |
1985/3/7 |
7000 |
105 |
Anne |
1975/5/13 |
16000 |
106 |
Ron |
1988/3/7 |
8000 |
107 |
Van |
1994/11/20 |
20000 |
108 |
Mike |
1990/7/19 |
7500 |
109 |
Jack |
1970/12/17 |
18000 |
110 |
Kelly |
1985/3/7 |
9500 |
111 |
Bonnie |
1975/5/13 |
12000 |
112 |
Adam |
1995/5/13 |
8500 |
9.3 有序分组
1. 根据员工的入职时间平均分成三组（有余数的顺序分配到某一组），并统计每组的平均工资。部分数据如下：
ID |
NAME |
BIRTHDAY |
ENTRYDATE |
DEPT |
SALARY |
1 |
Rebecca |
1974/11/20 |
2005/3/11 |
R&D |
7000 |
2 |
Ashley |
1980/7/19 |
2008/3/16 |
Finance |
11000 |
3 |
Rachel |
1970/12/17 |
2010/12/1 |
Sales |
9000 |
4 |
Emily |
1985/3/7 |
2006/8/15 |
HR |
7000 |
5 |
Ashley |
1975/5/13 |
2004/7/30 |
R&D |
16000 |
… |
… |
… |
… |
… |
… |
2. 根据历届奥运会奖牌榜统计表，求总成绩蝉联第一名届数最长的国家及其奖牌信息。部分数据如下：
Game |
Nation |
Gold |
Silver |
Copper |
30 |
USA |
46 |
29 |
29 |
30 |
China |
38 |
27 |
23 |
30 |
UK |
29 |
17 |
19 |
30 |
Russia |
24 |
26 |
32 |
30 |
Korea |
13 |
8 |
7 |
… |
… |
… |
… |
… |
Game表示第几届奥运会
3. 上证指数 2020 年收盘价最长连续上涨了多少天？（首个交易日指数上涨）。部分数据如下：
DATE |
CLOSE |
OPEN |
VOLUME |
AMOUNT |
2020/1/2 |
3085.198 |
3066.3357 |
292470208 |
3.27E+11 |
2020/1/3 |
3083.786 |
3089.022 |
261496667 |
2.90E+11 |
2020/1/6 |
3083.408 |
3070.9088 |
312575842 |
3.31E+11 |
2020/1/7 |
3104.802 |
3085.4882 |
276583111 |
2.88E+11 |
2020/1/8 |
3066.893 |
3094.2389 |
297872553 |
3.07E+11 |
… |
… |
… |
… |
… |
9.4 扩展与转置
1. 根据学生成绩表，统计每个班的各科最高分，按列显示。部分数据如下：
CLASS |
STUDENTID |
SUBJECT |
SCORE |
1 |
1 |
English |
84 |
1 |
1 |
Math |
77 |
1 |
1 |
PE |
69 |
1 |
2 |
English |
81 |
1 |
2 |
Math |
80 |
… |
… |
… |
… |
预期结果：
CLASS |
MAX_MATH |
MAX_ENGLISH |
MAX_PE |
1 |
97 |
96 |
97 |
2 |
97 |
96 |
97 |
… |
… |
… |
… |
2. 根据奥运会奖牌总榜，生成每种奖牌的榜单。部分数据如下：
Game |
Nation |
Gold |
Silver |
Copper |
30 |
USA |
46 |
29 |
29 |
30 |
China |
38 |
27 |
23 |
30 |
UK |
29 |
17 |
19 |
30 |
Russia |
24 |
26 |
32 |
30 |
Korea |
13 |
8 |
7 |
… |
… |
… |
… |
… |
预期结果：
Game |
Nation |
Medal_type |
Medals |
30 |
USA |
Gold |
46 |
30 |
USA |
Silver |
29 |
30 |
USA |
Copper |
29 |
30 |
China |
Gold |
38 |
30 |
China |
Silver |
27 |
30 |
China |
Copper |
23 |
… |
… |
… |
… |
3. 有员工薪资表如下：
Name |
Dept |
Area |
Salary |
David |
Sales |
Beijing |
8000 |
Daniel |
R&D |
Beijing |
15000 |
Andrew |
Sales |
Shanghai |
9000 |
Robert |
Sales |
Beijing |
26000 |
Rudy |
R&D |
Shanghai |
23000 |
… |
… |
… |
… |
统计各部门在不同地区的平均工资，预期结果：
Dept |
Beijing |
Shanghai |
… |
R&D |
13000 |
11000 |
… |
Sales |
15000 |
14000 |
… |
4. 按渠道分类的销售表，按年月进行记录。部分数据如下：
YEAR |
MONTH |
ONLINE |
STORE |
2020 |
1 |
2440 |
3746.2 |
2020 |
2 |
1863.4 |
448 |
2020 |
3 |
1813 |
624.8 |
2020 |
4 |
670.8 |
2464.8 |
2020 |
5 |
3730 |
724.5 |
… |
… |
… |
… |
期望查询 2020 年每种渠道每个月的销售额，预期结果：
CATEGORY |
1 |
2 |
3 |
… |
ONLINE |
2440 |
1863.4 |
1813 |
… |
STORE |
3746.2 |
448 |
624.8 |
… |
参考答案
9.1 分组与汇总
1.
A |
|
1 |
esProc SPL is a smart desktop data analysis tool |
2 |
=lower(A1).split("").select(~!=" ") |
3 |
=A2.group(~:letter;~.len():letter_count) |
2.
A |
|
1 |
=["English","Math","PE","Science","Arts"] |
2 |
=A1.(50.new(if(~<=25,"one","Two"):Class, ~:Id, A1.~:Subject, rand(100):Score)).conj().sort(Id) |
3 |
=A2.groups(Id;sum(Score):Total,maxp(Score).Subject:Max_sub) |
4 |
=A2.groups(Class,Subject;avg(Score):Average,max(Score):Max,min(Score):Min) |
5 |
=A2.group(Class,Subject;~.sort@z(Score):Subscore) |
6 |
=A5.(file(~.Class/"_"/~.Subject/".xlsx").xlsexport@t(~.Subscore)) |
7 |
=A2.groups(Class,Subject;median(,Score):Median_socre,count(Score<60):Failed_count) |
8 |
=A2.group(Class,Subject;~.select(Score>=90):Above_90) |
3.
A |
|
1 |
=30.(char(97+rand(26))/"@raqsoft.com") |
2 |
=A1.id() |
3 |
=A1.group().select(~.len()>1).(~(1)) |
4.
A |
|
1 |
=T("player_event.xlsx") |
2 |
=A1.groups(player_id;min(event_date):first_login) |
3 |
=A1.group(player_id;~.(device_id).id():device) |
4 |
=A1.group(player_id;~.(event_date).contain(min(event_date)+1):continu_login) |
5 |
=A4.count(continu_login)/A4.len() |
5.
A |
|
1 |
=T("employee.xlsx") |
2 |
=A1.group(managerId).select(~.len()>=5).(~.managerId) |
3 |
=A2.(A1.select(A2.~==id).name) |
9.2 枚举与对齐
1.
A |
|
1 |
=T("salary.xlsx") |
2 |
[A,B,C] |
3 |
=A1.align@a(A2,department) |
4 |
=A3.new(A2(#):department,~.avg(salary):avg_salary) |
2.
1 |
=T("orders.xlsx") |
2 |
=A1.select(year(Orderdate)==2013) |
3 |
=A2.groups@n(month(Orderdate):Month;count(id):OrderCount) |
3.
A |
|
1 |
=T("post_record.xlsx") |
2 |
=A1.conj(Label.split(",")).id() |
3 |
=A1.align@ar(A2.len(),A2.pos(Label.split(","))) |
4 |
=A3.new(A2(#):Label,~.count():Count).sort@z(Count) |
4.
A |
|
1 |
=T("salary.xlsx") |
2 |
[?<8000,?>=8000 && ?<12000,?>=12000] |
3 |
[L,M,H] |
4 |
=A1.enum(A2,salary) |
5 |
=A4.new(A3(#):Grade,count(~):Count) |
A |
|
1 |
=T("salary.xlsx") |
2 |
[8000,12000] |
3 |
[L,M,H] |
4 |
=A1.group@n(A2.pseg(salary)+1) |
5 |
=A4.new(A3(#):Grade,count(~):Count) |
5.
A |
|
1 |
=T("salary.xlsx") |
2 |
[?<35,?<45] |
3 |
[below 35,below 45] |
4 |
=A1.enum@nr(A2,age(birthday)) |
5 |
=A4.new(if (#>A2.len(), "Other",A3(#)):AGE,~.avg(salary):AvgSalary) |
9.3 有序分组
1.
A |
|
1 |
=T("Employee.csv").sort(ENTRYDATE) |
2 |
=A1.group@n((#-1)*3\A1.len()+ 1) |
3 |
=A2.new(#:GROUP_NO, ~.avg(SALARY):AVG_SALARY) |
2.
A |
|
1 |
=T("Olympic.txt") |
2 |
=A1.sort@z(Game,Gold,Silver,Copper) |
3 |
=A2.group@o1(Game) |
4 |
=A3.group@o(Nation) |
5 |
=A4.maxp@a(~.len()) |
3.
A |
|
1 |
=T("SSEC.csv") |
2 |
=A1.select(year(DATE)==2020).sort(DATE) |
3 |
=A2.group@i(CLOSE<CLOSE[-1]) |
4 |
=A3.max(~.len()) |
9.4 扩展与装置
1.
A |
|
1 |
=T("Scores.csv") |
2 |
=A1.groups(CLASS,SUBJECT; max(SCORE):MAX_SCORE) |
3 |
=A2.pivot(CLASS; SUBJECT, MAX_SCORE; "Math":"MAX_MATH", "English":"MAX_ENGLISH", "PE":"MAX_PE") |
2.
A |
|
1 |
=T("Olympic.txt") |
2 |
=A1.pivot@r(Game,Nation;Medal_type,Medals;Gold, Silver, Copper) |
3.
A |
|
1 |
=T("salary.txt") |
2 |
=A1.pivot@s(Dept; Area, avg(Salary)) |
4.
A |
|
1 |
=T("MonthSales.csv").select(YEAR==2020) |
2 |
=A1.pivot@r(YEAR,MONTH; CATEGORY, AMOUNT) |
3 |
=A2.pivot(CATEGORY; MONTH, AMOUNT) |
按照逻辑顺序，先使用函数 A.pivot@r()列转行，再使用函数 A.pivot() 行转列即可。
