I have a customers model:

const CustomerSchema = new Schema({
    ...
    activeStartDate: Date, 
    activeEndDate: Date
    ...
}

Now I want to create an aggregate that creates a timeseries of active customers. So an output of:

[
    {
       _id: {year: 2022 month: 7}
       activeCustomers: 500
    },
    ...
]

The issue I cant figure out is how to get one customer document to count in multiple groups. A customer could be active for years, and therefore they should appear in multiple timeframes.

CodePudding user response：

One option is:

1. Create a list of dates according to the months difference
2. $unwind to create a document per each month
3. $group by year and month and count the number of customers

db.collection.aggregate([
  {$set: {
      months: {$map: {
          input: {
            $range: [
              0,
              {$add: [
                  {$dateDiff: {
                      startDate: "$activeStartDate",
                      endDate: "$activeEndDate",
                      unit: "month"
                  }},
              1]}
            ]
          },
          in: {$dateAdd: {
              startDate: {$dateTrunc: {date: "$activeStartDate", unit: "month"}},
              unit: "month",
              amount: "$$this"
          }}
      }}
  }},
  {$unwind: "$months"},
  {$group: {
      _id: {year: {$year: "$months"}, month: {$month: "$months"}},
      activeCustomers: {$sum: 1}
  }}
])

See how it works on the playground example