title: MongoDB 实现行级别的聚合函数（类似于Oracle的开窗函数）
tags: []
notebook: test note book
场景： 显示每个班级每个人每科的分数，班级的平均分，班级排名等聚合列
row data 为每个人的名字,年级,科目,分数.要求统计出每个人的成绩总分,单科成绩排名,总分成绩排名,班级成绩排名.并且显示在每一行上.
测试数据
db.score.insert({ "_id":"1", "name":"test1", "subject":"语文", "score":70, "class":"四年级"});
db.score.insert({ "_id":"2", "name":"test2", "subject":"语文", "score":80, "class":"四年级"});
db.score.insert({ "_id":"3", "name":"test3", "subject":"语文", "score":59, "class":"四年级"});
db.score.insert({ "_id":"4", "name":"test4", "subject":"语文", "score":78, "class":"五年级"});
db.score.insert({ "_id":"5", "name":"test5", "subject":"语文", "score":99, "class":"五年级"});
db.score.insert({ "_id":"6", "name":"test6", "subject":"语文", "score":67, "class":"五年级"});
db.score.insert({ "_id":"7", "name":"test1", "subject":"数学", "score":56, "class":"四年级"});
db.score.insert({ "_id":"8", "name":"test2", "subject":"数学", "score":78, "class":"四年级"});
db.score.insert({ "_id":"9", "name":"test3", "subject":"数学", "score":57, "class":"四年级"});
db.score.insert({ "_id":"10", "name":"test4", "subject":"数学", "score":88, "class":"五年级"});
db.score.insert({ "_id":"11", "name":"test5", "subject":"数学", "score":69, "class":"五年级"});
db.score.insert({ "_id":"12", "name":"test6", "subject":"数学", "score":47, "class":"五年级"});
db.score.insert({ "_id":"13", "name":"test1", "subject":"英语", "score":79, "class":"四年级"});
db.score.insert({ "_id":"14", "name":"test2", "subject":"英语", "score":66, "class":"四年级"});
db.score.insert({ "_id":"15", "name":"test3", "subject":"英语", "score":88, "class":"四年级"});
db.score.insert({ "_id":"16", "name":"test4", "subject":"英语", "score":97, "class":"五年级"});
db.score.insert({ "_id":"17", "name":"test5", "subject":"英语", "score":68, "class":"五年级"});
db.score.insert({ "_id":"18", "name":"test6", "subject":"英语", "score":77, "class":"五年级"});
先给出查询语句,再一步步分析
db.score.aggregate([
{
$sort:{
score:-1
}
},
{
$group:{
_id:{
subject:"$subject",
class:"$class"
},
avgScore:{
$avg:"$score"
},
sumScore:{
$sum:"$score"
},
rowData:{
$push:"$$ROOT"
}
}
},
{
$unwind:{
path:"$rowData",
preserveNullAndEmptyArrays:true,
includeArrayIndex:"rangIndex"
}
},
{
$group:{
_id:"$rowData.name",
totalScore:{
$sum:"$rowData.score"
},
rowData:{
$push:"$$ROOT"
}
}
},
{
$unwind:{
path:"$rowData",
preserveNullAndEmptyArrays:true,
}
},
{
$project:{
_id:0,
姓名:"$_id",
年级:"$rowData._id.class",
科目:"$rowData._id.subject",
成绩:"$rowData.rowData.score",
总分:"$totalScore",
年级科目成绩排名:{$add:["$rowData.rangIndex",1]},
年级科目平均分:"$rowData.avgScore",
年级科目总分:"$rowData.sumScore"
}
}
]);
查询结果
| 姓名 | 年级 | 科目 | 成绩 | 总分 | 科目层级年级排名 | 年级科目平均分 | 年级科目总分 |
|---|---|---|---|---|---|---|---|
| test5 | 五年级 | 数学 | 69 | 236 | 2 | 68 | 204 |
| test5 | 五年级 | 语文 | 99 | 236 | 1 | 81 | 244 |
| test5 | 五年级 | 英语 | 68 | 236 | 3 | 80 | 242 |
| test6 | 五年级 | 数学 | 47 | 191 | 3 | 68 | 204 |
| test6 | 五年级 | 语文 | 67 | 191 | 3 | 81 | 244 |
| test6 | 五年级 | 英语 | 77 | 191 | 2 | 80 | 242 |
| test4 | 五年级 | 数学 | 88 | 263 | 1 | 68 | 204 |
| test4 | 五年级 | 语文 | 78 | 263 | 2 | 81 | 244 |
| test4 | 五年级 | 英语 | 97 | 263 | 1 | 80 | 242 |
| test1 | 四年级 | 数学 | 56 | 205 | 3 | 63 | 191 |
| test1 | 四年级 | 语文 | 70 | 205 | 2 | 69 | 209 |
| test1 | 四年级 | 英语 | 79 | 205 | 2 | 77 | 233 |
| test3 | 四年级 | 数学 | 57 | 204 | 2 | 63 | 191 |
| test3 | 四年级 | 语文 | 59 | 204 | 3 | 69 | 209 |
| test3 | 四年级 | 英语 | 88 | 204 | 1 | 77 | 233 |
| test2 | 四年级 | 数学 | 78 | 224 | 1 | 63 | 191 |
| test2 | 四年级 | 语文 | 80 | 224 | 1 | 69 | 209 |
| test2 | 四年级 | 英语 | 66 | 224 | 3 | 77 | 233 |
查询语句分析
{
$sort:{
score:-1
}
},
先按分数从高到低讲所有数据排序,目的是为了之后方便算出科目层级年级排名.
{
$group:{
_id:{
subject:"$subject",
class:"$class"
},
avgScore:{
$avg:"$score"
},
sumScore:{
$sum:"$score"
},
rowData:{
$push:"$$ROOT"
}
}
},
-
按年级和科目进行分组,目的是算出科目平均分和科目的总分.此处的rowData用了$group函数的子函数$push,$push的作用是进行分组时,将原始数据塞入到一个数组里面,有点类似于$unwind的反过来的用法.因为我们之后还是要用到row level 的数据.
-
"$$ROOT" 的意思是将整个目录push到数组里面, 这里也可以push自己想要的fields
rowData:{
$push:{
name:"$name",
score:"$score"
}
}
{
$unwind:{
path:"$rowData",
preserveNullAndEmptyArrays:true,
includeArrayIndex:"rangIndex"
}
},
unwind的作用是将数组解压,那么我们即又可以得到row level 的数据了,此时每一行的数据也包含sumScore,avgScoure 属性了.而这里的 includeArrayIndex属性的意思是在unwind时,给每条记录加一个rangIndex属性,属性的内容是行号,有点类似于oracle的rownumber.要注意的是这里的includeArrayIndex是每个组的行号,也就是换一组就又会从0开始,而且是默认排序,到这里也就会明白为什么我会在最前面加一个$sort函数先排好序.
{
$group:{
_id:"$rowData.name",
totalScore:{
$sum:"$rowData.score"
},
rowData:{
$push:"$$ROOT"
}
}
},
{
$unwind:{
path:"$rowData",
preserveNullAndEmptyArrays:true,
}
},
和上面的group类似,此处作用是算出个人的总成绩,再unwind 出row level data
{
$project:{
_id:0,
姓名:"$_id",
年级:"$rowData._id.class",
科目:"$rowData._id.subject",
成绩:"$rowData.rowData.score",
总分:"$totalScore",
年级科目成绩排名:{$add:["$rowData.rangIndex",1]},
年级科目平均分:"$rowData.avgScore",
年级科目总分:"$rowData.sumScore"
}
}
显示出需要的结果.这里的年级科目成绩排名稍微处理了一下,每个结果都+1,因为index是从0开始的
我们还可以计算
- 成绩类别 不及格/及格/良好/优秀
- 班级人数统计
- 总成绩排名
- 结果按年级,科目,年级科目成绩排名升序排名
有兴趣的可以根据测试数据进行查询语句的改写,算是巩固练习.
网友评论