Taking sum of “column” in MongoDB

问题:

In MySQL, I would simply go "SELECT sum(pts) FROM table" to get the sum of the pts column on the table. However, I am converting this application to MongoDB and cannot find a straightforward way to get a sum of the common key "type" in my collection. I am using PHP, so please give PHP code to make this work.

Here is my data:

{ "_id" : ObjectId("4f136dab5056f65b61000000"), "p_id" : "3", "g_id" : "1", "type" : "3" }
{ "_id" : ObjectId("4f136dad5056f65760000000"), "p_id" : "8", "g_id" : "1", "type" : "7" }
{ "_id" : ObjectId("4f136daf5056f65860000000"), "p_id" : "6", "g_id" : "1", "type" : "4" }
{ "_id" : ObjectId("4f136db15056f65460000000"), "p_id" : "27", "g_id" : "1", "type" : "2" }

How can I get $sum to equal the total of the "type" key?

回答1:

Utilizing MongoDB's new Aggregation Framework:

$result = $db->command(array(

    'aggregate' => 'COLLECTION_NAME',

    'pipeline'  => array(
        // Match:
        // We can skip this in case that we don't need to filter 
        // documents in order to perform the aggregation
        array('$match' => array(
            // Criteria to match against
        )),

        // Group:
        array('$group'  => array(
            // Groupby fields:
            '_id'       => "$fieldname1, $fieldname2, $or_empty, $etc",
            // Count:
            'count'     => array('$sum' => 1),
            // Sum:
            'type_sum'  => array('$sum' => '$type'),
        ))

        // Other aggregations may go here as it's really a PIPE :p

    ),
));

回答2:

MongoDB does not have a single simple query for performing a sum.

In MongoDB you will need to run a special command for this. You have three options.

1. Use Map/Reduce. This is the general method for doing sums, counts, averages, etc across a data set. This is not trivial, you will need to read the documentation to understand how this works.
2. Aggregation. This is a special framework for doing "mini-Map/Reduce" jobs. The group example clearly demonstrates how to do "sum by type". You simply need to convert the command to PHP.
3. The Aggregation Framework. This is currently only in the unstable build (2.1.0). This will be better than option #2, but it will only work on 2.1+.

... so please give PHP code to make this work

No simple snippet of PHP code here, aggregation is actually a complex topic in MongoDB.

First you have to pick one of the methods (#1, #2, #3) and then you have to decide what you plan to do with the output. For example, #1 Map/Reduce, can create a new collection with the aggregates or it can return the values in line, but only 10k values can be returned. Option #2 will runs in serial and will be very slow on sharded servers. Option #3 is still in "beta" and will eventually replace #2.

In any case, all of the options will require further reading before you pick a plan of attack.

回答3:

This can be done with the following map reduce functions:

var map = function () {
    emit("total", {sum:this.type}) 
    }

var reduce = function (key, values) {
    var result = {sum:0};
    values.forEach(function (value) {result.sum += value.type;});
    return result;
    }

db.table.mapReduce(map, reduce, {out : "type_totals"})

or in php:

$db->table->mapReduce( array( 
   'map' =>
   'function () {
      emit("total", {sum:this.type});
 }' 
,'reduce' =>
   'function (key, values) {
      var result = {sum:0};
      values.forEach(function (value) {result.sum += value.type;});
      return result;
     }'));

回答4:

Another solution is to create a script in the database, that will sum the require data and return your result something like:

The Script

function sum() {
   var sum = 0;
   for (var query = db.collection.find({}, {type: 1, _id:0}); query.hasNext();) {
      sum += query.next();
   }
   return sum;
}

PHP

In php after setting the connection with the db, it will be something like:

$toexec = 'function(x, y) { return sum() }';
$response = $database->execute($toexec, array());

Look this tutorial to learn more about scripting in mongo with php.

http://pointbeing.net/weblog/2010/08/getting-started-with-stored-procedures-in-mongodb.html

回答5:

$mongo = new Mongo();

$map = new MongoCode("function() { emit('total', this.type); }");
$reduce = new MongoCode("function(k, vals) {
    var sum = 0;
    for(i in vals) {
        sum += vals[i];
    }

    return sum;
}");

$sumtotal = $mongo->db->command(array(
    'mapreduce' => 'tableName',
    'map' => $map,
    'reduce' => $reduce,
    // 'query' => array('col' => 'value'),
    'out' => array('merge' => 'tableName_sum')
));

$result = $mongo->db->selectCollection($sumtotal['result'])->findOne();
$sum = $result['value'];

回答6:

Other answers have already given lot many sources. Just giving answer for asked question:

db.YOUR_COLLECTTION.aggregate([ { 
    $group: { 
        _id: null, 
        total: { 
            $sum: "$type" 
        } 
    } 
} ] )

回答7:

Try:
$m = new MongoClient();
$con = $m->selectDB("dbName")->selectCollection("collectionName");
$cond = array(
    array(
        '$group' => array(
            '_id' => null,
           'total' => array('$sum' => '$type'),
        ),
    )
);

$out = $con->aggregate($cond);
print_r($out);

More detail click here