Tidyverse玩转数据练习题-中级

library(tidyverse)

创建表

# 学生信息
student <- tribble(
  ~学号, ~姓名, ~生日, ~性别,
  "01", "赵雷", "1990-01-01", "男",
  "02", "钱电", "1990-12-21", "男",
  "03", "孙风", "1990-05-20", "男",
  "04", "李云", "1990-08-06", "男",
  "05", "周梅", "1991-12-01", "女",
  "06", "吴兰", "1992-03-01", "女",
  "07", "郑竹", "1989-07-01", "女",
  "08", "王菊", "1990-01-20", "女"
)
student

# A tibble: 8 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 01    赵雷  1990-01-01 男   
2 02    钱电  1990-12-21 男   
3 03    孙风  1990-05-20 男   
4 04    李云  1990-08-06 男   
5 05    周梅  1991-12-01 女   
6 06    吴兰  1992-03-01 女   
7 07    郑竹  1989-07-01 女   
8 08    王菊  1990-01-20 女   

# 分数信息
score <- tribble(
  ~学号, ~课程编号, ~成绩,
  "01", "01", 80,
  "01", "02", 90,
  "01", "03", 99,
  "02", "01", 70,
  "02", "02", 60,
  "02", "03", 80,
  "03", "01", 80,
  "03", "02", 80,
  "03", "03", 80,
  "04", "01", 50,
  "04", "02", 30,
  "04", "03", 20,
  "05", "01", 76,
  "05", "02", 87,
  "06", "01", 31,
  "06", "03", 34,
  "07", "02", 89,
  "07", "03", 98
)
score

# A tibble: 18 × 3
   学号  课程编号  成绩
   <chr> <chr>    <dbl>
 1 01    01          80
 2 01    02          90
 3 01    03          99
 4 02    01          70
 5 02    02          60
 6 02    03          80
 7 03    01          80
 8 03    02          80
 9 03    03          80
10 04    01          50
11 04    02          30
12 04    03          20
13 05    01          76
14 05    02          87
15 06    01          31
16 06    03          34
17 07    02          89
18 07    03          98

# 课程信息
course <- tribble(
  ~课程编号, ~课程名称, ~教师编号,
  "01", "语文", "02",
  "02", "数学", "01",
  "03", "英语", "03"
)
course

# A tibble: 3 × 3
  课程编号 课程名称 教师编号
  <chr>    <chr>    <chr>   
1 01       语文     02      
2 02       数学     01      
3 03       英语     03      

# 教师信息
teacher <- tribble(
  ~教师编号, ~教师姓名,
  "01", "张三",
  "02", "李四",
  "03", "王五"
)
teacher

# A tibble: 3 × 2
  教师编号 教师姓名
  <chr>    <chr>   
1 01       张三    
2 02       李四    
3 03       王五    

# 保存信息表格
save(course, score, student, teacher,
  file = "d:/myblog/posts/action9-tidyverse-median-2024-06-14/tech/data.rda"
)
load("d:/myblog/posts/action9-tidyverse-median-2024-06-14/tech/data.rda")

1 查询”01”课程比”02”课程成绩高的学生的信息及课程分数

# 长表变宽表
score_1 <- score %>%
  pivot_wider(
    names_from = 课程编号, values_from = 成绩,
    names_prefix = "课程"
  )
score_1

# A tibble: 7 × 4
  学号  课程01 课程02 课程03
  <chr>  <dbl>  <dbl>  <dbl>
1 01        80     90     99
2 02        70     60     80
3 03        80     80     80
4 04        50     30     20
5 05        76     87     NA
6 06        31     NA     34
7 07        NA     89     98

# 筛选出"01"课程比"02"课程成绩高的学号
score_1 %>%
  filter(课程01 > 课程02) %>%
  left_join(student, by = "学号")

# A tibble: 2 × 7
  学号  课程01 课程02 课程03 姓名  生日       性别 
  <chr>  <dbl>  <dbl>  <dbl> <chr> <chr>      <chr>
1 02        70     60     80 钱电  1990-12-21 男   
2 04        50     30     20 李云  1990-08-06 男   

2 查询”01”课程比”02”课程成绩低的学生的信息及课程分数

score_1 %>%
  filter(课程01 < 课程02) %>%
  left_join(student, by = "学号")

# A tibble: 2 × 7
  学号  课程01 课程02 课程03 姓名  生日       性别 
  <chr>  <dbl>  <dbl>  <dbl> <chr> <chr>      <chr>
1 01        80     90     99 赵雷  1990-01-01 男   
2 05        76     87     NA 周梅  1991-12-01 女   

3 查询平均成绩大于等于60分的学生学号、姓名和平均成绩

score_mean <- score %>%
  group_by(学号) %>%
  summarise(平均成绩 = mean(成绩)) %>%
  # 以上两步可简写为：
  # summarise(平均成绩 = mean(成绩), .by = 学号)
  filter(平均成绩 >= 60)
score_mean

# A tibble: 5 × 2
  学号  平均成绩
  <chr>    <dbl>
1 01        89.7
2 02        70  
3 03        80  
4 05        81.5
5 07        93.5

score_mean %>%
  left_join(student, by = "学号")

# A tibble: 5 × 5
  学号  平均成绩 姓名  生日       性别 
  <chr>    <dbl> <chr> <chr>      <chr>
1 01        89.7 赵雷  1990-01-01 男   
2 02        70   钱电  1990-12-21 男   
3 03        80   孙风  1990-05-20 男   
4 05        81.5 周梅  1991-12-01 女   
5 07        93.5 郑竹  1989-07-01 女   

4 查询平均成绩小于60分的学生的学号和姓名和平均成绩(包括有成绩的和无成绩的)

# 不考虑无成绩的
score %>%
  summarise(平均成绩 = mean(成绩), .by = 学号) %>%
  filter(平均成绩 < 60) %>%
  left_join(student, by = "学号") %>%
  select(学号, 姓名, 平均成绩)

# A tibble: 2 × 3
  学号  姓名  平均成绩
  <chr> <chr>    <dbl>
1 04    李云      33.3
2 06    吴兰      32.5

# 考虑所有学生(包括无成绩的)
student %>%
  left_join(score, by = "学号") %>%
  replace_na(list(成绩 = 0)) %>%
  summarise(平均成绩 = mean(成绩), .by = c(学号, 姓名)) %>%
  filter(平均成绩 < 60)

# A tibble: 3 × 3
  学号  姓名  平均成绩
  <chr> <chr>    <dbl>
1 04    李云      33.3
2 06    吴兰      32.5
3 08    王菊       0  

5 查询所有学生的学号、姓名、选课总数、所有课程的总成绩

student %>%
  left_join(score, by = "学号") %>%
  mutate(成绩 = replace_na(成绩, 0)) %>%
  summarise(
    选课总数 = n_distinct(课程编号),
    总成绩 = sum(成绩),
    .by = c(学号, 姓名)
  )

# A tibble: 8 × 4
  学号  姓名  选课总数 总成绩
  <chr> <chr>    <int>  <dbl>
1 01    赵雷         3    269
2 02    钱电         3    210
3 03    孙风         3    240
4 04    李云         3    100
5 05    周梅         2    163
6 06    吴兰         2     65
7 07    郑竹         2    187
8 08    王菊         1      0

6 查询”李”姓老师的数量

teacher %>%
  mutate(idx = str_detect(教师姓名, "^李")) %>%
  summarise(李姓老师数量 = sum(idx))

# A tibble: 1 × 1
  李姓老师数量
         <int>
1            1

7 查询学过张三老师教授课程的学生信息

teacher %>%
  left_join(course, by = "教师编号") %>%
  left_join(score, by = "课程编号") %>%
  filter(教师姓名 == "张三") %>%
  left_join(student, by = "学号") %>%
  select(学号, 姓名, 课程编号, 课程名称)

# A tibble: 6 × 4
  学号  姓名  课程编号 课程名称
  <chr> <chr> <chr>    <chr>   
1 01    赵雷  02       数学    
2 02    钱电  02       数学    
3 03    孙风  02       数学    
4 04    李云  02       数学    
5 05    周梅  02       数学    
6 07    郑竹  02       数学    

# 或使用半连接
# semi_join(student, ., by = "学号")

8 找出没有学过张三老师教授课程的学生信息

teacher %>%
  left_join(course, by = "教师编号") %>%
  left_join(score, by = "课程编号") %>%
  filter(教师姓名 == "张三") %>%
  # 使用 anti_join 函数从 student 数据集中移除与当前数据集匹配的行
  anti_join(student, ., by = "学号")

# A tibble: 2 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 06    吴兰  1992-03-01 女   
2 08    王菊  1990-01-20 女   

9 查询同时学过”01”和”02”课程的学生信息

score_1 %>%
  filter(!is.na(课程01), !is.na(课程02)) %>%
  semi_join(student, ., by = "学号")

# A tibble: 5 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 01    赵雷  1990-01-01 男   
2 02    钱电  1990-12-21 男   
3 03    孙风  1990-05-20 男   
4 04    李云  1990-08-06 男   
5 05    周梅  1991-12-01 女   

10 查询学过”01”课程，但没有学过”02”“课程的学生信息

score_1 %>%
  filter(!is.na(课程01), is.na(课程02)) %>%
  semi_join(student, ., by = "学号")

# A tibble: 1 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 06    吴兰  1992-03-01 女   

11 查询没有学完全部课程的学生信息

course %>%
  left_join(score, by = "课程编号") %>%
  select(-c(课程编号, 教师编号)) %>%
  pivot_wider(names_from = 课程名称, values_from = 成绩) %>%
  right_join(student, by = "学号") %>%
  filter(if_any(2:4, is.na)) %>%
  select(-c(2:4))

# A tibble: 4 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 05    周梅  1991-12-01 女   
2 06    吴兰  1992-03-01 女   
3 07    郑竹  1989-07-01 女   
4 08    王菊  1990-01-20 女   

12 查询至少有一门课与学生”01”所学课程相同的学生信息

score %>%
  filter(学号 == "01") %>%
  semi_join(score, ., by = "课程编号") %>%
  semi_join(student, ., by = "学号")

# A tibble: 7 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 01    赵雷  1990-01-01 男   
2 02    钱电  1990-12-21 男   
3 03    孙风  1990-05-20 男   
4 04    李云  1990-08-06 男   
5 05    周梅  1991-12-01 女   
6 06    吴兰  1992-03-01 女   
7 07    郑竹  1989-07-01 女   

13 查询与学生”01”学习的课程完全相同的学生信息

解题思路：

• 先将成绩表按学号分组，然后将每组的课程编号合并成一个字符串，作为新的一列。
• 然后将每组的课程编号字符串与”01”的课程编号字符串进行比较，如果相同，则保留该组数据。
• 最后将该组数据与学生信息表进行半连接，得到结果。

score %>%
  arrange(学号) %>%
  summarise(课程汇编 = str_c(课程编号, collapse = ","), .by = 学号) %>%
  filter(课程汇编 == .$课程汇编[学号 == "01"]) %>%
  semi_join(student, ., by = "学号")

# A tibble: 4 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 01    赵雷  1990-01-01 男   
2 02    钱电  1990-12-21 男   
3 03    孙风  1990-05-20 男   
4 04    李云  1990-08-06 男   

14 查询两门及以上不及格课程的学生学号，姓名及其平均成绩

score %>%
  pivot_wider(
    names_from = 课程编号, values_from = 成绩,
    names_prefix = "课程"
  ) %>%
  filter(
    pmap_lgl(
      .[-1],
      \(...) sum(c(...) < 60, na.rm = TRUE) >= 2
    )
  ) %>%
  mutate(
    平均成绩 = pmap_dbl(.[-1], \(...) mean(c(...), na.rm = TRUE))
  ) %>%
  left_join(student, by = "学号") %>%
  select(学号, 姓名, 平均成绩)

# A tibble: 2 × 3
  学号  姓名  平均成绩
  <chr> <chr>    <dbl>
1 04    李云      33.3
2 06    吴兰      32.5

• pmap_dbl(df, \(...) mean(c(...), na.rm = TRUE))可以理解为是pmap_**()系列函数行化操作数据一个固定搭配。

15 检索”01”课程分数小于60，按分数降序排列的学生信息

score %>%
  filter(课程编号 == "01", 成绩 < 60) %>%
  left_join(student, by = "学号") %>%
  select(-课程编号) %>%
  arrange(desc(成绩))

# A tibble: 2 × 5
  学号   成绩 姓名  生日       性别 
  <chr> <dbl> <chr> <chr>      <chr>
1 04       50 李云  1990-08-06 男   
2 06       31 吴兰  1992-03-01 女   

16 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

score %>%
  pivot_wider(
    names_from = 课程编号, values_from = 成绩, names_prefix = "课程"
  ) %>%
  mutate(平均成绩 = pmap_dbl(.[-1], \(...) mean(c(...), na.rm = TRUE))) %>%
  left_join(student, by = "学号") %>%
  arrange(desc(平均成绩)) %>%
  select(学号, 姓名, 性别, everything(), -生日)

# A tibble: 7 × 7
  学号  姓名  性别  课程01 课程02 课程03 平均成绩
  <chr> <chr> <chr>  <dbl>  <dbl>  <dbl>    <dbl>
1 07    郑竹  女        NA     89     98     93.5
2 01    赵雷  男        80     90     99     89.7
3 05    周梅  女        76     87     NA     81.5
4 03    孙风  男        80     80     80     80  
5 02    钱电  男        70     60     80     70  
6 04    李云  男        50     30     20     33.3
7 06    吴兰  女        31     NA     34     32.5

17 查询各科成绩最高分、最低分和平均分，以如下形式显示:

课程编号，课程名称，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

注：及格： >=60，中等为： 70-80，优良为： 80-90，优秀为： >=90

score %>%
  summarise(
    最高分 = max(成绩), 最低分 = min(成绩), 平均分 = mean(成绩),
    及格率 = paste0(round(mean(成绩 >= 60) * 100, 2), "%"), # 转换为百分数
    中等率 = mean(70 <= 成绩 & 成绩 < 80),
    优良率 = mean(80 <= 成绩 & 成绩 < 90), 优秀率 = mean(90 <= 成绩),
    .by = 课程编号
  ) %>%
  left_join(course, by = "课程编号") %>%
  # 将“课程名称”列移动到“课程编号”列之后
  relocate(课程名称, .after = 课程编号) %>%
  select(-教师编号)

# A tibble: 3 × 9
  课程编号 课程名称 最高分 最低分 平均分 及格率 中等率 优良率 优秀率
  <chr>    <chr>     <dbl>  <dbl>  <dbl> <chr>   <dbl>  <dbl>  <dbl>
1 01       语文         80     31   64.5 66.67%  0.333  0.333  0    
2 02       数学         90     30   72.7 83.33%  0      0.5    0.167
3 03       英语         99     20   68.5 66.67%  0      0.333  0.333

18 按照各科成绩进行排序，并且显示排名

score %>%
  group_by(课程编号) %>%
  # 使用mutate函数添加一个新列“排名”，通过min_rank函数根据“成绩”列的负值进行排名
  mutate(排名 = min_rank(-成绩)) %>%
  arrange(课程编号, 排名)

# A tibble: 18 × 4
# Groups:   课程编号 [3]
   学号  课程编号  成绩  排名
   <chr> <chr>    <dbl> <int>
 1 01    01          80     1
 2 03    01          80     1
 3 05    01          76     3
 4 02    01          70     4
 5 04    01          50     5
 6 06    01          31     6
 7 01    02          90     1
 8 07    02          89     2
 9 05    02          87     3
10 03    02          80     4
11 02    02          60     5
12 04    02          30     6
13 01    03          99     1
14 07    03          98     2
15 02    03          80     3
16 03    03          80     3
17 06    03          34     5
18 04    03          20     6

19 查询学生的总成绩，并进行排名

score %>%
  summarise(总成绩 = sum(成绩), .by = 学号) %>%
  left_join(student, by = "学号") %>%
  mutate(排名 = min_rank(-总成绩)) %>%
  arrange(排名) %>%
  relocate(where(is.numeric), .after = 性别)

# A tibble: 7 × 6
  学号  姓名  生日       性别  总成绩  排名
  <chr> <chr> <chr>      <chr>  <dbl> <int>
1 01    赵雷  1990-01-01 男       269     1
2 03    孙风  1990-05-20 男       240     2
3 02    钱电  1990-12-21 男       210     3
4 07    郑竹  1989-07-01 女       187     4
5 05    周梅  1991-12-01 女       163     5
6 04    李云  1990-08-06 男       100     6
7 06    吴兰  1992-03-01 女        65     7

20 查询不同老师所教不同课程平均分从高到低显示

score %>%
  left_join(course, by = "课程编号") %>%
  left_join(teacher, by = "教师编号") %>%
  summarise(平均分 = mean(成绩), .by = c(教师姓名, 课程名称)) %>%
  arrange(desc(平均分))

# A tibble: 3 × 3
  教师姓名 课程名称 平均分
  <chr>    <chr>     <dbl>
1 张三     数学       72.7
2 王五     英语       68.5
3 李四     语文       64.5

21 查询所有课程的成绩第2至3名的学生信息及该课程成绩

• 分组修改：按课程分组，计算成绩排名，则为每门课程的成绩排名
• 筛选行：排名为 2 或 3
• 排序行：按课程、排名排序让结果更整齐
• 左连接：学生信息，需要连接学生表进来
• 选择列：删除不想显示的列

score %>%
  mutate(排名 = min_rank(-成绩), .by = 课程编号) %>%
  filter(排名 %in% c(2, 3)) %>%
  arrange(课程编号, 排名) %>%
  left_join(student, by = "学号") %>%
  select(-c(排名, 课程编号))

# A tibble: 6 × 5
  学号   成绩 姓名  生日       性别 
  <chr> <dbl> <chr> <chr>      <chr>
1 05       76 周梅  1991-12-01 女   
2 07       89 郑竹  1989-07-01 女   
3 05       87 周梅  1991-12-01 女   
4 07       98 郑竹  1989-07-01 女   
5 02       80 钱电  1990-12-21 男   
6 03       80 孙风  1990-05-20 男   

22 统计各科成绩各分数段人数:课程编号，课程名称，[85,100]，[70,85)，[60,70)，[60)及所占百分比

score %>%
  group_by(课程编号) %>%
  mutate(
    分数段 = cut(成绩,
      breaks = c(0, 60, 70, 85, 100, 101), right = FALSE,
      # labels = c("不及格", "60-70", "70-85", "85-100", "优秀")
    )
  ) %>%
  count(课程编号, 分数段) %>%
  mutate(百分比 = scales::percent(n / sum(n), accuracy = 0.01)) %>%
  left_join(course, by = "课程编号") %>%
  select(-教师编号)

# A tibble: 9 × 5
# Groups:   课程编号 [3]
  课程编号 分数段       n 百分比 课程名称
  <chr>    <fct>    <int> <chr>  <chr>   
1 01       [0,60)       2 33.33% 语文    
2 01       [70,85)      4 66.67% 语文    
3 02       [0,60)       1 16.67% 数学    
4 02       [60,70)      1 16.67% 数学    
5 02       [70,85)      1 16.67% 数学    
6 02       [85,100)     3 50.00% 数学    
7 03       [0,60)       2 33.33% 英语    
8 03       [70,85)      2 33.33% 英语    
9 03       [85,100)     2 33.33% 英语    

23 查询学生的平均成绩及名次

score %>%
  summarise(平均成绩 = mean(成绩), .by = 学号) %>%
  mutate(名次 = min_rank(-平均成绩)) %>%
  arrange(名次) %>%
  left_join(student, by = "学号")

# A tibble: 7 × 6
  学号  平均成绩  名次 姓名  生日       性别 
  <chr>    <dbl> <int> <chr> <chr>      <chr>
1 07        93.5     1 郑竹  1989-07-01 女   
2 01        89.7     2 赵雷  1990-01-01 男   
3 05        81.5     3 周梅  1991-12-01 女   
4 03        80       4 孙风  1990-05-20 男   
5 02        70       5 钱电  1990-12-21 男   
6 04        33.3     6 李云  1990-08-06 男   
7 06        32.5     7 吴兰  1992-03-01 女   

24 查询各科成绩前三名的记录

score %>%
  group_by(课程编号) %>%
  slice_head(n = 3)

# A tibble: 9 × 3
# Groups:   课程编号 [3]
  学号  课程编号  成绩
  <chr> <chr>    <dbl>
1 01    01          80
2 02    01          70
3 03    01          80
4 01    02          90
5 02    02          60
6 03    02          80
7 01    03          99
8 02    03          80
9 03    03          80

# 方法2
score %>%
  slice_max(成绩, n = 3, by = 课程编号) %>%
  left_join(student, by = "学号")

# A tibble: 10 × 6
   学号  课程编号  成绩 姓名  生日       性别 
   <chr> <chr>    <dbl> <chr> <chr>      <chr>
 1 01    01          80 赵雷  1990-01-01 男   
 2 03    01          80 孙风  1990-05-20 男   
 3 05    01          76 周梅  1991-12-01 女   
 4 01    02          90 赵雷  1990-01-01 男   
 5 07    02          89 郑竹  1989-07-01 女   
 6 05    02          87 周梅  1991-12-01 女   
 7 01    03          99 赵雷  1990-01-01 男   
 8 07    03          98 郑竹  1989-07-01 女   
 9 02    03          80 钱电  1990-12-21 男   
10 03    03          80 孙风  1990-05-20 男   

25 查询每门课被选修的学生数

score %>%
  count(课程编号, name = "选课人数")

# A tibble: 3 × 2
  课程编号 选课人数
  <chr>       <int>
1 01              6
2 02              6
3 03              6

26 查询出只有两门课程的全部学生的学号和姓名

score %>%
  count(学号) %>%
  filter(n == 2) %>%
  # 使用 semi_join 函数根据 "学号" 列筛选 student 数据框中的记录
  semi_join(student, ., by = "学号") %>%
  select(学号, 姓名)

# A tibble: 3 × 2
  学号  姓名 
  <chr> <chr>
1 05    周梅 
2 06    吴兰 
3 07    郑竹 

27 查询男女生人数

student %>%
  group_by(性别) %>%
  summarise(人数 = n())

# A tibble: 2 × 2
  性别   人数
  <chr> <int>
1 女        4
2 男        4

28 查询名字中含有风字的学生信息

student %>%
  filter(str_detect(姓名, "风"))

# A tibble: 1 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 03    孙风  1990-05-20 男   

29 查询同姓名同性别的学生名单，并统计同姓名人数

• 按姓名、性别分组计数，数量大于1说明有重复。
• 选出姓名重复的行，有几行就说明有几个同姓名。

# 筛选同姓名、性别的名单
student %>%
  count(姓名, 性别) %>%
  filter(n > 1)

# A tibble: 0 × 3
# ℹ 3 variables: 姓名 <chr>, 性别 <chr>, n <int>

# 统计同姓名人数
student %>%
  count(姓名) %>%
  filter(n > 1) %>%
  count()

# A tibble: 1 × 1
      n
  <int>
1     0

30 查询1990年出生的学生信息

library(lubridate)
student %>%
  mutate(出生年份 = year(生日)) %>%
  filter(出生年份 == 1990)

# A tibble: 5 × 5
  学号  姓名  生日       性别  出生年份
  <chr> <chr> <chr>      <chr>    <dbl>
1 01    赵雷  1990-01-01 男        1990
2 02    钱电  1990-12-21 男        1990
3 03    孙风  1990-05-20 男        1990
4 04    李云  1990-08-06 男        1990
5 08    王菊  1990-01-20 女        1990

31 计算每门课程的平均成绩，并按降序排列;若平均成绩相同，按课程编号升序排列

score %>%
  summarise(平均成绩 = mean(成绩), .by = 课程编号) %>%
  arrange(desc(平均成绩), 课程编号)

# A tibble: 3 × 2
  课程编号 平均成绩
  <chr>       <dbl>
1 02           72.7
2 03           68.5
3 01           64.5

32 查询平均成绩>=85分的学生学号、姓名和平均成绩

score %>%
  summarise(平均成绩 = mean(成绩), .by = 学号) %>%
  filter(平均成绩 > 85) %>%
  left_join(student, by = "学号") %>%
  select(学号, 姓名, 平均成绩)

# A tibble: 2 × 3
  学号  姓名  平均成绩
  <chr> <chr>    <dbl>
1 01    赵雷      89.7
2 07    郑竹      93.5

33 查询课程名称为数学，且分数低于60的学生姓名和分数

score %>%
  left_join(course, by = "课程编号") %>%
  filter(课程名称 == "数学", 成绩 < 60) %>%
  left_join(student, by = "学号") %>%
  select(姓名, 成绩)

# A tibble: 1 × 2
  姓名   成绩
  <chr> <dbl>
1 李云     30

34 查询所有学生的课程及分数情况

score %>%
  left_join(course, by = "课程编号") %>%
  left_join(student, by = "学号") %>%
  select(-c(学号, 课程编号, 教师编号, 生日, 性别)) %>%
  pivot_wider(names_from = 课程名称, values_from = 成绩) %>%
  mutate(总分 = pmap_dbl(.[-1], \(...) sum(c(...), na.rm = TRUE)))

# A tibble: 7 × 5
  姓名   语文  数学  英语  总分
  <chr> <dbl> <dbl> <dbl> <dbl>
1 赵雷     80    90    99   269
2 钱电     70    60    80   210
3 孙风     80    80    80   240
4 李云     50    30    20   100
5 周梅     76    87    NA   163
6 吴兰     31    NA    34    65
7 郑竹     NA    89    98   187

35 查询任何一门课程成绩都在70分以上的姓名、课程名称和分数

score %>%
  left_join(course, by = "课程编号") %>%
  select(-c(课程编号, 教师编号)) %>%
  pivot_wider(names_from = 课程名称, values_from = 成绩) %>%
  left_join(student, by = "学号") %>%
  # 过滤数据框，保留第二到第四列中所有值大于70的行 
  filter(pmap_lgl(.[2:4], \(...) all(na.omit(c(...)) > 70))) %>%
  select(学号, 姓名, 语文, 数学, 英语)

# A tibble: 4 × 5
  学号  姓名   语文  数学  英语
  <chr> <chr> <dbl> <dbl> <dbl>
1 01    赵雷     80    90    99
2 03    孙风     80    80    80
3 05    周梅     76    87    NA
4 07    郑竹     NA    89    98

36 查询不及格的课程

score %>% 
  filter(成绩 < 60) %>%
  left_join(course, by = "课程编号") %>% 
  select(课程编号, 课程名称, 成绩)

# A tibble: 5 × 3
  课程编号 课程名称  成绩
  <chr>    <chr>    <dbl>
1 01       语文        50
2 02       数学        30
3 03       英语        20
4 01       语文        31
5 03       英语        34

37 查询课程01的成绩大于等于80的学生学号和姓名

score %>% 
  filter(课程编号 == "01", 成绩 >= 80) %>% 
  left_join(student, by = "学号") %>% 
  select(学号, 姓名, 成绩)

# A tibble: 2 × 3
  学号  姓名   成绩
  <chr> <chr> <dbl>
1 01    赵雷     80
2 03    孙风     80

38 查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩

# 方法1
score %>% 
  left_join(course, by = "课程编号") %>% 
  left_join(teacher, by = "教师编号") %>% 
  filter(教师姓名 == "张三") %>% 
  left_join(student, by = "学号") %>% 
  slice_max(成绩)

# A tibble: 1 × 9
  学号  课程编号  成绩 课程名称 教师编号 教师姓名 姓名  生日       性别 
  <chr> <chr>    <dbl> <chr>    <chr>    <chr>    <chr> <chr>      <chr>
1 01    02          90 数学     01       张三     赵雷  1990-01-01 男   

# 方法2
teacher %>% 
  left_join(course, by = "教师编号") %>% 
  filter(教师姓名 == "张三") %>% 
  semi_join(score, ., by = "课程编号")  %>% 
  slice_max(成绩, n = 1) %>% 
  left_join(student, by = "学号")

# A tibble: 1 × 6
  学号  课程编号  成绩 姓名  生日       性别 
  <chr> <chr>    <dbl> <chr> <chr>      <chr>
1 01    02          90 赵雷  1990-01-01 男   

39 查询不同课程成绩相同的学生学号、课程编号、学生成绩

score %>% 
  count(课程编号, 成绩) %>% 
  filter(n > 1) %>% 
  semi_join(score, ., by = c("课程编号", "成绩")) %>% 
  arrange(课程编号)

# A tibble: 4 × 3
  学号  课程编号  成绩
  <chr> <chr>    <dbl>
1 01    01          80
2 03    01          80
3 02    03          80
4 03    03          80

40 查询每门课程成绩最好的前两名

score %>% 
  group_by(课程编号) %>% 
  slice_max(成绩, n = 2)

# A tibble: 6 × 3
# Groups:   课程编号 [3]
  学号  课程编号  成绩
  <chr> <chr>    <dbl>
1 01    01          80
2 03    01          80
3 01    02          90
4 07    02          89
5 01    03          99
6 07    03          98

41 统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

score %>% 
  count(课程编号) %>% 
  filter(n > 5) %>% 
  arrange(desc(n), 课程编号)

# A tibble: 3 × 2
  课程编号     n
  <chr>    <int>
1 01           6
2 02           6
3 03           6

42 检索至少选修两门课程的学生学号

score %>% 
  count(学号) %>% 
  filter(n >= 2)

# A tibble: 7 × 2
  学号      n
  <chr> <int>
1 01        3
2 02        3
3 03        3
4 04        3
5 05        2
6 06        2
7 07        2

43 查询选修了全部课程的学生信息

course %>% 
  left_join(score, by = "课程编号") %>% 
  select(-c(课程编号, 教师编号)) %>% 
  pivot_wider(names_from = 课程名称, values_from = 成绩) %>% 
  right_join(student, by = "学号") %>% 
  filter(if_all(2:4, \(x) !is.na(x))) %>% 
  select(-c(2:4))

# A tibble: 4 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <chr>      <chr>
1 01    赵雷  1990-01-01 男   
2 02    钱电  1990-12-21 男   
3 03    孙风  1990-05-20 男   
4 04    李云  1990-08-06 男   

44 查询各学生的年龄: 按照出生日期来算,当前年月日<出生年月的月日,则年龄减1

student %>% 
  mutate(生日 = ymd(生日),
  年龄  = 生日 %--% today() %/% dyears(1))

# A tibble: 8 × 5
  学号  姓名  生日       性别   年龄
  <chr> <chr> <date>     <chr> <dbl>
1 01    赵雷  1990-01-01 男       34
2 02    钱电  1990-12-21 男       33
3 03    孙风  1990-05-20 男       34
4 04    李云  1990-08-06 男       34
5 05    周梅  1991-12-01 女       32
6 06    吴兰  1992-03-01 女       32
7 07    郑竹  1989-07-01 女       35
8 08    王菊  1990-01-20 女       34

45 查询本周过生日的学生

• 修改列：将生日转化为日期型
• 根据条件筛选：生日周等于每周，筛选行

student %>% 
  mutate(生日 = ymd(生日)) %>% 
  filter(week(生日) == week(today()))

# A tibble: 0 × 4
# ℹ 4 variables: 学号 <chr>, 姓名 <chr>, 生日 <date>, 性别 <chr>

46 查询下周过生日的学生

student %>% 
  mutate(生日 = ymd(生日)) %>% 
  filter(week(生日) == week(today()) + 1)

# A tibble: 0 × 4
# ℹ 4 variables: 学号 <chr>, 姓名 <chr>, 生日 <date>, 性别 <chr>

47 查询本月过生日的学生

student %>% 
  mutate(生日 = ymd(生日)) %>% 
  filter(month(生日) == month(today()))

# A tibble: 0 × 4
# ℹ 4 variables: 学号 <chr>, 姓名 <chr>, 生日 <date>, 性别 <chr>

48 查询下月过生日的学生

student %>% 
  mutate(生日 = ymd(生日)) %>% 
  filter(month(生日) == month(today()) + 1)

# A tibble: 2 × 4
  学号  姓名  生日       性别 
  <chr> <chr> <date>     <chr>
1 02    钱电  1990-12-21 男   
2 05    周梅  1991-12-01 女