SQL 行中多列含空值时的平均值计算

在本文中，我们将介绍如何在行中多个列包含空值时计算平均值。当我们在SQL查询中使用AVG函数时，如果某行中包含了空值，它会被忽略，导致计算结果不准确。本文将提供一种有效的方法来解决这个问题，并给出示例说明。

阅读更多：SQL 教程

问题描述

在某些情况下，我们需要计算一行中多个列的平均值，例如一个表格中的学生成绩，每个学生可能会有一到多个科目成绩，而每个科目成绩可能会有空值。我们想要计算每个学生的平均成绩，但如果直接使用AVG函数，空值将被忽略，导致结果不准确。

以下是一个示例表格”students”的数据：

我们想要计算每个学生的平均成绩，包括空值。

解决方案

为了计算行中多个列的平均值，我们可以做以下步骤：

1. 首先，我们使用COALESCE函数将空值替换为0。COALESCE函数接受多个参数，返回第一个非空值。这样，我们可以将空值替换为0，以便在计算平均值时不会被忽略。

   SELECT studentID, COALESCE(subject1_score, 0) AS subject1_score, COALESCE(subject2_score, 0) AS subject2_score, COALESCE(subject3_score, 0) AS subject3_score
   FROM students;
   

   替换空值后的结果如下：

2. 接下来，我们使用CASE语句来判断每个学生的科目成绩是否为0。如果成绩为0，则将其置为NULL，以便在计算平均值时不计入。

   SELECT studentID, 
         CASE WHEN COALESCE(subject1_score, 0) = 0 THEN NULL ELSE COALESCE(subject1_score, 0) END AS subject1_score, 
         CASE WHEN COALESCE(subject2_score, 0) = 0 THEN NULL ELSE COALESCE(subject2_score, 0) END AS subject2_score, 
         CASE WHEN COALESCE(subject3_score, 0) = 0 THEN NULL ELSE COALESCE(subject3_score, 0) END AS subject3_score
   FROM students;
   

   处理后的结果如下：

3. 最后，我们使用AVG函数计算每个学生的平均成绩。这次，空值将被包括在计算中。

   SELECT studentID, 
         AVG(CASE WHEN COALESCE(subject1_score, 0) = 0 THEN NULL ELSE COALESCE(subject1_score, 0) END) AS avg_subject1_score, 
         AVG(CASE WHEN COALESCE(subject2_score, 0) = 0 THEN NULL ELSE COALESCE(subject2_score, 0) END) AS avg_subject2_score, 
         AVG(CASE WHEN COALESCE(subject3_score, 0) = 0 THEN NULL ELSE COALESCE(subject3_score, 0) END) AS avg_subject3_score
   FROM students
   GROUP BY studentID;
   

   计算得到的平均成绩如下：

通过以上步骤，我们成功解决了在行中多列含有空值时计算平均值的问题。

总结

在本文中，我们介绍了如何在SQL查询中计算行中多列含有空值时的平均值。通过使用COALESCE函数将空值替换为0，并使用CASE语句将0值置为NULL，我们能够在计算平均值时不忽略空值。这种方法能够确保计算结果的准确性，并适用于各种包含空值的数据表。以上是一个简单示例，您可以根据自己的实际需求进行相应的调整和扩展。