I know these have been asked a bunch and I am brand new to MongoDB which means I am struggling. I am using compass and trying to figure out this aggregation pipeline. In short, I need to get the average time difference between the max and min timestamp grouped by id.
The expected result would just be: avg_time: 234.00 or such.
The equivalent MySQL query looks like this:
select SEC_TO_TIME(AVG(TIME_TO_SEC(TIMEDIFF(a.maxDate,a.minDate)))) FROM (select id, min(timestamp) as minDate, max(timestamp) as maxDate from counterHistory group by id) as a
Here is what the dataset looks like:
[{
"_id": {
"$oid": "617dce992743dd52bed811a6"
},
"dateStart": {
"$date": "2021-10-30T23:00:41.056Z"
},
"dateEnd": {
"$date": "2021-10-30T23:00:52.404Z"
},
"areas": {
"c2acc5cc-9a7a-4406-8d91-79cb7f7ded70": {
"color": "yellow",
"type": "rightleft_bottomtop",
"location": {
"point1": {
"x": 1397,
"y": 702
},
"point2": {
"x": 1808,
"y": 645
},
"refResolution": {
"w": 1920,
"h": 969
}
},
"computed": {
"a": 0.15457277801631616,
"b": -332.7843438566361,
"lineBearings": [
81.21317228796154,
261.2131722879615
],
"point1": {
"x": 465.6666666666667,
"y": -260.80495356037153
},
"point2": {
"x": 602.6666666666666,
"y": -239.62848297213623
}
},
"name": "Right line start"
},
"56885eaf-9808-4b5e-b193-06b20e10c39d": {
"color": "turquoise",
"type": "rightleft_bottomtop",
"location": {
"point1": {
"x": 770,
"y": 411
},
"point2": {
"x": 1085,
"y": 360
},
"refResolution": {
"w": 1920,
"h": 969
}
},
"computed": {
"a": 0.18045112781954886,
"b": -199.0092879256966,
"lineBearings": [
79.77099171264048,
259.77099171264047
],
"point1": {
"x": 256.6666666666667,
"y": -152.6934984520124
},
"point2": {
"x": 361.6666666666667,
"y": -133.74613003095976
}
},
"name": "right lane end"
}
},
"videoResolution": {
"w": 640,
"h": 360
},
"filename": "demo.mp4",
"counterSummary": {
"56885eaf-9808-4b5e-b193-06b20e10c39d": {
"_total": 8,
"car": 8
},
"c2acc5cc-9a7a-4406-8d91-79cb7f7ded70": {
"_total": 8,
"car": 7,
"truck": 1
}
},
"trackerSummary": {
"totalItemsTracked": 64
},
"counterHistory": [
{
"frameId": 29,
"timestamp": {
"$date": "2021-10-30T23:00:42.694Z"
},
"area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
"name": "car",
"id": 408,
"bearing": 291.8014094863518,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 30.588237198390278
},
{
"frameId": 43,
"timestamp": {
"$date": "2021-10-30T23:00:43.619Z"
},
"area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
"name": "truck",
"id": 457,
"bearing": 293.1985905136482,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 31.985418225686644
},
{
"frameId": 50,
"timestamp": {
"$date": "2021-10-30T23:00:44.063Z"
},
"area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
"name": "car",
"id": 421,
"bearing": 303.69006752597977,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 43.919075813339305
},
{
"frameId": 63,
"timestamp": {
"$date": "2021-10-30T23:00:44.927Z"
},
"area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
"name": "car",
"id": 458,
"bearing": 293.6293777306568,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 32.41620544269528
},
{
"frameId": 65,
"timestamp": {
"$date": "2021-10-30T23:00:45.054Z"
},
"area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
"name": "car",
"id": 464,
"bearing": 284.03624346792645,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 24.265251755286005
},
{
"frameId": 78,
"timestamp": {
"$date": "2021-10-30T23:00:45.888Z"
},
"area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
"name": "car",
"id": 469,
"bearing": 303.69006752597977,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 42.47689523801825
},
{
"frameId": 86,
"timestamp": {
"$date": "2021-10-30T23:00:46.415Z"
},
"area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
"name": "car",
"id": 427,
"bearing": 354.28940686250036,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 85.48158485014012
},
{
"frameId": 122,
"timestamp": {
"$date": "2021-10-30T23:00:48.757Z"
},
"area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
"name": "car",
"id": 479,
"bearing": 296.565051177078,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 35.35187888911645
},
{
"frameId": 125,
"timestamp": {
"$date": "2021-10-30T23:00:48.946Z"
},
"area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
"name": "car",
"id": 408,
"bearing": 323.13010235415595,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 63.359110641515514
},
{
"frameId": 143,
"timestamp": {
"$date": "2021-10-30T23:00:50.143Z"
},
"area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
"name": "car",
"id": 473,
"bearing": 284.03624346792645,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 24.265251755286005
},
{
"frameId": 152,
"timestamp": {
"$date": "2021-10-30T23:00:50.716Z"
},
"area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
"name": "car",
"id": 438,
"bearing": 296.565051177078,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 35.35187888911645
},
{
"frameId": 160,
"timestamp": {
"$date": "2021-10-30T23:00:51.242Z"
},
"area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
"name": "car",
"id": 455,
"bearing": 45,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 34.77099171264047
},
{
"frameId": 174,
"timestamp": {
"$date": "2021-10-30T23:00:52.149Z"
},
"area": "c2acc5cc-9a7a-4406-8d91-79cb7f7ded70",
"name": "car",
"id": 492,
"bearing": 327.2647737278924,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 66.05160143993088
},
{
"frameId": 175,
"timestamp": {
"$date": "2021-10-30T23:00:52.212Z"
},
"area": "56885eaf-9808-4b5e-b193-06b20e10c39d",
"name": "car",
"id": 469,
"bearing": 281.30993247402023,
"countingDirection": "rightleft_bottomtop",
"angleWithCountingLine": 21.538940761379738
}
]
}]
Here is my atrocious pipeline that at least gives me the result that I am looking for. So I guess, how do you optimize this?
[{$match: {
_id:ObjectId('617dce992743dd52bed811a6')
}}, {$unwind: {
path: "$counterHistory",
}}, {$group: {
_id: "$counterHistory.id",
maxDate:{$max:"$counterHistory.timestamp"},
minDate:{$min:"$counterHistory.timestamp"}
}}, {$project: {
_id:1,
minDate:1,
maxDate:1,
noMatchingDates:{$ne:["$maxDate","$minDate"]}
}}, {$match: {
noMatchingDates:true
}}, {$group: {
_id: null,
"avg_time": {
"$avg": {
"$subtract": [
{ "$ifNull": [ "$maxDate", 0 ] },
{ "$ifNull": [ "$minDate", 0 ] }
]
}
}
}}, {$project: {
avg_time:1,
hours: { $divide: [ "$avg_time", 3600000 ] },
minutes: { $divide: [ "$avg_time", 60000 ] },
seconds: { $divide: [ "$avg_time", 1000 ] }
}}]
CodePudding user response：
So as I put up above and confirmed by YuTing, this pipeline works:
[{$match: {
_id:ObjectId('617dce992743dd52bed811a6')
}}, {$unwind: {
path: "$counterHistory",
}}, {$group: {
_id: "$counterHistory.id",
maxDate:{$max:"$counterHistory.timestamp"},
minDate:{$min:"$counterHistory.timestamp"}
}}, {$project: {
_id:1,
minDate:1,
maxDate:1,
noMatchingDates:{$ne:["$maxDate","$minDate"]}
}}, {$match: {
noMatchingDates:true
}}, {$group: {
_id: null,
"avg_time": {
"$avg": {
"$subtract": [
{ "$ifNull": [ "$maxDate", 0 ] },
{ "$ifNull": [ "$minDate", 0 ] }
]
}
}
}}, {$project: {
avg_time:1,
hours: { $divide: [ "$avg_time", 3600000 ] },
minutes: { $divide: [ "$avg_time", 60000 ] },
seconds: { $divide: [ "$avg_time", 1000 ] }
}}]