mongodb的group简析

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 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}