Mongodb聚集-累积 [英] Mongodb aggregation-accumulate
本文介绍了Mongodb聚集-累积的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 PurchaseOrderLines $中拥有集合
PurchaseOrder
和嵌套数组 PurchaseOrderLines
。 c $ c>我有两个字段 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}
);
这篇关于Mongodb聚集-累积的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文