我在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)对象,然后提取 $year , $month , $dayOfMonth 部分,然后可以在您的 $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聚合
发布评论