代码之家 › 专栏 › 技术社区 › OliverKhl

条件中的表达式

aggregate mongoose mongodb node.js

0

OliverKhl · 技术社区 · 1 年前

这是一个后续问题:

我只想为“购买”类型的文档聚合集合的平均值。所以问题是如何获得sum表达式周围的if条件,对于totalBuyAunt也是如此。检查代码中的注释:)

以下是示例文档:

[
  {
    _id: ObjectId("653c4c017800342a3bedb82e"),
    type: 'buy',
    item: 555,
    amount: 1,
    priceEach: 100000
  },
  {
    _id: ObjectId("653c4c017800342a3bedb830"),
    type: 'buy',
    item: 384,
    amount: 2,
    priceEach: 70000
  },
  {
    _id: ObjectId("653c4c017800342a3bedb831"),
    type: 'buy',
    item: 384,
    amount: 1,
    priceEach: 50000
  },
  {
    _id: ObjectId("653c4c017800342a3bedb831"),
    type: 'sell',
    item: 384,
    amount: -1,
    priceEach: 50000
  }
]

只有当类型=='buy'时,我才需要totalBuyPrice

db.collection.aggregate([
  {
    $group: {
      _id: "$item",
      totalBuyPrice: {
        $sum: { // Only if type === 'buy'
          $multiply: [
            "$priceEach",
            "$amount"
          ]
        }
      },
      totalBuyAmount: // $sum $amount if type === 'buy'
      totalAmount: {
        $sum: "$amount"
      }
    }
  },
  {
    $project: {
      _id: 1,
      avg: {
        $divide: [
          "totalBuyPrice",
          "totalBuyAmount"
        ]
      },
      amount: "$totalAmount"
    }
  }
])

1 回复 | 直到 1 年前

1

0

Yong Shun 1 年前

您可以添加 $cond 操作员根据条件执行计算。

db.collection.aggregate([
  {
    $group: {
      _id: "$item",
      totalBuyPrice: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$type",
                "buy"
              ]
            },
            {
              $multiply: [
                "$priceEach",
                "$amount"
              ]
            },
            0
          ]
        }
      },
      totalBuyAmount: {
        $sum: {
          $cond: [
            {
              $eq: [
                "$type",
                "buy"
              ]
            },
            "$amount",
            0
          ]
        }
      },
      totalAmount: {
        $sum: "$amount"
      }
    }
  },
  {
    $project: {
      _id: 1,
      avg: {
        $divide: [
          "$totalBuyPrice",
          "$totalBuyAmount"
        ]
      },
      amount: "$totalAmount"
    }
  }
])

Demo @ Mongo Playground