mongoDB进行分组操作
一、$group 进行分组
1、每个职位的雇员人数：
db.getCollection('emp').aggregate(
[
{'$group'：{
‘_id’:'$job'，
job_count:{'$sum':1}
}
}
]
)
2、每个职位的总工资
db.getCollection('emp').aggregate(
[
{'$group'：{
‘_id’:'$job'，
job_salaryt:{'$sum':'$salary'}
}
}
]
)
3、每个职位的平均工资
db.getCollection('emp').aggregate(
[
{'$group'：{
‘_id’:'$job'，
job_salaryt:{'$sum':'$salary'}，
job_salary_avg:{'$avg':'$salary'}
}
}
]
)
4、每种职位的最高与最低工资
db.getCollection('emp').aggregate(
[
{'$group'：{
‘_id’:'$job'，
max_salaryt:{'$max':'$salary'}，
min_salary:{'$min':'$salary'}
}
}
]
)
5、每个职位的工资
db.getCollection('emp').aggregate(
[
{
‘$group’:{
'_id':'$job',
'salary_data':{'$push':'$salary'}
}
}
]
)
6、每个职位的人员
db.getCollection('emp').aggregate(
[
{
‘$group’:{
'_id':'$job',
'position_name':{'$addToSet':'$name'} //addToSet,如果有重复的人名，保留一个
}
}
]
)
二、$project 进行数据的规则显示
1、别名
db.getCollection('emp').aggregate(
[
{'$project':{
'_id':0
'职位':'$job',
'name':1
}
}
]
)
2、年薪
db.getCollection('emp').aggregate(
[
{
'$project'：{
'name':1,
'salary':{'年薪'：{'$multiply':['$salary',12]}}
}
}
]
)
3.判断职位，返回-1 或0
db.getCollection('emp').aggregate(
[
{'$project':{
'_id':0,
'name':1,
'职位':'$job',
'job':{'$strcasecmp':['$job','manager']}
}
}
]
)
三、$macth、$group、$project综合操作
db.getCollection('emp').aggregate（
[
{ $match:{
salary:{
{'$gte':1000,'$lte':30000}
}
}
}，
{
$project：{
{ '_id':1,
'name':1,
'salary':1,
'job':1
}
}
}，
{
$group:{
{'_id':'$job','count':{'$sum':1},'avg':{'$avg':'$salary'}}
}
}，
{sort:{'count':-1}}
]
）
四、$add ,$sum,$group,$sum,$match,$substr
db.photo.aggregate([{ $match: { siteId: { $in: datas.parks }, 'shootOn': { $gte: minT, $lte: maxT } } }, { $project: { siteId: 1, shootOn: 1, locationId: 1, saleNum: { $size: '$orderHistory' } } }, { $group: { _id: { year: { $year: { $add: ['$shootOn', 28800000] } }, month: { $month: { $add: ['$shootOn', 28800000] } }, day: { $dayOfMonth: { $add: ['$shootOn', 28800000] } }, location: '$locationId' }, count: { $sum: '$saleNum' } } }, { $project: { _id: 0, locationId: '$_id.location', saleInfo: { day: { '$concat': [{ $substr: ['$_id.year', 0, 4] }, '/', { $substr: ['$_id.month', 0, 2] }, '/', { $substr: ['$_id.day', 0, 2] }] }, count: '$count', } } }, { $group: { _id: '$locationId', saleInfo: { $addToSet: '$saleInfo' } } }])
db.order.aggregate([{
$match: {
siteId: {
$in: parks
},
'payInfo.payTime': {
$gte: minT,
$lte: maxT
},
'orderStatus.status':5
}
}, {
$group: {
_id: {
year: {
$year: {
$add: ['$payInfo.payTime', 28800000]
}
},
month: {
$month: {
$add: ['$payInfo.payTime', 28800000]
}
},
day: {
$dayOfMonth: {
$add: ['$payInfo.payTime', 28800000]
}
},
siteId: '$siteId'
},
count: {
$sum: 1
},
sales: {
$sum: '$charge'
}
}
}, {
$project: {
_id: 0,
count: '$count',
sales: '$sales',
siteId: '$_id.siteId',
day: {
'$concat': [{
$substr: ['$_id.year', 0, 4]
}, '/', {
$substr: ['$_id.month', 0, 2]
}, '/', {
$substr: ['$_id.day', 0, 2]
}]
}
}
}])
