使用IFELSE与时间区间实现SQL学生分数按季度时段汇总

现有如下TABLE1数据：

需要按TRIMESTERDATES划分的时间区间，通过IFELSE条件判断，对每个学生的SCORE值进行累加，生成如下TABLE2格式的汇总表：

步骤1：拆分数据并处理时间格式 #

先把TABLE1拆分为学生成绩数据和区间日期数据，同时统一时间格式：

library(dplyr)
library(lubridate)

# 假设原始数据已加载为df
# 提取区间日期并转换为标准日期格式
trimester_dates <- df %>% filter(is.na(STUDENT)) %>% pull(TRIMESTERDATES) %>% ymd()
# 提取学生成绩数据，转换TIME_STAMP为标准日期格式
student_scores <- df %>% filter(!is.na(STUDENT)) %>% mutate(TIME_STAMP = mdy(TIME_STAMP))

步骤2：用IFELSE匹配时间区间 #

通过嵌套IFELSE判断每个成绩所属的时间区间：

# 生成区间标签
interval_labels <- paste(lag(trimester_dates), trimester_dates, sep = "--") %>% na.omit()

# 为每条成绩匹配对应区间
student_scores <- student_scores %>%
  mutate(
    TIMES = ifelse(TIME_STAMP >= trimester_dates[1] & TIME_STAMP < trimester_dates[2], interval_labels[1],
           ifelse(TIME_STAMP >= trimester_dates[2] & TIME_STAMP < trimester_dates[3], interval_labels[2],
           ifelse(TIME_STAMP >= trimester_dates[3] & TIME_STAMP < trimester_dates[4], interval_labels[3],
           ifelse(TIME_STAMP >= trimester_dates[4] & TIME_STAMP < trimester_dates[5], interval_labels[4],
           ifelse(TIME_STAMP >= trimester_dates[5] & TIME_STAMP < trimester_dates[6], interval_labels[5],
           ifelse(TIME_STAMP >= trimester_dates[6] & TIME_STAMP < trimester_dates[7], interval_labels[6],
                  interval_labels[7])))))))

步骤3：生成完整汇总表 #

构建学生与区间的全组合，再合并求和结果：

# 获取所有学生ID
students <- unique(student_scores$STUDENT)
# 生成学生-区间的全量组合
full_table <- expand.grid(STUDENT = students, TIMES = interval_labels)

# 按学生和区间汇总成绩
sum_scores <- student_scores %>%
  group_by(STUDENT, TIMES) %>%
  summarise(SCORE = sum(SCORE), .groups = "drop")

# 合并得到完整汇总表，无成绩的区间填充0
final_table <- full_table %>%
  left_join(sum_scores, by = c("STUDENT", "TIMES")) %>%
  mutate(SCORE = replace_na(SCORE, 0)) %>%
  arrange(STUDENT, TIMES)

# 输出结果
print(final_table)

内容的提问来源于stack exchange，提问作者bvowe