php7中使用mongoDB的聚合操作对数据进行分组求和统计操作

• 本文将介绍mongoDB使用aggregate对数据分组，求和。给出shell命令行写法，php7中的写法，也将给出相同数据结构mysql命令行写法。

• mongoDB collection a_test 中数据：

> db.a_test.find()
{ "_id" : ObjectId("59a2431b57416663f0330a99"), "name" : "jack", "age" : 16, "sex" : "male" }
{ "_id" : ObjectId("59a2432f57416663f0330a9a"), "name" : "lucy", "age" : 16, "sex" : "female" }
{ "_id" : ObjectId("59a2433c57416663f0330a9b"), "name" : "mike", "age" : 17, "sex" : "male" }
{ "_id" : ObjectId("59a2434857416663f0330a9c"), "name" : "lili", "age" : 18, "sex" : "female" }
{ "_id" : ObjectId("59a2782657416663f0330a9d"), "name" : "jane", "age" : 17, "sex" : "female" }

• mysql table a_test 中数据：

mysql> select * from a_test;
+----+------+------+--------+
| id | name | age  | sex    |
+----+------+------+--------+
|  3 | jack |   16 | male   |
|  4 | lucy |   16 | female |
|  5 | mike |   17 | male   |
|  6 | lili |   18 | female |
|  7 | jane |   17 | female |
+----+------+------+--------+

一、简单操作操作操作

1.对sex字段分组，然后对age字段求和
(a) mongodb：
shell 命令行：

> db.a_test.aggregate([{"$group":{"_id":{sex:"$sex"}, 'age_count':{'$sum':'$age'}}}])
{ "_id" : { "sex" : "female" }, "age_count" : 51 }
{ "_id" : { "sex" : "male" }, "age_count" : 33 }

php7:

        $pipe = array(
            array(
                '$group' => array(
                    '_id' => array('sex' => '$sex'),'age_count' => array('$sum' => '$age'),
                ),
            ),
            array(
                '$project' => array(
                    'sex' => '$_id.sex',
                    'age_count' => '$age_count',
                ),
            ),
        );
        $cursor = LogDB::selectCollection('a_test')->aggregate($pipe);
        foreach ($cursor as $value) {
            print_r($value);
        }

输出打印：

MongoDB\Model\BSONDocument Object
(
    [storage:ArrayObject:private] => Array
        (
            [_id] => MongoDB\Model\BSONDocument Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [sex] => female
                        )

                )

            [age_count] => 51
            [sex] => female
        )

)
MongoDB\Model\BSONDocument Object
(
    [storage:ArrayObject:private] => Array
        (
            [_id] => MongoDB\Model\BSONDocument Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [sex] => male
                        )

                )

            [age_count] => 33
            [sex] => male
        )

)

(b) mysql命令行：

mysql> select sex,sum(`age`) as `age_count` from `a_test` group by `sex`;
+--------+-----------+
| sex    | age_count |
+--------+-----------+
| female |        51 |
| male   |        33 |
+--------+-----------+

由上可见sex为female的age总和为：55，sex为male的age总和为：33

2.根据字段sex分组统计数据条数
(a) mongoDB shell:

> db.a_test.aggregate([{"$group":{"_id":{sex:"$sex"}, 'age_count':{'$sum':1}}}])
{ "_id" : { "sex" : "female" }, "age_count" : 3 }
{ "_id" : { "sex" : "male" }, "age_count" : 2 }

php7:

        $pipe = array(
            array(
                '$group' => array(
                    '_id' => array('sex' => '$sex'),'count' => array('$sum' => 1),
                ),
            ),
            array(
                '$project' => array(
                    'sex' => '$_id.sex',
                    'count' => '$count',
                ),
            ),
        );
        $cursor = LogDB::selectCollection('a_test')->aggregate($pipe);
        foreach ($cursor as $value) {
            print_r($value);
        }

打印输出：

MongoDB\Model\BSONDocument Object
(
    [storage:ArrayObject:private] => Array
        (
            [_id] => MongoDB\Model\BSONDocument Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [sex] => female
                        )

                )

            [count] => 3
            [sex] => female
        )

)
MongoDB\Model\BSONDocument Object
(
    [storage:ArrayObject:private] => Array
        (
            [_id] => MongoDB\Model\BSONDocument Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [sex] => male
                        )

                )

            [count] => 2
            [sex] => male
        )

)

(b) mysql 命令行：

mysql> select sex,count(*) as `count` from `a_test` group by `sex`;
+--------+-------+
| sex    | count |
+--------+-------+
| female |     3 |
| male   |     2 |
+--------+-------+

由上可见sex为female的数据一共有3条，为male的共有2条

二、下面我们使用mongoDB做更复杂一点点的操作。同样给出相同数据结构mysql的命令行。

• mongoDB collection b_test中数据：

> db.b_test.find()
{ "_id" : ObjectId("59a288eacc90a3fcee840637"), "user" : "jack", "game" : "game-1", "date" : 20170826, "game_type" : "online" }
{ "_id" : ObjectId("59a288f2cc90a3fcee840638"), "user" : "jack", "game" : "game-1", "date" : 20170826, "game_type" : "online" }
{ "_id" : ObjectId("59a2890ecc90a3fcee840639"), "user" : "jack", "game" : "game-2", "date" : 20170826, "game_type" : "alone" }
{ "_id" : ObjectId("59a28927cc90a3fcee84063a"), "user" : "mike", "game" : "game-1", "date" : 20170826, "game_type" : "online" }
{ "_id" : ObjectId("59a28938cc90a3fcee84063b"), "user" : "lili", "game" : "game-3", "date" : 20170820, "game_type" : "online" }

• mysql table b_test中数据：
  ``shell
mysql> select * fromb_test`;
  +—-+——+——–+———-+———–+
  | id | user | game | date | game_type |
  +—-+——+——–+———-+———–+
  | 1 | jack | game-1 | 20170826 | online |
  | 2 | jack | game-1 | 20170826 | online |
  | 3 | jack | game-2 | 20170826 | alone |
  | 4 | mike | game-1 | 20170826 | online |
  | 5 | lili | game-3 | 20170820 | online |
  +—-+——+——–+———-+———–+

- 假设b_test是中用户游戏的登录日志。mysql和mongodb中date字段都为int类型，当然一般情况下存时间戳。

1.统计20170820以后每个游戏登录用户总数（同一帐号重复登录只算一次）
(a)mongoDB shell：   

先找出20170820以后的用户并去重复：   

db.b_test.aggregate([{'$match':{'date':{'$gt':20170820}}}, {'$group':{'_id':{'user':'$user','game':'$game'}}}]);
{ "_id" : { "user" : "mike", "game" : "game-1" } }
{ "_id" : { "user" : "jack", "game" : "game-2" } }
{ "_id" : { "user" : "jack", "game" : "game-1" } }

然后按照游戏统计:

> db.b_test.aggregate([{'$match':{'date':{'$gt':20170820}}}, {'$group':{'_id':{'user':'$user','game':'$game'}}},{'$group':{'_id':{'game':'$_id.game'}, count:{'$sum':1}}}]);
{ "_id" : { "game" : "game-2" }, "count" : 1 }
{ "_id" : { "game" : "game-1" }, "count" : 2 }

php7：

        $pipe = array(
            array(
                '$match' => array(
                    'date' => array('$gt' => 20170820),
                ),
            ),
            array(
                '$group' => array(
                    '_id' => array('user' => '$user', 'game' => '$game'),
                ),
            ),
            array(
                '$group' => array(
                    '_id' => array('game' => '$_id.game'),
                    'count' => array('$sum' => 1),
                ),
            ),
            array(
                '$project' => array(
                    'game' => '$_id.game',
                    'count' => '$count',
                ),
            ),
        );
        $cursor = LogDB::selectCollection('b_test')->aggregate($pipe);
        foreach ($cursor as $value) {
            print_r($value);
        }

打印输出：

MongoDB\Model\BSONDocument Object
(
    [storage:ArrayObject:private] => Array
        (
            [_id] => MongoDB\Model\BSONDocument Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [game] => game-2
                        )

                )

            [count] => 1
            [game] => game-2
        )

)
MongoDB\Model\BSONDocument Object
(
    [storage:ArrayObject:private] => Array
        (
            [_id] => MongoDB\Model\BSONDocument Object
                (
                    [storage:ArrayObject:private] => Array
                        (
                            [game] => game-1
                        )

                )

            [count] => 2
            [game] => game-1
        )

)

(b) mysql 命令行
先对去重复：

mysql> select * from b_test  where `date` > 20170820 group by `user`,`game`;
+----+------+--------+----------+-----------+
| id | user | game   | date     | game_type |
+----+------+--------+----------+-----------+
|  1 | jack | game-1 | 20170826 | online    |
|  3 | jack | game-2 | 20170826 | alone     |
|  4 | mike | game-1 | 20170826 | online    |
+----+------+--------+----------+-----------+

然后统计各个游戏总人数：

mysql> select `game`,count(*) as `count`,`game_type` from (select * from b_test  where `date` > 20170820 group by `user`,`game`) as dictint group by `game`;
+--------+-------+-----------+
| game   | count | game_type |
+--------+-------+-----------+
| game-1 |     2 | online    |
| game-2 |     1 | alone     |
+--------+-------+-----------+

疑惑：mysql中可以select选择保留字段，mongodb可以通过project保留上次操作后的字段。二（1）中如何保留字段game_type？尝试了一下，知道的朋友请留言。谢谢！