您可以使用以下聚合:
db.ccpsample.aggregate([
{ $sort: { ccp_id: 1, period: 1 } },
{
$group: {
_id: "$ccp_id",
items: { $push: "$$ROOT" },
baseSale: { $first: "$sales" },
growths: { $push: "$growth" }
}
},
{
$unwind: {
path: "$items",
includeArrayIndex: "index"
}
},
{
$project: {
cpp_id: "$items.cpp_id",
period: "$items.period",
growth: "$items.growth",
sales: {
$cond: {
if: { $ne: [ "$items.sales", "NULL" ] },
then: "$items.sales",
else: {
$reduce: {
input: { $slice: [ "$growths", "$index" ] },
initialValue: "$baseSale",
in: { $multiply: [ "$$value", { $add: [1, { $divide: [ "$$this", 100 ] }] } ] }
}
}
}
}
}
}
])
基本上是为了计算值n-th
您必须了解以下内容:
- 第一个元素的销售价值 (
$first
in $group
)
- 全部的数组
growths
($push
in $group
)
- the
n
这表明你必须执行多少次乘法
要计算该指数,您应该$push
将所有元素放入一个数组中,然后使用$unwind https://docs.mongodb.com/manual/reference/operator/aggregation/unwind/index.html with includeArrayIndex
选项将展开数组的索引插入到字段中index
.
最后一步计算累积乘法。它用$slice https://docs.mongodb.com/manual/reference/operator/aggregation/slice/index.html with index
字段来评估多少growths
应进行处理。所以会有一个元素601
, 两个元素602
等等。
那么是时候了$reduce https://docs.mongodb.com/manual/reference/operator/aggregation/reduce/index.html处理该数组并根据您的公式执行乘法:(1 + (growth/100)
)