如何计算mongodb聚合中数组内的统计信息？

ahy6op9u 于 2022-12-29 发布在 Go

关注(0)|答案(1)|浏览(121)

我正在Mongodb上构建一个简单的调查应用程序，最难的部分是使用Mongodb的聚合框架获得统计数据。
下面是一个提交调查答案的例子。answers属性是一个包含每个问题答案的数组。

{
   uid:'xxxx',
   surveyId:'xxxxx',
   answers：[
     {   answer: A },    // question 1 ‘s answer
     {   answer: B },    // question 2 ‘s answer 
     {   answer: C }      // question 3 ‘s answer 
   ]
}

我想得到的最后结果是了解调查的总体统计数字。

Question 1:
   A  50%  B 40% C 10%
   Question 2:
   A  60%  B 40% C 0%

mongodb聚合的棘手之处在于如何处理“数组”

mongodb

来源：https://stackoverflow.com/questions/74938608/how-to-calculate-the-statistic-inside-an-array-in-mongodb-aggregation

1条答案

按热度按时间

bzzcjhmw1#

一个简单的选项是$unwind和$group：

db.collection.aggregate([
  {$unwind: {path: "$answers", includeArrayIndex: "index"}},
  {$group: {
      _id: {
        surveyId: "$surveyId",
        index: "$index"
      },
      A: {$sum: {$cond: [{$eq: ["$answers.answer", "A"]}, 1, 0]}},
      B: {$sum: {$cond: [{$eq: ["$answers.answer", "B"]}, 1, 0]}},
      C: {$sum: {$cond: [{$eq: ["$answers.answer", "C"]}, 1, 0]}},
      all: {$sum: 1}
  }},
  {$project: {
      A: {$multiply: [{$divide: ["$A", "$all"]}, 100]},
      B: {$multiply: [{$divide: ["$B", "$all"]}, 100]},
      C: {$multiply: [{$divide: ["$C", "$all"]}, 100]},
      question: {$add: ["$_id.index", 1]},
      surveyId: "$_id.surveyId",
      _id: 0
  }}
])

了解它在playground example上的工作原理
或者，更通用的方法可以使用$group两次，而不知道每个问题的答案选项：

db.collection.aggregate([
  {$unwind: {path: "$answers", includeArrayIndex: "index"}},
  {$group: {
      _id: {
        surveyId: "$surveyId",
        index: "$index",
        res: "$answers.answer"
      },
      count: {$sum: 1}
  }},
  {$group: {
      _id: {
        surveyId: "$_id.surveyId",
        index: "$_id.index"
      },
      data: {$push: {answer: "$_id.res", count: "$count"}},
      all: {$sum: "$count"}
  }},
  {$project: {
      data: {
        $map: {
          input: "$data",
          in: {
            answer: "$$this.answer",
            percent: {$multiply: [{$divide: ["$$this.count", "$all"]}, 100]}
          }
        }
      },
      question: {$add: ["$_id.index", 1]},
      surveyId: "$_id.surveyId",
      _id: 0
  }}
])

了解它在playground example上的工作原理

赞(0）回复(0）举报 2022-12-29

我来回答

如何计算mongodb聚合中数组内的统计信息？

1条答案

相关问题

热门标签

最新问答