1、问题引出
学生们参加了10个课程的考试。每门课按成绩分布分成3等。
要求将学生每门课程分等。
- 学生成绩表_事实表
stdno学生号 | clsno课程号 | sc成绩 |
---|---|---|
1 | 1 | 37 |
1 | 2 | 33 |
1 | 3 | 43 |
2 | 1 | 32 |
2 | 2 | 71 |
2 | 3 | 69 |
- 课程成绩分级表
CLSNO课程号 | RANK1成绩等级 | SCMIN分组下线 | SCMAX分组上线 |
---|---|---|---|
1 | 1 | 0 | 8 |
1 | 2 | 8 | 25 |
1 | 3 | 25 | 100 |
2 | 1 | 0 | 38 |
2 | 2 | 38 | 55 |
2 | 3 | 55 | 100 |
成绩0~100,分区使用前开后闭
2、第一方法
本能反应:用游标的方法,遍历学生的每个成绩,再与对应课程的分级规则对比,判定成绩等级。
伪代码:
for c in (学生id,课程id,成绩) loop
select 成绩等级1 from 分等表 where 课程id=c.课程id and c.成绩 between 分等上线 and 分等下线;
update tab_学生成绩
set 成绩等级=成绩等级1
where 学生id=c.学生id
and 课程id=c.课程id;
end loop;
缺点:
学生成绩表是事实表,实际数据量可能很大,游标遍历、再回头更新记录,效率肯定不高
3、更优方法
能否有集合、批量的方式呢?一个sql就完成了全部课程的分等,不需要游标。
1. 学生成绩表与课程分级表 通过 课程id关联,这样课程成绩形成笛卡尔积
2. 在关联的全集结果里,挑选成绩在分等上下限里的,过滤无意义的笛卡尔积。
sql
select a.*, b.*
from a, b
where a.clsno = b.clsno
and a.sc > b.scmin
and a.sc <= b.scmax
order by a.stdno, a.clsno
4、思路关键点
1)集合思维,尽量不要一条条处理记录
2)where条件可以转换到select结果字段里,一个字段类似一个标签,通过标签的组合形成类似多种where的组合。
一般情况,一个sql只有一种where(或者一种数据集);这个方法,是数据全集,但对不同的数据做了集合标签
ps:sql不复杂,为啥写出来?在获取更优方法前,自己没有明确的思路导向,只是觉得方法一不优,尤其在事实表巨大的情况下。
能否事实表只查一次?后来才有了更优方法。希望梳理下适配场景,作为一种固定的思路模型。
5、适配场景
1)大事实表,不想对大表多次查询
2)多维度分组
3)打标签
4)where条件转select字段
ps:示例是个简单的说明,真实的业务场景比这个复杂,也更能体现方法二的优势,读者尽量体会意思。