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