在聚合期间计数事件并插入字符串文字 [英] Count events and insert string literal during aggregation

问题描述

我收集了大量代表某种事件的文档.集合包含不同 userId 的事件.

I have large collection of documents which represent some kind of events. Collection contains events for different userId.

{
    "_id" : ObjectId("57fd7d00e4b011cafdb90d22"),
    "userId" : "123123123",
    "userType" : "mobile",
    "event_type" : "clicked_ok",
    "country" : "US",
    "timestamp" : ISODate("2016-10-12T00:00:00.308Z")
}
{
    "_id" : ObjectId("57fd7d00e4b011cafdb90d22"),
    "userId" : "123123123",
    "userType" : "mobile",
    "event_type" : "clicked_cancel",
    "country" : "US",
    "timestamp" : ISODate("2016-10-12T00:00:00.308Z")
}

在午夜，我需要对前一天的所有文档进行聚合.文档需要以这种方式聚合，以便我可以获得特定 userId 的不同事件的数量.

At midnight I need to run aggregation for all documents for the previous day. Documents need to aggregated in the way so I could get number of different events for particular userId.

{
    "userId" : "123123123",
    "userType" : "mobile",
    "country" : "US",
    "clicked_ok" : 23,
    "send_message" : 14,
    "clicked_cancel" : 100,
    "date" : "2016-11-24",
}

在聚合期间我需要执行两件事:

During aggregation I need to perform two things:

1. 计算特定 userId 的事件数
2. 添加带有日期的日期"文本字段

非常感谢任何帮助！:)

Any help is greatly appreciated! :)

推荐答案

你可以像这样使用聚合来做到这一点:

you can do this with aggregation like this :

db.user.aggregate([
   {
      $match:{
         $and:[
            {
               timestamp:{
                  $gte: ISODate("2016-10-12T00:00:00.000Z")
               }
            },
            {
               timestamp:{
                  $lt: ISODate("2016-10-13T00:00:00.000Z")
               }
            }
         ]
      }
   },
   {
      $group:{
         _id:"$userId",
         timestamp:{
            $first:"$timestamp"
         },
         send_message:{
            $sum:{
               $cond:[
                  {
                     $eq:[
                        "$event_type",
                        "send_message"
                     ]
                  },
                  1,
                  0
               ]
            }
         },
         clicked_cancel:{
            $sum:{
               $cond:[
                  {
                     $eq:[
                        "$event_type",
                        "clicked_cancel"
                     ]
                  },
                  1,
                  0
               ]
            }
         },
         clicked_ok:{
            $sum:{
               $cond:[
                  {
                     $eq:[
                        "$event_type",
                        "clicked_ok"
                     ]
                  },
                  1,
                  0
               ]
            }
         }
      }
   },
   {
      $project:{
         date:{
            $dateToString:{
               format:"%Y-%m-%d",
               date:"$timestamp"
            }
         },
         userId:1,
         clicked_cancel:1,
         send_message:1,
         clicked_ok:1
      }
   }
])

解释:

在$match阶段仅保留特定日期的文档

keep only document for a specific day in $match stage

按 userId 对文档进行分组，并计算 $group 阶段中每个事件的发生次数

group doc by userId and count occurrences for each event in $group stage

最后在$project阶段将时间戳字段格式化为yyyy_MM-dd格式

finally format the timestamp field into yyyy_MM-dd format in $project stage

对于您提供的数据，这将输出

for the data you provided, this will output

{
   "_id":"123123123",
   "send_message":0,
   "clicked_cancel":1,
   "clicked_ok":1,
   "date":"2016-10-12"
}

这篇关于在聚合期间计数事件并插入字符串文字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！