Mongodb计算查询-累积乘法 [英] Mongodb calculation query--cummulative multiplication

问题描述

我最近开始在Mongodb从事POC工作.我下面有一个json集合

I recently started working in Mongodb for POC. I have one json collection below

db.ccpsample.insertMany([
  {
    "ccp_id":1,
    "period":601,
    "sales":100.00
  },
  {
    "ccp_id":1,
    "period":602,
    "growth":2.0,
    "sales":"NULL"    ##sales=100.00*(1+(2.0/100)) -- 100.00 comes from(ccp_id:1 and period=601) 
  },
  {
    "ccp_id":1,
    "period":603,
    "growth":3.0,
    "sales":"NULL"   ##sales=100.00*(1+(2.0/100))**(1+(3.0/100))-- 100.00 comes from(ccp_id:1 and period=601) 2.0 comes from (ccp_id:2 and period=602)  
  },
  {
    "ccp_id":2,
    "period":601,
    "sales":200.00
  },
  {
    "ccp_id":2,
    "period":602,
    "growth":2.0,
    "sales":"NULL"   ##sales=200.00*(1+(2.0/100))
  },
  {
    "ccp_id":2,
    "period":603,
    "growth":3.0,
    "sales":"NULL"   ##same like above
  }
])

并且我需要通过使用ccp_id匹配条件应该相同且期间字段应等于601的上述文档来计算具有NULL的销售字段.我在上面的集合中添加了一行来演示销售字段的计算.我尝试了$ graphlookup，但没有运气.大家可以帮忙还是建议一些方法?

And i need to calculate sales field which has NULL by using above documents with matching conditions of ccp_id should same and period field should be equal to 601. I have added a line to demonstrate calculation of sales field in collection itself above. I tried with $graphlookup but no luck. Can you people kindly help or suggest some way?

推荐答案

您可以使用以下聚合:

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元素的值，您必须了解以下内容:

Basically to calculate the value for n-th element you have to know following things:

• 第一个元素的销售额($group中的$first)
• 所有growths($group中的$push)的数组
• n，它指示您必须执行多少次乘法

• sales value of first element ($first in $group)
• the array of all growths ($push in $group)
• the n which indicates how many multiplications you have to perform

要计算索引，您应该将所有元素$push合并为一个数组，然后使用 $unwind ，带有includeArrayIndex选项，它将展开的数组的索引插入到字段index.

To calculate the index you should $push all elements into one array and then use $unwind with includeArrayIndex option which will insert the index of unwinded array to field index.

最后一步计算累积乘法.它使用 $ slice 和index字段进行评估应该处理多少个growths.因此，对于601，将有一个元素，对于602，将有两个元素，依此类推.

Last step calculates the cumulative multiplication. It uses $slice with index field to evaluate how many growths should be processed. So there will be one element for 601, two elements for 602 and so on.

然后是时候 $ reduce 进行处理了该数组并根据您的公式执行乘法:(1 + (growth/100))

Then it's time for $reduce to process that array and perform the multiplications based on your formula: (1 + (growth/100))

这篇关于Mongodb计算查询-累积乘法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！