MongoDB 中的条件 $sum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14102596/
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
Conditional $sum in MongoDB
提问by Aafreen Sheikh
My collection in mongodb is similar to the following table in SQL:
我在mongodb中的集合类似于SQL中的下表：
Sentiments(Company,Sentiment)
情绪（公司，情绪）
Now, I need to execute a query like this:
现在，我需要执行这样的查询：
SELECT
Company,
SUM(CASE WHEN Sentiment >0 THEN Sentiment ELSE 0 END) AS SumPosSenti,
SUM(CASE WHEN Sentiment <0 THEN Sentiment ELSE 0 END) AS SumNegSenti
FROM Sentiments
GROUP BY Company
What should I do to write this query in Mongo? I am stuck at the following query:
我应该怎么做才能在 Mongo 中编写这个查询？我被困在以下查询中：
db.Sentiments.aggregate(
{ $project: {_id:0, Company:1, Sentiment: 1} },
{ $group: {_id: "$Company", SumPosSenti: {$sum: ? }, SumNegSenti: {$sum: ? } } }
);
回答by JohnnyHK
As Sammaye suggested, you need to use the $condaggregation projection operator to do this:
正如 Sammaye 所建议的，您需要使用$cond聚合投影运算符来执行此操作：
db.Sentiments.aggregate(
{ $project: {
_id: 0,
Company: 1,
PosSentiment: {$cond: [{$gt: ['$Sentiment', 0]}, '$Sentiment', 0]},
NegSentiment: {$cond: [{$lt: ['$Sentiment', 0]}, '$Sentiment', 0]}
}},
{ $group: {
_id: "$Company",
SumPosSentiment: {$sum: '$PosSentiment'},
SumNegSentiment: {$sum: '$NegSentiment'}
}});
回答by styvane
Starting from version 3.4, we can use the $switchoperator which allows logical condition processing in the $groupstage. Of course we still need to use the $sumaccumulator to return the sum.
从 3.4 版本开始，我们可以使用$switch允许在$groupstage 中进行逻辑条件处理的运算符。当然，我们仍然需要使用$sum累加器来返回总和。
db.Sentiments.aggregate(
[
{ "$group": {
"_id": "$Company",
"SumPosSenti": {
"$sum": {
"$switch": {
"branches": [
{
"case": { "$gt": [ "$Sentiment", 0 ] },
"then": "$Sentiment"
}
],
"default": 0
}
}
},
"SumNegSenti": {
"$sum": {
"$switch": {
"branches": [
{
"case": { "$lt": [ "$Sentiment", 0 ] },
"then": "$Sentiment"
}
],
"default": 0
}
}
}
}}
]
)
If you have not yet migrated your mongodto 3.4 or newer, then note that the $projectstage in this answeris redundant because the $condoperator returns a numeric value which means that you can $groupyour documents and apply $sumto the $condexpression.
如果您尚未迁移您mongod到3.4或更高版本，那么请注意，$project在这个阶段，答案是多余的，因为$cond运营商收益的数值，这意味着你可以$group你的文件和应用$sum的$cond表达。
This will improve the performance in your application especially for large collection.
这将提高应用程序的性能，尤其是对于大型集合。
db.Sentiments.aggregate(
[
{ '$group': {
'_id': '$Company',
'PosSentiment': {
'$sum': {
'$cond': [
{ '$gt': ['$Sentiment', 0]},
'$Sentiment',
0
]
}
},
'NegSentiment': {
'$sum': {
'$cond': [
{ '$lt': ['$Sentiment', 0]},
'$Sentiment',
0
]
}
}
}}
]
)
Consider a collection Sentiments with the following documents:
考虑具有以下文档的集合 Sentiments：
{ "Company": "a", "Sentiment" : 2 }
{ "Company": "a", "Sentiment" : 3 }
{ "Company": "a", "Sentiment" : -1 }
{ "Company": "a", "Sentiment" : -5 }
The aggregation query produces:
聚合查询产生：
{ "_id" : "a", "SumPosSenti" : 5, "SumNegSenti" : -6 }
回答by dpolicastro
Explaining the snippets above, that uses the array syntax:
解释上面使用数组语法的片段：
PosSentiment: {$cond: [{$gt: ['$Sentiment', 0]}, '$Sentiment', 0]}
is equal to:
等于：
PosSentiment: {$cond: { if: {$gt: ['$Sentiment', 0]}, then: '$Sentiment', else: 0} }
The array syntax summarizes the long syntax to just { $cond: [if, then, else] }
数组语法将长语法总结为 { $cond: [if, then, else] }
