获取所有文档之间两个两个的时间差

Question

我正在做一个小型物联网项目，我想展示一些我从设备获得的有用信息。

设备是一个开关按钮,控制一个灯泡,每次开关被激活时,一个事件被发送到数据库中,往来文件的两个例子:

{
"_id" : ObjectId("62baf5b98218da3bc80e9d61"),
"light_on" : false,
"device_id" : "ab082e4d-e617-4c74-8d41-fa316246afac",
"timestamp" : ISODate("2022-07-01T10:00:24.000+0000")
}

{
"_id" : ObjectId("62baf5b98218da3bc90e9d6a"),
"light_on" : true,
"device_id" : "ab082e4d-e617-4c74-8d41-fa316246afac",
"timestamp" : ISODate("2022-07-01T10:32:00.000+0000")
}

从这些数据中，我想提取:

1. 灯亮时的平均持续时间
2. 灯亮的最长时间
3. 最长关灯时间
上所有持续时间超过4小时的闪电

这种请求需要我逐个比较文档，这是我在Mongo中从未做过的，我甚至不确定这是否可行，所以欢迎任何帮助。

Answer 1

试试这个:

db.collection.aggregate([
{
$setWindowFields: {
partitionBy: "$device_id",
sortBy: { timestamp: 1 },
output: {
following: {
$shift: {
output: "$timestamp",
by: 1
}
}
}
}
},
{
$set: {
duration: {
$dateDiff: {
startDate: "$timestamp",
endDate: "$following",
unit: "second"
}
}
}
},
{
$facet: {
mean: [
{
$group: {
_id: {
device_id: "$device_id",
light_on: "$light_on"
},
mean: { $avg: "$duration" },
longest: { $max: "$duration" }
}
}
],
exceeded: [
{
$match: {
light_on: true,
duration: { $gt: 14400 } // 4 hours = 14400 seconds
}
}
]
}
}
])

Mongo操场

For MongoDB version <你需要一个不同的解决方案。起始点是这个:>

db.collection.aggregate([
{ $sort: { timestamp: -1 } },
{
$group: {
_id: "$device_id",
timestamp: { $push: "$$ROOT" }
}
},
{
$set: {
timestamp: {
$reduce: {
input: "$timestamp",
initialValue: [],
in: {
$concatArrays: [
"$$value",
[
{
$mergeObjects: [
"$$this",
{ following: { $last: "$$value.timestamp" },
duration: {
$dateDiff: {
endDate: { $last: "$$value.timestamp" },
startDate: "$$this.timestamp",
unit: "second"
}
}
}
]
}
]
]
}
}
}
}
},
... some cosmetic, see above
])