根据SQL中的条件区分列的历史数据

Question

我有一个数据集，它有以下信息[STUDENT_INFORMATION]:

上课日期tbody> <<tr>

学生姓名	班级
莎拉	类1	2/3/2018
莎拉	二班	3/3/2018
莎拉	3班	6/3/2018
max	类1	1/4/2016
max	二班	1/4/2017
max	3班	1/4/2018
阿什利	二班	9/4/2016
阿什利	3班	10/8/2016
阿什利	二班	9/4/2018
阿什利	3班	10/8/2018

使用lead()窗口函数，这可以很容易地实现。下面的查询在MySQL中工作:

-- Prep data
create table student_infomation (
student     varchar(20),
class       varchar(20),
class_date  date);

insert into student_infomation 
values
('sara'  , 'class 1', '2018-02-03'),
('sara'  , 'class 2', '2018-03-03'),
('sara'  , 'class 3', '2018-06-03'),
('max'   , 'class 1', '2016-01-04'),
('max'   , 'class 2', '2017-01-04'),
('max'   , 'class 3', '2018-01-04'),
('ashley', 'class 2', '2016-09-04'),
('ashley', 'class 3', '2016-10-08'),
('ashley', 'class 2', '2018-09-04'),
('ashley', 'class 3', '2018-10-08');

-- SQL query
with cte as (
select student,
class,
class_date,
lead(class, 1)      over (partition by student order by class_date) as next_class,
lead(class_date, 1) over (partition by student order by class_date) as next_class_date
from student_infomation)
select *
from cte
where class      = 'class 2'
and next_class = 'class 3';

结果:

student|class  |class_date|next_class|next_class_date|
-------+-------+----------+----------+---------------+
ashley |class 2|2016-09-04|class 3   |     2016-10-08|
ashley |class 2|2018-09-04|class 3   |     2018-10-08|
max    |class 2|2017-01-04|class 3   |     2018-01-04|
sara   |class 2|2018-03-03|class 3   |     2018-06-03|