在 MongoDb 中按 15 分钟时间间隔分组结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26814427/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Group result by 15 minutes time interval in MongoDb
提问by Hein Zaw Htet
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")
}
....
....
I need to get result grouped by 15 minutes interval from that collection.
我需要从该集合中获得按 15 分钟间隔分组的结果。
回答by Neil Lunn
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:
第一个是Date Aggregation Operators，它允许您剖析文档中的“日期”值。专门用于“分组”作为主要意图：
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.
所以这取决于分组间隔需要什么样的输出格式。两者基本上表示相同的事物，并且有足够的数据在代码中重新构建为“日期”对象。
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.
您可以在 grouping 之后的“grouping operator”部分中放入任何其他内容_id。我只是使用基本的“计数”示例来代替您自己关于您真正想做的事情的任何真实陈述。
MongoDB 4.x and Upwards
MongoDB 4.x 及更高版本
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.
自最初编写以来，日期聚合运算符有一些补充，但从 MongoDB 4.0 开始，将有实际的“真正的类型转换”，而不是这里使用 BSON 日期转换完成的基本数学技巧。
For instance we can use $toLongand $toDateas new helpers here:
例如，我们可以在这里使用$toLong和$toDate作为新助手：
db.collection.aggregate([
{ "$group": {
"_id": {
"$toDate": {
"$subtract": [
{ "$toLong": "$created_at" },
{ "$mod": [ { "$toLong": "$created_at" }, 1000 * 60 * 15 ] }
]
}
},
"count": { "$sum": 1 }
}}
])
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.
这有点短，并且不需要将“纪元”值的外部 BSON 日期定义为定义管道的常量，因此它对于所有语言实现都非常一致。
Those are just two of the "helper" methods for type conversion which all tie back to the $convertmethod, which is a "longer" form of the implementation allowing for custom handling on nullor error in conversion.
这些只是用于类型转换的两个“辅助”方法，它们都与$convert方法相关联，这是实现的“更长”形式，允许自定义处理null转换或转换错误。
It's even possible with such casting to get the Dateinformation from the ObjectIdof the primary key, as this would be a reliable source of "creation" date:
甚至可以通过这种转换Date从ObjectId主键的 中获取信息，因为这将是“创建”日期的可靠来源：
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.
因此，具有这种转换的“类型转换”可能是非常强大的工具。
Warning-
ObjectIdvalues are limited to precision to the secondonly for the internal time value that makes up part of their data allowing the$toDateconversion. The actual inserted "time" is most probably dependent on the driver in use. Where precisionis required, it's still recommended to use a discrete BSON Date field instead of relying onObjectIdvalues.
警告-对于构成允许转换的数据的一部分的内部时间值，
ObjectId值仅限于精度到秒$toDate。实际插入的“时间”很可能取决于使用的驱动程序。在需要精度的地方，仍然建议使用离散的 BSON 日期字段而不是依赖ObjectId值。
回答by Blakes Seven
I like the other answer here, and mostly for the use of date math instead of aggregation date operators which while helpful can also be a little obscure.
我喜欢这里的另一个答案，主要是为了使用日期数学而不是聚合日期运算符，这虽然有用，但也有点晦涩。
The only thing I want to add here is that you can also return a Dateobject from the aggregation framework by this approach as opposed to the "numeric" timestamp as the result. It's just a little extra math on the same principles, using $add:
我想在这里添加的唯一一件事是，您还可以Date通过这种方法从聚合框架返回一个对象，而不是作为结果的“数字”时间戳。这只是基于相同原理的一些额外数学运算，使用$add：
db.collection.aggregate([
{ "$group": {
"_id": {
"$add": [
{ "$subtract": [
{ "$subtract": [ "$current_date", new Date(0) ] },
{ "$mod": [
{ "$subtract": [ "$current_date", new Date(0) ] },
1000 * 60 * 15
]}
] },
new Date(0)
]
},
"count": { "$sum": 1 }
}}
])
The Date(0)contructs in JavaScript here represent the same "epoch" date in a shorter form, as 0 millisecond from epoch is epoch. But the main point is that when the "addition" to another BSON date object is done with a numeric identifier, then the inverse of the described condition is true and the end result is actually now a Date.
Date(0)JavaScript 中的构造在这里以较短的形式表示相同的“纪元”日期，因为距纪元的 0 毫秒是纪元。但主要的一点是，当使用数字标识符“添加”到另一个 BSON 日期对象时，则所描述条件的逆为真，最终结果实际上现在是Date.
All drivers will return the native Datetype to their language by this approach.
所有驱动程序都将Date通过这种方法将本机类型返回到他们的语言。
回答by Stierlitz
A little more beautiful for mongo db.version() < 3.0
mongo db.version() < 3.0 更漂亮一点
db.collection.aggregate([
{$match: {created_at:{$exists:1}}},
{$group: {
_id: {$add:[
{$dayOfYear: "$created_at" },
{$multiply: [{$year: "$created_at"}, 1000]}
]},
count: {$sum: 1 }
}},
{$sort:{_id:-1}}
])
回答by Sergey Reutskiy
Another useful way:
另一个有用的方法：
db.collection.aggregate([
{$group: {
_id: {
overallTime: {
$dateToString: { format: "%Y-%m-%dT%H", date: "$created_at" }
},
interval: { $trunc: { $divide: [{ $minute: "$created_at" }, 15 ]}}
},
}},
])
And more easier for min, hour, dayintervals:
对于min、hour、day间隔更容易：
var format = "%Y-%m-%dT%H:%M"; // 1 min
var format = "%Y-%m-%dT%H"; // 1 hour
var format = "%Y-%m-%d"; // 1 day
db.collection.aggregate([
{$group: {
_id: { $dateToString: { format: format, date: "$created_at" } },
}},
])
回答by sanair96
@Neil Lunn's answer at https://stackoverflow.com/a/26814496/8474325for MongoDb 4.x upwards is fantastic. But there is a small mistake in the code where he uses ObjectId for the aggregation. The Line { "$toDate": "_id" }has to be changed to { "$toDate": "$_id" }for the code to work.
@Neil Lunn 在https://stackoverflow.com/a/26814496/8474325 上对 MongoDb 4.x的回答太棒了。但是他使用ObjectId进行聚合的代码有一个小错误。该行{ "$toDate": "_id" }必须更改{ "$toDate": "$_id" }为代码才能工作。
Here's the corrected code.
这是更正后的代码。
db.collection.aggregate([
{ "$group": {
"_id": {
"$toDate": {
"$subtract": [
{ "$toLong": { "$toDate": "$_id" } },
{ "$mod": [ { "$toLong": { "$toDate": "$_id" } }, 1000 * 60 * 15 ] }
]
}
},
"count": { "$sum": 1 }
}}
])
