mongodb 分组查询、指定时间段查询
1. 简单查询
查询全部
db.UserDailyStudyRecord.find({})
查询指定条件下数据，in查询
db.UserDailyStudyRecord.find({'userId':{$in:[1,2,3,4,5,6,7,8]}})
查询指定时间区间 2019-03-26整天的登录信息
db.UserLoginLog.find({'loginTime':{$gt:ISODate('2019-03-26T00:00:00Z'),$lte:ISODate( '2019-03-26T23:59:59Z' )}})
排序查询
db.UserLoginLog.find({}).sort({ loginTime:-1 })
2.复杂查询
查询指定条件下，按userId分组，计算studyTime总数，按总时间倒叙排列
db.UserDailyStudyRecord.aggregate([{ '$match' : {'userId' : { '$in' : [1,2,3,4,5,6,7,8,18]}}} ,{'$group': {'_id' :'$userId','totalStudyTime':{'$sum':'$studyTime'}}},{'$sort': {'totalStudyTime':-1}}])
查询指定条件下，按userId分组，计算所有行totalStudyTime的值
db.UserDailyStudyRecord.aggregate([{ '$match' : {'userId' : { '$in' : [1,2,3,4,5,6,7,8,18]}}} ,{'$group': {'_id' :'1','totalStudyTime':{'$sum':'$studyTime'}}},{'$sort': {'totalStudyTime':-1}}])
或者
db.UserDailyStudyRecord.aggregate([{ '$match' : {'userId' : { '$in' : [1,2,3,4,5,6,7,8,18]}}} ,{'$group': {'_id' :null,'totalStudyTime':{'$sum':'$studyTime'}}},{'$sort': {'totalStudyTime':-1}}])
查询指定时间段内,按userId分组
db.UserLoginLog.aggregate([ {$match:{'loginTime' : {$gt:ISODate('2019-03-26T00:00:00Z'),$lte:ISODate( '2019-03-26T23:59:59Z' )},'isValid':1}}, {$group:{_id:"$userId"}} ])
查询指定时间段内，userId总数
db.UserLoginLog.aggregate([ {$match:{'loginTime' : {$gt:ISODate('2019-03-26T00:00:00Z'),$lte:ISODate( '2019-03-26T23:59:59Z' )},'isValid':1}}, {$group:{_id:"$userId"}}, {$group:{_id:null,count:{$sum:1}}} ])
查询指定时间段，特定范围，userId总数
db.UserLoginLog.aggregate([ {$match: {'loginTime' : {$gt:ISODate('2020-02-27T00:00:00Z'),$lte:ISODate( '2020-03-27T23:59:59Z' )}, 'isValid':1,'userId':{ '$in' : [21317,19705,19672,21322,19673]}}}, {$group:{_id:"$userId"}}, {$group:{_id:null,count:{$sum:1}}} ])
查询userId及userId总数
db.UserLoginLog.aggregate([ {$match: {'loginTime' : {$gt:ISODate('2020-02-27T00:00:00Z'),$lte:ISODate( '2020-03-27T23:59:59Z' )}, 'isValid':1,'userId':{ '$in' : [92079,92081,92083,92085,92087,92089,92091,92093,92095]}}}, {$group:{_id:"$userId"}} ]) db.UserLoginLog.aggregate([ {$match: {'loginTime' : {$gt:ISODate('2020-02-27T00:00:00Z'),$lte:ISODate( '2020-03-27T23:59:59Z' )}, 'isValid':1,'userId':{ '$in' : [92079,92081,92083,92085,92087,92089,92091,92093,92095]}}}, {$group:{_id:"$userId"}}, {$group:{_id:null,count:{$sum:1}}} ])
