根据Unix时间戳按日进行Mongodb聚合

本文介绍了根据Unix时间戳按日进行Mongodb聚合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我在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 ])