如何在MongoDB中获得两个字段的差异之和 - How to get sum of differences of two fields in MongoDB?

How to get sum of differences of two fields in MongoDB?

本文关键字：字段两个 MongoDB | 更新日期: 2023-09-27

我已经有了一个解决方案，但我正在寻找一个解决方案，它可以在MongoServer上完成所有的工作(因为我认为它会更快，更少的内存消耗)

我有一个类方法:

function getTotalOutstandingAmount(){
    $outstandingAmount = 0;
    $subs = $this->mongo->selectCollection('SmsSubscriptions');
    $activeSubsctiptions = $subs->find(array('Status' => 1, '$where' => "this.SubscriptionPayments < this.SubscriptionTotal"));
    foreach ($activeSubsctiptions AS $sub){
        $outstandingAmount += $sub['SubscriptionTotal'] - $sub['SubscriptionPayments'];
    }
    return $outstandingAmount;
}

现在是否有一种方法可以使用MongoDB的aggregate方法计算两个字段的差异之和?还有其他更有效的方法吗?

聚合方法应该有这样的管道:

db.SmsSubscriptions.aggregate([
    { 
        "$project": {
            "outstandingAmount": { 
                "$subtract": ["$SubscriptionTotal", "$SubscriptionPayments"] 
            },
            "Status": 1
        }
    },
    { "$match": { "Status": 1, "outstandingAmount": { "$gt": 0 } } },   
    {
        "$group": {
            "_id": null,
            "totalOutstandingAmount": { "$sum": "$outstandingAmount" }
        }
    }
])

等效的PHP示例实现:

$ops = array(
    array(
        "$project" => array(
            "Status" => 1,
            "outstandingAmount" => array(               
                "$subtract" => array("$SubscriptionTotal", "$SubscriptionPayments")
                )
            )           
        )
    ),
    array( 
        "$match" => array( 
            "Status" => 1, 
            "outstandingAmount" => array("$gt" => 0) 
        ) 
    ),
    array(
        "$group" => array(
            "_id" => null,
            "totalOutstandingAmount" => array("$sum" => "$outstandingAmount" )
        )
    )
);
$results = $this->mongo->selectCollection('SmsSubscriptions')->aggregate($ops);

求差和的一般简单紧解

collectionX

{ _id: 'xxxx', itemOne: 100, itemTwo: 300 }
{ _id: 'yyyy', itemOne: 200, itemTwo: 800 }
{ _id: 'zzzz', itemOne: 50, itemTwo: 400 }

聚集操作

db.collectionX.aggregate([
  {
    $group: {
      _id: null,
      sumOfDifferences: { $sum: { $subtract: ['$itemTwo', '$itemOne']}
  }
])

反应

{
   "_id" : null,
   "sumOfDifferences" : 1150
}