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 :
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
5
for (Map
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}
