This is the 25th day of my participation in Gwen Challenge

The aggregation operation processes the data record and returns the corresponding calculation results. Aggregation operations take combined values from multiple documents and can process multiple pieces of data and return a single result. MongoDB provides three ways to accomplish aggregation: aggregation pipes, Map-Reduce functions, and single-purpose aggregation. This article introduces basic statistical aggregation functions.

Count count

SELECT count(*) FROM table_name WHERE count(*) = 1 To get the number of records that satisfy a condition. A similar method is provided in MondoDB. MongoDB can be used in the following ways:

Db. Collection. The count (} {conditions); Db. Collection. The find (} {condition.) length ();Copy the code

Take the following data for example:

[{"gender" : "Male"."name" : "Tom"."age": 10}, {"gender" : "Male"."name" : "Jack"."age": 12}, {"gender" : "Male"."name" : "Jim"."age": 15}, {"gender" : "Male"."name" : "Jimmy"."age": 18}, {"name" : "Mary"."gender" : "Female"."age": 10}]Copy the code

To find the number of male users, do this:

The db. The users. The count ({gender: 'male'}); Db. The users. The find ({gender: 'male'}) length ();Copy the code

If you want to query the user with a condition, you can use the condition, for example, query the user with a gender of male, age greater than or equal to 15:

Db. Users. Count ({$and: [{gender: 'male'}, {age: {$gte:15}}});Copy the code

MongoDB: conditional query and sort MongoDB also provides grouping counting methods. For example, the number of males and females can be counted in the following ways:

db.users.aggregate(
  [
    {
      $group: {
        _id: '$gender', 
        count:{$sum: 1}}}]);Copy the code

sum

The sum is done with the aggregate $sum operator, which can be used to calculate the sum of a numeric column, such as the sum of all ages.

db.users.aggregate(
  [
    {
      $group: {
        _id: null,
      	totalAge: {$sum: '$age'}
      }
    }
  ]
); 
Copy the code

If the _id is specified as null, the total data will be summed (i.e. not grouped). If a column is specified, the sum can be grouped, for example, male and female.

db.users.aggregate(
  [
    {
      $group: {
        _id: '$gender',
      	totalAge: {$sum: '$age'}
      }
    }
  ]
); 
Copy the code

It is also possible to sum multiple fields, such as the following takeout order data:

[{"goods_name" : "Celery dried and fragrant"."express_fee" : 3."amount": 10.5}, {"goods_name" : "Rice"."express_fee" : 0."amount": 3}, {"goods_name" : "Eggplant with Fish Flavor"."express_fee" : 2."amount": 15.5}, {"goods_name" : "Fish with pickled cabbage"."express_fee" : 3."amount": 48,},]Copy the code

We want to know the total value of the order including the delivery fee. We can do this as follows, or we can get the total value of each item if we add the name of the item.

db.orders.aggregate(   
  [     
    {       
      $group: {        
        _id: null,       
        totalAmount: {
          $sum: {
            $sum:['$amount', '$express_fee']
          }
        }     
      }
    }   
  ] 
);
Copy the code

The average

Averaging is done with the $AVg operator, such as calculating average unit prices for the data above:

db.orders.aggregate(   
  [     
    {       
      $group: {        
        _id: null,       
        avgAmount: {
          $avg: {
            $sum:['$amount', '$express_fee']
          }
        }     
      }
    }   
  ] 
);
Copy the code

conclusion

This article introduces the basic usage of MongoDB aggregate functions, including counting, summing, and averaging. MongoDB provides aggregate functions to efficiently complete statistical work.