Mongodb 聚合累积

Question

我有集合PurchaseOrder和嵌套数组PurchaseOrderLines，在PurchaseOrderLines我有两个字段ReceivingQty和SendingQty。

我想计算库存趋势(组 =>ProductID，Date，TotalReceivingQty-TotalSendingQty( 结果应该是这样的：

ProductID | TotalQty | SeqTotal | Date
1         | 5        | 5        | 01-01
1         | -2       | (5-2)=3  | 01-02
1         | 10       | (3+10)=13| 01-03
1         | -5       | (13-5)=8 | 01-04

我已经这样做了：

db.PurchaseOrder.aggregate(
[       
{ "$unwind":"$PurchaseOrderLines"},
{
"$group": {
"_id": {
"Warehouse": "$Warehouse",
"PurchaseOrderLines_ProductID": "$PurchaseOrderLines.ProductID",
"WarehouseTypeID": "$WarehouseTypeID"
,"NowDate": "$NowDate"
},
"TotalReceivingQty":{"$sum":"$PurchaseOrderLines.ReceivingQty"},
"TotalSendingQty":{"$sum":"$PurchaseOrderLines.SendingQty"},
"GProductID":{"$first":"$PurchaseOrderLines.ProductID"}
}
},
{
"$lookup": {
from: "Product",
localField: "GProductID",
foreignField: "ProductId",
as: "product_doc_list"
}
},
{
"$project": {
"ProductID": "$_id.PurchaseOrderLines_ProductID",
"Product": {
$map: {
input: "$product_doc_list",
as: "product_doc",
in: "$$product_doc"
}
},
"TotalQty": {"$subtract": ["$TotalReceivingQty", "$TotalSendingQty"]},
"WarehouseTypeID": "$_id.WarehouseTypeID",
"Warehouse": "$_id.Warehouse",
"TotalReceivingQty":"$TotalReceivingQty",
"TotalSendingQty":"$TotalSendingQty",
"Date":"$_id.NowDate"
}
}
],{ allowDiskUse: true });

这个答案用于$lookup自我加入。 但我无法弄清楚它如何与数组一起工作。

样品：

/* 1 */
{   
"ProductID" : NumberDecimal("26.101"),
"TotalQty" : NumberDecimal("52.000000"),
"TotalReceivingQty" : NumberDecimal("52.000000"),
"TotalSendingQty" : 0,
/* need this property */
"SeqTotal":sum(prev(SeqTotal),TotalQty)=NumberDecimal("52.000000"),
"Date" : "01-01"
},
/* 2 */
{
"ProductID" : NumberDecimal("26.101"),
"TotalQty" : NumberDecimal("-1.000000"),
"TotalReceivingQty" : 0,
"TotalSendingQty" : NumberDecimal("1.000000"),
/* need this property */
"SeqTotal":sum(prev(SeqTotal),TotalQty)=52-1=>51,
"Date" : "01-03"
},
/* 3 */
{
"ProductID" : NumberDecimal("26.101"),
"TotalQty" : NumberDecimal("1.000000"),
"TotalReceivingQty" : NumberDecimal("1.000000"),
"TotalSendingQty" : 0,
/* need this property */
"SeqTotal":sum(prev(SeqTotal),TotalQty)=51+1=>52,
"Date" : "01-04  "
},
/* 4 */
{
"ProductID" : NumberDecimal("26.101"),
"TotalQty" : NumberDecimal("92.000000"),
"TotalReceivingQty" : NumberDecimal("92.000000"),
"TotalSendingQty" : 0,
/* need this property */
"SeqTotal":sum(prev(SeqTotal),TotalQty)=52+92=>144,
"Date" : "01-17"
},
/* 5 */
{
"ProductID" : NumberDecimal("26.101"),
"TotalQty" : NumberDecimal("-96.000000"),
"TotalReceivingQty" : 0,
"TotalSendingQty" : NumberDecimal("96.000000"),
/* need this property */
"SeqTotal":sum(prev(SeqTotal),TotalQty)=144-96=>48,
"Date" : "01-20"
}

Answer 1

好的！感谢@Veeram的评论，我最终得到了这个解决方案。

db.PurchaseOrder.aggregate(
[    
{ '$unwind': '$PurchaseOrderLines'},
{
'$group': {
_id: {
'wh': '$Warehouse',
'pid': '$PurchaseOrderLines.ProductID',
'wht': '$WarehouseTypeID',
'dt': '$NowDate'
},
'trq':{'$sum': {$ifNull: [ '$PurchaseOrderLines.ReceivingQty', 0 ] }},
'tsq':{'$sum': {$ifNull: [ '$PurchaseOrderLines.SendingQty', 0 ] }}
}
},
{
'$sort': {
'_id.dt': 1
}
},
{
'$group': {
'_id': {pid:'$_id.pid'},
'_gp': { '$push': '$_id' },
'totals': { '$push': {'$subtract': ['$trq', '$tsq']} }
}
},
{
'$unwind': {
'path' : '$_gp',
'includeArrayIndex' : 'index'
}
},
{
'$lookup': {
from: 'Product',
localField: '_id.pid',
foreignField: 'ProductId',
as: 'product_doc_list'
}
},
{
'$project': {
'_id': 0,
'Product': {
$map: {
input: '$product_doc_list',
as: 'product_doc',
in: '$$product_doc'
}
},
'WarehouseTypeID': '$_gp.wht',
'Warehouse': '$_gp.wh',
'TotalQty': { '$arrayElemAt': [ '$totals', '$index' ] },
'SeqTotal':{ '$sum': { '$slice': [ '$totals', { '$add': [ '$index', 1 ] } ] } },
'Date':'$_gp.dt'
}
}
],{allowDiskUse:true}
);