计数事件并在聚合过程中插入字符串文字 [英] 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

按用户ID对文档进行分组，并统计 $ 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屋！