在 MongoDB Aggregate Framework 中将列合并为一个 [英] Combining columns into one in MongoDB Aggregate Framework

问题描述

是否可以跨多列按值分组?

Is it possible to group by values across multiple columns?

假设我每天存储人们之间的互动，并使用如下计数来跟踪 from's 和 to's.

Let's say I'm storing interactions between people by day, and keep track of from's and to's with a count as follows.

db.collection = 
[
    { from : 'bob',   to : 'mary',   day : 1,  count : 2 },
    { from : 'bob',   to : 'steve',  day : 2,  count : 1 },
    { from : 'mary',  to : 'bob',    day : 1,  count : 3 },
    { from : 'mary',  to : 'steve',  day : 3,  count : 1 },
    { from : 'steve', to : 'bob',    day : 2,  count : 2 },
    { from : 'steve', to : 'mary',   day : 1,  count : 1 }
]

这让我可以通过对 from: 进行分组，并求和 count:.

This allows me to get all interactions for, lets say, 'bob' with any one by grouping on from:, and summing count:.

现在我想获取用户的所有交互，所以基本上按 from: 和 to: 中的值分组.本质上，为每个名称总结 count:，无论它是在 from: 还是 to:

Now I want to get all interaction for a user, so basically group by values across from: and to:. Essentially, sum up count: for each name, regardless whether it was in from: or to:

[更新]

期望的输出是:

[
    { name : 'bob',   count : 8 },
    { name : 'mary',  count : 7 },
    { name : 'steve', count : 3 }
]

最简单的方法是创建一个新列 names: 并将 from: 和 to: 存储在里面，然后 $unwind，但这似乎很浪费.

The easiest would be to create a new column names: and store from: and to: inside, then $unwind, but that seems wasteful.

有什么提示吗?

谢谢

推荐答案

是否可以跨多列按值分组?

Is it possible to group by values across multiple columns?

是的，在 MongoDB 中可以跨不同列对值进行分组.

Yes, it's possible in MongoDB to group values across different columns.

通过 MapReduce 非常简单.但是也可以使用聚合框架来做到这一点，即使您不存储参与者数组(如果您有两个参与者的名称数组，那么它只是一个 $unwind 和一个 $group - 很简单，我认为比 MapReduce 或您必须与当前模式一起使用的管道更优雅).

It's very straight forward to do it via MapReduce. But it's also possible to do it with aggregation framework, even if you don't store an array of participants (if you had array of names with both participants, then it's just an $unwind, and a $group - quite simple and I think more elegant than either MapReduce or the pipeline you'd have to use with the current schema).

按原样与您的架构一起工作的管道:

Pipeline that works with your schema as is:

db.collection.aggregate( [
{
    "$group" : {
        "_id" : "$from",
        "sum" : {
            "$sum" : "$count"
        },
        "tos" : {
            "$push" : {
                "to" : "$to",
                "count" : "$count"
            }
        }
    }
}
{ "$unwind" : "$tos" }
{
    "$project" : {
        "prev" : {
            "id" : "$_id",
            "sum" : "$sum"
        },
        "tos" : 1
    }
}
{
    "$group" : {
        "_id" : "$tos.to",
        "count" : {
            "$sum" : "$tos.count"
        },
        "prev" : {
            "$addToSet" : "$prev"
        }
    }
}
{ "$unwind" : "$prev" }
{
    "$group" : {
        "_id" : "1",
        "t" : {
            "$addToSet" : {
                "id" : "$_id",
                "c" : "$count"
            }
        },
        "f" : {
            "$addToSet" : {
                "id" : "$prev.id",
                "c" : "$prev.sum"
            }
        }
    }
}
{ "$unwind" : "$t" }
{ "$unwind" : "$f" }
{
    "$project" : {
        "name" : {
            "$cond" : [
                {
                    "$eq" : [
                        "$t.id",
                        "$f.id"
                    ]
                },
                "$t.id",
                "nobody"
            ]
        },
        "count" : {
            "$add" : [
                "$t.c",
                "$f.c"
            ]
        },
        "_id" : 0
    }
}
{ "$match" : { "name" : { "$ne" : "nobody" } } }
]);

在您的示例输入中，输出为:

On your sample input the output is:

{
    "result" : [
        {
            "name" : "bob",
            "count" : 8
        },
        {
            "name" : "mary",
            "count" : 7
        },
        {
            "name" : "steve",
            "count" : 5
        }
    ],
    "ok" : 1
}

这篇关于在 MongoDB Aggregate Framework 中将列合并为一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！