mongodb - 根据MongoDB中两个日期的差异统计记录
问题描述
根据下面的文档,我需要统计initial_date和end_date之间的差异大于和小于5分钟并且name = A的记录。
[{
"_id": 1,
"name": "A",
"inicial_date": "2019-01-01 12:00:00",
"end_date": "2019-01-01 12:01:00"
},{
"_id": 2,
"name": "A",
"inicial_date": "2019-01-01 12:05:00",
"end_date": "2019-01-01 12:07:00"
},{
"_id": 3,
"name": "A",
"inicial_date": "2019-01-01 12:00:00",
"end_date": "2019-01-01 12:06:00"
},{
"_id": 4,
"name": "A",
"inicial_date": "2019-01-01 12:04:00",
"end_date": "2019-01-01 12:05:00"
},
"_id": 5,
"name": "A",
"inicial_date": "2019-01-01 12:10:00",
"end_date": "2019-01-01 12:20:00"
},{
"_id": 6,
"name": "A",
"inicial_date": "2019-01-01 12:00:00",
"end_date": "2019-01-01 12:08:00"
},{
"_id": 7,
"name": "A",
"inicial_date": "2019-01-01 13:00:00",
"end_date": "2019-01-01 13:01:00"
},{
"_id": 8,
"name": "B",
"inicial_date": "2019-01-01 14:00:00",
"end_date": "2019-01-01 14:09:00"
}]
预期结果:
{
"less_than_5": 4,
"greater_than_5": 3
}
解决方案
好吧,你可以使用聚合查询来做到这一点,
db.collection.aggregate([
{
$match: {
name: "A"
}
},
{
// find time difference, the result is in milliseconds
$project: {
timeDiffInMilliseconds: {
$subtract: [
{
$toDate: "$end_date"
},
{
$toDate: "$inicial_date"
}
]
}
}
},
{
// convert the time difference to minutes
$project: {
timeDiffInMinutes: {
$divide: [
"$timeDiffInMilliseconds",
60000
]
}
}
},
{
// check if the number of minutes is greater than 5 mins or not
$project: {
timeDiffGreaterThan5Mins: {
$cond: [
{
$gt: [
"$timeDiffInMinutes",
5
]
},
1,
0
]
}
}
},
{
// group according to greater than 5 minutes or not
$group: {
_id: "null",
greater_than_5: {
$sum: {
$cond: [
{
$eq: [
"$timeDiffGreaterThan5Mins",
1
]
},
1,
0
]
}
},
less_than_5: {
$sum: {
$cond: [
{
$eq: [
"$timeDiffGreaterThan5Mins",
0
]
},
1,
0
]
}
}
}
}
])
让它更有效率,
db.collection.aggregate([
{
$match: {
name: "A"
}
},
{
$project: {
more_than_5_mins: {
$cond: [
{
$gt: [
{
$subtract: [
{
$toDate: "$end_date"
},
{
$toDate: "$inicial_date"
}
]
},
300000
]
},
1,
0
]
}
}
},
{
$group: {
_id: "",
less_than_5: {
$sum: {
$cond: [
{
$eq: [
"$more_than_5_mins",
0
]
},
1,
0
]
}
},
greater_than_5: {
$sum: {
$cond: [
{
$eq: [
"$more_than_5_mins",
1
]
},
1,
0
]
}
}
}
}
])
推荐阅读
- android - 致命异常:Firebase-Messaging-Intent-Handle
- amazon-web-services - 如何使用 CloudFormation 或 CDK 将另一个 AWS 账户的事件总线指定为 EventBridge 规则的目标?
- python - 如何从 Python 中的数据框获取日期和生日的年龄列表?
- c++ - 从数组包中删除和插入一个元素。为什么是布尔数组而不是int?
- javascript - 道具不更新子组件
- reactjs - ReactJs:Date().toLocaleTimeString 不起作用
- java - 如何在 JTable 中显示从数据库中检索到的数据?
- c# - 将具有多个嵌套级别的任何 XML 读取到结构化表中以写入 Excel 的通用方法
- c# - 如果在开始游戏时父对象首先不活动,我如何注册事件?
- awk - 根据 2 列替换列匹配中的 velues