首页 > 代码库 > group() 实例学习
group() 实例学习
group（{
key:{key1:1,key2:1}, 对筛选后的数据根据key1，key2进行分组
cond:{},根据哪些条件进行筛选数据
reduce:function(curr,res){}, 每一行curr变一次，每个分组res变一次
initial:{},对每个分组中的临时变量进行初始化
finalize:function(){} 对最终的res进行其他操作，如：求平均。
}）
==============建表，插数据===================================
----mongo---
db.goods.insert([
{cat_id:2,goods_name:"Nokia",price:900},
{cat_id:3,goods_name:"iPhone6",price:5600},
{cat_id:3,goods_name:"Mac",price:7800},
{cat_id:3,goods_name:"iPhone7",price:7700},
{cat_id:3,goods_name:"iPad mini",price:4500},
{cat_id:4,goods_name:"SanSong",price:890},
{cat_id:4,goods_name:"JinLi",price:550}
{cat_id:5,goods_name:"Meizu1",price:890},
{cat_id:5,goods_name:"Meizu-Note",price:2300},
])
---mysql---
create table goods(
cat_id int(10) NOT NULL ,
goods_name varchar(100),
price int(10),
Primary KEY cat_id_goods_name(cat_id,goods_name)
);
insert into goods values
(2,"Nokia",900),
(3,"iPhone6",5600),
(3,"Mac",7800),
(3,"iPhone7",7700),
(3,"iPad mini",4500),
(4,"SanSong",890),
(4,"JinLi",550);
(5,"Meizu1",890),
(5,"Meizu-Note",2300);
=============group by cat_id============================
案例1：根据类目cat_id进行分组，并显示每个分组的数量
---mysql---
select cat_id,count(1) from goods group by cat_id order by cat_id;
---mongo---
db.goods.group({
key:{cat_id:1},
cond:{},
reduce:function(curr,res){res.cnt+=1},
initial:{cnt:0},
});
========= ==where price>=900====== =======================
案例2：筛选出价格大于900的数据，再根据类目cat_id进行分组，并显示每个分组的数量
---mysql---
select cat_id,count(1) from goods where price>=900 group by cat_id;
---mongo---
db.goods.group({
key:{cat_id:1},
cond:{price:{$gte:900}},
reduce:function(curr,res){res.cnt+=1},
initial:{cnt:0},
});
=========== =====sum(price)===== ===========================
案例3：根据cat_id分组，展示每个分组下总价格
---mysql---
select sum(price) from goods group by cat_id;
---mongo---
db.goods.group({
key:{cat_id:1},
cond:{},
reduce:function(curr,res){res.totalPrice += curr.price},
initial:{totalPrice:0}
});
=================max(price)===== ==========================
案例4：根据cat_id分组，展示每个分组下价格最高的记录
---mysql---
select cat_id,max(price) from goods group by cat_id;
---mongo---
db.goods.group({
key:{cat_id:0},
cond:{},
reduce:function(curr,res){
if(curr.price > res.maxPrice)
res.maxPrice=curr.price
},
initial:{maxPrice:0}
});
======== =====avg(price)===== ============================
案例5：根据cat_id分组，展示每个分组的平均价格
---mysql---
select cat_id,avg(price) from goods group by cat_id;
---mongo---
db.goods.group({
key:{cat_id:1},
cond:{},
reduce:function(curr,res){
res.sumPrice+=curr.price;
res.cnt+=1;},
initial:{sumPrice:0,cnt:0},
finalize:function(res){res.avg = res.sumPrice/res.cnt;},
});
============================================================
tips：
1，group需要手写聚合函数
2，group 不支持分片、集群，不支持分布式计算
3，分布式：aggregate()，mapReduce()
group() 实例学习