select province, avg(age) from student where province = '上海' group by provincejava代碼：
/*創(chuàng)建 $match, 作用相當(dāng)于query*/ DBObject match = new BasicDBObject("$match", new BasicDBObject("province", "上海")); /* Group操作*/ DBObject groupFields = new BasicDBObject("_id", "$province"); groupFields.put("AvgAge", new BasicDBObject("$avg", "$age")); DBObject group = new BasicDBObject("$group", groupFields); /* 查看Group結(jié)果 */ AggregationOutput output = connection.aggregate(match, group); // 執(zhí)行 aggregation命令 System.out.println(output.getCommandResult());123456789101112123456789101112輸出結(jié)果：
"result" : [ { "_id" : "上海" , "AvgAge" : 32.09375} ] , "ok" : 1.012341234task2：統(tǒng)計每個省各科平均成績
首先更具數(shù)據(jù)庫文檔結(jié)構(gòu)，subjects是數(shù)組形式，需要先分組，然后再進(jìn)行統(tǒng)計
主要處理步驟如下：
1.先用$unwind 拆數(shù)組 2. 按照 province, subject 分租并求各科目平均分
/* 創(chuàng)建 $unwind 操作, 用于切分?jǐn)?shù)組*/ DBObject unwind = new BasicDBObject("$unwind", "$subjects"); /* Group操作*/ DBObject groupFields = new BasicDBObject("_id", new BasicDBObject("subjname", "$subjects.name").append("province", "$province")); groupFields.put("AvgScore", new BasicDBObject("$avg", "$subjects.scores")); DBObject group = new BasicDBObject("$group", groupFields); /* 查看Group結(jié)果 */ AggregationOutput output = connection.aggregate(unwind, group); // 執(zhí)行 aggregation命令 System.out.println(output.getCommandResult());123456789101112123456789101112輸出結(jié)果：
"result" : [ { "_id" : { "subjname" : "英語" , "province" : "海南"} , "AvgScore" : 58.1} , { "_id" : { "subjname" : "數(shù)學(xué)" , "province" : "海南"} , "AvgScore" : 60.485} , { "_id" : { "subjname" : "語文" , "province" : "江西"} , "AvgScore" : 55.538} , { "_id" : { "subjname" : "英語" , "province" : "上海"} , "AvgScore" : 57.65625} , { "_id" : { "subjname" : "數(shù)學(xué)" , "province" : "廣東"} , "AvgScore" : 56.690} , { "_id" : { "subjname" : "數(shù)學(xué)" , "province" : "上海"} , "AvgScore" : 55.671875} , { "_id" : { "subjname" : "語文" , "province" : "上海"} , "AvgScore" : 56.734375} , { "_id" : { "subjname" : "英語" , "province" : "云南"} , "AvgScore" : 55.7301 } , . . . . "ok" : 1.012345678910111213141234567891011121314task3: 將同一省份的科目成績統(tǒng)計到一起( 即，期望 ‘province’:’xxxxx’, avgscores:[ {‘xxx’:xxx}, ….] 這樣的形式)
要做的有一件事，在前面的統(tǒng)計結(jié)果的基礎(chǔ)上，先用 project將平均分和成績?nèi)嗟揭黄穑侔词》輌roup，將各科目的平均分push到一塊,使用 group 再次分組.
Mongo m = new Mongo("localhost", 27017);DB db = m.getDB("test");DBCollection coll = db.getCollection("student");/* 創(chuàng)建 $unwind 操作, 用于切分?jǐn)?shù)組*/DBObject unwind = new BasicDBObject("$unwind", "$subjects");/* Group操作*/DBObject groupFields = new BasicDBObject("_id", new BasicDBObject("subjname", "$subjects.name").append("province", "$province"));groupFields.put("AvgScore", new BasicDBObject("$avg", "$subjects.scores"));DBObject group = new BasicDBObject("$group", groupFields);/* Reshape Group Result*/DBObject projectFields = new BasicDBObject();projectFields.put("province", "$_id.province");projectFields.put("subjinfo", new BasicDBObject("subjname","$_id.subjname").append("avgscore", "$AvgScore"));DBObject project = new BasicDBObject("$project", projectFields);/* 將結(jié)果push到一起*/DBObject groupAgainFields = new BasicDBObject("_id", "$province");groupAgainFields.put("avginfo", new BasicDBObject("$push", "$subjinfo"));DBObject reshapeGroup = new BasicDBObject("$group", groupAgainFields);/* 查看Group結(jié)果 */AggregationOutput output = coll.aggregate(unwind, group, project, reshapeGroup);System.out.println(output.getCommandResult());12345678910111213141516171819202122232425261234567891011121314151617181920212223242526 "result" : [ { "_id" : "遼寧" , "avginfo" : [ { "subjname" : "數(shù)學(xué)" , "avgscore" : 56.46666666666667} , { "subjname" : "英語" , "avgscore" : 52.093333333333334} , { "subjname" : "語文" , "avgscore" : 50.53333333333333}]} , { "_id" : "四川" , "avginfo" : [ { "subjname" : "數(shù)學(xué)" , "avgscore" : 52.72727272727273} , { "subjname" : "英語" , "avgscore" : 55.90909090909091} , { "subjname" : "語文" , "avgscore" : 57.59090909090909}]} , { "_id" : "重慶" , "avginfo" : [ { "subjname" : "語文" , "avgscore" : 56.077922077922075} , { "subjname" : "英語" , "avgscore" : 54.84415584415584} , { "subjname" : "數(shù)學(xué)" , "avgscore" : 55.33766233766234}]} , { "_id" : "安徽" , "avginfo" : [ { "subjname" : "英語" , "avgscore" : 55.458333333333336} , { "subjname" : "數(shù)學(xué)" , "avgscore" : 54.47222222222222} , { "subjname" : "語文" , "avgscore" : 52.80555555555556}]} . . . ] , "ok" : 1.0}123456789123456789task4:
DBCollection collection = MongoUtils.getCollection_Database( (String) ServletContextUtils.getsession().getAttribute( "game"), SysConst.TABLE_WAIGUA_RATIO); // query DBObject match = new BasicDBObject("$match", queryParam_n); // 利用$project拼裝group需要的數(shù)據(jù) DBObject fields = new BasicDBObject("name", 1); fields.put("count", 1); DBObject project = new BasicDBObject("$project", fields); DBObject groupFields = new BasicDBObject("_id", "$name"); groupFields.put("count", new BasicDBObject("$sum", "$count")); DBObject group = new BasicDBObject("$group", groupFields);// group DBObject limit = new BasicDBObject("$limit", Integer.parseInt(n)); DBObject sort = new BasicDBObject("$sort", new BasicDBObject( "count", -1)); AggregationOutput output = collection.aggregate(match, project, group, sort, limit); List<String> nameList = new ArrayList<String>(); for (DBObject obj : output.results()) { BasicDBObject obj2 = (BasicDBObject) obj; nameList.add(obj2.getString("_id")); }
