SQL开发:用笛卡尔积+分组条件分组

1、问题引出

学生们参加了10个课程的考试。每门课按成绩分布分成3等。
要求将学生每门课程分等。
- 学生成绩表_事实表

stdno学生号clsno课程号sc成绩
1137
1233
1343
2132
2271
2369

- 课程成绩分级表

CLSNO课程号RANK1成绩等级SCMIN分组下线SCMAX分组上线
1108
12825
1325100
21038
223855
2355100

成绩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:示例是个简单的说明,真实的业务场景比这个复杂,也更能体现方法二的优势,读者尽量体会意思。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值