集合数据为嵌套列表结构,对嵌套列表中的值计算后取最大值的记录。如有集合 scores 记录学生的各科成绩,查询平均成绩最好的学生,其数据如下:
{
"StudentID":"S0001", "Scores":[{"Course":"Chinese","Score":75},{"Course":"Maths","Score":81}, {"Course":"English","Score":78}] } { "StudentID":"S0002", "Scores":[{"Course":"Chinese","Score":80},{"Course":"Maths","Score":78}, {"Course":"English","Score":76}] } { "StudentID":"S0001", "Scores":[{"Course":"Chinese","Score":78},{"Course":"Maths","Score":75}, {"Course":"English","Score":72}] } |
MongoDB实现思路是:aggregate下用 $unwind 对Scores展开成行,按 StudentID 分组求平均成绩avg_score,再按avg_score分组统计学生,根据avg_score排序取最好成绩的学生号记录,再用 $unwind 对学生号展开,最后显示记录。
使用集算器, 根据最好的平均成绩返回记录,实现比较容易。
1、编写SPL脚本 scores.dfx:
A | B | |
---|---|---|
1 | =mongo_open("mongodb://localhost:27017/raqdb") | / 连接 MongoDB 数据库 |
2 | =mongo |