开窗函数

题目

现有学生成绩表（t_scores） 包含学生id、语文、数学、成绩，现为对比总分成绩差不多的学生其各科成绩表现水平，计算出与自己总分相差5分以内（+-5）的同学各科平均分；

样例数据

+-------------+----------+-------+
| student_id  | chinese  | math  |
+-------------+----------+-------+
| 1           | 93       | 85    |
| 2           | 81       | 96    |
| 3           | 88       | 82    |
| 4           | 79       | 77    |
| 5           | 85       | 76    |
| 6           | 69       | 85    |
| 7           | 77       | 90    |
| 8           | 79       | 81    |
+-------------+----------+-------+

期望结果

+-------------+----------+-------+--------+--------------+-----------+
| student_id  | chinese  | math  | total  | avg_chinese  | avg_math  |
+-------------+----------+-------+--------+--------------+-----------+
| 1           | 93       | 85    | 178    | 87.0         | 90.5      |
| 2           | 81       | 96    | 177    | 87.0         | 90.5      |
| 3           | 88       | 82    | 170    | 82.5         | 86.0      |
| 4           | 79       | 77    | 156    | 78.0         | 79.75     |
| 5           | 85       | 76    | 161    | 81.0         | 78.0      |
| 6           | 69       | 85    | 154    | 74.0         | 81.0      |
| 7           | 77       | 90    | 167    | 82.5         | 86.0      |
| 8           | 79       | 81    | 160    | 81.0         | 78.0      |
+-------------+----------+-------+--------+--------------+-----------+

建表语句及数据插入语句

CREATE TABLE t_scores (
    student_id BIGINT,
    chinese BIGINT,
    math BIGINT
);

INSERT INTO t_scores VALUES
(1, 93, 85),
(2, 81, 96),
(3, 88, 82),
(4, 79, 77),
(5, 85, 76),
(6, 69, 85),
(7, 77, 90),
(8, 79, 81);

分析

本题是对开窗函数的考察，但是窗口的范围不是固定行数，而是根据总分数的值进行判断，所以需要用到不常用的range方式，属于考察知识范围的题目，知道就很简单，不知道就比较费劲了。

执行SQL

select student_id,
       chinese,
       math,
       chinese + math                                 as total,
       avg(chinese) over (ORDER BY chinese + math
           RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING) as avg_chinese,
       avg(math) over (ORDER BY chinese + math
           RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING) as avg_math
from t_scores

执行结果

+-------------+----------+-------+--------+--------------+-----------+
| student_id  | chinese  | math  | total  | avg_chinese  | avg_math  |
+-------------+----------+-------+--------+--------------+-----------+
| 6           | 69       | 85    | 154    | 74.0         | 81.0      |
| 4           | 79       | 77    | 156    | 78.0         | 79.75     |
| 8           | 79       | 81    | 160    | 81.0         | 78.0      |
| 5           | 85       | 76    | 161    | 81.0         | 78.0      |
| 7           | 77       | 90    | 167    | 82.5         | 86.0      |
| 3           | 88       | 82    | 170    | 82.5         | 86.0      |
| 2           | 81       | 96    | 177    | 87.0         | 90.5      |
| 1           | 93       | 85    | 178    | 87.0         | 90.5      |
+-------------+----------+-------+--------+--------------+-----------+

你平时有遇到过用range的场景吗?欢迎留言补充