时间戳

首页 > 解决方案 > Calculate Average Duration from a Series of Dates

mongodb - Calculate Average Duration from a Series of Dates

问题描述

Well, I have one collection with too much documents(millions), and now i wanna know how I can calculate average of session duration.

This is a example of structure of my docs:

{
    "session" : "xyz",
    "date" : {"$date" : ""Tue May 15 2018 10:35:08 GMT-0400 LT""}
}
{
    "session" : "xyz",
    "date" : {"$date" : ""Tue May 15 2018 12:35:08 GMT-0400 LT""}
}
{
    "session" : "xyz",
    "date" : {"$date" : ""Tue May 15 2018 15:35:08 GMT-0400 LT""}
}
{
    "session" : "abc",
    "date" : {"$date" : ""Tue May 15 2018 09:35:08 GMT-0400 LT""}
}
{
    "session" : "abc",
    "date" : {"$date" : ""Tue May 15 2018 11:35:08 GMT-0400 LT""}
}

And I wanna define a algorithm like this:

  1. Get all documents by "$session"
  2. Calculate min(first) date and max(last) date of these(2nd point) documents.
  3. Calculate duration(time difference) by session
  4. Repat first 1st point with another "$session"
  5. after calculate all average by "$session", calculate average of these total.

I think first need using aggregate with $group, but I dont have experience with that and now I have a existencial problem hahaha

Thanks

标签： mongodbmongoosemongodb-queryaggregation-framework

解决方案

只要“顺序”在这里并不重要$min并且$max是正确的事情：

db.collection.aggregate([
  { "$group": {
    "_id": "$session",
    "minDate": { "$min": "$date" },
    "maxDate": { "$max": "$date" }
  }},
  { "$group": {
    "_id": null,
    "avg": {
      "$avg": { "$subtract": [ "$maxDate", "$minDate" ] }
    }
  }}
])

初始值从提供的“分组键”中$group获取$min和值，这是文档中的值。第二个是在没有任何分组键（或）的情况下完成的，以便积累“一切”。$max"session"$groupnull

当您将$subtract一个 BSON 日期与另一个日期时，结果是以毫秒为单位的差异。通常，这也是一种转换为数值的处理方式。但是您所做的只是$subtract从$minand输出两个值$max并将其馈送到$avg累加器。

推荐阅读