数组中的聚合总数
问题描述：
我在使用MongoDb对数据进行分组时遇到问题。数组中的聚合总数
我有一些项目与一些进出运动，我想计算一个项目的总量与运动量，但运动不正确计算。
这里是我的样本数据，小的单子有两股和一些运动....
/* 1 */
{
"TemplateName" : "SAALottoStagionatura",
"idStock" : 31789,
"idWarehouse" : 191,
"StockCode" : "71529902",
"Marks" : [
{
"idMark" : 20145,
"idWarehouse" : 191,
"idStock" : 31789,
"ProgressivoDocumento" : 486,
"Year" : 2016,
"RefDate" : ISODate("2016-03-28T22:00:00.000Z"),
"MarkedItems" : 72
},
{
"idMark" : 20156,
"idWarehouse" : 191,
"idStock" : 31789,
"ProgressivoDocumento" : 497,
"Year" : 2016,
"RefDate" : ISODate("2016-03-30T22:00:00.000Z"),
"MarkedItems" : 144
},
{
"idMark" : 23424,
"idWarehouse" : 191,
"idStock" : 31789,
"ProgressivoDocumento" : 840,
"Year" : 2016,
"RefDate" : ISODate("2016-06-12T22:00:00.000Z"),
"MarkedItems" : 3
}
],
"Details" : [
{
"idLSDetail" : 42781,
"idStock" : 31789,
"idStockOrig" : 54502,
"StockCode" : "71529902",
"Items" : 4532
}
],
"MovementsOut" : [
{
"idMovementDetail" : 633,
"idMovement" : 511,
"MovedItems" : 3528 ,
"idStockOrig" : null,
"idStock" : 31789
}
],
"MovementsIn" : [
{
"idMovementDetail" : 715,
"idMovement" : 570,
"MovedItems" : 3528,
"idStockOrig" : null,
"idStock" : 33678
}
]
}
/* 2 */
{
"TemplateName" : "SAALottoStagionatura",
"idStock" : 33678,
"idWarehouse" : 190,
"StockCode" : "71529902",
"Marks" : [],
"Details" : [
{
"idLSDetail" : 45206,
"idStock" : 33678,
"idStockOrig" : 56684,
"StockCode" : "71529902",
"Items" : 3528
}
],
"MovementsOut" : [
{
"idMovementDetail" : 715,
"idMovement" : 570,
"MovedItems" : 3528,
"idStockOrig" : null,
"idStock" : 33678
}
],
"TrasferimentiInEntrata" : []
}
，并在我的查询我尝试组运动
db.getCollection('Test')
.aggregate(
[
{$match: {"idWarehouse": 191, StockCode: "71529902" } },
{$unwind: "$Details"},
{$unwind: "$Marks"},
{$unwind: "$MovementsIn"},
{$unwind: "$MovementsOut"},
{
$group : {
_id : {
StockCode: "$idStock",
StockCode: "$StockCode"
},
tot: { $sum: "$Details.Items" },
cer: { $sum: "$Marks.MarkedItems" },
in: { $sum: "$MovementsIn.MovedItems" },
out: { $sum: "$MovementsOut.MovedItems" }
}
}
]
)
我的期望应该是这样的
{
"_id" : {
"StockCode" : "71529902"
},
"tot" : 13596,
"cer" : 219,
"in" : 3528,
"out" : 7056
}
但是我总是得到运动的全部总和（10584）in and out。我在哪里错了？
答
其实在任何现代的MongoDB上映以来，3.2只需使用的$sum“双barelled”调用，并没有$unwind可言：
db.getCollection('Test').aggregate([
{ "$group": {
"_id": {
"StockCode": "$StockCode"
},
"tot": { "$sum": { "$sum": "$Details.Items" } },
"cer": { "$sum": { "$sum": "$Marks.MarkedItems" } },
"in": { "$sum": { "$sum": "$MovementsIn.MovedItems" } },
"out": { "$sum": { "$sum": "$MovementsOut.MovedItems" } }
}}
])
这是因为该版本，当你谱写一个元素在诸如"$Details.Items"的数组中，投影结果是在指定路径中找到的“值的数组”。第二个补充是$sum也是“总和数组”，所以它在组中被调用为$sum数组内容，然后$sum作为文件之间的“累加器”。
/* 1 */
{
"_id" : {
"StockCode" : "71529902"
},
"tot" : 8060.0,
"cer" : 219.0,
"in" : 3528.0,
"out" : 7056.0
}
在早期版本如MongoDB的2.6你可以避开“笛卡尔积”，这是$unwind多个结果：
时，针对你的问题的两个文件运行返回结果由阵列组合成一个，可能使用$setUnion阵列如果标识符和值实际上唯一的：
db.getCollection('Test').aggregate([
{ "$project": {
"StockCode": 1,
"combined": {
"$setUnion": [
{ "$map": {
"input": { "$ifNull": [ "$Details", [] ] },
"as": "el",
"in": { "id": "$idLSDetail", "k": "Details", "v": "$$el.Items" }
}},
{ "$map": {
"input": { "$ifNull": [ "$Marks", [] ] },
"as": "el",
"in": { "id": "$idMark", "k": "Marks", "v": "$$el.MarkedItems" }
}},
{ "$map": {
"input": { "$ifNull": [ "$MovementsIn", [] ] },
"as": "el",
"in": { "id": "$idMovementDetail", "k": "MoveIn", "v": "$$el.MovedItems" }
}},
{ "$map": {
"input": { "$ifNull": [ "$MovementsOut", [] ] },
"as": "el",
"in": { "id": "$idMovementDetail", "k": "MoveOut", "v": "$$el.MovedItems" }
}}
]
}
}},
{ "$unwind": "$combined" },
{ "$group": {
"_id": {
"StockCode": "$StockCode"
},
"tot": {
"$sum": {
"$cond": {
"if": { "$eq": [ "$combined.k", "Details" ] },
"then": "$combined.v",
"else": 0
}
}
},
"cer": {
"$sum": {
"$cond": {
"if": { "$eq": [ "$combined.k", "Marks" ] },
"then": "$combined.v",
"else": 0
}
}
},
"in": {
"$sum": {
"$cond": {
"if": { "$eq": [ "$combined.k", "MoveIn" ] },
"then": "$combined.v",
"else": 0
}
}
},
"out": {
"$sum": {
"$cond": {
"if": { "$eq": [ "$combined.k", "MoveOut" ] },
"then": "$combined.v",
"else": 0
}
}
}
}}
])
而我n旧版本或确实无法实现“唯一性”的情况下，您分别替换每个阵列，然后重复该过程，直到阵列“减少”为止。然后你可以将$group作为最终的文件。
但是和上面一样，你需要注意，因为并不是所有的文件都有所有的数组，所以需要处理。在上面的例子中，我们可以提供一个“空”数组而不是null（在最初的$sum并不在意）。但是，一旦你在每次使用$unwind单独你进入的问题，如果没有什么存在或为空：
db.getCollection('Test').aggregate([
{ "$project": {
"StockCode": 1,
"Details": {
"$cond": [
{ "$eq": [{ "$size": { "$ifNull": [ "$Details", [] ] } }, 0] },
[null],
"$Details"
]
},
"Marks": {
"$cond": [
{ "$eq": [{ "$size": { "$ifNull": [ "$Marks", [] ] } }, 0] },
[null],
"$Marks"
]
},
"MovementsIn": {
"$cond": [
{ "$eq": [{ "$size": { "$ifNull": [ "$MovementsIn", [] ] } }, 0] },
[null],
"$MovementsIn"
]
},
"MovementsOut": {
"$cond": [
{ "$eq": [{ "$size": { "$ifNull": [ "$MovementsOut", [] ] } }, 0] },
[null],
"$MovementsOut"
]
}
}},
{ "$unwind": "$Details" },
{ "$group": {
"_id": "$_id",
"StockCode": { "$first": "$StockCode" },
"tot": { "$sum": "$Details.Items" },
"Marks": { "$first": "$Marks" },
"MovementsIn": { "$first": "$MovementsIn" },
"MovementsOut": { "$first": "$MovementsOut" }
}},
{ "$unwind": "$Marks" },
{ "$group": {
"_id": "$_id",
"StockCode": { "$first": "$StockCode" },
"tot": { "$first": "$tot" },
"cer": { "$sum": "$Marks.MarkedItems" },
"MovementsIn": { "$first": "$MovementsIn" },
"MovementsOut": { "$first": "$MovementsOut" }
}},
{ "$unwind": "$MovementsIn" },
{ "$group": {
"_id": "$_id",
"StockCode": { "$first": "$StockCode" },
"tot": { "$first": "$tot" },
"cer": { "$first": "$cer" },
"in": { "$sum": "$MovementsIn.MovedItems" },
"MovementsOut": { "$first": "$MovementsOut" }
}},
{ "$unwind": "$MovementsOut" },
{ "$group": {
"_id": "$_id",
"StockCode": { "$first": "$StockCode" },
"tot": { "$first": "$tot" },
"cer": { "$first": "$cer" },
"in": { "$first": "$in" },
"out": { "$sum": "$MovementsOut.MovedItems" }
}},
{ "$group": {
"_id": {
"StockCode": "$StockCode",
},
"tot": { "$sum": "$tot" },
"cer": { "$sum": "$cer" },
"in": { "$sum": "$in" },
"out": { "$sum": "$out" }
}}
])
非常清楚和完整的答案。非常感谢！ – Grappachu