根据Unix时间戳按日进行Mongodb聚合 [英] Mongodb aggregation by day based on unix timestamp
问题描述
我在Google上搜索了很多,但没有找到任何有用的解决方案...我想查找每日用户的总数. 我有一个名为 session_log 的集合,其中包含如下文件
I have googled alot, but not found any helpful solution... I want to find total number of daily users. I have a collection named session_log having documents like following
{
"_id" : ObjectId("52c690955d3cdd831504ce30"),
"SORTID" : NumberLong(1388744853),
"PLAYERID" : 3,
"LASTLOGIN" : NumberLong(1388744461),
"ISLOGIN" : 1,
"LOGOUT" : NumberLong(1388744853)
}
我想从LASTLOGIN进行汇总...
I want to aggregate from LASTLOGIN...
这是我的查询:
db.session_log.aggregate(
{ $group : {
_id: {
LASTLOGIN : "$LASTLOGIN"
},
count: { $sum: 1 }
}}
);
但是它是按每个登录时间而不是每天进行汇总.任何帮助将不胜感激
But it is aggregating by each login time, not by each day. Any help would be appreciated
推荐答案
MongoDB 4.0及更高版本
使用 $toDate
db.session_log.aggregate([
{ "$group": {
"_id": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": {
"$toDate": {
"$multiply": [1000, "$LASTLOGIN"]
}
}
}
},
"count": { "$sum": 1 }
} }
])
或 $convert
db.session_log.aggregate([
{ "$group": {
"_id": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": {
"$convert": {
"input": {
"$multiply": [1000, "$LASTLOGIN"]
},
"to": "date"
}
}
}
},
"count": { "$sum": 1 }
} }
])
MongoDB> = 3.0并且< 4.0:
db.session_log.aggregate([
{ "$group": {
"_id": {
"$dateToString": {
"format": "%Y-%m-%d",
"date": {
"$add": [
new Date(0),
{ "$multiply": [1000, "$LASTLOGIN"] }
]
}
}
},
"count": { "$sum": 1 }
} }
])
您需要将值乘以1000,将LASTLOGIN
字段转换为毫秒时间戳记
You would need to convert the LASTLOGIN
field to a millisecond timestamp through multiplying the value by 1000
{ "$multiply": [1000, "$LASTLOGIN"] }
,然后转换为日期
"$add": [
new Date(0),
{ "$multiply": [1000, "$LASTLOGIN"] }
]
,可以在 $project
管道,方法是将毫秒时间添加到零毫秒Date(0)
对象,然后提取 $month
, $group
管道按天对文档进行分组
and this can be done in the $project
pipeline by adding your milliseconds time to a zero-milliseconds Date(0)
object, then extract $year
, $month
, $dayOfMonth
parts from the converted date which you can then use in your $group
pipeline to group the documents by the day.
因此,您应该将聚合管道更改为此:
You should thus change your aggregation pipeline to this:
var project = {
"$project":{
"_id": 0,
"y": {
"$year": {
"$add": [
new Date(0),
{ "$multiply": [1000, "$LASTLOGIN"] }
]
}
},
"m": {
"$month": {
"$add": [
new Date(0),
{ "$multiply": [1000, "$LASTLOGIN"] }
]
}
},
"d": {
"$dayOfMonth": {
"$add": [
new Date(0),
{ "$multiply": [1000, "$LASTLOGIN"] }
]
}
}
}
},
group = {
"$group": {
"_id": {
"year": "$y",
"month": "$m",
"day": "$d"
},
"count" : { "$sum" : 1 }
}
};
运行聚合管道:
db.session_log.aggregate([ project, group ])
将给出以下结果(基于示例文档):
would give the following results (based on the sample document):
{ "_id" : { "year" : 2014, "month" : 1, "day" : 3 }, "count" : 1 }
一种改进是将上述内容作为单个管道运行
An improvement would be to run the above in a single pipeline as
var group = {
"$group": {
"_id": {
"year": {
"$year": {
"$add": [
new Date(0),
{ "$multiply": [1000, "$LASTLOGIN"] }
]
}
},
"mmonth": {
"$month": {
"$add": [
new Date(0),
{ "$multiply": [1000, "$LASTLOGIN"] }
]
}
},
"day": {
"$dayOfMonth": {
"$add": [
new Date(0),
{ "$multiply": [1000, "$LASTLOGIN"] }
]
}
}
},
"count" : { "$sum" : 1 }
}
};
运行聚合管道:
db.session_log.aggregate([ group ])
这篇关于根据Unix时间戳按日进行Mongodb聚合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!