如何在Laravel中基于每日gc_score计算代理月度平均gc_score？

原查询按audit_date和cru_agent_detail_id分组，统计每日的审核数、通过数、失败数及当日GC评分平均值。现在需要修改为按月份+代理分组，其中月度GC评分是该代理当月所有每日GC评分的平均值（比如某代理两天的GC评分分别是75和85，月度评分就是(75+85)/2=80），同时统计该代理当月的总审核数、总通过数、总失败数。

现有查询代码 #

$query = DB::table('qc_feedbacks')
    ->selectRaw("
        COUNT(cru_agent_detail_id) as numberofEvaluations,
        COUNT( CASE WHEN qc_feedbacks.status='Passed' Then 1 ELSE Null End) as passed,
        COUNT( CASE WHEN qc_feedbacks.status!='Passed' Then 1 ELSE Null End) as failed,
        AVG(score) as gc_score,
        cru_agent_details.cru_id as cru_id,
        cru_agent_details.name as agentName,
        cru_agent_details.team_lead as team_lead,
        evaulated_by,
        cru_agent_detail_id,
        audit_date
    ")
    ->join('cru_agent_details', 'cru_agent_details.id', '=', 'qc_feedbacks.cru_agent_detail_id')
    ->groupBy('audit_date', 'cru_agent_detail_id')
    ->orderBy('audit_date', 'desc');

尝试的代码 #

$query = DB::table('qc_feedbacks')
    ->selectRaw("
        COUNT(cru_agent_detail_id) as numberofEvaluations,
        COUNT(CASE WHEN qc_feedbacks.status='Passed' THEN 1 ELSE Null END) AS passed,
        COUNT(CASE WHEN qc_feedbacks.status!='Passed' THEN 1 ELSE Null END) AS failed,
        AVG(score) AS gc_score,
        cru_agent_details.cru_id AS cru_id,
        cru_agent_details.name AS agentName,
        cru_agent_details.team_lead AS team_lead,
        cru_agent_detail_id,
        DATE_FORMAT(audit_date, '%Y-%m') AS month
    ")
    ->join('cru_agent_details', 'cru_agent_details.id', '=', 'qc_feedbacks.cru_agent_detail_id')
    ->groupBy('month', 'cru_agent_detail_id')
    ->orderBy('month', 'desc');

问题分析 #

你当前的尝试代码存在核心逻辑错误：直接对所有score取平均，得到的是所有单条审核记录的评分平均值，而不是每日平均评分再取月度平均。举个例子：

• 某代理某天有2条记录，评分70和80，当日平均75
• 另一天1条记录，评分85
  按照需求月度平均应该是(75+85)/2=80，但你的代码会计算(70+80+85)/3≈78.33，不符合预期。

正确实现方案 #

需要先按「日期+代理」分组计算每日的统计数据，再基于这个结果按「月份+代理」分组计算月度统计：

子查询实现（兼容所有Laravel支持的数据库） #

$query = DB::table(function ($subQuery) {
    // 先计算每日+代理的统计数据
    $subQuery->selectRaw("
        COUNT(cru_agent_detail_id) as daily_evaluations,
        COUNT(CASE WHEN status='Passed' THEN 1 ELSE NULL END) as daily_passed,
        COUNT(CASE WHEN status!='Passed' THEN 1 ELSE NULL END) as daily_failed,
        AVG(score) as daily_gc_score,
        cru_agent_detail_id,
        cru_agent_details.cru_id as cru_id,
        cru_agent_details.name as agentName,
        cru_agent_details.team_lead as team_lead,
        DATE_FORMAT(audit_date, '%Y-%m') as month
    ")
    ->from('qc_feedbacks')
    ->join('cru_agent_details', 'cru_agent_details.id', '=', 'qc_feedbacks.cru_agent_detail_id')
    ->groupBy('audit_date', 'cru_agent_detail_id', 'cru_id', 'agentName', 'team_lead');
}, 'daily_stats')
->selectRaw("
    SUM(daily_evaluations) as numberofEvaluations,
    SUM(daily_passed) as passed,
    SUM(daily_failed) as failed,
    AVG(daily_gc_score) as gc_score,
    cru_id,
    agentName,
    team_lead,
    cru_agent_detail_id,
    month
")
->groupBy('month', 'cru_agent_detail_id', 'cru_id', 'agentName', 'team_lead')
->orderBy('month', 'desc');

CTE实现（适用于MySQL 8.0+、PostgreSQL等支持CTE的数据库） #

如果你的数据库支持公共表表达式（CTE），可以用更清晰的语法：

$query = DB::withExpression('daily_stats', function ($subQuery) {
    $subQuery->selectRaw("
        COUNT(cru_agent_detail_id) as daily_evaluations,
        COUNT(CASE WHEN status='Passed' THEN 1 ELSE NULL END) as daily_passed,
        COUNT(CASE WHEN status!='Passed' THEN 1 ELSE NULL END) as daily_failed,
        AVG(score) as daily_gc_score,
        cru_agent_detail_id,
        cru_agent_details.cru_id as cru_id,
        cru_agent_details.name as agentName,
        cru_agent_details.team_lead as team_lead,
        DATE_FORMAT(audit_date, '%Y-%m') as month
    ")
    ->from('qc_feedbacks')
    ->join('cru_agent_details', 'cru_agent_details.id', '=', 'qc_feedbacks.cru_agent_detail_id')
    ->groupBy('audit_date', 'cru_agent_detail_id', 'cru_id', 'agentName', 'team_lead');
})
->selectRaw("
    SUM(daily_evaluations) as numberofEvaluations,
    SUM(daily_passed) as passed,
    SUM(daily_failed) as failed,
    AVG(daily_gc_score) as gc_score,
    cru_id,
    agentName,
    team_lead,
    cru_agent_detail_id,
    month
")
->from('daily_stats')
->groupBy('month', 'cru_agent_detail_id', 'cru_id', 'agentName', 'team_lead')
->orderBy('month', 'desc');

代码说明 #

1. 内层查询（子查询/CTE）先按日期+代理分组，计算每日的审核数、通过数、失败数，以及当日GC评分的平均值daily_gc_score。
2. 外层查询基于内层结果，按月份+代理分组：
   • 总审核数、通过数、失败数用SUM累加每日数据
   • 月度GC评分用AVG(daily_gc_score)计算每日平均的平均值，完全符合需求
3. 分组时明确列出cru_id、agentName、team_lead等非聚合字段，避免数据库开启only_full_group_by模式时报错。

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