MongoDB聚合运算符：$avg

$avg

运算符返回给定数值的平均值

$avg

可用于以下阶段：

• $addFields阶段(从MongoDB 3.4开始可用)
• $bucket阶段
• $bucketAuto阶段
• $group阶段
• 包含$expr表达式的$match阶段
• $project阶段
• $replaceRoot阶段(从MongoDB 3.4开始可用)
• $replaceWith阶段(从MongoDB 4.2开始可用)
• $set阶段(从MongoDB 4.2开始可用)
• $setWindowFields阶段(从MongoDB 5.0开始可用)

语法

{$avg:<expression>}

或

{$avg:[<expression1>,<expression2>...]}

使用

非数值或缺失值

$avg

会忽略非数值，包括缺失值。如果平均值的所有操作数都是非数值，则
返回空值。

数组操作

在

$group

阶段，如果表达式解析为一个数组，则会被认为是非数值类型。对于其他支持的阶段：

• 对于以单个表达式的情况，如果表达式解析为数组，则$avg会遍历数组对数字元素进行平均值运算。
• 对于以表达式列表为操作数，如果其中任何表达式被解析为数组，则$avg会将数组视为非数值。

举例

在

$group

阶段中使用

$avg

sales

集合有下列的文档：

{"_id":1,"item":"abc","price":10,"quantity":2,"date":ISODate("2014-01-01T08:00:00Z")}{"_id":2,"item":"jkl","price":20,"quantity":1,"date":ISODate("2014-02-03T09:00:00Z")}{"_id":3,"item":"xyz","price":5,"quantity":5,"date":ISODate("2014-02-03T09:05:00Z")}{"_id":4,"item":"abc","price":10,"quantity":10,"date":ISODate("2014-02-15T08:00:00Z")}{"_id":5,"item":"xyz","price":5,"quantity":10,"date":ISODate("2014-02-15T09:12:00Z")}

下面的聚合按照

item

字段对文档进行分组，使用

$avg

计算分组的平均价格和平均文档数量：

db.sales.aggregate([{$group:{_id:"$item",avgAmount:{$avg:{$multiply:["$price","$quantity"]}},avgQuantity:{$avg:"$quantity"}}}])

操作返回下面的结果：

{"_id":"xyz","avgAmount":37.5,"avgQuantity":7.5}{"_id":"jkl","avgAmount":20,"avgQuantity":1}{"_id":"abc","avgAmount":60,"avgQuantity":6}

在

$project

阶段中使用

$avg

students

集合包含下列文档：

{"_id":1,"quizzes":[10,6,7],"labs":[5,8],"final":80,"midterm":75}{"_id":2,"quizzes":[9,10],"labs":[8,8],"final":95,"midterm":80}{"_id":3,"quizzes":[4,5,5],"labs":[6,5],"final":78,"midterm":70}

下面的例子在

$project

阶段中使用

$avg

计算测验、实验室、其中和期末平均得分：

db.students.aggregate([{$project:{quizAvg:{$avg:"$quizzes"},labAvg:{$avg:"$labs"},examAvg:{$avg:["$final","$midterm"]}}}])

操作返回下面的结果：

{"_id":1,"quizAvg":7.666666666666667,"labAvg":6.5,"examAvg":77.5}{"_id":2,"quizAvg":9.5,"labAvg":8,"examAvg":87.5}{"_id":3,"quizAvg":4.666666666666667,"labAvg":5.5,"examAvg":74}

在

$setWindowFields

阶段使用

$avg

从MongoDB5.0开始支持。

创建

cakeSales

集合包含了在加利福尼亚和华盛顿的蛋糕销售状态：

db.cakeSales.insertMany([{_id:0,type:"chocolate",orderDate:newDate("2020-05-18T14:10:30Z"),state:"CA",price:13,quantity:120},{_id:1,type:"chocolate",orderDate:newDate("2021-03-20T11:30:05Z"),state:"WA",price:14,quantity:140},{_id:2,type:"vanilla",orderDate:newDate("2021-01-11T06:31:15Z"),state:"CA",price:12,quantity:145},{_id:3,type:"vanilla",orderDate:newDate("2020-02-08T13:13:23Z"),state:"WA",price:13,quantity:104},{_id:4,type:"strawberry",orderDate:newDate("2019-05-18T16:09:01Z"),state:"CA",price:41,quantity:162},{_id:5,type:"strawberry",orderDate:newDate("2019-01-08T06:12:03Z"),state:"WA",price:43,quantity:134}])

下面的例子在

$setWindowFields

阶段使用

$avg

运算符计算各州蛋糕销售数量的平均值：

db.cakeSales.aggregate([{$setWindowFields:{partitionBy:"$state",sortBy:{orderDate:1},output:{averageQuantityForState:{$avg:"$quantity",window:{documents:["unbounded","current"]}}}}}])

在这个例子中：

• partitionBy: "$state"根据state州对文档进行分区，包括CA和WA两个分区
• sortBy: { orderDate: 1}按照orderDate对分区文档升序排序，最早的orderDate排在最前面
• output将文档窗口中文档中quantity的移动平均值设置给averageQuantityForState字段。窗口中包含的文档在unbounded下限和current文档之间，$avg返回从开始到当前文档quantity的移动平均值。

在下面的输出结果中，

averageQuantityForState

为

CA

和

WA

的

quantity

的移动平均值：

{"_id":4,"type":"strawberry","orderDate":ISODate("2019-05-18T16:09:01Z"),"state":"CA","price":41,"quantity":162,"averageQuantityForState":162}{"_id":0,"type":"chocolate","orderDate":ISODate("2020-05-18T14:10:30Z"),"state":"CA","price":13,"quantity":120,"averageQuantityForState":141}{"_id":2,"type":"vanilla","orderDate":ISODate("2021-01-11T06:31:15Z"),"state":"CA","price":12,"quantity":145,"averageQuantityForState":142.33333333333334}{"_id":5,"type":"strawberry","orderDate":ISODate("2019-01-08T06:12:03Z"),"state":"WA","price":43,"quantity":134,"averageQuantityForState":134}{"_id":3,"type":"vanilla","orderDate":ISODate("2020-02-08T13:13:23Z"),"state":"WA","price":13,"quantity":104,"averageQuantityForState":119}{"_id":1,"type":"chocolate","orderDate":ISODate("2021-03-20T11:30:05Z"),"state":"WA","price":14,"quantity":140,"averageQuantityForState":126}