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 * from b_test`;
+—-+——+——–+———-+———–+
| id | user | game | date | game_type |
+—-+——+——–+———-+———–+
| 1 | jack | game-1 | 20170826 | online |
mongodb和mysql结合
| 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 { "_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);
}
打印输出：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。