database - 查询以计算并返回另一个集合的平均评分?
问题描述
我有两个集合,计算机,如下所示
{"_id":{"$oid":"6185bee4aca3214e77e748a9"},"name":"Gaming PC","manufacturer":"Dell","category":["Gaming"],"colour":"black","price":1200.0,"quantity":47.0,"weight":10.0,"parts":[{"type":"motherboard","manufacturer":"AMD","cost":150.0,"description":"Motherboard suitable for gaming PCs"},{"type":"ram","manufacturer":"Corsair","cost":50.0,"description":"16gb 2400mhz RAM"},{"type":"cpu","manufacturer":"Intel","cost":250.0,"description":"AMD 5600X Processor"},{"type":"gpu","manufacturer":"NVIDIA","cost":600.0,"description":"NVIDIA GTX3080"},{"type":"storage","manufacturer":"Seagate","cost":80.0,"description":"1TB SSD"}],"reviews":["review01","review02","review10"]},
{"_id":{"$oid":"6185c06eaca3214e77e748aa"},"name":"Office PC","manufacturer":"HP","category":["Work","Office"],"colour":"black","price":400.0,"quantity":102.0,"weight":12.0,"parts":[{"type":"motherboard","manufacturer":"XYZ","cost":70.0,"description":"Reliable motherboard"},{"type":"ram","manufacturer":"HP","cost":10.0,"description":"4gb 2400mhz RAM"},{"type":"cpu","manufacturer":"Intel","cost":100.0,"description":"Built In Processor"},{"type":"gpu","manufacturer":"HP","cost":75.0,"description":"On board graphics"},{"type":"storage","manufacturer":"Seagate","cost":20.0,"description":"1TB HDD"}],"reviews":["review03"]},
{"_id":{"$oid":"6185c244aca3214e77e748ab"},"name":"Chromebook","manufacturer":"Acer","category":["Portable","Lightweight","Work","Student"],"colour":"red","price":350.0,"quantity":61.0,"weight":4.0,"parts":[{"type":"motherboard","manufacturer":"XYZ","cost":40.0,"description":"Reliable motherboard for Laptops"},{"type":"ram","manufacturer":"Acer","cost":10.0,"description":"Built in 4gb RAM"},{"type":"cpu","manufacturer":"AMD","cost":100.0,"description":"Built In Processor"},{"type":"gpu","manufacturer":"Acer","cost":45.0,"description":"On board graphics"},{"type":"storage","manufacturer":"Acer","cost":25.0,"description":"64GB HDD"}],"reviews":["review04","review05"]}
和评论集合;
{"_id":"review01","reviewer":"Jack Weir","rating":5.0,"text":"Very good product!","created":"2021, 08, 03"},
{"_id":"review02","reviewer":"Adam Smith","rating":3.0,"text":"Good value for money","created":"2021, 09, 04"},
{"_id":"review03","reviewer":"Luke Bridges","rating":1.0,"text":"Not good!","created":"2021, 10, 21"},
{"_id":"review04","reviewer":"Richard Lamb","rating":4.0,"text":"Runs very smoothly","created":"2021, 11, 03"},
{"_id":"review05","reviewer":"Pamela Halpert","rating":4.0,"text":"Easy to setup","created":"2021, 11, 04"},
{"_id":"review06","reviewer":"Michael Scott","rating":5.0,"text":"Would highly recommend","created":"2021, 11, 05"},
{"_id":"review07","reviewer":"Angela Lewis","rating":2.0,"text":"A bit too pricey","created":"2021, 11, 06"},
{"_id":"review08","reviewer":"Dexter Morgan","rating":3.0,"text":"Does the job","created":"2021, 11, 06"},
{"_id":"review09","reviewer":"Lisa Simpson","rating":2.0,"text":"Okay but not great","created":"2021, 11, 06"},
{"_id":"review10","reviewer":"Marge Simpson","rating":5.0,"text":"Wonderful Computer exactly what I was after!","created":"2021, 11, 07"}
如何编写一个查询来返回每个产品的名称以及它从评论属性中的任何评级获得的平均评级?
例如,我希望查询结果显示“名称:“Gaming PC”,平均评分:4.3'
解决方案
您只需要$lookup
加入集合(将审阅者数据获取到计算机集合中)并$avg
获取平均值。
db.computers.aggregate({
"$lookup": {
"from": "reviews",
"localField": "reviews",
"foreignField": "_id",
"as": "reviewers"
}
},
{
"$project": {
"name": 1,
"avg": {
"$avg": "$reviewers.rating"
}
}
})
这里的例子
此查询仅输出name
和avg
字段,但您可以根据需要将任意数量$project
的字段添加到阶段。您也可以使用$set
or$addFields
代替 project 以不丢失所有值(示例)
推荐阅读
- python - 我的 if-else 语句有问题吗?
- sql-server - T-SQL BULK INSERT 类型不匹配
- command-line-interface - 寻找一种为 DataStage 用户自动映射用户凭据的方法
- java - 错误:0:3:错误(#279)无效的布局限定符“位置”
- angular - 在 Angular 10 中初始化之前无法访问“MyComponent”
- ruby-on-rails - 渲染集合 rails 6 时有没有办法传递依赖于项目的本地
- soap - NetSuite - Postman - REST - “请求的数据中心不正确!”
- reactjs - 我是否正确地认为 ApolloClient 缓存了我的查询——甚至跨组件——所以 useContext 不是必需的?
- appium - appium - XCUITest 并不总是以错误 ECONNREFUSED 开始
- python - 如何将单个函数应用于对象列表中的不同属性组合?