Calculate Percentage Increase from Array - MongoDB

Question

My db looks like this: (Sanitized Version)

[{
    "_id": 1,
    "Type": "Car",
    "Total": 82.03,
    "DateTime": "20210713T0834"
}, {
    "_id": 2,
    "Type": "Car",
    "Total": 20.26,
    "DateTime": "20210827T0834"
}, {
    "_id": 3,
    "Type": "Air",
    "Total": 50.43,
    "DateTime": "20210913T0834"
}, {
    "_id": 4,
    "Type": "Car",
    "Total": 42.03,
    "DateTime": "20210813T0834"
}]

I need to get the percentage increase each Type per month.

I tried to do it as follows:

1. Filter data in db for 2 consecutive months
2. Group the data by month
3. Group the data by type and outputs it to an array
4. Tries to compute the percentage increase from the array. This is the part I am stuck.

I feel that there is another proper way to do it and that I am doing too much work for it to work. Can anyone guide me?

Thank you.

The code for the above steps (Step 1 - 3) can be found here:

{"$match": {"DateTime": {"$gte": "20210701", "$lte": "20210901"}}},
{"$addFields": {"month": {"$substr": ["$DateTime", 4, 2]}}},
#Split by month
{"$group": {"_id":  {"month" : "$month", "type" : "$Type"}, "amount": {"$sum": "$Total"}}},
#Split further by types and create array
{"$group":{"_id": "$_id.type", "totalUSDPerTypePerMonth" : { "$push" : {"month":"$_id.month","type" : "$_id.type", "total" : "$amount"}}}},

I am getting this output so far:

How to proceed any further?

Thank you.

Answer 1

• $setWindowFields

db.collection.aggregate([
  {
    $match: {}
  },
  {
    $setWindowFields: {
      partitionBy: "$Type",
      sortBy: { DateTime: 1 },
      output: {
        sumOfThePreviousTotal: {
          $sum: "$Total",
          window: { documents: [ -1, 0 ] }
        },
        previousDateTime: {
          $push: "$DateTime",
          window: { documents: [ -1, 0 ] }
        }
      }
    }
  },
  {
    $set: {
      "previousDateTime": { "$toDate": { "$first": "$previousDateTime" } },
      "DateTime": { "$toDate": "$DateTime" },
      "perviousTotal": { "$subtract": [ "$sumOfThePreviousTotal", "$Total" ] }
    }
  },
  {
    $set: {
      "dateDiff": {
        "$dateDiff": {
          startDate: "$previousDateTime",
          endDate: "$DateTime",
          unit: "day"
        }
      }
    }
  },
  {
    $set: {
      "percentageIncreasePerDay": {
        "$cond": {
          "if": { "$eq": [ "$dateDiff", 0 ] },
          "then": 0,
          "else": { "$divide": [ { "$subtract": [ "$Total", "$perviousTotal" ] }, "$dateDiff" ] }
        }
      }
    }
  }
])

mongoplayground