最近使用mongodb需要查询数据,用到了aggregate,学习下,上代码
db.表名.aggregate([ {$match:{'created_time':{$gte:'2016-01-15',$lte:'2019-01-20'},'token':{'$ne':null}}} ,{$group:{"_id":{"lm_number":"$lm_number","source_type":"$source_type","app":"$app","position":"$position","created_time":"$created_time","token":"$token"}}} ,{$group:{"_id":{"lm_number":"$_id.lm_number","source_type":"$_id.source_type","app":"$_id.app","created_time":"$_id.created_time","position":"$_id.position"}, "COUNT(token)" : {"$sum" : NumberInt(1)}}} ,{$project:{"_id": 0,"lm_number":"$_id.lm_number","created_time":"$_id.created_time","source_type":"$_id.source_type","app":"$_id.app","position":"$_id.position","COUNT(token)":"$COUNT(token)"}} ,{$sort:{"created_time":-1} ,{$skip:0} ,{$limit:1} ,{$unwind: {path: '$created_time',preserveNullAndEmptyArrays: true}} ]);
对应的目标数据结构
{ "_id" : ObjectId("5c469ad680499b3d42558c1b"), "event_id" : "1002106", "is_login" : "0", "event_time" : "1548131024375", "device_id" : "5c469ab9879842003a2dca3d", "token" : "A4pudYt6COzQ-f2mSVAriPaloRGKKqKg", "mobile" : "", "version_code" : "10100", "source_type" : "client", "os" : "android", "app" : "ep", "channel" : "autoupdate", "utm_source" : "", "created_time" : "2019-01-18 12:23:50" } { "_id" : ObjectId("5c469b5f526159282e5daabf"), "ad_id" : "3", "event_id" : "1002107", "is_login" : "1", "position" : "1", "lm_number" : "2018122614102766196661", "event_time" : "1548131161304", "device_id" : "5c469b53879842003a2dd0f1", "token" : "A4pudYt6COzQ-f2mSVAriPaloRGKKqKg", "mobile" : "13894101298", "version_code" : "10100", "source_type" : "client", "os" : "android", "app" : "ep", "channel" : "autoupdate", "utm_source" : "aaa", "created_time" : "2019-01-16 12:26:07", "user_id" : "258" }
#$match:过滤,按条件筛选。$gte大于等于,$lte小于等于,$gt大于,$lt小于
#$group:分组聚合。$sum计算总和,{$sum: 1}表示返回总和×1的值(即总和的数量),使用{$sum: '$制定字段'}也能直接获取制定字段的值的总和
#$project:投射,从聚合的子文档中提取字段供显示,也可重命名(注意显示的字段必须要在子文档中存在);其中的"_id"为0则不显示其值,反之为1显示
#$sort:排序,-1为desc降序,1位asc正序
#skip:跳过指定数量条数,默认0。类似mysql的limit的第一个参数
#limit:限制读取的条数。类似mysql的limit的第二个参数
#unwind:对其中指定的数组类型进行拆分,最终每条信息中包含数组的一个值。还可以写成{$unwind:'$created_time'},其中的$created_time是数据中的一个字段,此字段可以为空数组、非数组、null、字符串。数组会拆分如上述所讲,其他值若不设置会丢失(除字符串)。所以就需要使用preserveNullAndEmptyArrays的写法,其值为true既保留空数组等;path为指定的字段。
###注意,这里连续使用了两个group,第一个$group为多条件分组聚合,第二个为多重分组聚合
###用mysql的思想就是
select lm_number,source_type,app,position,count(token) from ( select lm_number ,source_type ,app ,position ,token from 表名 group by lm_number,source_type,app,position,token ) as t group by lm_number,source_type,app,position;
###第一个相当于上述的子查询那部分,第二个就是外面的那部分。在很多聚合后还会有重复的场景中就可以使用多重聚合。
###多重聚合取总数:
##多个条件时:
db.表名.aggregate([ {$group:{"_id":{"lm_number":"$_id.lm_number","source_type":"$_id.source_type","app":"$_id.app","created_time":"$_id.created_time","position":"$_id.position"}, "COUNT(token)" : {"$sum" : NumberInt(1)}}} ]);
##单个条件时:
db.表名.aggregate([ {$group:{"_id":"lm_number", "count" : {"$sum" : NumberInt(1)}}} ]);
学习学习学习...