先数据准备
将数据插入Mongodb集合collection;集合相当于Mysql的Table
db.getCollection("collection").insert( {
_id: ObjectId("5df0c634529899671d56e287"),
id: "5761597",
"record_id": "700f98e8-6cef-478f-ad59-3d79326e2a97",
"user_id": "112324551",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-11 18:34:28",
title: "告白气球",
score1: "0.000000",
player: "周杰伦",
"order_id": "DC11588089828288",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df0c634529899671d56e28a"),
id: "5761598",
"record_id": "700f98e8-6cef-478f-ad59-3d79326e2a97",
"user_id": "112319823",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-11 18:34:28",
title: "告白气球",
score1: "0.000000",
player: "周杰伦",
"order_id": "DC11588089828288",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df0c635529899671d56e28d"),
id: "5761599",
"record_id": "700f98e8-6cef-478f-ad59-3d79326e2a97",
"user_id": "3294736",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-11 18:34:29",
title: "告白气球",
score1: "0.000000",
player: "周杰伦",
"order_id": "DC11588089828288",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1b78352989971f8160e30"),
id: "5761646",
"record_id": "2d6bf03d-06c6-48ec-8476-6334866c8740",
"user_id": "112324551",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 11:44:03",
title: "告白气球",
score1: "51.861290",
player: "周杰伦",
"order_id": "DC12212142635393",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1b78352989971f8160e33"),
id: "5761647",
"record_id": "2d6bf03d-06c6-48ec-8476-6334866c8740",
"user_id": "3294736",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 11:44:03",
title: "告白气球",
score1: "51.861290",
player: "周杰伦",
"order_id": "DC12212142635393",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1b78452989971f8160e36"),
id: "5761648",
"record_id": "2d6bf03d-06c6-48ec-8476-6334866c8740",
"user_id": "112319823",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 11:44:04",
title: "告白气球",
score1: "51.861290",
player: "周杰伦",
"order_id": "DC12212142635393",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1b7fe529899161003532f"),
id: "5761649",
"record_id": "cfb15da6-4be5-48b4-829c-bed84c7bbf39",
"user_id": "3294736",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 11:46:06",
title: "告白气球",
score1: "27.942974",
player: "周杰伦",
"order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1b7fe5298991610035332"),
id: "5761650",
"record_id": "cfb15da6-4be5-48b4-829c-bed84c7bbf39",
"user_id": "112319823",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 11:46:06",
title: "告白气球",
score1: "27.942974",
player: "周杰伦",
"order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1b8e052989967c757dcf0"),
id: "5761651",
"record_id": "000fba24-dea1-4020-be32-56307c7e1159",
"user_id": "3294736",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 11:49:52",
title: "告白气球",
score1: "28.129450",
player: "周杰伦",
"order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1b8e052989967c757dcf3"),
id: "5761652",
"record_id": "000fba24-dea1-4020-be32-56307c7e1159",
"user_id": "112319823",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 11:49:52",
title: "告白气球",
score1: "28.129450",
player: "周杰伦",
"order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1b8e152989967c757dcf6"),
id: "5761653",
"record_id": "000fba24-dea1-4020-be32-56307c7e1159",
"user_id": "112324599",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 11:49:53",
title: "告白气球",
score1: "28.129450",
player: "周杰伦",
"order_id": "DC12216569151223",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1dfd252989967cf2a22ef"),
id: "5761665",
"record_id": "801e9458-963d-45c4-9cce-3750a1263936",
"user_id": "112324599",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 14:36:02",
title: "告白气球",
score1: "27.686806",
player: "周杰伦",
"order_id": "DC12322420857755",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1dfd252989967cf2a22f2"),
id: "5761666",
"record_id": "801e9458-963d-45c4-9cce-3750a1263936",
"user_id": "112319823",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 14:36:02",
title: "告白气球",
score1: "27.686806",
player: "周杰伦",
"order_id": "DC12322420857755",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1dfd352989967cf2a22f5"),
id: "5761667",
"record_id": "801e9458-963d-45c4-9cce-3750a1263936",
"user_id": "112318769",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 14:36:03",
title: "告白气球",
score1: "27.686806",
player: "周杰伦",
"order_id": "DC12322420857755",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df1dfd352989967cf2a22f8"),
id: "5761668",
"record_id": "801e9458-963d-45c4-9cce-3750a1263936",
"user_id": "112318751",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 14:36:03",
title: "告白气球",
score1: "27.686806",
player: "周杰伦",
"order_id": "DC12322420857755",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df2258f5298996e4e5bb886"),
id: "5761713",
"record_id": "32f4b52a-2ecd-4042-8cce-1ebbfdd21273",
"user_id": "3065878",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 19:33:35",
title: "告白气球",
score1: "27.806236",
player: "周杰伦",
"order_id": "DC12490227249516",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df225905298996e4e5bb889"),
id: "5761714",
"record_id": "32f4b52a-2ecd-4042-8cce-1ebbfdd21273",
"user_id": "112324669",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 19:33:36",
title: "告白气球",
score1: "27.806236",
player: "周杰伦",
"order_id": "DC12490227249516",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df225905298996e4e5bb88c"),
id: "5761715",
"record_id": "32f4b52a-2ecd-4042-8cce-1ebbfdd21273",
"user_id": "112324551",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-12 19:33:36",
title: "告白气球",
score1: "27.806236",
player: "周杰伦",
"order_id": "DC12490227249516",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df32bf75298996db377c0bf"),
id: "5761803",
"record_id": "c4fb33e8-8f52-4a1f-b988-dd343d0a183f",
"user_id": "3065878",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-13 14:13:11",
title: "告白气球",
score1: "28.435293",
player: "周杰伦",
"order_id": "DC13170117860737",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df32bf75298996db377c0c2"),
id: "5761804",
"record_id": "c4fb33e8-8f52-4a1f-b988-dd343d0a183f",
"user_id": "112324669",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-13 14:13:11",
title: "告白气球",
score1: "28.435293",
player: "周杰伦",
"order_id": "DC13170117860737",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df34b39529899671d56e4ea"),
id: "5761895",
"record_id": "ed8c770e-3d29-4dd4-9b6f-0a01048f0e1b",
"user_id": "112324669",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-13 16:26:33",
title: "告白气球",
score1: "27.078077",
player: "周杰伦",
"order_id": "DC13213360874927",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df34b39529899671d56e4ed"),
id: "5761896",
"record_id": "ed8c770e-3d29-4dd4-9b6f-0a01048f0e1b",
"user_id": "112324599",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-13 16:26:33",
title: "告白气球",
score1: "27.078077",
player: "周杰伦",
"order_id": "DC13213360874927",
} );
db.getCollection("collection").insert( {
_id: ObjectId("5df34b39529899671d56e4f0"),
id: "5761897",
"record_id": "ed8c770e-3d29-4dd4-9b6f-0a01048f0e1b",
"user_id": "3065878",
file: "周杰伦/告白气球$hd18817@4242(原+).b2a.mkv",
timestamp: "2019-12-13 16:26:33",
title: "告白气球",
score1: "27.078077",
player: "周杰伦",
"order_id": "DC13213360874927",
} );
最终目的是title为【告白气球】根据数据中的record_id GROUP分组按分数从高到低,并且user_id不能重复
第一次group查出数据后,发现user_id也有重复的,所以再GROUP了一次。不知道你们有没有更好的办法可以交流一番。。。
先看看原生的MongoDB写法:
//colection 名为 colection ,自己据实况修改
db.collection.aggregate([
{
$match: {
'title': '告白气球'
}
},
{
$group: {
_id: {
record_id: '$record_id'
}
,doc: {$first: '$$ROOT'}
,
numberOfCount: {
$sum: 1
}
}
},
{//第一次的结果排序
$sort: {
'doc.score1': - 1
}
}
,
{
$group: {
_id: {
group2: '$doc.user_id'
}
,doc2: {$first: '$$ROOT'}
}
},
{
$sort: {
'doc2.doc.score1': - 1
}
}
,
]);
下面是php7写法
$command = [
'aggregate' => 'collection', //sing是你的集合
'pipeline' => [
[
'$match' => [
'title' => ['$eq' => $title],
]
],
[
'$group' => [
'_id' => [
'record_id' => '$record_id',
],
'coun' => ['$sum' => 1],
'doc' => ['$first' => '$$ROOT'],
]
],
[
'$sort' => ['doc.score1' => -1]
],
[
'$group' => [
'_id' => [
'group2' => '$doc.user_id',
],
'doc2' => ['$first' => '$$ROOT'],
]
],
[
'$sort' => ['doc2.doc.score1' => -1]
],
[
'$limit' => 3
]
],
'cursor' => new stdClass,
];
try{
$mogoInfo = mogoConnection();//方法下面有
$cmd = new MongoDB\Driver\Command($command);
$cursor = $mogoInfo['manager']->executeCommand('logs', $cmd);//logs是数据库 如第一张图
$temp = array();
$userIds = array();
foreach ($cursor as $key => $document) {
$score = $document->doc2->doc->score1;
$temp[$key]['id'] = $document->doc2->doc->id;
$temp[$key]['score2'] = round($score,2);
$temp[$key]['user_id'] = $document->doc2->doc->user_id;
$temp[$key]['record_id'] = $document->doc2->doc->record_id;
}
}catch (Exception $e){
//处理异常
}
//===================以上=============以上===================
//mongodb的链接方法
function mogoConnection(){
static $manager,$writeConcern;
$enArr = [
'devlop' =>['name'=>'开发','loghost'=>'192.168.0.1:27017'],
'product' =>['name'=>'线上','loghost'=>'192.168.0.1:27017'],
];
if(empty($manager)){
try{
//链接得语法可以参考[菜鸟教程](https://www.runoob.com/mongodb/mongodb-connections.html)
$manager = new MongoDB\Driver\Manager("mongodb://".$enArr['devlop']['loghost']);
//$manager = new \MongoDB\Driver\Manager("mongodb://username:password@192.168.0.1:27017");
$writeConcern = new MongoDB\Driver\WriteConcern(MongoDB\Driver\WriteConcern::MAJORITY, 1000);
} catch (Exception $e) {
return false;
}
}
$data['manager'] = $manager;
$data['writeConcern'] = $writeConcern;
$data['MongoDBConfig'] = $enArr['develop'];
return $data;
}
我们可以看到其实是用了两次GROUP,并排序
主要是$commond 的写法,容易出错,自己摸索,一脸懵逼,不喜勿喷
$command = [
'aggregate' => 'collection',
'pipeline' => [
[
'$match' => [
'title' => ['$eq' => $title]
]
],
[
'$group' => [
'_id' => [
'record_id' => '$record_id',
],
'coun' => ['$sum' => 1],
'doc' => ['$first' => '$$ROOT'],
]
],
[
'$sort' => ['doc.score1' => -1]
],
[
'$limit' => 3
]
],
'cursor' => new stdClass,
];
另外插入写法
$mogoInfo = mogoConnection();//上面写的一个方法
$inLog = [
'field1' => '',
'type' => '',
'field3' => '',
'datetime' => date('Y-m-d H:i:s'),
'serverip' => '',
'field5' => '',
'time_excute' => '',
'field7' => '',
'field8' => '',
'text' => '',
];
$bulk = new MongoDB\Driver\BulkWrite;
$bulk->insert($inLog);
try{
$result = $mogoInfo ['manager']->executeBulkWrite('logs.collection', $bulk, $mogoInfo['writeConcern']);
}catch(Exception $e){
//throw $e;
}
删除Collection
$get = $_REQUEST['collection']; // input:cccc
$command = [
'drop' => $get ,
];
$mogoInfo = mogoConnection();
$cmd = new MongoDB\Driver\Command($command);
$cursor = $mogoInfo['manager']->executeCommand('logs', $cmd);
foreach ($cursor as $document) {
var_dump($document);
}
//看起来虽然就几行代码;但到处找都找不到,百度了n久,简直RLG