mongodb aggregate 聚合

按包名分组， 统计每个包名的uuid数量，按数量大小倒叙排列

db.InstallAPP.aggregate([
    {
    $group: 
        {
            _id: "$app_package",
                         total: {$addToSet: "$uuid"}
        }
    },
    {
    $project: {
            app_package: 1,
            size: {$size: "$total"}
        }
    },
    {
    $sort: {
        size: -1
        }
    }
])

或者这样：

db.InstallAPP.aggregate([
    {
    $group: 
        {
            _id: "$app_package",
            size: { $sum: 1 }
        }
    },
    {
    $sort: {
        size: -1
        }
    }
])

同时使用and or

chn_uuids = db.InstallAPP.find({                                                           
     "$or":[
         {"$and": [{"app_package": {"$regex": pattern}}, {"app_package": {"$ne": uc_package_    name}}]},
         {"app_package": channel_add}
     ],
     # "app_package": {"$regex": pattern, "$ne": uc_package_name},
     # 两个app_package并排写就会把两个条件的记录累加起来了
     # "app_package": {"$ne": uc_package_name},
     "time": {
         "$gte": start_time,
         "$lt": end_time
     }
 }).distinct('uuid')

比较经典的案例：
大数据的时候mongo有很多内存限制
http://zackku.com/Mongo-exceed-size/
https://docs.mongodb.com/manual/core/aggregation-pipeline-limits/
https://docs.mongodb.com/manual/reference/limits/#BSON-Document-Size

报错1：Exception: aggregation result exceeds maximum document size (16MB)
报错2： BSONObj size: 21167864 (0x142FEF8) is invalid. Size must be between 0 and 16793600(16MB)

#!/usr/bin/env python
# -*- coding: utf-8 -*-
from connection import db_mongo
from datetime import datetime, timedelta
from base.date_utils import DateValid
import argparse
import time
import traceback 
import xlwt

# link:  http://web.pm.netease.com/v6/my?issueId=44762

db = db_mongo

start_time = datetime(2018, 5, 12)
end_time = datetime(2018, 5, 13)
print start_time, end_time

# 由于读取的数据过大，一开始用distinct()获取几十万的uuid时候，一致报错
# 然后用返回cursor的方式，拿到了才迭代，就不报错了。而不是一次过把全部数据返回过来
uuid_list_cursor = db.LaunchEmulator.aggregate([
        {"$match":
            {"time": 
                {
                   "$gt": start_time,
                    "$lte": end_time
                }
            }
        },
        {"$group":
            {"_id": "$uuid"}
        }
], allowDiskUse=True)
uuid_list = []
for i in uuid_list_cursor:
    uuid_list.append(i["_id"])
print 'uuid总数', len(uuid_list)


page = 0
perpage = 5000
# 0 5000
# 5000*1 5000*2
# 5000*2 5000*3

ret_dict = {}
while True:
    slice_uuids = uuid_list[perpage*page:int(perpage*(page+1))]
    print 'uuid长度',len(slice_uuids)
    if not slice_uuids:
        print '结束'
        break
    print '范围', perpage*page, perpage*(page+1)

    """
    需求是：把所有日活的uuid取出来，然后到Download表去查看他们的分辨率 
    但是，一个uuid可能对应多个Download表的记录，如果不去重的话，有可能一个uuid对应多条记录，就把
多条分辨率都累加上去了。
    所以，首先要在日活表取出uuid列表（40W条），所以分辨率也能只有40W条。 那么查询Download表的时候，就要一个uuid，只取最新时间的那条分辨率记录。
 可以看到下面的筛选，第一个group 首先按uuid分组，按时间倒叙排序，把最新的一条分辨率对应到uuid，这样就能uuid->分辨率一一对应，不会出现多余的记录。
这是断点看记录是
 [
{"_id": "uuidxxxx1", screen: "{"width":500, "height":200},
{"_id": "uuidxxxx2", screen: "{"width":500, "height":200}
]
可以看出，这里还是文档集合，相当于还是mongodb里面的记录，所以！
可以继续在这个基础上使用group!!!!
可以看出第二个group by 是screen.width, screen.height, 说明group 是在上面的记录上做group的，理论上可以无限次group!!
下面为了让输出结果是{width_height: 500}这样，使用了$project 选择显示的字段
还用了concat 和 substr 来组合他们
    """
    results = db.Download.aggregate([
        {"$match":
            {
                "uuid": {"$in": slice_uuids}
            }
        },
        {"$sort":
            {
                "time": -1
            }
        
        },
        {"$group":
            {
                "_id": "$uuid",
                "screen": {"$first": "$machine.screen"}
            }
        },
    # ])
    # results = results.get("result")
    # print len(results)
    # for i in results:
    #     print i
    #     pass
    # break
        {"$group":
            {
                "_id": {
                        # "width-height": {"$concat": ["", "__"] },
                        "width": "$screen.width" ,
                        "height": "$screen.height"
                       },
                "count": {"$sum": 1}
            }
        },
        {"$sort":
            {
                "count": -1
            }
        
        },
        # {"$match":
        #     {
        #         "count": {"$gt": 10}
        #     }
        # },
        {"$project":
            {
                "_id": {"$concat": [
                                        {"$substr": ["$_id.width", 0, -1]},
                                         "_", 
                                        {"$substr": ["$_id.height", 0, -1]}
                                    ]
                        },
                "count": 1
            }
        }
    ], allowDiskUse=True)
    page +=1
    # 处理结果
    ret = results
    for r in ret:
        _id = r['_id']
        count = r['count']
        if _id not in ret_dict.keys():
            ret_dict[_id] = count
        else:
            ret_dict[_id] += count

sorted_dict = sorted(ret_dict.items(), lambda x, y: cmp(x[1], y[1]), reverse=True)
for i in sorted_dict:
    if i[1] < 100:
        continue
    print  i

统计某个版本的uuid 个数是多少

# 这个是统计所有的package的uuid总数
 db.LaunchEmulator.aggregate([
    {$match: {
            time: {$gte: ISODate("2018-05-24T")}, 
            version: "1.25.2.1", 
            engine: "NEMU",
            package : "mumu-green"
        }
    },
    {$group:    
        
        {
            _id: null,
            uuid_list: {$addToSet: "$uuid"},
        }
    },
    {
    $project: {
         "size": {$size: "$uuid_list"}
        }
    }
])

返回结果是

 { "_id" : null, "size" : 293 }

# 这个是按版本来统计uuid个数
 db.LaunchEmulator.aggregate([
    {$match: {
            time: {$gte: ISODate("2018-05-24T")}, 
            version: "1.25.2.1", 
            engine: "NEMU",
        }
    },
    {$group:    
        
        {
            _id: {package: "$package"},
            uuid_list: {$addToSet: "$uuid"},
        }
    },
    {
    $project: {
         "size": {$size: "$uuid_list"}
        }
    }
])

结果

{ "_id" : { "package" : "com.netease.soulofhunter.netease_simulator" }, "size" : 2 }
{ "_id" : { "package" : "mumu-green" }, "size" : 10 }
{ "_id" : { "package" : "com.miHoYo.enterprise.NGHSoD" }, "size" : 25 }
{ "_id" : { "package" : "com.bilibili.azurlane" }, "size" : 9 }
{ "_id" : { "package" : "" }, "size" : 15 }
{ "_id" : { "package" : "com.tencent.tmgp.pubgmhd" }, "size" : 1 }
{ "_id" : { "package" : "mumu" }, "size" : 217 }
{ "_id" : { "package" : "com.xd.ro.roapk" }, "size" : 6 }
{ "_id" : { "package" : "com.netease.zjz" }, "size" : 1 }
{ "_id" : { "package" : "com.netease.dwrg" }, "size" : 6 }
{ "_id" : { "package" : "com.netease.zgz" }, "size" : 2 }
{ "_id" : { "package" : "com.georgie.SoundWireFree" }, "size" : 1 }