如何使用MongoDB查询根据之前的用户数计算每个月的用户数

Question

我需要根据上个月的计数值计算累计用户数。

结果数组应该如下所示：

RESULTS  [
{ count: 1, accumulatedCount: 1, month: 12, year: 2021, verified: true },
{ count: 3, accumulatedCount: 4, month: 1, year: 2022, verified: true },
{ count: 3, accumulatedCount: 7, month: 2, year: 2022, verified: true },
{ count: 1, accumulatedCount: 8, month: 3, year: 2022, verified: true },
]

现在，我的聚合管道是这样的：

const results = await this.accountModel.aggregate([
{
$match: {
...match,
createdAt: {
// $gte: range.from,
$lte: range.to,
},
},
},
{ $unwind: '$organizations' },
{
$group: {
_id: {
month: { $month: '$createdAt' },
year: { $year: '$createdAt' },
verified: '$organizations.verified',
},
count: { $sum: 1 },
},
},
{
$project: {
_id: 0,
month: '$_id.month',
year: '$_id.year',
count: 1,
verified: '$_id.verified',
},
},
]);

它返回这个：

RESULTS  [
{ count: 1, month: 10, year: 2022, verified: true },
{ count: 4, month: 7, year: 2022, verified: true },
{ count: 3, month: 2, year: 2022, verified: true },
{ count: 1, month: 3, year: 2022, verified: true },
{ count: 1, month: 12, year: 2021, verified: true },
{ count: 2, month: 1, year: 2022, verified: true },
{ count: 1, month: 8, year: 2022, verified: true }
]

基本上，我需要reduce基于prev和当前值的计数。

我在MongoDB文档中找不到任何合适的选项。

我使用"；mongodb"："3.6.3"；。

Answer 1

这是一种方法：

db.collection.aggregate([
{
"$group": {
"_id": null,
"docs": {
"$push": "$$ROOT"
}
}
},
{
"$project": {
docs: {
"$reduce": {
"input": "$docs",
"initialValue": {
accumulatedCount: 0,
data: []
},
"in": {
accumulatedCount: {
"$sum": [
"$$this.count",
"$$value.accumulatedCount"
]
},
data: {
"$concatArrays": [
"$$value.data",
[
{
"$mergeObjects": [
"$$this",
{
accumulatedCount: {
"$sum": [
"$$this.count",
"$$value.accumulatedCount"
]
}
}
]
}
]
]
}
}
}
}
}
},
{
"$unwind": "$docs.data"
},
{
"$replaceRoot": {
"newRoot": "$docs.data"
}
}
])

将所有文档分组为一个数组。使用$reduce在数组上循环，在两个变量中保持计数的运行总和和使用accumulatedCount更新的文档列表。最后，展开列表并将文档放到根目录下。

游乐场链接。

您可以在管道中添加新阶段。