假设以下是我的元素结构。我如何在DB中的每次旅行中进行每次旅行的范围并获得AVG差异(平均长度)?我猜是减去日期?但是,如何减去和avg?
"_id": {
"$oid": "5445ab058767000062"
},
"comment": null,
"scheduled_request": false,
"status": "blah",
"timestamp_started": {
"$date": "2014-10-21T00:38:28.990Z"
},
"timestamp_transaction_complete": {
"$date": "2014-10-21T00:49:12.990Z"
},
"user_id": "5445a9000057"
udpate ==========这是我的查询
db.ambulance_requests.aggregate([
{ "$group": {
"_id": null,
"avg_time": {
"$avg": {
"$subtract": [
"$timestamp_transaction_complete",
"$timestamp_started"
]
}
}
}}
])
和我的结果(来自MAC终端外壳):
{ "_id" : null, "avg_time" : 0 }
您通过在$group
管道阶段应用$subtract
和$avg
。对于"一切",请使用null
进行分组密钥:
db.trips.aggregate([
{ "$group": {
"_id": null,
"avg_time": {
"$avg": {
"$subtract": [
{ "$ifNull": [ "$timestamp_completed", 0 ] },
{ "$ifNull": [ "$timestamp_started", 0 ] }
]
}
}
}}
])
当您从另一个bson日期对象上进行 $subtract
时,差异会作为它们之间的毫秒间隔返回。这也是用于提取其他目的毫秒价值的通常方便的技术。
您提供的单个文档:
{
"comment" : null,
"scheduled_request" : false,
"status" : "blah",
"timestamp_started" : ISODate("2014-10-21T00:38:28.990Z"),
"timestamp_completed" : ISODate("2014-10-21T00:49:12.990Z"),
"user_id" : "5445a9000057"
}
问题中的单个文档的结果:
/* 1 */
{
"_id" : null,
"avg_time" : 644000.0
}
https://mongoplayground.net/p/nfo54i5gixu
如果完成 doc中不存在剂量,然后从AVG计算中跳过该文档
db.collection.aggregate([
{
"$match": {
"finishedAt": {
"$exists": true
}
}
},
{
"$unwind": "$tags"
},
{
"$match": {
"$or": [
{
"tags.name": "Canada"
},
{
"tags.name": "ABC"
},
]
}
},
{
"$group": {
"_id": null,
"avg_time": {
"$avg": {
"$subtract": [
"$finishedAt",
"$createdAt"
]
}
}
}
}
])