在 MongoDb 中按 15 分钟时间间隔分组结果 [英] Group result by 15 minutes time interval in MongoDb

问题描述

我有一个像这种结构的状态"集合 -

I have a "status" collection like this strcture -

{
    _id: ObjectId("545a0b63b03dbcd1238b4567"),
    status: 1004,
    comment: "Rem dolor ipsam placeat omnis non. Aspernatur nobis qui nisi similique.",
    created_at: ISODate("2014-11-05T11:34:59.804Z")
},
{
    _id: ObjectId("545a0b66b03dbcd1238b4568"),
    status: 1001,
    comment: "Sint et eos vero ipsa voluptatem harum. Hic unde voluptatibus et blanditiis quod modi.",
    created_at: ISODate("2014-11-05T11:35:02.814Z")
}
....
....

我需要从该集合中获得按 15 分钟间隔分组的结果.

推荐答案

有几种方法可以做到这一点.

There are a couple of ways to do this.

第一个是 日期聚合运算符，它允许您剖析日期"文档中的值.专门用于分组"作为主要意图:

The first is with Date Aggregation Operators, which allow you to dissect the "date" values in documents. Specifically for "grouping" as the primary intent:

db.collection.aggregate([
  { "$group": {
    "_id": {
      "year": { "$year": "$created_at" },
      "dayOfYear": { "$dayOfYear": "$created_at" },
      "hour": { "$hour": "$created_at" },
      "interval": {
        "$subtract": [ 
          { "$minute": "$created_at" },
          { "$mod": [{ "$minute": "$created_at"}, 15] }
        ]
      }
    }},
    "count": { "$sum": 1 }
  }}
])

第二种方法是使用一个小技巧，当一个日期对象从另一个日期对象中减去(或其他直接数学运算)时，结果是一个数值，表示两个对象之间的纪元时间戳毫秒.因此，只需使用纪元日期即可获得纪元毫秒表示.然后对时间间隔使用日期数学:

The second way is by using a little trick of when a date object is subtracted (or other direct math operation) from another date object, then the result is a numeric value representing the epoch timestamp milliseconds between the two objects. So just using the epoch date you get the epoch milliseconds representation. Then use date math for the interval:

db.collection.aggregate([
    { "$group": {
        "_id": {
            "$subtract": [
                { "$subtract": [ "$created_at", new Date("1970-01-01") ] },
                { "$mod": [ 
                    { "$subtract": [ "$created_at", new Date("1970-01-01") ] },
                    1000 * 60 * 15
                ]}
            ]
        },
        "count": { "$sum": 1 }
    }}
])

所以这取决于分组间隔需要什么样的输出格式.两者基本上代表相同的事物并且有足够的数据来重新构建为日期".代码中的对象.

So it depends on what kind of output format you want for the grouping interval. Both basically represent the same thing and have sufficient data to re-construct as a "date" object in your code.

您可以在分组运算符"中添加任何您想要的内容；分组 _id 之后的部分.我只是使用基本的计数"示例代替您对您真正想做的事情的任何真实陈述.

You can put anything else you want in the "grouping operator" portion after the grouping _id. I'm just using the basic "count" example in lieu of any real statement from yourself as to what you really want to do.

自最初编写以来，对日期聚合运算符进行了一些添加，但从 MongoDB 4.0 开始，将有实际的真正的类型转换".与此处使用 BSON 日期转换完成的基本数学技巧相反.

There were some additions to Date Aggregation Operators since the original writing, but from MongoDB 4.0 there will be actual "real casting of types" as opposed to the basic math tricks done here with BSON Date conversion.

例如我们可以使用 $toLong 和 $toDate 作为这里的新帮手:

For instance we can use $toLong and $toDate as new helpers here:

db.collection.aggregate([
  { "$group": {
    "_id": {
      "$toDate": {
        "$subtract": [
          { "$toLong": "$created_at" },
          { "$mod": [ { "$toLong": "$created_at" }, 1000 * 60 * 15 ] }
        ]
      }
    },
    "count": { "$sum": 1 }
  }}
])

这有点短，不需要为纪元"定义外部 BSON 日期.value 作为定义管道的常量，因此它对于所有语言实现都非常一致.

That's a bit shorter and does not require defining an external BSON Date for the "epoch" value as a constant in defining the pipeline so it's pretty consistent for all language implementations.

那些只是两个帮手"所有与 $convert 方法，这是一个更长"的方法允许自定义处理 null 或转换错误的实现形式.

Those are just two of the "helper" methods for type conversion which all tie back to the $convert method, which is a "longer" form of the implementation allowing for custom handling on null or error in conversion.

甚至可以通过这种转换从主键的 ObjectId 获取 Date 信息，因为这将是创建"的可靠来源；日期:

It's even possible with such casting to get the Date information from the ObjectId of the primary key, as this would be a reliable source of "creation" date:

db.collection.aggregate([
  { "$group": {
    "_id": {
      "$toDate": {
        "$subtract": [
          { "$toLong": { "$toDate": "$_id" }  },
          { "$mod": [ { "$toLong": { "$toDate": "$_id" } }, 1000 * 60 * 15 ] }
        ]
      }
    },
    "count": { "$sum": 1 }
  }}
])

所以类型转换"这种转换可以成为非常强大的工具.

So "casting types" with this sort of conversion can be pretty powerful tool.

警告 - ObjectId 值仅限于秒 的精度，仅适用于构成其部分数据的内部时间值，允许$toDate 转换.实际插入的时间"很可能取决于正在使用的驱动程序.在需要精度的情况下，仍然建议使用离散的 BSON 日期字段，而不是依赖于 ObjectId 值.

Warning - ObjectId values are limited to precision to the second only for the internal time value that makes up part of their data allowing the $toDate conversion. The actual inserted "time" is most probably dependent on the driver in use. Where precision is required, it's still recommended to use a discrete BSON Date field instead of relying on ObjectId values.

这篇关于在 MongoDb 中按 15 分钟时间间隔分组结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！