sql中分类avg函数_SQL AVG（）函数介绍和示例

sql中分类avg函数

In this article, we will learn the SQL Average function which is known as AVG() function in T-SQL. AVG() function is an aggregate function that calculates the average value of a numerical dataset that returns from the SELECT statement.

在本文中，我们将学习SQL平均函数，该函数在T-SQL中被称为AVG（）函数。 AVG（）函数是一个聚合函数，用于计算从SELECT语句返回的数值数据集的平均值。

介绍 (Introduction)

Assume that we have a collection of numbers. Firstly, we add up all members of the collection and then divide the total member number of the collection. As a result, the obtained number will be the average. Let’s explain this mathematical notion with a straightforward example.

假设我们有一组数字。 首先，我们将集合的所有成员相加，然后除以集合的成员总数。 结果，获得的数字将是平均值。 让我们用一个简单的例子来解释这个数学概念。

John is a student at university and decides to record his expenses every day. The following chart and table represent John’s last week’s expenses.

约翰是一名大学学生，决定每天记录他的费用。 以下图表代表约翰上周的支出。

Now, we will calculate the last week’s average expense of John. At first, we will add up all the expenses for the aforementioned week.

现在，我们将计算约翰上周的平均费用。 首先，我们将把上述一周的所有费用加起来。

($20+$60+$20+$42+$10+$15+$8) = $175 is the total amount of the expenses for the week.

（$ 20 + $ 60 + $ 20 + $ 42 + $ 10 + $ 15 + $ 8）= $ 175是一周的总支出。

In the second step, we will divide the total expense amount to 7 because this collection is formed on 7 members. In other words, a week consists of seven days.

在第二步中，我们将总费用除以7，因为此集合是由7个成员组成的。 换句话说，一周包括七天。

$175 / 7 = $25 is the average expense of the week.

$ 175/7 = $ 25是该周的平均费用。

After discussing the mathematical concept of the average, let’s continue to learn the basics of the AVG() function in SQL.

在讨论了平均值的数学概念之后，让我们继续学习SQL中AVG（）函数的基础。

资料准备 (Data Preparation)

With the help of the following query, we will create the WeekExpense table and then insert John’s 3 weeks expenses. We will use this table in all examples of this article.

借助以下查询，我们将创建WeekExpense表，然后插入John的3周费用。 我们将在本文的所有示例中使用此表。

CREATE TABLE WeekExpense
( WeekNumber VARCHAR(20),WeekDayName VARCHAR(50), Expense MONEY)
INSERT INTO WeekExpense
VALUES
('Week05','Monday', 20 ), 
(  'Week05','Tuesday', 60 ),
(  'Week05','Wednesday', 20 ), 
(  'Week05','Thurusday', 42 ), 
(  'Week05','Friday', 10 ), 
(  'Week05','Saturday', 15 ) ,
(  'Week05','Sunday', 8 ),
('Week04','Monday', 29 ), 
(  'Week04','Tuesday', 17 ),
(  'Week04','Wednesday', 42 ), 
(  'Week04','Thurusday', 11 ), 
(  'Week04','Friday', 43 ), 
(  'Week04','Saturday', 10 ) ,
(  'Week04','Sunday', 15 ),
    
('Week03','Monday', 10 ), 
(  'Week03','Tuesday', 32 ),
(  'Week03','Wednesday', 35 ), 
(  'Week03','Thurusday', 19 ), 
(  'Week03','Friday', 30 ), 
(  'Week03','Saturday', 10 ) ,
(  'Week03','Sunday', 15 )
    
GO
SELECT *
FROM WeekExpense

SQL平均函数语法 (SQL Average function syntax)

AVG() syntax function will look like the following in its simple form:

AVG（）语法函数的简单形式如下所示：

SELECT AVG ( [ ALL | DISTINCT ] columname )  
FROM TABLENAME
WHERE CONDITION

ALL keyword enables us to calculate an average for all values of the resultset and it is used by default. The DISTINCT keyword implements the AVG() function only for unique values.

ALL关键字使我们能够计算结果集所有值的平均值，并且默认情况下使用它。 DISTINCT关键字仅对唯一值实现AVG（）函数。

AVG（）函数示例 (AVG() function example)

The following query will calculate John’s average expense with the help of the AVG() function.

以下查询将在AVG（）函数的帮助下计算John的平均费用。

SELECT AVG(Expense) AS [Avg_Expense]
FROM WeekExpense
WHERE WeekNumber = 'Week05'

The following image illustrates the calculation methodology of the AVG() function by default usage.

下图说明了默认用法下AVG（）函数的计算方法。

As we can see, AVG() considers all weekdays and weekends values in its calculation. Also, we can obtain the same result when we add the ALL keyword to syntax.

如我们所见， AVG（）在计算时会考虑所有工作日和周末的值。 另外，将ALL关键字添加到语法中时，我们可以获得相同的结果。

SELECT AVG(ALL Expense) AS [Avg_Expense]
FROM WeekExpense
WHERE WeekNumber ='Week05'

If we want to ignore duplicate values during the AVG() function calculation, we can use the DISTINCT keyword. After executing the query below, let’s analyze the result:

如果我们想在AVG（）函数计算过程中忽略重复值，可以使用DISTINCT关键字。 执行以下查询后，让我们分析结果：

SELECT AVG(DISTINCT Expense) AS [Avg_Expense]
FROM WeekExpense
WHERE WeekNumber ='Week05'

DISTINCT keyword eliminates duplicate values, therefore, it takes into account only one of the expenses whose values are $20 in the calculation. The following image basically illustrates the working mechanism of the DISTINCT keyword.

DISTINCT关键字消除了重复的值，因此，在计算中仅考虑了其中一项值为$ 20的费用。 下图基本上说明了DISTINCT关键字的工作机制。

SQL Average函数和NULL值 (SQL Average function and NULL values)

AVG() function does not consider the NULL values during its calculation. Now, we will study an example of this issue. At first, we will update Sunday expenses as NULL in the WeekExpense table.

AVG（）函数在计算过程中不考虑NULL值。 现在，我们将研究此问题的示例。 首先，我们将WeekExpense表中的Sunday费用更新为NULL。

UPDATE WeekExpense
    SET 
        Expense = NULL
WHERE WeekDayName = 'Sunday' AND 
      WeekNumber = 'Week05'

Now, we will execute the following query in order to calculate the average value.

现在，我们将执行以下查询以计算平均值。

SELECT AVG(Expense) AS [Avg_Expense]
FROM WeekExpense
WHERE WeekNumber ='Week05'

As we can see, the NULL value did not take into account by the AVG() function in the calculation. The following image illustrates the calculation method:

如我们所见， AVG（）函数在计算中未考虑NULL值。 下图说明了计算方法：

If we want to include the NULL values into the calculation, we can use the ISNULL function. ISNULL function is used to change the NULL values into the defined values. So we will execute the following in order to include NULL expressions into the calculation.

如果要在计算中包括NULL值，则可以使用ISNULL函数。 ISNULL函数用于将NULL值更改为定义的值。 因此，我们将执行以下操作，以便将NULL表达式包括在计算中。

SELECT AVG(ISNULL(Expense,0)) AS [Avg_Expense]
FROM WeekExpense
WHERE WeekNumber ='Week05'

The following image illustrates the calculation method of the previous query:

下图说明了上一个查询的计算方法：

At this point, we should remark here is that we included the NULL expression to calculation as 0.

此时，我们应该在这里指出，我们将要计算的NULL表达式包括为0。

SQL平均函数用法与GROUP BY语句 (SQL Average function usage with GROUP BY statement)

GROUP BY statement is used for grouping the data and it mainly uses with aggregate functions.

GROUP BY语句用于对数据进行分组，它主要与聚合函数一起使用 。

John decided to calculate the average expense of all weeks. To handle John’s issue, we need to use GROUP BY statement and AVG() function at the same time. The following query will calculate the average expense by each individual week.

约翰决定计算所有星期的平均费用。 要处理John的问题，我们需要同时使用GROUP BY语句和AVG（）函数。 以下查询将计算每个单独星期的平均费用。

SELECT WeekNumber, AVG(Expense) AS [Avg_Expense]
FROM WeekExpense
GROUP BY WeekNumber
ORDER BY WeekNumber DESC

When we have taken a glance at the result set of the query, we can see that the averages were calculated for all weeks separately.

浏览查询的结果集后，我们可以看到平均值是分别计算所有星期的。

温馨提示：SQL Average函数的执行计划详细信息 (Bonus Tip: Execution plan details of the SQL Average function)

The execution plan helps to understand the execution details of a query. When we analyze the execution plan of a query, we can obviously understand what’s happening behind the scenes.

执行计划有助于了解查询的执行细节。 当我们分析查询的执行计划时，我们显然可以理解幕后发生的事情。

Now, we will analyze the following query actual execution plan with ApexSQL Plan so that we can clearly understand what happens behind the scenes of the AVG() function.

现在，我们将使用ApexSQL计划分析以下查询实际执行计划，以便我们可以清楚地了解AVG（）函数幕后发生的情况。

SELECT AVG(Expense) AS [Avg_Expense]
FROM WeekExpense

The visual execution plan of the query is shown as below:

查询的可视执行计划如下所示：

The Table scan read all rows in the tables because we don’t create any index in this table. In the next step, the Stream aggregate operator computes the sum and the count value of the expressions.

Table扫描会读取表中的所有行，因为我们没有在该表中创建任何索引。 在下一步中，Stream聚合运算符将计算表达式的总和和计数值。

Compute scalar takes these expressions from Stream aggregate and calculates the average value through the following formula.

计算标量从Stream聚合中获取这些表达式，并通过以下公式计算平均值。

CASE
    WHEN [Expr1004] = (0)
    THEN NULL
    ELSE [Expr1005] / CONVERT_IMPLICIT(money, [Expr1004], 0)
END

In this formula, we should remark on one point. When the total number of the expressions will return 0, the average calculation will return NULL. If the total number of expressions returns 0, the average value will be NULL. The main intention of this is to avoid divide by zero error.

在这个公式中，我们应指出一点。 当表达式的总数返回0时，平均计算将返回NULL。 如果表达式总数返回0，则平均值将为NULL。 这样做的主要目的是避免除以零误差。

结论 (Conclusion)

In this article, we learned SQL Average function and reinforced our learning with basic examples and illustrations. Along the way, we discussed the execution plan details of the AVG() function.

在本文中，我们学习了SQL Average函数，并通过基本示例和插图加强了学习。 在此过程中，我们讨论了AVG（）函数的执行计划详细信息。

翻译自: https://www.sqlshack.com/sql-avg-function-introduction-and-examples/

sql中分类avg函数