具有空值的 Laravel 平均值

Question

我正在尝试对数据库表中的每一行进行平均。但它不能正常工作我想忽略该值，如果为空，它不会计算为零。使用我的代码，它将空值计算为零我想这样做 MS Excel 如果行/单元格为空，它将忽略。
Controller 代码:

public function update_average(Request $request) 
        { 
          $scores = $request->input('scores'); //here scores is the input array param 
          foreach($scores as $row){ 
          $score = cie_score::find($row['id']); 
         
          $score->term2_average =round( ($row['term1_result'?: null] + $row['term2_result'?: null]) /2);
          $score->term3_average =round( ($row['term1_result'?: null] + $row['term2_result'?: null] + $row['term3_result'?: null])/3);
          $score->term4_average =round( ($row['term1_result'?: null] + $row['term2_result'?: null] + $row['term3_result'?: null] + $row['term4_result'?: null])/4);
       
          $score->save(); 
          }
          
        

          return redirect()->back();
        }

图像结果

Answer 1

您可以计算您要查找的内容，如下所示:

    $currentTerm = 4;
    $scores = $request->input('scores');

    for ($term = 1; $term <= $currentTerm; $term++) {
        foreach ($scores as $row) {
            $score = cie_score::find($row['id']);

            $total = 0;
            $terms = 0;

            for ($i = 1; $i <= $term; $i++) {
                $total += $row['term'.$i.'_result'] ?? 0;
                $terms += empty($row['term'.$i.'_result']) ? 0 : 1;
            }

            $key = 'term'.$term.'_average';
            
            if ($terms > 0) {
                $score->$key = round($total/$terms);
            } else {
               $score->$key = 0;
            }
           
            $score->save();
        }
    }

这将计算从 1 到当前术语的所有术语的平均值。如果 'term{$i}_result' 为空，则它在总数中被忽略，并且不会计入除数。
您的数据库执行以下操作更有效:

    $currentTerm = 4;
    $averages = [];
    $scores = $request->input('scores');

    for ($term = 1; $term <= $currentTerm; $term++) {
        foreach ($scores as $row) {
            $id = $row['id'];
            if (!isset($averages[$id])) {
                $averages[$id] = [];
            }

            $total = 0;
            $terms = 0;

            for ($i = 1; $i <= $term; $i++) {
                $total += $row['term'.$i.'_result'] ?? 0;
                $terms += empty($row['term'.$i.'_result']) ? 0 : 1;
            }

            $key = 'term'.$term.'_average';
            
            if ($terms > 0) {
                $averages[$id][$key] = round($total/$terms);
            } else {
                $averages[$id][$key];
            }
        }

        foreach($averages as $id => $data) {
            cie_score::where('id', $id)
                     ->update($data);
        }
    }