Rounding to 2 decimal places using MongoDB aggrega

问题:

I am using the mongodb aggregation framework and doing some calculations as shown below

db.RptAgg.aggregate( 
{ $group :
 { _id : {Region:"$RegionTxt",Mth:"$Month"},           
   ActSls:{$sum:"$ActSls"},
   PlnSls:{$sum:"$PlnSls"}
 } 
},
{ $project : 
 {
   ActSls:1,
   PlnSls:1,
   ActToPln:{$cond:[{ $ne: ["$PlnSls", 0] },{$multiply:[{$divide: ['$ActSls', '$PlnSls']},100]},0]}
  }

}

); 

I am trying to figure out what is the best and easiest way to round my results to 2 decimal places. Following is my result

{
    "result" : [
            {
                    "_id" : {
                            "Region" : "East",
                            "Mth" : 201301
                    },
                    "ActSls" : 72,
                    "PlnSls" : 102,
                    "ActToPln" : 70.58823529411765
            }
    ],
    "ok" : 1

}

I want "ActToPln" to show 70.59 instead of "ActToPln" : 70.58823529411765, in the results from aggegation framework itself. I want to avoid doing the rounding in my application

Can you please help with the same.

Following is the dataset i used.

{
    "_id" : ObjectId("51d67ef69557c507cb172572"),
    "RegionTxt" : "East",
    "Month" : 201301,
    "Date" : "2013-01-01",
    "ActSls" : 31,
    "PlnSls" : 51
}
{
    "_id" : ObjectId("51d67ef69557c507cb172573"),
    "RegionTxt" : "East",
    "Month" : 201301,
    "Date" : "2013-01-02",
    "ActSls" : 41,
    "PlnSls" : 51
}

Thanks in advance. Nandu

回答1:

There is no $round operator but you can do this in the aggregation framework - doing it in specific order will usually avoid floating point precision issues.

> db.a.save({x:1.23456789})
> db.a.save({x:9.87654321})
> db.a.aggregate([{$project:{ _id:0, 
         y:{$divide:[
              {$subtract:[
                      {$multiply:['$x',100]},
                      {$mod:[{$multiply:['$x',100]}, 1]}
              ]},
              100]}
}}])
{ "y" : 1.23 }
{ "y" : 9.87 }

Given the existing pipeline in the problem, replace:

{$multiply:[{$divide: ['$ActSls', '$PlnSls']},100]}

with

{$divide:[
     {$subtract:[ 
          {$multiply:[
             {$divide: ['$ActSls','$PlnSls']},
             10000
          ]}, 
          {$mod:[
             {$multiply:[{$divide: ['$ActSls','$PlnSls']}, 10000 ]},
             1]}
          ]}, 
     100
]}

With your sample data points this is the result:

{ "ActSls" : 31, "PlnSls" : 51, "ActToPln" : 60.78 }
{ "ActSls" : 41, "PlnSls" : 51, "ActToPln" : 80.39 }
{ "ActSls" : 72, "PlnSls" : 102, "ActToPln" : 70.58 }

回答2:

mongo-round works nice. The most clean way I have found.

Say the number is 3.3333333

var round = require('mongo-round');

db.myCollection.aggregate([
    { $project: {
        roundAmount: round('$amount', 2)  // it will become 3.33
    } }
]);

回答3:

There is no round operator in current version of Aggregation Framework. You can try this snippet:

> db.a.save({x:1.23456789})
> db.a.save({x:9.87654321})
> db.a.aggregate([{$project:{y:{$subtract:['$x',{$mod:['$x', 0.01]}]}}}])
{
    "result" : [
        {
            "_id" : ObjectId("51d72eab32549f94da161448"),
            "y" : 1.23
        },
        {
            "_id" : ObjectId("51d72ebe32549f94da161449"),
            "y" : 9.870000000000001
        }
    ],
    "ok" : 1
}

but as you see, this solution doesn't works well because of precision problems. The easiest way in this case is to follow @wiredprairie's advice and make rounds in you application.

回答4:

This solution correctly rounds up or down to 2dp:

"rounded" : {
  $subtract:[
    {$add:['$absolute',0.0049999999999999999]},
    {$mod:[{$add:['$absolute',0.0049999999999999999]}, 0.01]}
  ]
}

For example it rounds 1.2499 upwards to 1.25, but 1.2501 downwards to 1.25.

Notes:

1. This solution is based on the examples given at http://www.kamsky.org/stupid-tricks-with-mongodb/rounding-numbers-in-aggregation-framework
2. It resolves the problem in Asya Kamsky's answer, that it only truncates and does not round up/down correctly; even after the change suggested in the comments.
3. The number of trailing 9s in the addition factor is large, to accommodate high-precision input numbers. Depending on the precision of the numbers to be rounded, the addition factor may need to be made even more precise than this.

回答5:

I don't know why, but all the answers (at this page) give me 12.34 for 12.345. So I wrote my own project stage:

x = 12.345

{'$project': {
    y: {'$divide': [{'$trunc': {'$add': [{'$multiply': ['$x', 100]}, 0.5]}}, 100]},
}},

It gives 12.35.

Here is simple arithmetic, no tricks:

1. 12.345 * 100 = 1234.5 # This step gets us to rounding position: 100 = 10^2 (two signs after dot). Step will be balanced back by step 4.
2. 1234.5 + 0.5 = 1235.0 # Here I get my round half up
3. truncate(1235.0) = 1235 # Simply drops fractional part
4. 1235 / 100 = 12.35

However, it doesn't work correctly for negatives (that was enough for my aggregation). For both (positive and negative) cases you should use it with abs:

{'$project': {
    z: {'$multiply': [
        {'$divide': ['$x', {'$abs': '$x'}]}, 
        {'$divide': [{'$trunc': {'$add': [{'$multiply': [{'$abs': '$x'}, 100]}, 0.5]}}, 100]}
    ]},
}}

Here I get number's sign, wrap original number by abs and then multiply sign by rounding output.

回答6:

rounded:{'$multiply': [{ "$cond": [{ "$gte": [ "$x", 0 ] }, 1,-1 ]},{'$divide': [{'$trunc': {'$add': [{'$multiply': [{'$abs': '$x'}, {$pow:[10,2]}]}, 0.5]}}, {$pow:[10,2]}]}]}

egvo's solution is cool but gives division by zero if it is zero. To avoid $cond may be used to detect sign

(Replace x with field_name and number 2 with desired decimal number)

回答7:

{$divide:[
            {$cond: { if: { $gte: [ {$mod:[{$multiply:['$dollarAmount',100]}, 1]}, 0.5 ] }, then: {$add: [{$subtract:[
                  {$multiply:['$dollarAmount',100]},
                  {$mod:[{$multiply:['$dollarAmount',100]}, 1]}
          ]}
                ,1]}, else: {$subtract:[
                  {$multiply:['$dollarAmount',100]},
                  {$mod:[{$multiply:['$dollarAmount',100]}, 1]}
          ]} }}
          , 
          100]}

hopefully these one could help in rounding off.

回答8:

Let me say that it's shame MongoDB is missing this function. I'm hopping they will add it soon.

However, I came up with a lengthy aggregation pipeline. Admitting, it may not be efficient but it honors rules of rounding.

db.t.aggregate([{
    $project: {
        _id: 0,
        number: {
            $let: {
                vars: {
                    factor: {
                        $pow: [10, 3]
                    },
                },
                in: {
                    $let: {
                        vars: {
                            num: {$multiply: ["$$factor", "$number"]},
                        },
                        in: {
                            $switch: {
                                branches: [
                                    {case: {$gte: ["$$num", {$add: [{$floor: "$$num"}, 0.5]}]}, then: {$divide:[ {$add: [{$floor: "$$num"}, 1.0]},"$$factor"]}},
                                    {case: {$lt: ["$$num", {$add: [{$floor: "$$num"}, 0.5]}]}, then: {$divide:[{$floor: "$$num"}, "$$factor"]}}                                    
                                ]
                            }
                        }
                    }
                }
            }
        }
    }
}])

Let's assume, I have following documents in my collection named t

{ number" : 2.341567 }
{ number" : 2.0012 }
{ number" : 2.0012223 }

After running above queries, I got:

{ "number" : 2.342 }
{ "number" : 2.001 }
{ "number" : 2.001 }