网易云课堂\『李兴华java培训23』MongoDB数据库\网易云课堂\『李兴华java培训23』MongoDB数据库

--  章节4课时26聚合（取得集合个数）  http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1462152&courseId=1211033 


db.students.count();
db.students.count({"name":/xyt/i});






-- 章节4课时27聚合（消除重复数据）  http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1464127&courseId=1211033






db.students.find().pretty();
db.students.insert({"name":"E","age":19,"course":["语文","政治","地理","英语"],"score":89});




db.runCommand({"distinct":"students","key":"name"});






-- 章节4课时28聚合（group操作）  http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1462153&courseId=1211033







db.runCommand({"group":{"ns":"students","key":{"age":true},"initial":{"count":0},"condition":{"age":{"$gte":19}},"$reduce":function(doc,prev){
print("doc="+doc+" prev = "+prev);
}}});




--  章节4课时29聚合（MapReduce） http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1464129&courseId=1211033 






db.emps.drop();
db.emps.insert({"name":"xyt1","age":30,"sex":"男","job":"clerk","salary":100})
db.emps.insert({"name":"xyt2","age":52,"sex":"男","job":"clerk","salary":8000})
db.emps.insert({"name":"xyt3","age":32,"sex":"男","job":"manager","salary":5600})
db.emps.insert({"name":"xyt4","age":30,"sex":"女","job":"clerk","salary":8000})
db.emps.insert({"name":"xyt5","age":32,"sex":"男","job":"clerk","salary":8000})
db.emps.insert({"name":"xyt6","age":30,"sex":"男","job":"boss","salary":20000})
db.emps.insert({"name":"xyt7","age":54,"sex":"男","job":"clerk","salary":8000})
db.emps.insert({"name":"xyt8","age":86,"sex":"男","job":"clerk","salary":200})
db.emps.insert({"name":"xyt9","age":32,"sex":"男","job":"manager","salary":12000})
db.emps.insert({"name":"xyt9","age":32,"sex":"男","job":"manager","salary":12000})


db.emps.find();






编写分组的定义 ：    第一组 ：{key:"clerk",values:[姓名,姓名,...]}

-- 1 
var jobMapFun=function(){
emit(this.job,this.name);  //按照job分组 

};

var jobReduceFun=function(key,values){
return {"job":key,"names":values};
};


db.runCommand({"mapreduce":"emps","map":jobMapFun,"reduce":jobReduceFun,"out":"t_job_emp"});      进行操作的整合:
db.t_job_emp.find();


-- 2

var jobMapFun=function(){
emit(this.job,this.name);  

};
var jobReduceFun=function(key,values){
return {"job":key,"names":values};
};
var jobFinalizeFun=function(key,values){
if (key == "boss"){
return {"job":Key,"names":values,"info":"老板"};
}
return {"job":key,"names":values};

};


db.runCommand({"mapreduce":"emps","map":jobMapFun,"reduce":jobReduceFun,"out":"t_job_emp2","finalize":jobFinalizeFun}); 
db.t_job_emp2.find(); 




-- 统计各性别的人数、平均工资、最低工资、姓名

var sexMapFun=function(){

emit(this.sex,{"ccount":1,"csal":this.salary,"cmax" :this.salary,"cmin":this.salary,"cname":this.name});
};

var sexReduceFun=function(key,values){
var total=0;
var sum=0;
var max=values[0].cmax;//假设第一个数据是最高工资
var min=values[0].cmin;//假设第一个数据是最低工资
var names=new Array();
for(var x in values){
total+=values[x].ccount;
sum+=values[x].csal;
if(max<values[x].cmax){
max=values[x].cmax;
}
if(min >values[x].cmin){
min=values[x].cmin;
}
names[x]=values[x].cname;
}
var avg=(sum/total).toFixed(2);
return {"count":total,"avg":avg,"sum":sum,"max":max,"min":min,"names":names };
}




db.runCommand({"mapreduce":"emps","map":sexMapFun,"reduce":sexReduceFun,"out":"t_job_emp3"}); 
db.t_job_emp3.find(); 




-- 章节4课时30聚合框架（$grouop）  http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1462154&courseId=1211033

--求出每个职位的人数
db.emps.aggregate([{"$group":{"_id":"$job",job_count:{"$sum":1}}}]);

-- 求每个职位的总工资
db.emps.aggregate([{"$group":{"_id":"$job",job_sal:{"$sum":"$salary"}}}]);

-- 求每个职位的平均工资
db.emps.aggregate([{"$group":{"_id":"$job",job_sal:{"$sum":"$salary"},"job_avg":{"$avg":"$salary"}}}]);

-- 求最高与最低工资
db.emps.aggregate([{"$group":{"_id":"$job",max_sal:{"$max":"$salary"},"min_avg":{"$min":"$salary"}}}]);

-- 求每个职位的工资数据
db.emps.aggregate([{"$group":{"_id":"job","sal_date":{"$push":"$salary"}}}]);

-- 求每个职位的人员
db.emps.aggregate([{"$group":{"_id":"job","sal_date":{"$push":"$name"}}}]);

-- $push 将数据变为数组 但是不保证重复  这里 xyt9 有两条 怎么去重复？   addToSet
db.emps.aggregate([{"$group":{"_id":"job","sal_date":{"$addToSet":"$name"}}}]);

-- 保留第一个内容
db.emps.aggregate([{"$group":{"_id":"job","sal_date":{"$first":"$name"}}}]);

-- 保留最后一个内容
db.emps.aggregate([{"$group":{"_id":"job","sal_date":{"$last":"$name"}}}]);


--  章节4课时31聚合框架（$project）  http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1463151&courseId=1211033

$project  用来控制列显示规则 

普通列({成员：1|true}) :表示要显示的内容
"_id"列({"_id":0|false}):表示"_id"列是否显示
条件过滤列 ({成员:表达式}):满足表达式之后的数据进行显示

-- 只显示 name 、job ，不显示 _id 、salary 列
db.emps.aggregate([{"$project":{"_id":0,"name":1}}]);


$add 加  ， $substract 减   乘： $multiply


-- 
db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"salary":1}}]);
db.emps.aggregate([{"$project":{"_id":0,"name":1,"职位":"$job","salary":1}}]);

db.emps.aggregate([{"$project":{"_id":0,"name":1,"职位":"$job","salary":{"年薪":{"$multiply":["$salary",12]}}}}]);


$concat :连接  截取 : $substr   转小写 : $toLower 

-- 找出所有工资大于等于 2000 的员工
db.emps.aggregate([{"$project":{"_id":0,"name":1,"job":1,"salary":{"$gte":["$salary",2000]}}}]);


-- 查询manager 
db.emps.aggregate([{"$project":{"_id":0,"name":1,"职位":"$job","job":{"$eq":["$job","manager"]}}}]);

db.emps.aggregate([{"$project":{"_id":0,"name":1,"职位":"$job","job":{"$strcasecmp":["$job","manager"]}}}]);

-- 使用字符串截取 
db.emps.aggregate([{"$project":{"_id":0,"name":1,"职位":"$job","job":{"前面三位":{"$substr":["$job",0,3]}}}}]);



--  章节4课时32聚合框架（$sort） http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1464130&courseId=1211033 


$sort :排序  1 升序 -1 降序 

db.emps.aggregate([{"$sort":{"age":-1,"salary":1}}]);


-- 

db.emps.aggregate([
{"$match":{"salary":{"$gte":2000,"$lte":9000}}},
{"$project":{"_id":0,"name":1,"salary":1,"job":1}},
{"$group":{"_id":"$job","count":{"$sum":1},"avg":{"$avg":"$salary"}}},
{"$sort":{"count":-1}}
]);


-- 章节4课时33聚合框架（分页处理）   http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1462159&courseId=1211033 

$skip : 跨过   $limit  ： 个数


db.emps.aggregate([
{"$project":{"_id":0,"name":1,"salary":1 ,"job":1}},
{"$limit":2}
]);


db.emps.aggregate([
{"$project":{"_id":0,"name":1,"salary":1 ,"job":1}},
{"$skip":3},
{"$limit":2},

]);


--  章节4课时34聚合框架（$unwind）  http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1464133&courseId=1211033 

$unwind : 数组中数据变成单行数据 

db.depts.drop();
db.depts.insert({"title":"技术部","bus":["研发","生产","培训"]});
db.depts.insert({"title":"财务部","bus":["工资","税收"]});

db.depts.aggregate([{"$unwind":"$bus"}]);


db.depts.aggregate([
{"$project":{"_id":0,"title":true,"bus":true}},
{"$unwind":"$bus"}
]);




--  章节4课时35聚合框架（$geoNear）  http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1462161&courseId=1211033


$geoNear : 得到附近的坐标点


商铺集合：
db.shop.drop();
db.shop.insert({loc:[10,10]});
db.shop.insert({loc:[11,10]});
db.shop.insert({loc:[10,11]});
db.shop.insert({loc:[12,15]});
db.shop.insert({loc:[16,17]});
db.shop.insert({loc:[90,90]});
db.shop.insert({loc:[120,130]});

为shop 定义2D 索引 ： db.shop.ensureIndex({"loc":"2d"});

db.shop.aggregate([
{"$geoNear":{"near":[10,11],"distanceField":"loc","maxDistance":1,"num":2,"spherical":false}}
]);


-- 章节4课时36聚合框架（out） http://study.163.com/course/courseLearn.htm?courseId=1211033#/learn/video?lessonId=1464135&courseId=1211033



db.emps.aggregate([
{"$project":{"_id":0,"name":1,"salary":1 ,"job":1}},
{"$out":"emp_infos"},

]);
db.emp_infos.find();