我如何使用Node.js和Mongoose计算正价总额，负价总额和总和 [英] How can i calculate total positive ,total negative price and sum using Node.js and Mongoose

问题描述

我已编写查询以获取所有用户记录

i have written query for getting all user records

exports.index = function(req, res) {
    Userdata.find(function(err, userdatas) {
        if (err) {
            return handleError(res, err);
        }

        console.log(userdatas);

    });

};

控制台，我正在查看以下文件

console i am getting below documents

{
    "_id" : ObjectId("584bc9ba420a6b189c510af6"),
    "user_id" : 1,
    "price" : 2000.0,
    "type" : "credit",

},
{
    "_id" : ObjectId("584bc9ba420a6b189c510af7"),
    "user_id" : 1,
    "price" : -1000.0,
    "type" : "credit",

},
{
    "_id" : ObjectId("584bc9ba420a6b189c510af8"),
    "user_id" : 2,
    "price" : 1000.0,
    "type" : "credit",

}

现在我要计算总正价，总负价和总和(正总负数)

now i want to calculate total positive ,total negative price and sum(total positive-total negative)

计算后，我需要在摘要集合中插入/更新

once this calculated i need to insert/update in summary collection

如果那时用户ID已显示在摘要集合中，那么我们需要更新特定的用户文档

if user id already presented in summary collection that time we need to update the particular user document

如果不存在用户ID，我们需要为该用户创建新文档

if user id not present we need to create the new document for that user

这里汇总集合已经存在user_id = 1，因此我们需要更新此文档，而汇总集合中不存在user_id = 2，因此我们需要创建.

here summary collection already user_id = 1 is present so we need to update this document and user_id = 2 not present in summary collection so we need to create .

    {
        "_id" : ObjectId("584bc9ba420a6b189c510af9"),
        "user_id": 1,
        "Totalpositiveprice": 3000.0,
        "Totalnegativeprice": 0,
        "Balanceprice": 3000.0
    },
{
        "_id" : ObjectId("584bc9ba420a6b189c510af9"),
        "user_id": 3,
        "Totalpositiveprice": 200.0,
        "Totalnegativeprice": -190,
        "Balanceprice": 10.0
    }

我的期望结果:

    {
        "_id" : ObjectId("584bc9ba420a6b189c510af9"),
        "user_id": "1",
        "Totalpositiveprice": 5000.0,
        "Totalnegativeprice": -1000.0,
        "Balanceprice": 4000.0
    },
    {

        "user_id": "2",
        "Totalpositiveprice": 1000.0,
        "Totalnegativeprice": 0,
        "Balanceprice": 1000.0
    },
   {
        "_id" : ObjectId("584bc9ba420a6b189c510af9"),
        "user_id": 3,
        "Totalpositiveprice": 200.0,
        "Totalnegativeprice": -190,
        "Balanceprice": 10.0
    }

计算后，我需要在摘要集合中插入/更新

once this calculated i need to insert/update in summary collection

推荐答案

考虑到预期结果是数字，而summary集合中的值是数字(否则需要将其设置为数字)，这里是计算结果的汇总:

Considering that expected results are numbers and the values from summary collection are numbers (otherwise you need to make them numbers) here is an aggregation that computes the result:

db.getCollection('userpricing').aggregate([
    {$group: {
        _id:"$user_id", 
        user_id: {$first: "$user_id"}, 
        Totalpositiveprice:{$sum:{$cond:[{ '$gt': ['$price', 0]}, "$price", 0]}}, 
        Totalnegativeprice:{$sum:{$cond:[{ '$lt': ['$price', 0]}, "$price", 0]}},
        Balanceprice:{"$sum":"$price"}}
     },
    {
      $lookup:
        {
          from: "summary",
          localField: "user_id",
          foreignField: "user_id",
          as: "user_id2"
        }
   },
   {$project: {
    _id:0, 
    user_id:1, 
    Totalpositiveprice: {$sum: ["$Totalpositiveprice", {$sum: "$user_id2.Totalpositiveprice"}] },
    Totalnegativeprice: {$sum: ["$Totalnegativeprice", {$sum: "$user_id2.Totalnegativeprice"}] },
    Balanceprice: {$sum: ["$Balanceprice", {$sum: "$user_id2.Balanceprice"}] },
}},

     {$out: "summary"}
]).pretty()

结果:

db.summary.find().pretty()

{
    "_id" : ObjectId("584fde2906c7385883be0d15"),
    "user_id" : 2,
    "Totalpositiveprice" : 10000,
    "Totalnegativeprice" : 0,
    "Balanceprice" : 10000
}
{
    "_id" : ObjectId("584fde2906c7385883be0d16"),
    "user_id" : 1,
    "Totalpositiveprice" : 23000,
    "Totalnegativeprice" : -10000,
    "Balanceprice" : 13000
}

稍后，如果需要，您需要将它们转换为字符串.

Later you need to convert them to string if you need.

注意: 其结果将使用新的计算(和更新)值覆盖摘要集合. 最好先测试您的结果.

Note: The result it overrides the summary collection with new computed (and updated) values. Better test your resuls first.

这篇关于我如何使用Node.js和Mongoose计算正价总额，负价总额和总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！