SQL查询进阶-开窗函数

简介

开窗函数又称分析函数，是用于对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。下面重点介绍Lag函数 和 Lead函数使用方法，分别用于获取在某些条件限制下列表数据中当前行之前或之后 偏移 n 行的值，通常在需要比较相邻行数据或进行时间分析时使用。

LAG函数

• Lag 函数用于获取 当前行之前 的某一列的值。它可以帮助我们查看上一行的数据。
• 语法如下

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

• 参数示意
  1） column_name：要获取值的列名。
  2）offset：表示要向上偏移的行数。例如，offset为1表示获取上一行的值，offset为2表示获取上两行的值，以此类推。
  3）default_value：可选参数，用于指定当没有前一行时的默认值。
  4）PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

LEAD函数

• Lead 函数用于获取 当前行之后 的某一列的值。它可以帮助我们查看下一行的数据。
• 语法如下

LEAD(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY sort_column)

• 参数示意
  1） column_name：要获取值的列名。
  2）offset：表示要向下偏移的行数。例如，offset为1表示获取下一行的值，offset为2表示获取下两行的值，以此类推
  3）default_value：可选参数，用于指定当没有前一行时的默认值。
  4）PARTITION BY和ORDER BY子句可选，用于分组和排序数据。

---

应用

1. 学生成绩表 scores，其中包含学号、成绩和考试日期，数据如下：

student_id	exam_date	score
101	2023-01-01	85
101	2023-01-05	78
101	2023-01-10	92
101	2023-01-15	80

2. 要求：查询每个学生所有参加的考试日期和上一次考试的成绩和下一次考试成绩，用于观察学生考试成绩浮动，示例SQL如下：

SELECT 
    student_id,
    exam_date,
    score,
    LAG(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS previous_score,
    LEAD(score, 1, NULL) OVER (PARTITION BY student_id ORDER BY exam_date) AS next_score
FROM
    scores;

3.查询结果

student_id	exam_date	score	previous_score	next_score
101	2023-01-01	85	NULL	78
101	2023-01-05	78	85	92
101	2023-01-10	92	78	80
101	2023-01-15	80	92	NULL