整整恶心了我两天,一度差点放弃使用分组。直接将数据按照分组的维度存储就直接简单查询了。直到我找到了 $project 和 $dateToString $year $dayOfMonth 这些指令。
下面列一下我了解到的这些指令:
名称 | 含义 |
---|---|
$year | 返回该日期的年份部分 |
$month | 返回该日期的月份部分(between 1 and 12.) |
$dayOfMonth | 返回该日期的日部分 |
$hour | 返回该日期的小时部分 |
$minute | 返回该日期的分钟部分 |
$second | 返回该日期的秒部分(以0到59之间的数字形式返回日期的第二部分,但可以是60来计算闰秒。) |
$millisecond | 返回该日期的毫秒部分(between 0 and 999.) |
$dayOfYear | 返回该日期是这一年的第几天。(全年366天) |
$dayOfMonth | 返回该日期是这一个月的第几天。(1到31) |
$dayOfWeek | 返回的是这个周的星期几。(1:星期日,7:星期六) |
$week | 返回该日期是所在年的第几个星期(between 0 and 53) |
$dateToString | 格式日期:{ $dateToString: { format: <formatString>, date: <dateExpression> } } |
# $dateToString的 format格式化
%Y Year (4 digits, zero padded) 0000-9999
%m Month (2 digits, zero padded) 01-12
%d Day of Month (2 digits, zero padded) 01-31
%H Hour (2 digits, zero padded, 24-hour clock) 00-23
%M Minute (2 digits, zero padded) 00-59
%S Second (2 digits, zero padded) 00-60
%L Millisecond (3 digits, zero padded) 000-999
%j Day of year (3 digits, zero padded) 001-366
%w Day of week (1-Sunday, 7-Saturday) 1-7
%U Week of year (2 digits, zero padded) 00-53
%% Percent Character as a Literal %
# 执行下面的语句可查看效果
# 注意:语句中的 requestTime字段必须是ISODate("") 方式存储,如果是字符串或者long类型不能使用这些函数
# java.util.Date 类型字段,使用spring的mongoTemplate 存储则保存的就是ISODate类型的
db.requestLog.aggregate(
[
{
$project:
{
date: { $dateToString : {format: "%Y-%m-%d", date: "$requestTime"} },
year: { $year: "$requestTime" },
month: { $month: "$requestTime" },
day: { $dayOfMonth: "$requestTime" },
hour: { $hour: "$requestTime" },
minutes: { $minute: "$requestTime" },
seconds: { $second: "$requestTime" },
milliseconds: { $millisecond: "$requestTime" },
dayOfYear: { $dayOfYear: "$requestTime" },
dayOfWeek: { $dayOfWeek: "$requestTime" },
week: { $week: "$requestTime" }
}
},
{
$match:{ # 查询条件 ( year=2020 and month=1)
year:2020,
month:1
}
}
]
)
db.requestLog.aggregate(
[
{
$project: {
yearMonthDayUTC: { $dateToString: { format: "%Y-%m-%d", date: "$requestTime" } },
timewithOffsetNY: { $dateToString: { format: "%H:%M:%S:%L%z", date: "$requestTime", timezone: "America/New_York"} },
timewithOffset430: { $dateToString: { format: "%H:%M:%S:%L%z", date: "$requestTime", timezone: "+04:30" } },
minutesOffsetNY: { $dateToString: { format: "%Z", date: "$requestTime", timezone: "America/New_York" } },
minutesOffset430: { $dateToString: { format: "%Z", date: "$requestTime", timezone: "+04:30" } }
}
}
]
)
db.requestLog.aggregate([
{$match:{"rime":{'$gte': ISODate("2019-03-11T00:00:00Z") ,'$lt':ISODate("2019-03-12T00:00:00Z")}}},
{
$group:{
"_id":"$requestTime",
"c":{$sum:1},
"b":{$avg:"$requestTime"}
}
},{$sort:{"$requestTime":1}}
])
db.requestLog.aggregate(
[
{
$project:
{
date: { $dateToString : {format: "%Y-%m-%d", date: "$requestTime"} },
year_month: { $dateToString : {format: "%Y-%m", date: "$requestTime"} },
day: { $dayOfMonth: "$requestTime" },
responseTime: "$responseTime"
}
},
{
$match:{
year_month:'2020-01'
}
},
{
$group:{
"_id":"$day",
"b":{$avg:"$responseTime"},
"c":{$sum:1}
}
},
{$sort:{"_id":1}}
]
)
db.requestLog.aggregate(
[
{
$project:
{
year_month: { $dateToString : {format: "%Y-%m-%d", date: "$requestTime"} }
}
},
{
$group:{
"_id":"$year_month"
}
},
{$sort:{"_id":1}}
]
)
db.requestLog.aggregate(
[
{
$project:
{
year_month_day_hour: { $dateToString : {format: "%Y-%m-%d %H", date: "$requestTime"} },
reqUrl: "$reqUrl",
responseTime: "$responseTime"
}
},
{
$match:{
year_month_day_hour:'2020-01-14 01'
}
},
{
$group:{
"_id":"$reqUrl",
"b":{$avg:"$responseTime"},
"c":{$sum:1}
}
},
{$sort:{"b":1}}
]
)