MongoDB用于构建条件求和的聚合

huangapple go评论81阅读模式
英文:

MongoDB Aggregation for building conditional sums

问题

让我们假设我在我的MongoDB中有一些用户文档。每个用户都有一个type(整数)和presence天数(布尔值)。

我的文档架构

{
  "type": NumberInt(), // 1|2|3
  "presence": {
    "day1": true,
    "day2": false,
    "day3": true
  }
}

我想要计算每种用户类型的出勤天数,以便可视化它们在线图中的出勤情况。所需的输出应如下所示。一个数组中包含三个表示类型的对象。每个对象有一系列数据。它们代表了在指定日期出勤的特定类型用户的总数。

所需结果

[{
  "name": "Type1",
  "series": [{
    "name": "Day1",
    "value": 5 // 计数(type==1 && d1==true)
  }, {
    "name": "Day2",
    "value": 8 // 计数(type==1 && d2==true)
  }, {
    "name": "Day3",
    ...
  }]
}, {
  "name": "Type2",
  "series": [{
    "name": "Day1",
    "value": 10 // 计数(type==2 && d1==true)
  }, {}, {}]
}, {
  "name": "Type3",
  "series": [{}, {}, {}]
}, {
  "name": "AllCombined",
  "series": [{}, {}, {}]
}]

问题

我如何使用MongoDB聚合来实现这个目标?我尝试过$match$sort$project$sum,但无法弄清楚。提前感谢您的帮助!

请求示例:用户集合

{ "_id": ..., "type": 1, "presence": { "d1": true, "d2": true, "d3": false } }
{ "_id": ..., "type": 2, "presence": { "d1": false, "d2": true, "d3": false } }
{ "_id": ..., "type": 3, "presence": { "d1": true, "d2": false, "d3": true } }
{ "_id": ..., "type": 1, "presence": { "d1": true, "d2": false, "d3": false } }
英文:

Let's pretend I have some user documents in my MongoDB. Every user has a type (int) and presence days (boolean).

my documents schema

{
  type: NumberInt() // 1|2|3
  presence: {
    day1: true,
    day2: false,
    day3: true
}

I want to count the precence days for each type of user to visualize their precence in a line chart. The needed output has to look like this. Three objects in an array representing the types. Every object has series. They represent the sum of users of a specific type present at the named day.

wanted result

[{
  name: "Type1"
  series: [{
    name: "Day1",
    value: 5, // count (type==1 && d1==true)
  }, {
    name: "Day2",
    value: 8, // count (type==1 && d2==true)
  }, {
    name: "Day3",
    ...
  }]
}, {
  name: "Type2"
  series: [{
    name: "Day1",
    value: 10, // count (type==2 && d1==true)
  }, {}, {}]
}, {
  name: "Type3"
  series: [{}, {}, {}]
}, {
  name: "AllCombined"
  series: [{}, {}, {}]
}]

Question

How can I achive that with mongodb aggregation? I played around with $match, $sort, $project, $sum but I couldn't figure it out. Thanks in advance MongoDB用于构建条件求和的聚合

requested samples: user collection

{_id: ..., type: 1, presence: {d1:true, d2: true, d3: false}}
{_id: ..., type: 2, presence: {d1:false, d2: true, d3: false}}
{_id: ..., type: 3, presence: {d1:true, d2: false, d3: true}}
{_id: ..., type: 1, presence: {d1:true, d2: false, d3: false}}

答案1

得分: 1

你可以使用以下的聚合操作:

db.collection.aggregate([
  { "$group": {
    "_id": "$type",
    "d1": { "$sum": { "$cond": [{ "$eq": ["$presence.d1", true] }, 1, 0] }},
    "d2": { "$sum": { "$cond": [{ "$eq": ["$presence.d2", true] }, 1, 0] }},
    "d3": { "$sum": { "$cond": [{ "$eq": ["$presence.d3", true] }, 1, 0] }}
  }},
  { "$project": {
    "_id": 0,
    "name": { "$concat": ["Type", { "$toString": "$_id" }] },
    "series": [
      {
        "name": "Day1",
        "value": "$d1"
      },
      {
        "name": "Day2",
        "value": "$d2"
      },
      {
        "name": "Day3",
        "value": "$d3"
      }
    ]
  }}
])
英文:

You can use below aggregation

db.collection.aggregate([
  { "$group": {
    "_id": "$type",
    "d1": { "$sum": { "$cond": [{ "$eq": ["$presence.d1", true] }, 1, 0] }},
    "d2": { "$sum": { "$cond": [{ "$eq": ["$presence.d2", true] }, 1, 0] }},
    "d3": { "$sum": { "$cond": [{ "$eq": ["$presence.d3", true] }, 1, 0] }}
  }},
  { "$project": {
    "_id": 0,
    "name": { "$concat": ["Type", { "$toString": "$_id" }] },
    "series": [
      {
        "name": "Day1",
        "value": "$d1"
      },
      {
        "name": "Day2",
        "value": "$d2"
      },
      {
        "name": "Day3",
        "value": "$d3"
      }
    ]
  }}
])

huangapple
  • 本文由 发表于 2020年1月3日 19:21:15
  • 转载请务必保留本文链接:https://go.coder-hub.com/59577704.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定