Laravel实现分组查询并计算平均值：基于assessment_data表的技术问询

我看你已经有了一个统计各评估等级学生数量的查询基础，现在需要扩展它来实现分组计算平均值的功能对吧？根据你的表结构和需求，我分几种常见场景给你完善查询语句：

场景1：按学科分组，计算该学科所有学生的平均评估等级 #

如果你的assessed_level是数值类型（比如1-5的分数），可以直接用AVG()聚合函数：

$assessment_tally_levels = DB::table('assessment_data')
    ->join('subjects', 'assessment_data.subject_id', '=', 'subjects.id')
    ->select(
        'subjects.short_name as Subject',
        DB::raw('count(*) as TotalStudents'), // 统计该学科总学生数
        DB::raw('AVG(assessment_data.assessed_level) as AverageLevel') // 计算平均等级
    )
    ->where('assessment_data.user_id', Auth::id())
    ->groupBy('subjects.short_name') // 按学科分组
    ->get();

场景2：按学科+单元分组，计算每个单元的平均等级 #

如果需要更细粒度的分组（比如结合学科和单元），只需在groupBy中添加对应的字段：

$assessment_tally_levels = DB::table('assessment_data')
    ->join('subjects', 'assessment_data.subject_id', '=', 'subjects.id')
    ->select(
        'subjects.short_name as Subject',
        'assessment_data.unit as Unit',
        DB::raw('count(*) as StudentsInUnit'),
        DB::raw('AVG(assessment_data.assessed_level) as AverageLevelInUnit')
    )
    ->where('assessment_data.user_id', Auth::id())
    ->groupBy('subjects.short_name', 'assessment_data.unit') // 多字段分组
    ->get();

场景3：处理字符串类型的评估等级 #

如果assessed_level是字符串（比如"A/B/C/D/F"这类等级），需要先把它映射为数值才能计算有意义的平均值，用SQL的CASE语句实现：

$assessment_tally_levels = DB::table('assessment_data')
    ->join('subjects', 'assessment_data.subject_id', '=', 'subjects.id')
    ->select(
        'subjects.short_name as Subject',
        DB::raw('count(*) as TotalStudents'),
        DB::raw('AVG(CASE assessment_data.assessed_level 
            WHEN "A" THEN 5 
            WHEN "B" THEN 4 
            WHEN "C" THEN 3 
            WHEN "D" THEN 2 
            WHEN "F" THEN 1 
            ELSE 0 END) as AverageLevel')
    )
    ->where('assessment_data.user_id', Auth::id())
    ->groupBy('subjects.short_name')
    ->get();

关键注意点： #

• 确保groupBy包含所有select中的非聚合字段，避免因SQL的only_full_group_by模式导致报错。
• AVG()函数会自动忽略NULL值，如果你的assessed_level可能存在空值，不需要额外处理。
• 如果需要保留原查询中按评估等级分组的逻辑，同时计算平均值，可以把assessment_data.assessed_level加入groupBy和select中。

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