Mongodb聚集

本文介绍了Mongodb聚集-累积的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在 PurchaseOrderLines 我有两个字段 ReceivingQty 和 SendingQty 。

I have collection PurchaseOrder and nested array PurchaseOrderLines, in the PurchaseOrderLines I have two fields ReceivingQty and SendingQty.

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

I want to calculate inventory trending(Group => ProductID, Date, TotalReceivingQty-TotalSendingQty) Result should be something like this:

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 进行自我加入。

This answer used $lookup to self join. but I can't figure out how it works with arrays.

示例：

/* 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" }

推荐答案

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

OK! Thank to @Veeram for his comment, I ended up with this solution.

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} );