蒙哥和枢轴 [英] 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为每个事件创建一个计数器字段，并从文档中填充计数，如果事件匹配，则为零.然后，我们将根据用户ID $group，汇总所有事件计数器.

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/

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