$sum (aggregation)

On this page本页内容

Definition定义

$sum

Changed in version 5.0.在版本5.0中更改。

Calculates and returns the collective sum of numeric values. 计算并返回数值的集合和。$sum ignores non-numeric values.忽略非数值。

$sum is available in these stages:可在以下阶段使用：

In MongoDB 3.2 and earlier, $sum is available in the $group stage only.在MongoDB 3.2及更早版本中，$sum仅在$group阶段可用。

Syntax语法

When used in the $bucket, $bucketAuto, $group, and $setWindowFields stages, $sum has this syntax:在$bucket、$bucketAuto、$group和$setWindowFields阶段中使用时，$sum具有以下语法：

{ $sum: <expression> }

When used in other supported stages, $sum has one of two syntaxes:在其他受支持的阶段中使用时，$sum具有以下两种语法之一：

  • $sum has one specified expression as its operand:将一个指定的表达式作为其操作数：

    { $sum: <expression> }

  • $sum has a list of specified expressions as its operand:具有指定表达式列表作为其操作数：

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

For more information on expressions, see Expressions.有关表达式的详细信息，请参阅表达式。

Behavior行为

Result Data Type结果数据类型

The result will have the same type as the input except when it cannot be represented accurately in that type. 结果将与输入具有相同的类型，除非无法在该类型中准确表示。In these cases:在这些情况下：

  • A 32-bit integer will be converted to a 64-bit integer if the result is representable as a 64-bit integer.如果结果可以表示为64位整数，则32位整数将转换为64位整型。
  • A 32-bit integer will be converted to a double if the result is not representable as a 64-bit integer.如果结果不能表示为64位整数，则32位整数将转换为双精度数。
  • A 64-bit integer will be converted to double if the result is not representable as a 64-bit integer.如果结果不能表示为64位整数，则64位整数将转换为双精度。

Non-Numeric or Non-Existent Fields非数字或不存在的字段

If used on a field that contains both numeric and non-numeric values, $sum ignores the non-numeric values and returns the sum of the numeric values.如果在同时包含数值和非数值的字段上使用，$sum将忽略非数值并返回数值的总和。

If used on a field that does not exist in any document in the collection, $sum returns 0 for that field.如果用于集合中任何文档中都不存在的字段，$sum将为该字段返回0。

If all operands are non-numeric, $sum returns 0.如果所有操作数都是非数字的，则$sum返回0。

Array Operand数组操作数

In the $group stage, if the expression resolves to an array, $sum treats the operand as a non-numerical value.在$group阶段，如果表达式解析为数组，$sum会将操作数视为非数值。

In the other supported stages:在其他受支持的阶段中：

  • With a single expression as its operand, if the expression resolves to an array, $sum traverses into the array to operate on the numerical elements of the array to return a single value.使用单个表达式作为其操作数，如果表达式解析为数组，$sum将遍历数组，对数组的数字元素进行运算，以返回单个值。
  • With a list of expressions as its operand, if any of the expressions resolves to an array, $sum does not traverse into the array but instead treats the array as a non-numerical value.使用表达式列表作为操作数，如果任何表达式解析为数组，$sum不会遍历数组，而是将数组视为非数值。

Examples示例

Use in $group Stage在$group阶段中使用

Consider a sales collection with the following documents:考虑使用以下文档的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:05:00Z") }

Grouping the documents by the day and the year of the date field, the following operation uses the $sum accumulator to compute the total amount and the count for each group of documents.以下操作按date字段的日期和年份对文档进行分组，使用$sum累加器计算每组文档的总金额和计数。

db.sales.aggregate(
   [
     {
       $group:
         {
           _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
           totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } },
           count: { $sum: 1 }
         }
     }
   ]
)

The operation returns the following results:该操作返回以下结果：

{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 150, "count" : 2 }
{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 45, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 20, "count" : 1 }

Using $sum on a non-existent field returns a value of 0. 对不存在的字段使用$sum将返回值0。The following operation attempts to $sum on qty:以下操作尝试对qty执行$sum操作：

db.sales.aggregate(
   [
     {
       $group:
         {
           _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } },
           totalAmount: { $sum: "$qty" },
           count: { $sum: 1 }
         }
     }
   ]
)

The operation returns:操作返回：

{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 0, "count" : 2 }
{ "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 0, "count" : 2 }
{ "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 0, "count" : 1 }

The $count aggregation accumulator can be used in place of { $sum : 1 } in the $group stage.$count聚合累加器可以代替$group阶段中的{ $sum : 1 }。

Tip提示

Use in $project Stage在$project阶段中使用

A collection students contains the following documents:集合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 }

The following example uses the $sum in the $project stage to calculate the total quiz scores, the total lab scores, and the total of the final and the midterm:下面的示例使用$project阶段中的$sum来计算测验总分、实验室总分以及期末和期中考试总分：

db.students.aggregate([
   {
     $project: {
       quizTotal: { $sum: "$quizzes"},
       labTotal: { $sum: "$labs" },
       examTotal: { $sum: [ "$final", "$midterm" ] }
     }
   }
])

The operation results in the following documents:该操作产生以下文档：

{ "_id" : 1, "quizTotal" : 23, "labTotal" : 13, "examTotal" : 155 }
{ "_id" : 2, "quizTotal" : 19, "labTotal" : 16, "examTotal" : 175 }
{ "_id" : 3, "quizTotal" : 14, "labTotal" : 11, "examTotal" : 148 }

Use in $setWindowFields Stage在$setWindowFields阶段中使用

New in version 5.0.在版本5.0中新增。

Create a cakeSales collection that contains cake sales in the states of California (CA) and Washington (WA):创建包含加利福尼亚州（CA）和华盛顿州（WA）蛋糕销售的cakeSales集合：

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

This example uses $sum in the $setWindowFields stage to output the sum of the quantity of cakes sold in each state:本例使用$setWindowFields阶段中的$sum输出每个state销售的蛋糕quantity的总和：

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

In the example:在示例中：

  • partitionBy: "$state" partitions the documents in the collection by state. 按state对集合中的文档进行分区。There are partitions for CA and WA.CA和WA有分区。
  • sortBy: { orderDate: 1 } sorts the documents in each partition by orderDate in ascending order (1), so the earliest orderDate is first.按orderDate以升序（1）对每个分区中的文档进行排序，因此最早的orderDate第一个。

  • output sets the sumQuantityForState field to the sum of the quantity values using $sum that is run in a documents window.output使用在文档窗口中运行的$sum将sumQuantityForState字段设置为quantity值的总和。

    The window contains documents between an unbounded lower limit and the current document in the output. 该窗口包含输出中处于unbounded下限和current文档之间的文档。This means $sum returns the sum of the quantity values for the documents between the beginning of the partition and the current document.这意味着$sum返回分区开始处和当前文档之间的文档quantity值的总和。

In this output, the sum of the quantity values for CA and WA is shown in the sumQuantityForState field:在此输出中，CA和WA的quantity值之和显示在sumQuantityForState字段中：

{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate("2019-05-18T16:09:01Z"),
  "state" : "CA", "price" : 41, "quantity" : 162, "sumQuantityForState" : 162 }
{ "_id" : 0, "type" : "chocolate", "orderDate" : ISODate("2020-05-18T14:10:30Z"),
  "state" : "CA", "price" : 13, "quantity" : 120, "sumQuantityForState" : 282 }
{ "_id" : 2, "type" : "vanilla", "orderDate" : ISODate("2021-01-11T06:31:15Z"),
  "state" : "CA", "price" : 12, "quantity" : 145, "sumQuantityForState" : 427 }
{ "_id" : 5, "type" : "strawberry", "orderDate" : ISODate("2019-01-08T06:12:03Z"),
  "state" : "WA", "price" : 43, "quantity" : 134, "sumQuantityForState" : 134 }
{ "_id" : 3, "type" : "vanilla", "orderDate" : ISODate("2020-02-08T13:13:23Z"),
  "state" : "WA", "price" : 13, "quantity" : 104, "sumQuantityForState" : 238 }
{ "_id" : 1, "type" : "chocolate", "orderDate" : ISODate("2021-03-20T11:30:05Z"),
  "state" : "WA", "price" : 14, "quantity" : 140, "sumQuantityForState" : 378 }
←  $subtract (aggregation)$switch (aggregation) →