"【摘要】 MongoDB 在进行分组统计时如果面对一些比较复杂的计算情况，往往会遇到 shell 脚本过于复 .."

oradt 北京
乾学院 43 号会员
1 回帖 • 6,389 浏览 • 5 年前

MongoDB 分组统计

计算＆AI

【摘要】
MongoDB 在进行分组统计时如果面对一些比较复杂的计算情况，往往会遇到 shell 脚本过于复杂的问题。而集算器 SPL 语言，则因其有丰富的函数库及易用性恰好能弥补 Mongo 这方面的不足。若想了解更多，请前往乾学院：MongoDB 分组统计!

MongoDB 作为 NoSql 文档型数据库，在全球范围得到广泛的支持与应用。在比较常用的数据库功能中，相对于普通的增删改查，使用 group 聚合分组统计有些复杂，而 MongoDB 也给予了支持。本文将对MongoDb分组的实现方法及示例进行分析，通过在 MongoDB 脚本中操作、使用集算器 SPL 语言操作两种操作途径，进行简单的归纳总结。具体的问题场景包括以下几个方面：

1. 内嵌数组结构的统计........................................................................... 1
2. 内嵌文档求和..................................................................................... 2
3. 分段分组结构统计.............................................................................. 4
4. 多字段分组统计................................................................................. 6

1. 内嵌数组结构的统计

对嵌套数组结构中的数据进行统计处理例如查询考试科目的平均分及每个学生的总成绩：

测试数据：

_id	name	sex	Scroe
1	Tom	F	[{"lesson":" Physics","mark":60 }, {"lesson":" Chemical","mark":72 }]
2	Jerry	M	[{"lesson":" Physics","mark":92 }, {"lesson":" Math","mark":81 }]

期待统计结果：

Physics	76	Tom	132
Chemical	72	Jerry	173
Math	81

Mongodb脚本：

db.student.aggregate( [
{\$unwind : "$scroe"},
{$group: {
"_id": {"lesson":"$scroe.lesson"} ,
"qty":{"\$avg": "$scroe.mark"}
}
}
] )

db.student.aggregate( [
{\$unwind : "$scroe"},
{$group: {
"_id": {"name" :"$name"} ,
"qty":{"\$sum" : "$scroe.mark"}
}
}
] )

由于各科分数 scroe 是按课目、成绩记录的数组结构，统计前需要将它拆解，将每科成绩与学生对应，然后再实现分组计算。这需要熟悉 unwind 与 group 组合的应用。

SPL 脚本 (student.dfx)：

	A	B
1	=mongo_open("mongodb://127.0.0.1:27017/raqdb")
2	=mongo_shell(A1,"student.find()").fetch()
3	=A2.conj(scroe).groups(lesson:LESSON;avg(mark):AVG)
4	=A2.new(name:NAME,scroe.sum(mark):TOTAL)
5	>A1.close()

按课目统计的总分数：

LESSON	AVG
Chemical	72.0
Math	81.0
Physics	76.0

每个学生的总成绩：

NAME	TOTAL
Tom	132
Jerry	173

脚本说明：
       A1：连接 mongodb 数据库。
       A2：获取 student 表中的数据。
       A3：将 scroe 数据合并成序表，再按课程分组，计算平均分。
       A4：统计每个学生的成绩后返回列名为 NAME、TOTAL 的序表。new 函数表示生成新序表。
       A5：关闭数据库连接。

这个嵌套结构统计的例子比较常见，相信很多人都遇到过，需要先拆解再分组计算，主要是熟悉 mongodb 对嵌套数据结构的处理。

2. 内嵌文档求和

对内嵌文档中的数据求和处理, 例如统计下面每条记录中 income，output 的数量和。
测试数据：

_id	income	output
1	｛"cpu":1000, "mem":500, "mouse":"100"｝	｛"cpu":1000, "mem":600 ,"mouse":"120"｝
2	｛"cpu":2000, "mem":1000, "mouse":"50","mainboard":500 ｝	{"cpu":1500, "mem":300}

期待统计结果：

_id	income	output
1	1600	1720
2	3550	1800

Mongodb脚本：

var fields = [ "income", "output"];
db.computer.aggregate([
   {
      $project:{
         "values":{
            $filter:{
               input:{
                  "\$objectToArray":"$$ROOT"
               },
               cond:{
                  $in:[
                     "$$this.k",
                     fields
                  ]
               }
            }
         }
      }
   },
   {
      \$unwind:"$values"
   },
   {
      $project:{
         key:"$values.k",
         values:{
            "$sum":{
               "$let":{
                  "vars":{
                     "item":{
                        "\$objectToArray":"$values.v"
                     }
                  },
                  "in":"$$item.v"
               }
            }
         }
      }
   },
   {$sort: {"_id":-1}},
   { "$group": {
    "_id": "$_id",
    'income':{"\$first": "$values"},
    "output":{"\$last": "$values"}
    }},
]);

filter将income，output 部分信息存放到数组中，用 unwind 拆解成记录，再累计各项值求和，按 _id 分组合并数据。

SPL脚本：

	A	B
1	=mongo_open("mongodb://127.0.0.1:27017/raqdb")
2	=mongo_shell(A1,"computer.find()").fetch()
3	=A2.new(_id:ID,income.array().sum():INCOME,output.array().sum():OUTPUT)
4	>A1.close()

统计结果

ID	INCOME	OUTPUT
1	1600.0	1720.0
2	3550.0	1800.0

脚本说明：
      A1：连接数据库
      A2：获取 computer 表中的数据
      A3：将 income、output 字段中的数据分别转换成序列求和，再与 ID 组合生成新序表
      A4：关闭数据库连接。

获取子记录的字段值，然后求和，相对于 mongo 脚本简化了不少。这个内嵌文档与内嵌数组在组织结构上有点类似，不小心容易混淆，因此需要特别注意与上例中的 scroe 数组结构比较，写出的脚本有所不同。

3. 分段分组结构统计

统计各段内的记录数量。例如下面按销售量分段，统计各段内的数据量，数据如下：

_id	NAME	STATE	SALES
1	Ashley	New York	11000
2	Rachel	Montana	9000
3	Emily	New York	8800
4	Matthew	Texas	8000
5	Alexis	Illinois	14000

分段方法：0-3000;3000-5000;5000-7500;7500-10000;10000 以上。

期望结果：

Segment	number
3	3
4	2

Mongo 脚本

这个需求按条件分段分组，mongodb 没有提供对应的 api，实现起来有点繁琐，上面的程序是其中实现的一个例子参考，当然也可以写成其它实现形式。下面看看集算器脚本的实现。

SPL脚本：

	A	B
1	[3000,5000,7500,10000,15000]
2	=mongo_open("mongodb://127.0.0.1:27017/raqdb")
3	=mongo_shell(A2,"sales.find()").fetch()
4	=A3.groups(A1.pseg(int(~.SALES)):Segment;count(1): number)
5	>A2.close()

脚本说明：
       A1：定义 SALES 分组区间。
       A2：连接 mongodb 数据库。
       A3：获取 sales 表中的数据。
       A4：根据 SALES 区间分组统计员工数。其中函数 pseg()表示返回成员在序列中的区段序号，int() 表示转换成整数。
       A5：关闭数据库连接。

Mongodb脚本与 SPL 脚本都实现了预期的结果，但函数pseg 的使用让 SPL 脚本精简了不少。

4. 多字段分组统计

统计分类项下的总数及各子项数。下面统计按 addr 分类的 book 的数量以及其下不同 book 类型的数量。

addr	book
address1	book1
address2	book1
address1	book5
address3	book9
address2	book5
address2	book1
address1	book1
address15	book1
address4	book3
address5	book1
address7	book11
address1	book1

期望结果：

_id	Total	books	Count
address1	4	book1	3
		book5	1
address15	1	book1	1
address2	3	book1	2
		book5	1
address3	1	book9	1
address4	1	book3	1
address5	1	book1	1
address7	1	book11	1

Mongo脚本

db.books.aggregate([
    { "$group": {
        "_id": {
            "addr": "$addr",
            "book": "$book"
        },
        "bookCount": {"\$sum": 1}
    }},
    { "$group": {
        "_id": "$_id.addr",
        "books": {
            "$push": {
                "book": "$_id.book",
                "count": "$bookCount"
            },
        },
        "count": {"\$sum": "$bookCount"}
    }},
    {"$sort": { "count": -1} },
    { "$project": {
        "books": {"\$slice": [ "$books", 2] },
        "count": 1
    }}
]).pretty()

先按 addr,book 分组统计 book 数，再按 addr 分组统计 book 数，调整显示顺序。

SPL脚本 (books.dfx)：

	A	B
1	=mongo_open("mongodb://127.0.0.1:27017/raqdb")
2	=mongo_shell(A1,"books.find()")
3	=A2.groups(addr,book;count(book): Count)
4	=A3.groups(addr;sum(Count):Total)
5	=A3.join(addr,A4:addr,Total)	return A5
6	>A1.close()

计算结果：

Address	book	Count	Total
address1	book1	3	4
address1	book5	1	4
address15	book1	1	1
address2	book1	2	3
address2	book5	1	3
address3	book9	1	1
address4	book3	1	1
address5	book1	1	1
address7	book11	1	1

脚本说明：
        A1：连接 mongodb 数据库。
        A2：获取 books 表中的数据。
        A3：按 addr,book 分组统计 book 数。
        A4：再按 addr 分组统计 book 数。
        A5：将 A4 中的 Total 按 addr 关联后合并到序表中。
        B5: 返回序表 A5。
        A6：关闭数据库连接。

这个例子中的 SPL 脚本除了一如既往的精简清晰外，还显示了如何简单方便地与 Java 程序集成。

        在 Java 程序中如果要对 MongoDB 实现上面的分组统计功能，需要根据不同的需求重新一五一十地实现，比较麻烦的同时也不通用。而如果用集算器来实现就容易多了，集算器提供了 JDBC 驱动程序，支持在 Java 程序中用 JDBC 存储过程方式访问计算结果，调用方法与调用存储过程相同。（JDBC 具体配置参考《集算器教程》中的“JDBC基本使用”章节）
       Java 调用主要过程如下：
       public void testStudent (){
              Connection con = null;
              com.esproc.jdbc.InternalCStatement st;
       try{
             // 建立连接
            Class.forName("com.esproc.jdbc.InternalDriver");
             con= DriverManager.getConnection("jdbc:esproc:local://");
             //调用存储过程，其中books是 dfx 的文件名
             st =(com. esproc.jdbc.InternalCStatement)con.prepareCall("call books ()");
             //执行存储过程
             st.execute();
             // 获取结果集
             ResultSet rs = st.getResultSet();
              。。。。。。。
       catch(Exception e){
             System.out.println(e);
       }

可以看到，集算器的计算结果能够很方便地供 Java 应用程序使用。除了上面的调用方式，程序也可以修改成直接加载 SPL 脚本的函数，用 SPL 脚本文件名当参数来实现。同时，集算器也支持 ODBC 驱动，与其它支持 ODBC 的语言集成也与此类似。

简单总结一下，MongoDB 的聚合分组计算的操作与存储文档的结构息息相关，丰富的文档结构一方面有利于存储，同时数据查询展示也可以做到多样化，但另一方面也带来了 shell 脚本操作的复杂性，写起来比较不容易, 需要考虑的细节、步骤也比较多。通过上面这几个简单案例的分析比较，可以看到集算器 SPL 在实现分组统计方面能简化操作，降低难度，从而有效地帮助我们解决问题。

集算器(484) MongoDB(9) 分组统计(2) 内嵌文档求和(2)

MongoDB 分组统计

1. 内嵌数组结构的统计

2. 内嵌文档求和

3. 分段分组结构统计

4. 多字段分组统计

目录