mongodb 分组查询、指定时间段查询

• • 1. 简单查询
  • 2.复杂查询

查询全部

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 })      

查询指定条件下，按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}}}
])