假设现在有一份学生成绩表,具体如下图所示:
sname | school_term | subject | score |
---|---|---|---|
张三 | 一年级期末 | 数学 | 99 |
张三 | 二年级期中 | 数学 | 100 |
李四 | 一年级期末 | 数学 | 66 |
李四 | 二年级期中 | 数学 | 88 |
王朝 | 一年级期末 | 数学 | 92 |
王朝 | 二年级期中 | 数学 | 91 |
马汉 | 一年级期末 | 数学 | 88 |
马汉 | 二年级期中 | 数学 | 89 |
现在要根据每个学生近两个学期的得分情况进行评优,评优规则如下:
按照上述评优规则评定后,可以得到如下结果
sname | school_term | subject | score | starred_title |
---|---|---|---|---|
张三 | 一年级期末 | 数学 | 99 | |
张三 | 二年级期中 | 数学 | 100 | 学习之星 |
李四 | 一年级期末 | 数学 | 66 | |
李四 | 二年级期中 | 数学 | 88 | 进步之星 |
王朝 | 一年级期末 | 数学 | 92 | |
王朝 | 二年级期中 | 数学 | 91 | 希望之星 |
马汉 | 一年级期末 | 数学 | 88 | |
马汉 | 二年级期中 | 数学 | 89 | 努力之星 |
按照评优规则,需要计算两个学期得分的差值,有两个方案可以考虑
分别将近两个学期的得分情况构造为子查询,然后将两个子查询相关联( JOIN
),便可以对两次得分情况进行比较,这是一种比较简单的解题思路,具体的解题过程留给你思考。
MySQL 8.0 以上版本可以使用窗口函数,其中 LAG 函数可以访问当前行的前几行,LAG 函数语法如下:
LAG(<expression>[,offset[, default_value]]) OVER (
PARTITION BY expr,...
ORDER BY expr [ASC|DESC],...
)
其参数介绍如下:
LAG()
函数返回 expression
当前行之前的行的值,其值为 offset
其分区或结果集中的行数。
offset
是从当前行返回的行数,以获取值。offset
必须是零或文字正整数。如果 offset
为零,则 LAG()
函数计算 expression
当前行的值。如果未指定 offset
,则 LAG()
默认情况下函数使用一个。
如果没有前一行,则 LAG()
函数返回 default_value
。例如,如果 offset
为2,则第一行的返回值为 default_value
。如果省略 default_value
,则默认 LAG()
返回函数 NULL
。
PARTITION BY
子句将结果集中的行划分 LAG()
为应用函数的分区。如果省略 PARTITION BY
子句,LAG()
函数会将整个结果集视为单个分区。
ORDER BY
子句指定在 LAG()
应用函数之前每个分区中的行的顺序。LAG()
函数可用于计算当前行和上一行之间的差异。
具体到这个例子,我们可以按照学生姓名及科目进行分组,按照学期进行排序,但是按照学期的中文描述进行排序是不合适的,因为中文是按照拼音首字母进行排序的,排序结果不一定是我们想要的,那么,我们要添加辅助列来显示指定学期的顺序,具体语句如下:
SELECT
sname,
school_term,
subject,
score,
CASE WHEN school_term = '一年级期末' THEN 1 ELSE 2
END AS term_id
FROM
score
查询结果如下:
有了学期的明确顺序 term_id
,就可以利用 LAG
函数得到上学期的得分了。
SELECT *
,LAG( score ) OVER( PARTITION BY sname,subject ORDER BY term_id) former_score
FROM (
SELECT
sname,
school_term,
subject,
score,
CASE WHEN school_term = '一年级期末' THEN 1 ELSE 2
END AS term_id
FROM
score ) T1
查询结果如下:
进而,我们可以将本学期的成绩 score
和上学期的成绩 former_score
相比,得到一些判断的标志位,在计算标志位的过程中,要注意将最苛刻的条件放到第一个 CASE WHEN
中,否则会得到意外的结果。另外,由于每个人的上学期成绩没有前一个学期的成绩,所以一年级期末的 former_socre
都是空值。该比较结果需要进行特殊指定。
SELECT *
,CASE WHEN score - LAG( score ) OVER( PARTITION BY sname,subject ORDER BY term_id) >= 20 and term_id=2 then 2
-- 分数增加20分及以上,置为 2
WHEN score >= LAG( score ) OVER( PARTITION BY sname,subject ORDER BY term_id) and term_id=2 then 1
-- 分数持平或者增加,置为 1
WHEN term_id = 1 THEN 999
-- 特殊场景,置为 999
ELSE 0 END AS flag
FROM (
SELECT
sname,
school_term,
subject,
score,
CASE WHEN school_term = '一年级期末' THEN 1 ELSE 2
END AS term_id
FROM
score ) T1
查询结果如下:
到这里,我们就可以根据标志位来进行评优的判定了。
SELECT sname,school_term,subject,score
,CASE WHEN score >= 95 AND flag = 1 THEN '学习之星'
WHEN score >= 90 AND flag = 0 THEN '希望之星'
WHEN score >= 85 AND flag = 1 THEN '努力之星'
WHEN flag = 2 THEN '进步之星'
ELSE '' END AS starred_title
FROM
(SELECT *
,CASE WHEN score - LAG( score ) OVER( PARTITION BY sname,subject ORDER BY term_id) >= 20 and term_id=2 then 2
-- 分数增加20分及以上,置为 2
WHEN score >= LAG( score ) OVER( PARTITION BY sname,subject ORDER BY term_id) and term_id=2 then 1
-- 分数持平或者增加,置为 1
WHEN term_id = 1 THEN 999
-- 特殊场景,置为 999
ELSE 0 END AS flag
FROM (
SELECT sname,school_term,subject,score
,CASE WHEN school_term = '一年级期末' THEN 1 ELSE 2
END AS term_id
FROM
score ) T1 ) T2
查询结果如下: