MongoDB聚集普查

Question

我想把这些数据汇总起来，我需要按月来统计数据，比如人口普查。对于我有到达日期和出发日期的每一件物品，这个项目在这个范围内有一个月的总和+1。我该怎么做？

数据：

[{
    _id:1
    history:
    [
       {_id:1, date:2021-03-01, type:"Departure"},     
       {_id:2, date:2021-01-01, type:"Arrival"}
    ]
},
{
    _id:2
    history:
    [
       {_id:1, date:2021-07-03, type:"Departure"}, 
       {_id:2, date:2021-02-10, type:"Arrival"}
    ]
},
{
    _id:3
    history:
    [
       {_id:1, date:2021-08-22, type:"Departure"}, 
       {_id:2, date:2021-11-5, type:"Arrival"}
    ]
}]

结果：

    {month:1, year:2021, counter:1},
    {month:2, year:2021, counter:2},
    {month:3, year:2021, counter:2},
    {month:3, year:2021, counter:1},
    {month:4, year:2021, counter:1},
    {month:5, year:2021, counter:1},
    {month:6, year:2021, counter:1},
    {month:7, year:2021, counter:0},
    {month:8, year:2021, counter:1},
    {month:9, year:2021, counter:1},
    {month:10, year:2021, counter:1},
    {month:11, year:2021, counter:1},
    {month:12, year:2021, counter:0}
]

Answer 1

我更改您的数据_id=3，因为出发日期应该大于到达日期。

• $match
• $set
• $set
• $set
• $project
• $unwind
• $group
• $project
• $sort

db.collection.aggregate([
  {
    "$match": {}
  },
  {
    "$set": {
      arrival: {
        $dateFromString: {
          dateString: {
            $arrayElemAt: [ "$history.date", 1 ]
          }
        }
      },
      departure: {
        $dateFromString: {
          dateString: {
            $arrayElemAt: [ "$history.date", 0 ]
          }
        }
      }
    }
  },
  {
    "$set": {
      monthDiff: {
        $dateDiff: {
          startDate: "$arrival",
          endDate: "$departure",
          unit: "month"
        }
      }
    }
  },
  {
    "$set": {
      historyRange: {
        "$map": {
          "input": { $range: [ 0, { $add: [ "$monthDiff", 1 ] }, 1 ] },
          "as": "m",
          "in": {
            item: {
              $dateAdd: {
                startDate: "$arrival",
                unit: "month",
                amount: "$$m"
              }
            }
          }
        }
      }
    }
  },
  {
    "$project": {
      historyRange: {
        "$map": {
          "input": "$historyRange",
          "as": "h",
          "in": {
            year: { $year: "$$h.item" },
            month: { $month: "$$h.item" }
          }
        }
      }
    }
  },
  {
    "$unwind": "$historyRange"
  },
  {
    "$group": {
      "_id": "$historyRange",
      "counter": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      _id: 0,
      counter: 1,
      month: "$_id.month",
      year: "$_id.year"
    }
  },
  {
    "$sort": { month: 1 }
  }
])

蒙古操场