MongoDB 聚合查询一点理解

亚马逊联盟(原装雀巢奶粉)

db.sp_rpt_adGroups.aggregate(
[
    //根据collection字段过滤
    {
        $match:{
            profileId:{$eq:2892412125226015},
            recordDate:{$gte:20210120},
            recordDate:{$lte:20220328}
        }
    },

    //聚合取点击汇总
    {
        $group:{
            clicks:{$sum:"$clicks"},
            _id:{
                campaignId:"$campaignId",
                adGroupId:"$adGroupId"
            }
        }
    },

    //上一步聚合作为条件过滤
  {
        $match:{clicks:{$gte:100}}
    },
    
    //根据上述结果，计算记录数
    {
        $group:{
            records:{$sum:1},
            _id: null
        }
    }
]
)

MongoDB 截止目前没有子查询功能，以上用例，可以查询 sp_rpt_adGroups 集合里指定profileId在指定日期范围内，clicks总量超过100的记录总数。

聚合查询管道理解：每个代码块就好比一节带有处理能力水加工设备，它能对MogonDB数据（好比水）进行加工，得出结果到下一道管道再进行加工，最终得出你预设的咖啡或红茶。

JAVA 实现（以下代码MongoCliDrv是我已封装过的类，所以直接复制下面代码是无法执行，代码可供开发思路）

	public static void main(String[] args) {
		MongoCliDrv.initMongoConfigFile("/config/mongo.properties");
		MongoCliDrv mongo = MongoCliDrv.getInstance();
		
		//例一、字段作为条件 ------------------------------------------
		Bson match = new BasicDBObject("$match",new BasicDBObject("recordDate",new BasicDBObject("$gte",20220101L)));
		Document group = new Document("$group", new BasicDBObject("clicks",new BasicDBObject("$sum","$clicks"))
				  												  .append("_id", "$campaignId"));
		
		Document project = new Document("$project", new BasicDBObject("clicks","$clicks").append("campaignId", "$_id").append("_id", null));

		Page<Map<String, Object>> ret = mongo.queryDocumentGroup("sp_rpt_adGroups", match, group, project, null, 1, 5);
		System.out.println(JsonTool.objectToJson(ret));

		//例二、聚合作为条件,找出合计点击超过2000的广告ID---------------
		List<Bson> pipeline = new ArrayList<>();
		//按字段值过滤
		match = new BasicDBObject("$match",new BasicDBObject("profileId", new BasicDBObject("$eq",2892412125226015L))
															.append("recordDate", new BasicDBObject("$gte",20220120).append("$lte",20220228)));
		pipeline.add(match);

		//goup by 字段
		group = new Document("$group", new BasicDBObject("clicks",new BasicDBObject("$sum","$clicks"))
																  .append("impressions", new BasicDBObject("$sum","$impressions"))
				  												  .append("_id", new BasicDBObject("campaignId","$campaignId").append("adGroupId", "$adGroupId")));
		pipeline.add(group);

		//输出字段
		project = new Document("$project", new BasicDBObject("campaignId", "$_id.campaignId")
												.append("adGroupId", "$_id.adGroupId")
												.append("clicks", "$clicks")
												.append("impressions", "$impressions")
												.append("_id", null));
		pipeline.add(project);
		
		//增加聚合过滤
		match = new BasicDBObject("$match",new BasicDBObject("clicks", new BasicDBObject("$gt",100)).append("impressions", new BasicDBObject("$gt",10000)));
		pipeline.add(match);
		
		//增加排序功能: 按 clicks 升序，impressions 降序
		BasicDBObject sort = new BasicDBObject("$sort", new BasicDBObject("impressions", -1).append("clicks", 1));
		pipeline.add(sort);
		
		//分页
		int page = 1;
		int pagesize = 5;
		Document skip = new Document("$skip", (page-1) * pagesize);
		Document limit = new Document("$limit", pagesize);
		pipeline.add(skip);
		pipeline.add(limit);

		List<Map<String, Object>> rets = mongo.queryDocumentGroup("sp_rpt_adGroups", pipeline);
		System.out.println(JsonTool.listBeanToJson(rets));
		
		//例三、获取点击超过指定次数的记录数
		//按字段值过滤
		pipeline.clear();
		match = new BasicDBObject("$match",new BasicDBObject("profileId", new BasicDBObject("$eq",2892412125226015L))
															.append("recordDate", new BasicDBObject("$gte",20210120).append("$lte",20220328)));
		pipeline.add(match);

		//goup by 字段
		group = new Document("$group", new BasicDBObject("clicks",new BasicDBObject("$sum","$clicks"))
				  												  .append("_id", new BasicDBObject("campaignId","$campaignId").append("adGroupId", "$adGroupId")));
		pipeline.add(group);
		
		//增加聚合过滤
		match = new BasicDBObject("$match",new BasicDBObject("clicks", new BasicDBObject("$gt",100)));
		pipeline.add(match);
		
		//二次聚合算合计
		group = new Document("$group", new BasicDBObject("records",new BasicDBObject("$sum",1)).append("_id", null));
		pipeline.add(group);
		rets = mongo.queryDocumentGroup("sp_rpt_adGroups", pipeline);
		System.out.println(JsonTool.listBeanToJson(rets));
	}