Aggregation for every 10 weeks between week 1 2021 (including) and week 45 2021 (excluding)

Let us say for example I have a collection which contains car sales information for a manufacturer worldwide.

Timeline     Country    Sales
2021-W01       A          10
2021-W02       B          20
2021-W03       C          30
…
2022-W33       Z          50

Now I would like the aggregation to compute total sales for every 10 weeks between week 1 2021 (including) and week 45 2021 (excluding).

Desired Output:

{
    "result": [
        {
            "start": "2021-W01",
            "end": "2021-W10",
            "totalSales": 100
        },
        {
            "start": "2021-W10",
            "end": "2021-W20",
            "totalSales": 20
        },
        …
        {
            "start": "2021-W40",
            "end": "2021-W45",
            "totalSales": 1
        }
    ]
}

For this so far, I have come up with this solution.

db.collection.aggregate([
    {"$match": {"$and":[{"Country": "A"}, {"Timeline": {"$in": [‘2021-W01’, ‘2021-W11’, … ‘2021-W45’]}}]}},
    {"$group": {"_id": {Timeline: "$Timeline", totalSales: {"$sum": "$Sales"}}}},
    {"$project": {"_id": 0, result: "$_id"}}
])

But this is producing output like this

[
    {
        "result": {
            "Timeline": "2021-W01",
            "totalSales": 10
        }
    },
    {
        "result": {
            "Timeline": "2021-W02",
            "totalSales": 20
        }
    },
    …
    {
    "result": {
            "Timeline": "2021-W45",
            "totalSales": 23
        }
    }
]

I am unable to get aggregated results for every 10 weeks as this is only doing it for every week.
If possible, I kindly request everyone to help me understand this. Thanks.

Hi :wave: @Aswin_Ramani,

Welcome to the MongoDB Community forums :sparkles:

As a sample dataset in MongoDB, I have considered the following:

[{
  "Timeline": "2021-W01",
  "Sales": 11,
  "Country": "A"
},{
  "Timeline": "2021-W02",
  "Sales": 11,
  "Country": "B"
},{
  "Timeline": "2021-W03",
  "Sales": 30,
  "Country": "C"
},{
  "Timeline": "2021-W04",
  "Sales": 40,
  "Country": "D"
},{
  "Timeline": "2021-W05",
  "Sales": 11,
  "Country": "C"
},{
  "Timeline": "2021-W06",
  "Sales": 11,
  "Country": "B"
},{
  "Timeline": "2021-W07",
  "Sales": 11,
  "Country": "D"
},{
  "Timeline": "2021-W08",
  "Sales": 11,
  "Country": "E"
}]

For the sample dataset, I have considered every 3 weeks between week 1 of 2021 and week 8 of 2021:

This is the aggregation pipeline I used to produce the output mentioned in the problem statement:

db.collection.aggregate([
  {
    $addFields: {
      week: {
        $week: {
          $dateFromString: {
            dateString: "$Timeline",
            format: "%G-W%V"
          }
        }
      }
    }
  },
  {
    $bucket: {
      groupBy: "$week",
      boundaries: [
        1,
        4,
        7
      ],
      default: "Other",
      output: {
        "totalSales": {
          $sum: "$Sales"
        },
        "start": {
          $min: "$Timeline"
        },
        "end": {
          $max: "$Timeline"
        },
        
      }
    }
  },
  {
    "$project": {
      "_id": 0
    }
  }
])

Here $bucket categorizes the documents into groups, called buckets, based on a specified expression and its boundaries and outputs a document per each bucket.

So, it will give the following output, similar to the desired one:

[
  {
    "end": "2021-W03",
    "start": "2021-W01",
    "totalSales": 52
  },
  {
    "end": "2021-W06",
    "start": "2021-W04",
    "totalSales": 62
  },
  {
    "end": "2021-W08",
    "start": "2021-W07",
    "totalSales": 22
  }
]

Some of the aggregation stages/operators used in the above for your reference:

Please note that I have only tested on a few sample documents. Depending on your use case(s), you can adjust accordingly. Before using it in production, it is highly recommended to test it in a test environment to ensure it meets all your requirements and use cases.

Please let us know if you have any further questions.

Regards,
Kushagra

2 Likes

Hi @Kushagra_Kesav,

Thanks for this explanation! Now I have an idea. I will try it out and let you know.

1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.