【问题】
Hi All,
Thanks for the wonderful support the community gets from this forum.
I am trying to accomplish this in MongoDB. Didn’t think it could get this complicated. thought the problem was interesting to solve.
I am trying to get a count of students by scores for various subjects.
the example below shows 2 subjects, in reality we could run this ad-hoc query for 1 or more subjects. (so cannot “can” and should be real-time)
(for e.g. run the query for a bunch of schools and a bunch of subjects)
the grades are always 1-5 no decimal point
use students
db.studentsummary.insert ({school:‘atl1’, sname : ‘Sean’ , sub1: 4, sub2 :5})
db.studentsummary.insert ({school:‘atl1’, sname : ‘chris’ , sub1: 4, sub2 :3})
db.studentsummary.insert ({school:‘atl1’, sname : ‘becky’ , sub1: 5, sub2 :4})
db.studentsummary.insert ({school:‘atl1’, sname : ‘sam’ , sub1: 5, sub2 :4})
db.studentsummary.insert ({school:‘atl2’, sname : ‘dustin’ , sub1: 2, sub2 :2})
db.studentsummary.insert ({school:‘atl2’, sname : ‘greg’ , sub1: 3, sub2 :4})
db.studentsummary.insert ({school:‘atl2’, sname : ‘peter’ , sub1: 5, sub2 :1})
db.studentsummary.insert ({school:‘atl2’, sname : ‘brad’ , sub1: 2, sub2 :2})
db.studentsummary.insert ({school:‘atl2’, sname : ‘liz’ , sub1: 3, sub2 :null})
Desired Output:(Would like to see how close we could get to the desired output below)
show how many got a 5; how many got a 4 and so on…
I tried quite a bit - trying to group by each subject and run different pipelines based on the subjects chosen for query and let the front end manage the merge and pivot , performance was unacceptable. what not.
help will be very highly appreciated.
【回答】
Mongodb 不直接支持行转列,枚举分组做起来也很麻烦,要把数据读出来再用 Java、Php 等编程语言来实现;还涉及到动态列,实现这样的集合运算也很麻烦。使用 SPL 完成这个需求更容易。以下例子不使用 MongoDB 展现,如果使用 MongoDB 需要用 SPL 的 mongo_open()函数连接数据库,用 mongo_shell() 函数查询原始数据结构
A |