按15分钟的时间间隔在MongoDb中分组结果 [英] 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 }
  }}
])

这有点短，并且不需要为"epoch"值定义一个外部BSON日期作为定义管道的常量，因此它对于所有语言实现都是相当一致的.

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值的精度仅限于秒，仅适用于构成其数据一部分的内部时间值，从而允许 precision 的地方，仍然建议使用离散的BSON Date字段，而不要依赖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.

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