行转列,动态列枚举分组

该博客讨论了在 MongoDB 中如何实现行转列和动态列枚举分组来统计学生分数。由于 MongoDB 不直接支持这种操作,作者提出了使用 Java、PHP 等编程语言或者 SPL 框架来处理数据,通过示例展示了如何按学校和科目统计各分数段的学生人数。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

【问题】

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

B

C

1

=mongo_open(“mongodb://localhost:27017/local?user=test&password=test”)

2

=mongo_shell(A1,“student.find()”)

3

=[5,4,3,2,1]

>subs=[“sub1”,“sub2”]

4

=A2.group(school)

>newfields=[]

5

for subs

>newfields=newfields|A3.(A5+“_”+string(~))

6

>result=create(${“school,”+newfields.concat@c()})

7

for A4

>temp=[]

8

for subs

>temp=temp|A7.align@a(A3,${B8}).(~.len())

9

>temp=[A7.school]|temp

10

>result.record(temp)

11

>mongo_close(A1)

A1、A2:查询原始数据到 A2 序表

A3、B3:准备动态的分数段和列名

A4:将原始数据按学校分组

A5、B5、A6:生成结果表,结构是 school、sub1_5、sub1_4…sub1_1、sub2_5、sub2_4…sub2_1

A7:按学校循环,准备每次向 result 中插入一条数据(B10)

B8、C8:根据 subs 循环,将 A4 中单个学校各科的学生成绩统计出来并按顺序拼接成序列。统计时将 A7 的某科目(B8)按照 A3 对齐分组

B9:拼接学校名称到上述序列,这样值可以和 result 序表的顺序对应上了

B10:插入数据到结果表

如果非动态列,科目较少,写法简化一点,逻辑与上面的方法大同小异:

A

1

=mongo_open(“mongodb://localhost:27017/local?user=test&password=test”)

2

=mongo_shell(A1,“student.find()”)

3

=A2.group(school)

4

=[5,4,3,2,1]

5

=A3.new(school,~.align@a(A4,sub1).(~.len()):sub1,~.align@a(A4,sub2).(~.len()):sub2)

6

=A5.new(school,~.sub1(1):sub1\_5,~.sub1(2):sub1\_4,~.sub1(3):sub1\_3,~.sub1(4):sub1\_2,~.sub1(5):sub1\_1,~.sub2(1):sub2\_5,~.sub2(2):sub2\_4,~.sub2(3):sub2\_3,~.sub2(4):sub2\_2,~.sub2(5):sub2\_1)

7

>mongo_close(A1)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值