• aggregate聚合


    最近使用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)}}}
    ]);
    

      学习学习学习...

  • 相关阅读:
    P5331 [SNOI2019]通信
    P3700 [CQOI2017]小Q的表格
    Linux
    P3268 [JLOI2016]圆的异或并
    P3317 [SDOI2014]重建
    P5492 [PKUWC2018]随机算法
    P3210 [HNOI2010]取石头游戏
    支配树
    P5401 [CTS2019]珍珠
    P4027 [NOI2007]货币兑换
  • 原文地址:https://www.cnblogs.com/two-bees/p/10483330.html
Copyright © 2020-2023  润新知