数据集
我们这一篇文章采用PostgreSQL的SQL语法。重点我们关注select...from...where这种读操作，分析query (analytical query)。
数据集在 https://hyper-db.de/interface.html 可以直接使用。另外在这个网页不允许进行写操作:insert, update, delete之类的transactional query。当然create table和drop table也不被允许。
架构 Schema:
schema.png
schema_en.png
下载:
https://db.in.tum.de/teaching/ws1920/grundlagen/uni_mysql.sql?lang=de
Schma和大部分SQL语句来自Prof. Alfons Kemper, Ph.D.的课件和书。
课件：
- https://db.in.tum.de/teaching/bookDBMSeinf/folien/?lang=de
- https://db.in.tum.de/teaching/bookDBMSeinf/folien/pdf/Kapitel4.pdf?lang=de
书： https://db.in.tum.de/teaching/bookDBMSeinf/?lang=de
中级SQL
- 比较听课并考试的同学的成绩和不听课只考试的同学成绩：
with no_lec as (
select avg(note) as avg_note
from pruefen p
where not exists (
select *
from hoeren h
where h.matrnr = p.matrnr
)),
with_lec as (
select avg(note) as avg_note
from pruefen p
where exists (
select *
from hoeren h
where h.matrnr = p.matrnr
))
select *
from no_lec, with_lec;
- 计算权重的成绩(
sws就是我们的权重)：
我们采用一个更大的表格：
with pruefenxl (matrnr, vorlnr, persnr, note) as (
select * from pruefen
union all
values
(25403 , 5049 , 2126 , 1),
(26120 , 5001 , 2137 , 1),
(26120 , 5043 , 2126 , 3),
(26120 , 5052 , 2126 , 4),
(26120 , 4630 , 2137 , 1)
)
with pruefenxl (matrnr, vorlnr, persnr, note) as (
select * from pruefen
union all
values
(25403, 5049, 2126, 1),
(26120, 5001, 2137, 1),
(26120, 5043, 2126, 3),
(26120, 5052, 2126, 4),
(26120, 4630, 2137, 1)
), factorGrade as (
select s.name, s.matrnr, v.titel, p.note, v.sws as factor, (p.note * v.sws) as finalGrade
from studenten s, pruefenxl p, vorlesungen v
where p.vorlnr = v.vorlnr and s.matrnr = p.matrnr
)
select matrnr, name, sum(finalGrade) / sum(factor) as finalAVG
from factorGrade
group by matrnr, name
order by matrnr
或者
with pruefenxl (matrnr, vorlnr, persnr, note) as (
select * from pruefen
union all
values
(25403 , 5049 , 2126 , 1),
(26120 , 5001 , 2137 , 1),
(26120 , 5043 , 2126 , 3),
(26120 , 5052 , 2126 , 4),
(26120 , 4630 , 2137 , 1)
)
select s.matrnr, s.name, sum(p.note * v.sws) / sum(v.sws) as avg
from studenten s, pruefenxl p, vorlesungen v
where s.matrnr = p.matrnr and p.vorlnr = v.vorlnr
group by s.matrnr , s.name
order by s.matrnr
- 搜索学生通过上课能认识的其他学生名字：
select s1.name, s2.name
from studenten s1, hoeren h1, hoeren h2, studenten s2
where h1.vorlnr = h2.vorlnr and h1.matrnr = s1.matrnr and h2.matrnr = s2.matrnr and s1.matrnr != s2.matrnr
- 对每一个同学认识的人进行计数：
with bekannte as (
select s1.matrnr as student, s2.matrnr as sein_bekannte
from studenten s1,
hoeren h1,
hoeren h2,
studenten s2
where h1.vorlnr = h2.vorlnr
and h1.matrnr = s1.matrnr
and h2.matrnr = s2.matrnr
and s1.matrnr != s2.matrnr
)
select s.matrnr, s.name, count(b.sein_bekannte) as num_friends
from studenten s, bekannte b
where s.matrnr = b.student
group by s.matrnr, s.name
order by num_friends desc
- 在2.的基础上再考虑：不上课(也就不认识同学)的人
with bekannte as (
select s1.matrnr as student, s2.matrnr as sein_bekannte
from studenten s1,
hoeren h1,
hoeren h2,
studenten s2
where h1.vorlnr = h2.vorlnr
and h1.matrnr = s1.matrnr
and h2.matrnr = s2.matrnr
and s1.matrnr != s2.matrnr
)
select s.matrnr, s.name, count(b.sein_bekannte) as num_friends
from studenten s left outer join bekannte b
on s.matrnr = b.student
group by s.matrnr, s.name
order by num_friends desc
这里用了一个left outer join。右边的表格bekannte b只含有上课的同学(即出现在hoeren表格中的同学)，但是左边的表格studenten s含有所有学生。
- 求每个学生的选课数量的平均数，需要考虑不上课的学生：
select count(h.vorlnr), count(distinct s.matrnr) -- 听课和不听课的学生都在
from studenten s left outer join hoeren h on s.matrnr = h.matrnr
select hcount / (scount * 1.00)
from (select count(*) as hcount from hoeren) h,
(select count(*) as scount from studenten) s -- 听课和不听课的学生都在
- 搜索选课超过学生选课sws平均数的学生，需要考虑不上课的学生：
with num_stu as (
select count(*) as count_stu
from studenten),
num_sws as (
select sum(vor.sws) as count_sws
from hoeren h, vorlesungen vor
where h.vorlnr = vor.vorlnr)
select s.*
from studenten s
where s.matrnr in (
select h.matrnr
from hoeren h, vorlesungen v
where h.vorlnr = v.vorlnr
group by h.matrnr
having sum(sws) > (select cast(num_sws.count_sws as decimal (5, 2)) / num_stu.count_stu from num_sws, num_stu)
)
或者
with num_stu as (
select count(*) as count_stu
from studenten),
num_sws as (
select sum(vor.sws) as count_sws
from hoeren h, vorlesungen vor
where h.vorlnr = vor.vorlnr),
avg_sws as (
select cast(num_sws.count_sws as decimal(5, 2)) / num_stu.count_stu as sws
from num_stu, num_sws),
stu_sws as (
select s.matrnr, s.name, s.semester, sum(v.sws) as sum_sws
from studenten s, hoeren h, vorlesungen v
where s.matrnr = h.matrnr and h.vorlnr = v.vorlnr
group by s.matrnr, s.name, s.semester)
select s.*
from stu_sws s, avg_sws
where s.sum_sws > avg_sws.sws
或者
with swsProStudent as (
select s.matrnr, s.name,
cast((case when sum(v.sws) is null then 0
else sum(v.sws) end) as real) as anzSWS
from studenten s
left outer join hoeren h on s.matrnr = h.matrnr
left outer join vorlesungen v on h.vorlnr = v.vorlnr
group by s.matrnr, s.name
)
select s.*
from studenten s
where s.matrnr in (
select sws.matrnr
from swsProStudent sws
where sws.anzSWS > (
select avg(anzSWS)
from swsProStudent
)
)
- 确定一门的
semester属性：这个根据听这门课中人数最多的一个学生semester组决定。如果有很多组人数相同，取这些组们中semester最小的数值。比如一门课被100第一学期的同学和100个第三学期的同学上，我们当这门课是属于第一学期的课(那100第三学期的被我们当做前一年挂科重修的人)。
with vorl_semester_anz as (
select h.vorlnr, s.semester, count(*) as num
from hoeren h, studenten s
where h.matrnr = s.matrnr
group by h.vorlnr, s.semester
)
select v.vorlnr, min(v.semester) as semester
from vorl_semester_anz v
where v.num = (
select max(vhelp.num)
from vorl_semester_anz vhelp
where v.vorlnr = vhelp.vorlnr
)
group by v.vorlnr
这个结构其实很难用join完成，但是也是可行的。另外一种间接的方式：
with vorl_semester_anz as (
select h.vorlnr, s.semester, count(*) as num
from hoeren h, studenten s
where h.matrnr = s.matrnr
group by h.vorlnr, s.semester
), vorl_semester_maxanz as (
select v.vorlnr, max(num) as max -- 每一门课最多的人数是一个确定的数字，用这个数字可以找回semester
from vorl_semester_anz v
group by v.vorlnr
)
select v1.vorlnr, min(v1.semester) as semester
from vorl_semester_anz v1, vorl_semester_maxanz v2
where v1.vorlnr = v2.vorlnr and v1.num = v2.max
group by v1.vorlnr
- 搜索提前上课的同学(同学的
semester比课程的semester要小)：
with vorl_semester_anz as (
select h.vorlnr, s.semester, count(*) as num
from hoeren h, studenten s
where h.matrnr = s.matrnr
group by h.vorlnr, s.semester
), vorl_semester as (
select v.vorlnr, min(v.semester) as semester
from vorl_semester_anz v
where v.num = (
select max(vhelp.num)
from vorl_semester_anz vhelp
where v.vorlnr = vhelp.vorlnr
)
group by v.vorlnr)
select v.vorlnr, v.titel, count(s.matrnr) as num_advanced_stu
from vorlesungen v left outer join vorl_semester vs on v.vorlnr = vs.vorlnr
left outer join hoeren h on v.vorlnr = h.vorlnr
left outer join studenten s on h.matrnr = s.matrnr and s.semester < vs.semester
group by v.vorlnr, v.titel
当然还有另一种：
with vorl_semester_anz as (
select h.vorlnr, s.semester, count(*) as num
from hoeren h, studenten s
where h.matrnr = s.matrnr
group by h.vorlnr, s.semester
), vorl_semester_maxanz as (
select v.vorlnr, max(num) as max -- 每一门课最多的人数是一个确定的数字，用这个数字可以找回semester
from vorl_semester_anz v
group by v.vorlnr
), vorl_semester as (
select v1.vorlnr, min(v1.semester) as semester
from vorl_semester_anz v1, vorl_semester_maxanz v2
where v1.vorlnr = v2.vorlnr and v1.num = v2.max
group by v1.vorlnr
)
select v.vorlnr, v.titel, count(s.matrnr) as num_advanced_stu
from vorlesungen v left outer join vorl_semester vs on v.vorlnr = vs.vorlnr
left outer join hoeren h on v.vorlnr = h.vorlnr
left outer join studenten s on h.matrnr = s.matrnr and s.semester < vs.semester
group by v.vorlnr, v.titel
这里必须要提到一下:
-
count(s.matrnr) as num_advanced_stu不能写成count(*) - 原因是：
left outer join会生成很多null，因为很多课没有人听，因此这些课也没有semester。
但是count(*)会将这些课计数，而count(s.matrnr)计的数字是真正上这个课学生人数，而不是null。
该文章遵循创作共用版权协议 CC BY-NC 3.0，要求署名、非商业 、保持一致。在满足创作共用版权协议 CC BY-NC 3.0 的基础上可以转载，但请以超链接形式注明出处。文章仅代表作者的知识和看法，如有不同观点，可以回复并讨论。
