使用 MongoDB 聚合框架四舍五入到小数点后两位
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17482623/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rounding to 2 decimal places using MongoDB aggregation framework
提问by user2552537
I am using the mongodb aggregation framework and doing some calculations as shown below
我正在使用 mongodb 聚合框架并进行一些计算，如下所示
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.59instead of "ActToPln" : 70.58823529411765, in the results from aggegation framework itself. I want to avoid doing the rounding in my application
我希望 "ActToPln" 在聚合框架本身的结果中显示 70.59而不是"ActToPln" ： 70.58823529411765。我想避免在我的应用程序中进行四舍五入
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
提前致谢。南都
回答by Asya Kamsky
There is no $roundoperator but you can do this in the aggregation framework - doing it in specific order will usually avoid floating point precision issues.
没有$round运算符，但您可以在聚合框架中执行此操作 - 按特定顺序执行通常会避免浮点精度问题。
> 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 }
回答by egvo
I don't know why, but all the answers (at this page) give me 12.34for 12.345. So I wrote my own project stage:
我不知道为什么，但所有答案（在此页面上）都给了我12.34for 12.345. 所以我写了我自己的项目阶段：
x = 12.345
{'$project': {
y: {'$divide': [{'$trunc': {'$add': [{'$multiply': ['$x', 100]}, 0.5]}}, 100]},
}},
It gives 12.35.
它给12.35.
Here is simple arithmetic, no tricks:
这是简单的算术，没有技巧：
- 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.
- 1234.5 + 0.5 = 1235.0 # Here I get my
round half up - truncate(1235.0) = 1235 # Simply drops fractional part
- 1235 / 100 = 12.35
- 12.345 * 100 = 1234.5 # 这一步让我们四舍五入：100 = 10^2（点后的两个符号）。Step 4 将被平衡。
- 1234.5 + 0.5 = 1235.0 # 在这里我得到我的
round half up - truncate(1235.0) = 1235 # 简单地去掉小数部分
- 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:
但是，对于底片它不能正常工作（这对于我的聚合来说已经足够了）。对于（正面和负面）两种情况，您都应该将其用于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.
在这里，我得到数字的符号，用 abs 包裹原始数字，然后将符号乘以舍入输出。
回答by Xavier Guihot
Starting Mongo 4.2, there is a new $roundaggregation operatorwhich can be used to round a numberwith a certain precision to a specified decimal place:
首先Mongo 4.2，有一个新的$round聚合运算符，可用于将具有特定精度的数字舍入到指定的小数位：
{ $round : [ <number>, <place> ] }
{ $round : [ <number>, <place> ] }
Which can be used as such within an aggregation pipeline (here we round xs to 2decimal places):
可以在聚合管道中这样使用（这里我们将xs舍入到2小数位）：
// db.collection.insert([{x: 1.23456}, {x: 9.87654}, {x: 0.055543}, {x: 12.345}])
db.collection.aggregate([{ $project: { "rounded_x": { $round: ["$x", 2] }}}])
// [{"rounded_x": 1.23}, {"rounded_x": 9.88}, {"rounded_x": 0.06}, {"rounded_x": 12.35}]
Note that the placeparameter is optional, and omitting it results in rounding to a whole integer (i.e. rounding at 0 decimal places).
请注意，该place参数是可选的，省略它会导致四舍五入为整数（即四舍五入到 0 位小数）。
回答by Hongbo Miao
mongo-roundworks nice. The most clean way I have found.
mongo-round很好用。我找到的最干净的方法。
Say the number is 3.3333333
说号码是 3.3333333
var round = require('mongo-round');
db.myCollection.aggregate([
{ $project: {
roundAmount: round('$amount', 2) // it will become 3.33
} }
]);
回答by Vince Bowdren
This solution correctly rounds up or down to 2dp:
此解决方案正确向上或向下舍入为 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.
例如，它将 1.2499 向上舍入到 1.25，但将 1.2501 向下舍入到 1.25。
Notes:
笔记：
- This solution is based on the examples given at http://www.kamsky.org/stupid-tricks-with-mongodb/rounding-numbers-in-aggregation-framework
- 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.
- 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.
- 此解决方案基于http://www.kamsky.org/stupid-tricks-with-mongodb/rounding-numbers-in-aggregation-framework给出的示例
- 它解决了 Asya Kamsky 的回答中的问题，即它只截断而不正确向上/向下舍入；即使在评论中建议的更改之后。
- 加法因子中尾随 9 的数量较多，以适应高精度输入数字。根据要舍入的数字的精度，加法因子可能需要比这更精确。
回答by Artem Mezhenin
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.
但如您所见，由于精度问题，此解决方案效果不佳。在这种情况下，最简单的方法是遵循@wiredprairie的建议并round在您的应用程序中创建s。
回答by Brcn
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
egvo 的解决方案很酷，但如果为零则除以零。为避免 $cond 可用于检测符号
(Replace x with field_name and number 2 with desired decimal number)
（将 x 替换为 field_name 并将数字 2 替换为所需的十进制数）
回答by Saleem
Let me say that it's shame MongoDB is missing this function. I'm hopping they will add it soon.
让我说很遗憾 MongoDB 缺少这个功能。我希望他们很快就会添加它。
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
让我们假设，我的集合中有以下文档名为 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 }
回答by edoms06
{$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.
希望这些可以帮助四舍五入。
