MongoDB中的条件$ sum

Question

我在mongodb中的集合类似于SQL中的下表：

情感（公司，感悟）

现在，我需要执行这样的查询：

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

我该怎么做才能在Mongo中编写这个查询？我被困在以下查询中：

db.Sentiments.aggregate(

{ $project: {_id:0, Company:1, Sentiment: 1} },

{ $group: {_id: "$Company", SumPosSenti: {$sum: ? }, SumNegSenti: {$sum: ? } } }

);

Answer 1

从版本3.4开始，我们可以使用$switch允许在$group阶段中进行逻辑条件处理的运算符。当然我们仍然需要使用$sum累加器来返回总和。db.Sentiments.aggregate(&nbsp; &nbsp; [&nbsp; &nbsp; &nbsp; &nbsp; { "$group": {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "_id": "$Company",&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "SumPosSenti": {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "$sum": {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "$switch": {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "branches": [&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "case": { "$gt": [ "$Sentiment", 0 ] },&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "then": "$Sentiment"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "default": 0&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "SumNegSenti": {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "$sum": {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "$switch": {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "branches": [&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "case": { "$lt": [ "$Sentiment", 0 ] },&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "then": "$Sentiment"&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ],&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "default": 0&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }}&nbsp; &nbsp; ])如果您尚未迁移您mongod到3.4或更高版本，那么请注意，$project在这个阶段，答案是多余的，因为$cond运营商返回一个数值，这意味着你可以$group你的文件和应用$sum的$cond表达。这将改善您的应用程序的性能，尤其是对于大型集合。db.Sentiments.aggregate(&nbsp; &nbsp; [&nbsp; &nbsp; &nbsp; &nbsp; { '$group': {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '_id': '$Company',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'PosSentiment': {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '$sum': {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '$cond': [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; { '$gt': ['$Sentiment', 0]},&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '$Sentiment',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; },&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'NegSentiment': {&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '$sum': {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '$cond': [&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; { '$lt': ['$Sentiment', 0]},&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; '$Sentiment',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; }}&nbsp; &nbsp; ])考虑一个带有以下文档的集合Sentiments：{ "Company": "a", "Sentiment" : 2 }{ "Company": "a", "Sentiment" : 3 }{ "Company": "a", "Sentiment" : -1 }{ "Company": "a", "Sentiment" : -5 }聚合查询产生：{ "_id" : "a", "SumPosSenti" : 5, "SumNegSenti" : -6 }

Answer 2

解释上面使用数组语法的片段：PosSentiment: {$cond: [{$gt: ['$Sentiment', 0]}, '$Sentiment', 0]}等于：PosSentiment: {$cond: { if: {$gt: ['$Sentiment', 0]}, then: '$Sentiment', else: 0} }数组语法总结了长语法 { $cond: [if, then, else] }