php7中使⽤mongoDB的聚合操作对数据进⾏分组求和统计操作
本⽂将介绍mongoDB使⽤aggregate对数据分组，求和。给出shell命令⾏写法，php7中的写法，也将给出相同数据结构mysql命令⾏写法。
mongoDB collection a_test 中数据：
> db.a_test.find()
{ "_id" : ObjectId("59a2431b57416663f0330a99"), "name" : "jack", "age" : 16, "x" : "male" }
关于成长的素材
{ "_id" : ObjectId("59a2432f57416663f0330a9a"), "name" : "lucy", "age" : 16, "x" : "female" }
{ "_id" : ObjectId("59a2433c57416663f0330a9b"), "name" : "mike", "age" : 17, "x" : "male" }
{ "_id" : ObjectId("59a2434857416663f0330a9c"), "name" : "lili", "age" : 18, "x" : "female" }
{ "_id" : ObjectId("59a2782657416663f0330a9d"), "name" : "jane", "age" : 17, "x" : "female" }
mysql table a_test 中数据：
mysql> lect * from a_test;
+----+------+------+--------+
| id | name | age | x |
+----+------+------+--------+
| 3 | jack | 16 | male |
| 4 | lucy | 16 | female |
| 5 | mike | 17 | male |
| 6 | lili | 18 | female |
| 7 | jane | 17 | female |
+----+------+------+--------+
⼀、简单操作操作操作
1.对x字段分组，然后对age字段求和
(a) mongodb：
shell 命令⾏：
> db.a_test.aggregate([{"$group":{"_id":{x:"$x"}, 'age_count':{'$sum':'$age'}}}])
{ "_id" : { "x" : "female" }, "age_count" : 51 }
{ "_id" : { "x" : "male" }, "age_count" : 33 }
php7:
$pipe = array(
array(
'$group' => array(
'_id' => array('x' => '$x'),'age_count' => array('$sum' => '$age'),
),
)
,
array(
'$project' => array(
'x' => '$_id.x',
'age_count' => '$age_count',
),
),
);
$cursor = LogDB::lectCollection('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
(
[x] => female
)
)
[age_count] => 51须组词有哪些
托福试卷[x] => female
)
)
MongoDB\Model\BSONDocument Object
(
[storage:ArrayObject:private] => Array
(
[_id] => MongoDB\Model\BSONDocument Object
(
[storage:ArrayObject:private] => Array
(
[x] => male
)
)
[age_count] => 33
[x] => male
)
)
(b) mysql命令⾏：
mysql> lect x,sum(`age`) as `age_count` from `a_test` group by `x`;
+--------+-----------+
| x | age_count |
+--------+-----------+
| female | 51 |
| male | 33 |
+--------+-----------+
由上可见x为female的age总和为：55，x为male的age总和为：33
2.根据字段x分组统计数据条数
(a) mongoDB shell:
> db.a_test.aggregate([{"$group":{"_id":{x:"$x"}, 'age_count':{'$sum':1}}}])
{ "_id" : { "x" : "female" }, "age_count" : 3 }
{ "_id" : { "x" : "male" }, "age_count" : 2 }
php7:
$pipe = array(
array(
'$group' => array(
'_id' => array('x' => '$x'),'count' => array('$sum' => 1), ),
),
array(
'$project' => array(
'x' => '$_id.x',
'count' => '$count',
)
,
),
);
$cursor = LogDB::lectCollection('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
(
[x] => female
)
)
[count] => 3
[x] => female
)
)
MongoDB\Model\BSONDocument Object
(
[storage:ArrayObject:private] => Array
(
[_id] => MongoDB\Model\BSONDocument Object
(
[storage:ArrayObject:private] => Array
(
[x] => male
)
)
[count] => 2
[x] => male
)
)
(b) mysql 命令⾏：
mysql> lect x,count(*) as `count` from `a_test` group by `x`;
+--------+-------+
| x | count |
+--------+-------+
| female | 3 |
| male | 2 |
爆炒猪心
+--------+-------+
由上可见x为female的数据⼀共有3条，为male的共有2条
⼆、下⾯我们使⽤mongoDB做更复杂⼀点点的操作。同样给出相同数据结构mysql的命令⾏。
mongoDB collection b_test中数据：
七夕单身> db.b_test.find()
{ "_id" : ObjectId("59a288eacc90a3fcee840637"), "ur" : "jack", "game" : "game-1", "date" : 20170826, "game_type" : "online"}
{ "_id" : ObjectId("59a288f2cc90a3fcee840638"), "ur" : "jack", "game" : "game-1", "date" : 20170826, "game_type" : "online"}
{ "_id" : ObjectId("59a2890ecc90a3fcee840639"), "ur" : "jack", "game" : "game-2", "date" : 20170826, "game_type" : "alone"}
{ "_id" : ObjectId("59a28927cc90a3fcee84063a"), "ur" : "mike", "game" : "game-1", "date" : 20170826, "game_type" : "online"}
{ "_id" : ObjectId("59a28938cc90a3fcee84063b"), "ur" : "lili", "game" : "game-3", "date" : 20170820, "game_type" : "online"}
mysql table b_test中数据：
``shell
mysql> lect * from b_test`;
+—-+——+——–+———-+———–+
| id | ur | 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':{'ur':'$ur','game':'$game'}}}]);
{ "_id" : { "ur" : "mike", "game" : "game-1" } }
{ "_id" : { "ur" : "jack", "game" : "game-2" } }
{ "_id" : { "ur" : "jack", "game" : "game-1" } }
然后按照游戏统计:
> db.b_test.aggregate([{'$match':{'date':{'$gt':20170820}}}, {'$group':{'_id':{'ur':'$ur','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('ur' => '$ur', 'game' => '$game'), ),
),
array(
'$group' => array(
集体利益'_id' => array('game' => '$_id.game'),
'count' => array('$sum' => 1),
),
),
array(
'$project' => array(
'game' => '$_id.game',
'count' => '$count',
),
),
);
$cursor = LogDB::lectCollection('b_test')->aggregate($pipe);
foreach ($cursor as $value) {
print_r($value);
}
打印输出：
