SQL面试题003-行与行的比较

披头

发布于 2024-04-24 15:37:44

32500

代码可运行

文章被收录于专栏：datartisandatartisan

运行总次数：0

代码可运行

unsetunset问题背景unsetunset

假设现在有一份学生成绩表，具体如下图所示：

sname	school_term	subject	score
张三	一年级期末	数学	99
张三	二年级期中	数学	100
李四	一年级期末	数学	66
李四	二年级期中	数学	88
王朝	一年级期末	数学	92
王朝	二年级期中	数学	91
马汉	一年级期末	数学	88
马汉	二年级期中	数学	89

现在要根据每个学生近两个学期的得分情况进行评优，评优规则如下：

学习之星：两个学期分数均为95+，与上学期分数相比，持平或者增加
希望之星：两个学期分数90+，但是分数较上学期有所降低
努力之星：两个学期分数均在85至90之间，与上学期分数相比，持平或者增加
进步之星：本学期较上学期分数增加大于等于20分

unsetunset要求的结果unsetunset

按照上述评优规则评定后，可以得到如下结果

sname	school_term	subject	score	starred_title
张三	一年级期末	数学	99
张三	二年级期中	数学	100	学习之星
李四	一年级期末	数学	66
李四	二年级期中	数学	88	进步之星
王朝	一年级期末	数学	92
王朝	二年级期中	数学	91	希望之星
马汉	一年级期末	数学	88
马汉	二年级期中	数学	89	努力之星

unsetunset解析unsetunset

按照评优规则，需要计算两个学期得分的差值，有两个方案可以考虑

将两个学期的得分情况构造为子查询

分别将近两个学期的得分情况构造为子查询，然后将两个子查询相关联（ JOIN ），便可以对两次得分情况进行比较，这是一种比较简单的解题思路，具体的解题过程留给你思考。

使用窗口函数 LAG

MySQL 8.0 以上版本可以使用窗口函数，其中 LAG 函数可以访问当前行的前几行，LAG 函数语法如下：

LAG(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY expr,...
    ORDER BY expr [ASC|DESC],...
)

其参数介绍如下：

expression

LAG() 函数返回 expression 当前行之前的行的值，其值为 offset 其分区或结果集中的行数。

offset

offset 是从当前行返回的行数，以获取值。offset 必须是零或文字正整数。如果 offset 为零，则 LAG() 函数计算 expression 当前行的值。如果未指定 offset ，则 LAG() 默认情况下函数使用一个。

default_value

如果没有前一行，则 LAG() 函数返回 default_value 。例如，如果 offset 为2，则第一行的返回值为 default_value 。如果省略 default_value ，则默认 LAG() 返回函数 NULL 。

PARTITION BY 子句

PARTITION BY 子句将结果集中的行划分 LAG() 为应用函数的分区。如果省略 PARTITION BY 子句，LAG() 函数会将整个结果集视为单个分区。

ORDER BY 子句

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

查询结果如下：