Mongo 和 Pivot [英] Mongo and Pivot

问题描述

在这个问题上我需要 mongo 的帮助:我有收集统计​​信息(UserId、EventId、Count、Date)收集的是数据

I need help with mongo in this problem: I have collection stats (UserId, EventId, Count, Date) in collection are data

1     |     1    |    10  |   01.01.2012
1     |     1    |   15   | 01.02.2012
1     |     2    |   12   | 01.01.2012
2     |     1    |    5   |   01.01.2012
3     |     2    |    10  |   01.01.2012

我需要这个结果

  1     |     25          |      12         
  2     |     5           |      0
  3     |     0           |      10

在没有 Map Reduce 的情况下在 Mongo 中是否可行?谢谢你的帮助.

Its possibile in Mongo without Map Reduce? Thank You for your helps.

推荐答案

使用 aggregate() 完成这项工作更容易，也更快！

It is easier, and much faster, to do the job with an aggregate()!

我们将使用 $project 为每个事件创建一个计数器字段，如果事件匹配，则填写文档中的计数，否则为零.然后我们将 $group by user-id，汇总所有事件计数器.

We will use a $project to create a counter field for each event, filling in the count from the document, if the event matches, zero otherwise. Then we will $group by user-id, summing up all the event counters.

为了解释起见，让我首先展示一下在您的示例中，这对于两个不同的事件(1 和 2)是如何硬编码的:

For the sake of explanation, let me first show how this looks like hard-coded for the two different events (1 and 2) in your example:

db.xx.aggregate([
    { $project: { userid:1,
                  cnt_e1: { $cond: [ { $eq: [ "$event", 1 ] }, "$count", 0 ] },
                  cnt_e2: { $cond: [ { $eq: [ "$event", 2 ] }, "$count", 0 ] },
    } },
    { $group: { _id: "$userid", cnt_e1: { $sum: "$cnt_e1" }, cnt_e2: { $sum: "$cnt_e2" } } },  
    { $sort: { _id: 1 } },
])

对于给定的集合:

> db.xx.find({},{_id:0})
{ "userid" : 1, "event" : 1, "count" : 10 }
{ "userid" : 1, "event" : 1, "count" : 15 }
{ "userid" : 1, "event" : 2, "count" : 12 }
{ "userid" : 2, "event" : 1, "count" : 5 }
{ "userid" : 3, "event" : 2, "count" : 10 }

结果是:

{
    "result" : [
        {
            "_id" : 1,
            "cnt_e1" : 25,
            "cnt_e2" : 12
        },
        {
            "_id" : 2,
            "cnt_e1" : 5,
            "cnt_e2" : 0
        },
        {
            "_id" : 3,
            "cnt_e1" : 0,
            "cnt_e2" : 10
        }
    ],
    "ok" : 1
}

要为可变事件完成这项工作，我们必须生成投影和分组.我们将使用 distinct() 命令获取所有可能事件的数组(您可能希望在事件"上定义索引).然后我们通过遍历数组将这两个语句创建为 JSON 对象:

To get this done for variable events, we'll have to generate the projection and the grouping. We'll get an array of all possible events using the distinct() command (you might want to define an index on "event"). Then we create the two statements as JSON objects by looping over the array:

project = {};
project.$project = {};
project.$project.userid = 1;

group = {};
group.$group = {};
group.$group._id = '$userid'

events = db.xx.distinct( "event" );
events.forEach( function( e ) {
    field = "cnt_e" + e;

    eval("project.$project." + field + " = {}");
    eval("project.$project." + field + ".$cond = []");
    eval("project.$project." + field + ".$cond[0] = {}");
    eval("project.$project." + field + ".$cond[0].$eq = []");
    eval("project.$project." + field + ".$cond[0].$eq[0] = '$event'");
    eval("project.$project." + field + ".$cond[0].$eq[1] = " + e );
    eval("project.$project." + field + ".$cond[1] = '$count'");
    eval("project.$project." + field + ".$cond[2] = 0");

    eval("group.$group." + field + " = {}");
    eval("group.$group." + field + ".$sum = '$" + field + "'");
});

//printjson(project);
//printjson(group);

db.xx.aggregate([
    project,
    group,
    { $sort: { _id: 1 } },
])

结果和上面一样.

注意:以上适用于数字事件.如果它们是字符串，则必须调整生成器.

Note: the above works for numerical events. If they were strings, you'd have to adapt the generator.

乍一看，这可能看起来比 @Philipp 的 mapReduce 更复杂.但这不会返回每个用户的所有事件 - 只有那些确实有计数的事件.对于完整的垂直到水平映射，您还必须生成映射和 reduce 函数.

At first sight, this might look more complicated than @Philipp 's mapReduce. But that will not return all events for each user - only the ones that do have a count. For a complete vertical to horizontal mapping you would have to generate the map and the reduce functions as well.

有关 aggregate() 的更多信息，请参阅 http://docs.mongodb.org/manual/聚合/

For more information on aggregate(), see http://docs.mongodb.org/manual/aggregation/

这篇关于Mongo 和 Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！