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

MongoDB-查询计算和分组多个项目

mongodb-query mongodb

Windy · 技术社区 · 3 年前

假设我有以下数据:

{"Plane":"5546","Time":"55.0", City:"LA"}
{"Plane":"5548","Time":"25.0", City:"CA"}
{"Plane":"5546","Time":"6.0", City:"LA"}
{"Plane":"5548","Time":"5.0", City:"CA"}
{"Plane":"5555","Time":"15.0", City:"XA"}
{"Plane":"5555","Time":"8.0", City:"XA"}

还有更多,但我只是将数据可视化我想计算和分组所有时间和平面,这是预期输出:

{"_id:":["5546","LA"],"Sum":2,"LateRate":1,"Prob"0.5}

总和始终是总和,延迟始终是总和,随时间而变>“15”和Prob延迟/总计我尝试过的代码仍然缺少一些东西:

db.Collection.aggregate([
    {
        $project: {
            Sum: 1,
            Late: {
                $cond: [{ $gt: ["$Time", 15.0] }, 1, 0]
            },
            prob:1
        }
    },
    {
        $group:{
            _id:{Plane:"$Plane", City:"$City"},
            Sum: {$sum:1},
            Late: {$sum: "$Late"}
        }
    },
    {
    $addFields: {
      prob: {
        "$divide": [
          "$Late",
          "$Sum"
        ]
      }
    }
  },
])

1 回复 | 直到 3 年前

Gibbs 3 年前

db.collection.aggregate([
  {
    $project: {
      Time: 1,
      Late: {
        $cond: [
          {
            $gt: [
              {
                $toDouble: "$Time"
              },
              15.0
            ]
          },
          "$Time",
          0
        ]
      },
      prob: 1,
      Plane: 1,
      City: 1
    }
  },
  {
    $group: {
      _id: {
        Plane: "$Plane",
        City: "$City"
      },
      Sum: {
        $sum: {
          "$toDouble": "$Time"
        }
      },
      Late: {
        $sum: {
          $toDouble: "$Late"
        }
      }
    }
  },
  {
    $addFields: {
      prob: {
        "$divide": [
          "$Late",
          "$Sum"
        ]
      }
    }
  }
])