MONGODB Multi-collection multi-table statistics combat
Complete the following reports:
Table person, Appointmentrecord number of people involved
Using aggregate functions in the person table
Db. Person.aggregate () on the line
[
{
$match: {
' dateOfBirth ': {
$gt: ' 1986-12-02 ',
$lt: ' 1996-12-02 ',
}
}
}, {
$group: {
_id: ' $SEX ',
count: {
$sum: 1
},
}
},
]
statistics frequently hung department
Departments and users are associated via UserID
How do I manage two collections?
-person has 10.58 million data shards sharded = True
-Appointmentrecord has about 3 million data, no shards.
Logic is simple.
* 1. Filter the registered records according to the user
* 2. Calculate the number in the registered records according to the hospital and Department group
* 3. Select the number of the first 10 of the Grouping error scheme one with aggregate function $lookup
Db. Appointmentrecord.aggregate ([
{//+ limit is to be tested with small data
$limit: 10000
},{
$lookup: {from
: ' Person ',
Localfield: "userid",
Foreignfield: "userid", as
: "Ar_docs",
}
},
//{' arr ': {' $ Elemmatch ': {' addr ': ' DDR2 ', ' dept ': ' Oo '}}}
{
$match: {
' Ar_docs ': {'
$elemMatch ': {
' Channelcode ': ' WECHAT '}}
, {
$group: {
_id: {
hospitalcode: ' $hospitalCode ',
Hospitalname: ' $hospitalName ',
LOC: ' $cTLOCDesc ',
},
count: {
$sum: 1
},
}
}, {
$sort: {
count:-1
}
}, {
$limit: Ten
},
])
Error because lookup does not support a collection of shards. Error Scheme II with MapReduce
MapReduce does not directly support multiple sets of operations unless you use DBREF.
Our watch is not dbref.
Then try out the variables it can accept not
var userArr2 = [];
Db. Paperson.find ({' Idcard ': {$ne: null}, $where: function () {
var birth = this.iDCard.substr (6,8);
if (Birth > 19861202 && Birth < 19961202) {
return true;
}
}},{
urhospitalid:1,
Userid:1,
_id:0
}). (). ForEach ((it) = {
Userarr2.push (It.userid + '-' + it.urhospitalid);
});
print (userarr2.length)
var mapfun = function () {
if (userarr2.indexof (This.userid + '-' + This.urhospitalid)!==-1) {
emit (This.hospitalname + "-" + this.ctlocdesc,1);
}
};
Db. Appointmentrecord.mapreduce (
mapfun,//mapfunction
function (key, values) {
return array.sum (values) ;
},//reducefunction
{
limit:10000,
query:{' channelcode ': ' WECHAT '}, out
: "Output_user2 _loc "
})
The result or failure map method can not receive the outside variables, in which to write the query db this keyword can not be found. error Scenario Three creating a new table with aggregations
Lookup does not support sharding then I'll build a filter person table to insert a new collection OK:
Db. Person.find ({' Idcard ': {$ne: null}, $where: function () {
var birth = this.iDCard.substr (6,8);
if (Birth > 19661202 && Birth < 19761202) {
return true;
}
}},{
urhospitalid:1,
Userid:1
}). Limit (batchsize) (80000). ForEach (function (it) {
db.xtemp_user4.insert (it);//40 years old + is User4
});
The new collection is set up soon enough.
Well, then, let's converge.
Db. Appointmentrecord.aggregate ([
{$limit: 10000},
{
$match: {
' channelcode ': ' WECHAT ',
}
},
{
$lookup: {from
: "Xtemp_user4",
Localfield: "userid",
Foreignfield: "userid", as
: "Ar_ Docs ",
}
},
{
$match: {
' Ar_docs ': {$elemMatch: {' userid ': {$ne: null}},
}
},
{
$group: {
_id: {
hospitalcode: ' $hospitalCode ',
hospitalname: ' $hospitalName ',
LOC: ' $cTLOCDesc ',
},
count: {
$sum: 1
},
}
}, {
$sort: {
count:-1
}
},
{
$limit: Ten
},
])
The small data test also passed.
However, no results were performed overnight. Scenario Four
In
var parr4 = db.xtemp_user4.distinct ("userid");
Db. Appointmentrecord.aggregate ([
//{$limit: $},
{
$match:
{' channelcode ': ' WECHAT ', ' userid ': {$in : PARR4}}
,
{
$group: {
_id: {
LOC: ' $cTLOCDesc ',
hospitalcode: ' $hospitalCode ',
Hospitalname: ' $hospitalName ',
},
count: {
$sum: 1
},
}
}, {
$sort: {
count :-1
}
},
{
$limit: Ten
},
])
Only 50s around the results came out.
Because MongoDB contact is not much engaged in a few days to do well. Check a lot of blog many official website information, MongoDB version of some functions do not support the pit will not say. See the example is not a lot to write this blog hope that everyone less step on the pit.
There are better solutions to welcome feedback.
By the right, the above code runs in MongoDB 3.2.9.
Add batchsize because the cursor timeout is not reported.