时间戳

首页 > 解决方案 > 如何使用 MongoDB 汇总余额和收入？

mongodb - 如何使用 MongoDB 汇总余额和收入？

问题描述

我有一个exchange_history像这样的文件：

[
  {
    "_id": {"$oid": "611fd7059ec255088af94e4d"},
    "amount": {"$numberDecimal": 1.2},
    "exchange_time": {"$date": "2021-08-20T16:02:00.448Z"},
    "user_id": "60ef435299b1760752cbafe1"
  },
  {
    "_id": {"$oid": "611fed2a31ef85394c4f9b10"},
    "amount": {"$numberDecimal": 1.02},
    "exchange_time": {"$date": "2021-08-20T16:02:00.448Z"},
    "user_id": "60ef435299b1760752cbafe1"
  },
  {
    "_id": {"$oid": "6120b47d4cf35f7f70312a71"},
    "amount": {"$numberDecimal": -1},
    "exchange_time": {"$date": "2021-08-21T08:08:16.397Z"},
    "user_id": "60ef435299b1760752cbafe1"
  },
  {
    "_id": {"$oid": "6120b4834cf35f7f70312a72"},
    "amount": {"$numberDecimal": -2},
    "exchange_time": {"$date": "2021-08-21T08:08:16.397Z"},
    "user_id": "60ef435299b1760752cbafe1"
  },
  {
    "_id": {"$oid": "6120b4e94cf35f7f70312a73"},
    "amount": {"$numberDecimal": 10},
    "exchange_time": {"$date": "2021-08-21T08:08:16.397Z"},
    "user_id": "611df4d9ece358586d212d91"
  }
]

如何使用MongoDB同时计算余额（sum amount）和收入（sum大于0） ？amountamountuser_id

我的 MongoDB 版本是 4.4。请帮助我！

标签： mongodb

解决方案

试试这个 ...

文档 ：

[
  {
    "_id": {
      "oid": "611fd7059ec255088af94e4d"
    },
    "amount": {
      "numberDecimal": 1.2
    },
    "exchange_time": {
      "date": "2021-08-20T16:02:00.448Z"
    },
    "user_id": "60ef435299b1760752cbafe1"
  },
  {
    "_id": {
      "oid": "611fed2a31ef85394c4f9b10"
    },
    "amount": {
      "numberDecimal": 1.02
    },
    "exchange_time": {
      "date": "2021-08-20T16:02:00.448Z"
    },
    "user_id": "60ef435299b1760752cbafe1"
  },
  {
    "_id": {
      "oid": "6120b47d4cf35f7f70312a71"
    },
    "amount": {
      "numberDecimal": -1
    },
    "exchange_time": {
      "date": "2021-08-21T08:08:16.397Z"
    },
    "user_id": "60ef435299b1760752cbafe1"
  },
  {
    "_id": {
      "oid": "6120b4834cf35f7f70312a72"
    },
    "amount": {
      "numberDecimal": -2
    },
    "exchange_time": {
      "date": "2021-08-21T08:08:16.397Z"
    },
    "user_id": "60ef435299b1760752cbafe1"
  },
  {
    "_id": {
      "oid": "6120b4e94cf35f7f70312a73"
    },
    "amount": {
      "numberDecimal": 10
    },
    "exchange_time": {
      "date": "2021-08-21T08:08:16.397Z"
    },
    "user_id": "611df4d9ece358586d212d91"
  }
]

代码 ：

db.collection.aggregate([
  {
    $group: {
      _id: "$user_id",
      balance: {
        $sum: "$amount.numberDecimal"
      },
      income: {
        $sum: {
          $cond: [
            {
              $gt: [
                "$amount.numberDecimal",
                0
              ]
            },
            "$amount.numberDecimal",
            0
          ]
        }
      }
    }
  },
  
])

和输出：

[
  {
    "_id": "611df4d9ece358586d212d91",
    "balance": 10,
    "income": 10
  },
  {
    "_id": "60ef435299b1760752cbafe1",
    "balance": -0.78,
    "income": 2.2199999999999998
  }
]

试试mongoplayground

推荐阅读