mongodb的group简析
数据如下 www.2cto.com
{ _id : 0, name : hexin0, value : 0, date : isodate(2012-12-19t11:48:07.151z), group : 0 }
{ _id : 1, name : hexin1, value : 1, date : isodate(2012-12-19t11:48:07.151z), group : 1 }
{ _id : 2, name : hexin2, value : 2, date : isodate(2012-12-19t11:48:07.151z), group : 2 }
{ _id : 3, name : hexin3, value : 3, date : isodate(2012-12-19t11:48:07.151z), group : 0 }
{ _id : 4, name : hexin4, value : 4, date : isodate(2012-12-19t11:48:07.151z), group : 1 }
{ _id : 5, name : hexin5, value : 5, date : isodate(2012-12-19t11:48:07.151z), group : 2 }
{ _id : 6, name : hexin6, value : 6, date : isodate(2012-12-19t11:48:07.151z), group : 0 }
{ _id : 7, name : hexin7, value : 7, date : isodate(2012-12-19t11:48:07.151z), group : 1 }
{ _id : 8, name : hexin8, value : 8, date : isodate(2012-12-19t11:48:07.151z), group : 2 }
{ _id : 9, name : hexin9, value : 9, date : isodate(2012-12-19t11:48:07.151z), group : 0 }
{ _id : 10, name : hexin10, value : 10, date : isodate(2012-12-19t11:48:07.151z), group : 1 }
{ _id : 11, name : hexin11, value : 11, date : isodate(2012-12-19t11:48:07.151z), group : 2 }
{ _id : 12, name : hexin12, value : 12, date : isodate(2012-12-19t11:48:07.151z), group : 0 }
{ _id : 13, name : hexin13, value : 13, date : isodate(2012-12-19t11:48:07.151z), group : 1 }
{ _id : 14, name : hexin14, value : 14, date : isodate(2012-12-19t11:48:07.151z), group : 2 }
{ _id : 15, name : hexin15, value : 15, date : isodate(2012-12-19t11:48:07.151z), group : 0 }
{ _id : 16, name : hexin16, value : 16, date : isodate(2012-12-19t11:48:07.151z), group : 1 }
{ _id : 17, name : hexin17, value : 17, date : isodate(2012-12-19t11:48:07.151z), group : 2 }
{ _id : 18, name : hexin18, value : 18, date : isodate(2012-12-19t11:48:07.151z), group : 0 }
{ _id : 19, name : hexin19, value : 19, date : isodate(2012-12-19t11:48:07.151z), group : 1 }
需要实现下面sql :
1
select date as d_o_f , goup ,sum(value),count(*),avg(sum(value)/count(*))
2
from xx
3
where name like 'hexin%'
4
group by goup, date
www.2cto.com
1.定义分组的key
1
stringbuilder keyfun = new stringbuilder();
2
keyfun.append(function(d) {);
3
keyfun.append( return { );
4
keyfun.append( goup : d.group ,);
5
keyfun.append( d_o_f: d.date.getday() );
6
keyfun.append( } ;);
7
keyfun.append( });
2. 遍历每个组的处理方式
1
stringbuffer reduce = new stringbuffer();
2
reduce.append(function ( curr, result) {);
3
reduce.append( result.total += curr.value; );
4
reduce.append( result.count++;);
5
reduce.append(});
3. 计算平均数
1
stringbuffer finalize = new stringbuffer();
2
finalize.append(function(result){);
3
finalize.append( var weekdays = [ '星期天', '星期一', '星期二',);
4
finalize.append( '星期三', '星期四', );
5
finalize.append( '星期五', '星期六' ];);
6
finalize.append( result.d_o_f = weekdays[result.d_o_f]; );
7
finalize.append( result.avg = math.round(result.total / result.count); );
8
finalize.append(});
4. 调用dao查询
1
group group = group.keyfunction(keyfun.tostring()).initial(count, 0).initial(total, 0)
2
.reduce(reduce.tostring()).finalizefunction(finalize.tostring());
3
//调用自己封装的dao来实现 ， 并输出结果
4
list> list = dao.group(c, query.where(name).startwith(hexin), group);
5
for (map map : list) {
6
system.out.println(map);
7
}
封装的查询条件 ：
www.2cto.com
1
{ $regex : { $regex : ^hexin , $options : m}}
封装的group命令
01
{
02
group: {
03
$keyf: function(d) { return { goup : d.group , d_o_f: d.date.getday() } ; },
04
$reduce: function ( curr, result) {result.total += curr.value; result.count++;},
05
initial: {
06
total: 0,
07
count: 0
08
},
09
finalize: function(result){
10
var weekdays = [ '星期天', '星期一', '星期二','星期三', '星期四','星期五', '星期六' ];
11
result.d_o_f = weekdays[result.d_o_f];
12
result.avg = math.round(result.total / result.count);
13
},
14
ns: c,
15
cond: {
16
name: {
17
$regex: {
18
$regex: ^hexin,
19
$options: m
20
}
21
}
22
}
23
}
24
}
/////查询结果
{goup=0.0, d_o_f=星期三, total=63.0, count=7.0, avg=9.0}
{goup=1.0, d_o_f=星期三, total=70.0, count=7.0, avg=10.0}
{goup=2.0, d_o_f=星期三, total=57.0, count=6.0, avg=10.0}