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

快来打我* 2023-07-18 12:50 2阅读 0赞

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

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

## 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总数

查询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}}}
    ])