我对 Mongo 很陌生,并且有一个关于使用条件计算聚合查询的问题:
我有一个评论集合,每个文档都包含一个情绪分数。我想要:
1) 按项目分组评论
2)获取该项目所有评论中每个项目的平均情绪分数,并以此排序
3) 获取每个项目组的评论总数
4) 获取每个项目的正面情绪评论总数(例如,情绪得分 > 75 的评论数)
5) 获取每个项目的负面情绪评论总数(例如,情绪评分 < 75 的评论数)
到目前为止,我有以下查询,涵盖 1-3,但不确定如何在此处获得 4/5:
db.reviews.aggregate(
{"$group" :
{_id: "$item",
sentiment: {$avg : "$sentimentScore"},
count: {$sum: 1 }
}
},
{"$sort": { sentiment: -1 } }
)
我假设您希望为具有给定阈值的负值和正值分别设置count
字段sentiment
iepositive - >75
和negative - <75
,即正面情绪总数和负面情绪总数以及情绪总数。
db.sentiments.aggregate([
{"$group" :
{_id: "$item",
sentiment: {$avg : "$sentiment_score"},
postiive_sentiments: {$sum: { $cond: { if: { $gt: [ "$sentiment_score", 75 ] }, then: 1, else: 0 } }},
negative_sentiments: {$sum: { $cond: { if: { $lt: [ "$sentiment_score", 75 ] }, then: 1, else: 0 } }},
count: {$sum: 1 }
}
},
{"$sort": { sentiment: -1 } }
])
样本数据:
{ "_id" : ObjectId("5991329ea37dbc24842a68be"), "item" : "test1", "sentiment_score" : 50 }
{ "_id" : ObjectId("599132a2a37dbc24842a68bf"), "item" : "test1", "sentiment_score" : 40 }
{ "_id" : ObjectId("599132a4a37dbc24842a68c0"), "item" : "test1", "sentiment_score" : 80 }
{ "_id" : ObjectId("599132aba37dbc24842a68c1"), "item" : "test2", "sentiment_score" : 80 }
{ "_id" : ObjectId("599132ada37dbc24842a68c2"), "item" : "test2", "sentiment_score" : 30 }
{ "_id" : ObjectId("599132b0a37dbc24842a68c3"), "item" : "test2", "sentiment_score" : 38 }
{ "_id" : ObjectId("599132b6a37dbc24842a68c4"), "item" : "test3", "sentiment_score" : 78 }
{ "_id" : ObjectId("599132b9a37dbc24842a68c5"), "item" : "test3", "sentiment_score" : 88 }
{ "_id" : ObjectId("599132bba37dbc24842a68c6"), "item" : "test3", "sentiment_score" : 58 }
{ "_id" : ObjectId("599132c4a37dbc24842a68c7"), "item" : "test3", "sentiment_score" : 98 }
{ "_id" : ObjectId("599132cba37dbc24842a68c8"), "item" : "test4", "sentiment_score" : 65 }
{ "_id" : ObjectId("599132d2a37dbc24842a68c9"), "item" : "test4", "sentiment_score" : 30 }
{ "_id" : ObjectId("599132d6a37dbc24842a68ca"), "item" : "test4", "sentiment_score" : 10 }
//结果:
{ "_id" : "test3", "sentiment" : 80.5, "negative_sentiments" : 3, "positive_sentiments" : 1, "count" : 4 }
{ "_id" : "test1", "sentiment" : 56.666666666666664, "negative_sentiments" : 1, "positive_sentiments" : 2, "count" : 3 }
{ "_id" : "test2", "sentiment" : 49.333333333333336, "negative_sentiments" : 1, "positive_sentiments" : 2, "count" : 3 }
{ "_id" : "test4", "sentiment" : 35, "negative_sentiments" : 0, "positive_sentiments" : 3, "count" : 3 }
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句