英文:
How can I calculate the average between fields outside a document array with those inside a documents array in MongoDB and Java?
问题
我在我的数据库中有这个文档：
[
{
"_id": {
"$oid": "5f5f280ffa2236115655cb6a"
},
"Name": "Rovilio Chipman",
"Last_season": {
"year": "2010-2011",
"goals": 10,
"assists": 1
},
"Last_season_2": {
"year": "2011-2012",
"goals": 1,
"assists": 12
},
"Seasons": [
{
"year": "2012-2013",
"goals": 11,
"assists": 4
},
{
"year": "2013-2014",
"goals": 6,
"assists": 2
},
{
"year": "2014-2015",
"goals": 5,
"assists": 5
}
]
}
]
我想获取所有目标的平均值，即在 "Last_season"、"Last_season_2" 和 "Seasons" 中包含的目标的平均值。结果应为 33/5 = 6.6。
注意：用于计算此平均值的文档大小不同，即 "Seasons" 数组可以包含不同数量的文档且不固定。
在这种情况下，我如何计算这个平均值？我如何使用 Java Driver 编写代码？
英文:
I have this document in my database:
[
{
"_id": {
"$oid": "5f5f280ffa2236115655cb6a"
},
"Name": "Rovilio Chipman",
"Last_season": {
"year": "2010-2011",
"goals": 10,
"assists": 1
},
"Last_season_2": {
"year": "2011-2012",
"goals": 1,
"assists": 12
},
"Seasons": [
{
"year": "2012-2013",
"goals": 11,
"assists": 4
},
{
"year": "2013-2014",
"goals": 6,
"assists": 2
},
{
"year": "2014-2015",
"goals": 5,
"assists": 5
}
]
}
]
I would like to get the average of all goals, ie the average of the goals included in "Last_season", "Last_season_2" and "Season". The result should be 33/5 = 6.6.
NB: the documents on which to make this average are of different size, ie the "Season" array can contain a different number of documents and not fixed.
How do I calculate this average in this case? How do I code it with Java Driver?
答案1
得分: 3
首先，您需要将所有值放入一个数组中，然后可以计算平均值。这可能是一个解决方案：
db.collection.aggregate([
{
$set: {
AllSeasons: {
$concatArrays: [
"$Seasons",
[ "$Last_season" ],
[ "$Last_season_2" ]
]
}
}
},
{ $set: { average: { $avg: [ "$AllSeasons.goals" ] } } },
{ $unset: "AllSeasons" }
])
<details>
<summary>英文:</summary>
First you need to put all values in one array, then you can calculate the average. This could be one solution:
db.collection.aggregate([
{
$set: {
AllSeasons: {
$concatArrays: [
"$Seasons",
[ "$Last_season" ],
[ "$Last_season_2" ]
]
}
}
},
{ $set: { average: { $avg: [ "$AllSeasons.goals" ] } } },
{ $unset: "AllSeasons" }
])
[Mongo Playground][1]
[1]: https://mongoplayground.net/p/QLsvSsBoAGz
</details>
# 答案2
**得分**: 1
你可以使用Mongo聚合来实现这个。
```json
db.collection.aggregate([
{
"$project": {
/* 在Seasons列表中求目标之和 */
"seasons_goals": {
"$sum": [
"$Seasons.goals"
]
},
/* 计算季节数量：Seasons的长度 + 2 */
"nb_seasons": {
"$sum": [
{
"$size": "$Seasons"
},
2
]
},
/* 求最后两个季节的目标之和 */
"total": {
"$sum": [
"$Last_season.goals",
"$Last_season_2.goals"
]
}
}
},
/* 通过将seasons_goals+total除以nb_seasons来计算平均值 */
{
"$project": {
"result": {
"$divide": [
{
"$sum": [
"$seasons_goals",
"$total"
]
},
"$nb_seasons"
]
}
}
}
])
这里有一篇关于：使用Java驱动程序进行MongoDB聚合
英文:
You can do that with a mongo aggregate.
db.collection.aggregate([
{
"$project": {
/* Summing goals in the Seasons list */
"seasons_goals": {
"$sum": [
"$Seasons.goals"
]
},
/* Counting the number of seasons: length of Seasons + 2 */
"nb_seasons": {
"$sum": [
{
"$size": "$Seasons"
},
2
]
},
/* Summing goals of the two last seasons */
"total": {
"$sum": [
"$Last_season.goals",
"$Last_season_2.goals"
]
}
}
},
/* Calculate the average by dividing seasons_goals+total by nb_seasons */
{
"$project": {
"result": {
"$divide": [
{
"$sum": [
"$seasons_goals",
"$total"
]
},
"$nb_seasons"
]
}
}
}
])
And here is a post on: MongoDB aggregation with Java driver
答案3
得分: 1
请检查一下这是否适用于您：
[
{
"$set": {
"Seasons": {
"$concatArrays": [
"$Seasons",
[
"$Last_season_2",
"$Last_season"
]
]
}
}
},
{
"$project": {
"Name": 1,
"avgGoals": {
"$divide": [
{
"$reduce": {
"input": "$Seasons",
"initialValue": 0,
"in": {
"$sum": [
"$$this.goals",
"$$value"
]
}
}
},
{
"$size": "$Seasons"
}
]
}
}
}
]
英文:
Please check if this works for you:
[
{
$set: {
"Seasons": {
$concatArrays: [
"$Seasons",
[
"$Last_season_2",
"$Last_season"
]
]
}
}
},
{
$project: {
"Name": 1,
"avgGoals": {
$divide: [
{
$reduce: {
input: "$Seasons",
initialValue: 0,
in: {
$sum: [
"$$this.goals",
"$$value"
]
}
}
},
{
$size: "$Seasons"
}
]
}
}
}
]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论