聚合方法中两列的总和 [英] summation of two columns in Aggregate Method

问题描述

我正在使用 mongodb 聚合查询.我的数据库是这样的:

I am using mongodb Aggregate query. My db is like this:

{
  "_id" : ObjectId("5a81636f017e441d609283cc"),
  "userid": "123",
  page : 'A',
  newpage: 'A',
},
{
  "_id" : ObjectId("5a81636f017e441d609283cd"),
  "userid": "123",
  page : 'B',
  newpage: 'A',
},
{
  "_id" : ObjectId("5a81636f017e441d609283ce"),
  "userid": "123",
  page : 'D',
  newpage: 'D',
}

我想获得所有页面和新页面值的总和.我能够得到一个可以给出非常精确结果的列值.

I want to get the Sum of all page and new page value. I am able to get one column value which can give the very precise result.

但我被这两列卡住了.我为获取一列的值的总和/重复所做的是:

But I am stuck with the two columns. What I did for getting the sum/repetition of values for one column is:

db.Collection.aggregate([
                    {$match:{ "userid":"123"}},
                    {$unwind:"$newpage"},
                    {$group:{"_id":"$newpage", "count":{"$sum":1}}}, 
                    {$project: {_id:0, pagename :"$_id", count:{ $multiply: [ "$count", 1 ] }}},
                    {$sort: {count: -1}},
                    //{$limit: 10}              
               ], function(error, data){
                   if (error) {
                       console.log(error);
                   } else {
                    console.log(data);
        }
    });

期望的结果如下:

    {
        "pagename": "A",
        "count": 3
    },
    {
        "pagename": "D",
        "count": 2
    },
    {
        "pagename": "B",
        "count": 1
    }

有没有人有办法为两列获取这些东西?任何帮助表示赞赏

Is anyone has any approach to getting these things for Two Column? Any Help is appreciated

推荐答案

使用 $facet 管道阶段，用于在同一输入文档集的单个阶段内处理多个聚合管道.在您的情况下，您需要分别汇总计数，然后将两个结果合并并计算最终汇总.

Use $facet pipeline stage to process multiple aggregation pipelines within a single stage on the same set of input documents. In your case you need to aggregate the counts separately then join the two results and calculate the final aggregates.

这可以通过运行以下管道来证明:

This can be demonstrated by running the following pipeline:

db.collection.aggregate([
    { "$match": { "userid": "123" } },
    {
        "$facet": {
            "groupByPage": [
                { "$unwind": "$page" },
                { 
                    "$group": {
                        "_id": "$page",
                        "count": { "$sum": 1 }
                    }
                }
            ],   
            "groupByNewPage": [
                { "$unwind": "$newpage" },
                { 
                    "$group": {
                        "_id": "$newpage",
                        "count": { "$sum": 1 }
                    }
                }
            ]
        }
    },
    { 
        "$project": {
            "pages": {
                "$concatArrays": ["$groupByPage", "$groupByNewPage"]
            }
        }
    },
    { "$unwind": "$pages" },
    { 
        "$group": {
            "_id": "$pages._id",
            "count": { "$sum": "$pages.count" }
        }
    },
    { "$sort": { "count": -1 } }
], function(error, data){
    if (error) {
        console.log(error);
    } else {
        console.log(data);
    }
)

这篇关于聚合方法中两列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！