MongoDB按持续时间跨度分组 [英] MongoDB group by duration span
问题描述
我有用于表示折线图数据的汇总查询.
I have aggregation query for representing line chart data.
如下:
[
{
"$match": {
"Category": {
"$in": ["Mobile"]
},
"StartTime": {
"$gte": {"$date": "2014-01-29T00:00:00.000Z"},
"$lt": {"$date": "2014-09-29T00:00:00.000Z"}
}
}
},
{
"$group": {
"_id": {
"Category": "$Category",
"Country":"$Country",
"City":"$City",
"day": {
"day":{"$dayOfMonth": "$StartTime"},
"month":{"$month":"$month"},
"year":{"$year":"$year"}
}
},
"UniqueVisits": {
"$sum": 1
},
"Date": {
"$first": "$StartTime"
}
}
},
{
"$project": {
"_id": "$_id.Category",
"Header": {
"$concat": [{"$substr": [{"$month": "$Date"}, 0,2]},
"/",
{"$substr": [{"$dayOfMonth": "$Date"}, 0, 2]},
"/",
{"$substr": [{"$year": "$Date"}, 0, 4]}]
},
"Name": {
"$concat": [
{
"$ifNull": ["$_id.Country","notset"]
},
"~",
{
"$ifNull": ["$_id.City","notset"]
}
]
},
"UniqueVisits": "$UniqueVisits",
}
}
]
工作正常. 同样，我通过使用$ hour，$ week，$ month分组来按小时，周和月显示折线图.
It works fine. Similarly i have line chart by hour,week and month by grouping using $hour,$week,$month.
现在我想通过AM/PM添加折线图，即从0-12和12-24的小时持续时间，甚至是早上(6-12)，下午(12-18)，晚上(18-24)，晚上(0-6)
Now i want to add line chart by AM/PM i.e hour duration from 0-12 and 12-24 and even by Morning(6-12), Afternoon(12-18), Evening(18-24), Night(0-6)
有关如何在上述期限内实现分组的任何帮助. 预先感谢.
Any help on how can i achieve grouping for above duration's. Thanks in advance.
例如:
数据:
{ "_id" : 1, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : 2, "Date" : ISODate("2014-10-02T20:44:09Z") }
{ "_id" : 3, "Date" : ISODate("2014-09-03T20:44:09Z") }
{ "_id" : 4, "Date" : ISODate("2014-09-02T20:44:09Z") }
{ "_id" : 5, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : 6, "Date" : ISODate("2014-09-03T07:00:00Z") }
{ "_id" : 7, "Date" : ISODate("2014-09-03T14:00:00Z") }
{ "_id" : 8, "Date" : ISODate("2014-10-02T20:47:09Z") }
我希望每天进行AM/PM分组的目的如下:
What i want to achieve for AM/PM for grouping each day is as following :
{ "_id" : "PM", "Count" : 3, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : "AM", "Count" : 1, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : "AM", "Count" : 4, "Date" : ISODate("2014-09-03T00:00:00Z") }
对于早晨，下午，晚上和晚上，情况如下:
And for Morning, Afternoon, Evening, Night is as follows :
{ "_id" : "Evening", "Count" : 3, "Date" : ISODate("2014-10-02T19:44:09Z") }
{ "_id" : "Evening", "Count" : 1, "Date" : ISODate("2014-09-02T20:44:09Z") }
{ "_id" : "Night", "Count" : 1, "Date" : ISODate("2014-09-03T00:00:00Z") }
{ "_id" : "Morning", "Count" : 1, "Date" : ISODate("2014-09-03T07:00:00Z") }
{ "_id" : "Afternoon", "Count" : 1, "Date" : ISODate("2014-09-03T14:00:00Z") }
{ "_id" : "Evening", "Count" : 1, "Date" : ISODate("2014-09-03T20:44:09Z") }
推荐答案
将这些部分插入$group._id的正确位置可能会达到目标.
Inserting these parts into the proper position of your $group._id may accomplish your target.
ampm : {
$cond : {
"if" : {
$lt : [ {
$hour : "$StartTime"
}, 12 ]
},
"then" : "AM",
"else" : "PM"
}
},
segment : {
$let : {
"vars" : {
h : {
$hour : "$StartTime"
}
},
"in" : {
$cond : {
"if" : {
$lt: [ "$$h", 6 ]
},
"then" : "Night",
"else" : {
$cond : {
"if" : {
$lt : [ "$$h", 12 ]
},
"then" : "Morning",
"else" : {
$cond : {
"if" : {
$lt : [ "$$h", 18 ]
},
"then" : "Afternoon",
"else" : "Evening"
}
}
}
}
}
}
}
}
为了便于理解，我将整个粘贴在这里
To make it easier to understand, I paste the whole one here
[
{
"$match": {
"Category": {
"$in": ["Mobile"]
},
"StartTime": {
"$gte": {"$date": "2014-01-29T00:00:00.000Z"},
"$lt": {"$date": "2014-09-29T00:00:00.000Z"}
}
}
},
{
"$group": {
"_id": {
"Category": "$Category",
"Country":"$Country",
"City":"$City",
"day": {
"day":{"$dayOfMonth": "$StartTime"},
"month":{"$month":"$month"},
"year":{"$year":"$year"},
// add this part to group by for each day of each month of each year
ampm : {
$cond : {
"if" : {
$lt : [ {
$hour : "$StartTime"
}, 12 ]
},
"then" : "AM",
"else" : "PM"
}
},
segment : {
$let : {
"vars" : {
h : {
$hour : "$StartTime"
}
},
"in" : {
$cond : {
"if" : {
$lt: [ "$$h", 6 ]
},
"then" : "Night",
"else" : {
$cond : {
"if" : {
$lt : [ "$$h", 12 ]
},
"then" : "Morning",
"else" : {
$cond : {
"if" : {
$lt : [ "$$h", 18 ]
},
"then" : "Afternoon",
"else" : "Evening"
}
}
}
}
}
}
}
}
}
},
"UniqueVisits": {
"$sum": 1
},
"Date": {
"$first": "$StartTime"
}
}
},
{
"$project": {
"_id": "$_id.Category",
"Header": {
"$concat": [{"$substr": [{"$month": "$Date"}, 0,2]},
"/",
{"$substr": [{"$dayOfMonth": "$Date"}, 0, 2]},
"/",
{"$substr": [{"$year": "$Date"}, 0, 4]}]
},
"Name": {
"$concat": [
{
"$ifNull": ["$_id.Country","notset"]
},
"~",
{
"$ifNull": ["$_id.City","notset"]
}
]
},
"UniqueVisits": "$UniqueVisits",
}
}
]
这篇关于MongoDB按持续时间跨度分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
