时间戳

首页 > 解决方案 > MongoDB查询仅显示大于平均值的数据

mongodb - MongoDB查询仅显示大于平均值的数据

问题描述

演示 https://mongoplayground.net/p/ImiJWm1s-lx

db={
  BRANCH: [
    {
      "BCODE": "B1",
      "BNAME": "CANADA"
    },
    {
      "BCODE": "B2",
      "BNAME": "USA"
    },
    {
      "BCODE": "B3",
      "BNAME": "CANADA"
    }
  ],
  ACCOUNT: [
    {
      "ANO": 1,
      "Amount": 1000,
      "BCODE": "B1"
    },
    {
      "ANO": 2,
      "Amount": 350,
      "BCODE": "B1"
    },
    {
      "ANO": 3,
      "Amount": 450,
      "BCODE": "B1"
    },
    {
      "ANO": 4,
      "Amount": 360,
      "BCODE": "B2"
    },
    {
      "ANO": 5,
      "Amount": 800,
      "BCODE": "B2"
    },
    {
      "ANO": 6,
      "Amount": 450,
      "BCODE": "B3"
    },
    {
      "ANO": 7,
      "Amount": 360,
      "BCODE": "B2"
    },
    {
      "ANO": 8,
      "Amount": 800,
      "BCODE": "B1"
    }
  ]
}

我有这个 MongoDB 集合。我可以使用此查询在每个分支中显示没有帐户：

db.BRANCH.aggregate([
  {
    $lookup: {
      from: "ACCOUNT",
      localField: "BCODE",
      foreignField: "BCODE",
      as: "TOTAL_ACCOUNTS"
    }
  },
  {
    "$addFields": {
      "TOTAL ACCOUNTS": {
        $size: "$TOTAL_ACCOUNTS"
      }
    }
  },
  {
    "$project": {
      _id: 0,
      TOTAL_ACCOUNTS: 0
    }
  }
])

输出

[
  {
    "BCODE": "B1",
    "BNAME": "CANADA",
    "TOTAL ACCOUNTS": 4
  },
  {
    "BCODE": "B2",
    "BNAME": "USA",
    "TOTAL ACCOUNTS": 3
  },
  {
    "BCODE": "B3",
    "BNAME": "CANADA",
    "TOTAL ACCOUNTS": 1
  }
]

但我只需要显示那些账户数量超过所有分支机构平均账户数量的分支机构详细信息。

所以根据给定的数据，所有分支机构的 AVG Accounts 为 (4+3+1)/3 = 2

这意味着只应显示Branch B1 和Branch B2，因为它们分别有4 个帐户和3 个帐户。

不应显示分行 B3，因为它只有 1 个帐户和 1<2（平均帐户数）。

我应该在这里使用什么？

标签： mongodbaggregation-framework

解决方案

  • $group通过 null 并构造数组branches并获取帐户总大小的平均值
  • $filter迭代数组循环branches并检查总计应大于帐户总大小的平均值的条件
db.BRANCH.aggregate([
  {
    $lookup: {
      from: "ACCOUNT",
      localField: "BCODE",
      foreignField: "BCODE",
      as: "TOTAL_ACCOUNTS"
    }
  },
  { $addFields: { TOTAL_ACCOUNTS: { $size: "$TOTAL_ACCOUNTS" } } },
  {
    $group: {
      _id: null,
      branches: { $push: "$$ROOT" },
      totalAvg: { $avg: "$TOTAL_ACCOUNTS" }
    }
  },
  {
    $project: {
      _id: 0,
      branches: {
        $filter: {
          input: "$branches",
          cond: { $gt: ["$$this.TOTAL_ACCOUNTS", "$totalAvg"] }
        }
      }
    }
  }
])

操场

推荐阅读