每年总成绩都有提升的学生

每年总成绩都有提升的学生

2020,语文,A,83
2020,数学,A,58
2020,英语,A,29
2020,语文,B,83
2020,数学,B,77
2020,英语,B,67
2021,语文,A,50
2021,数学,A,93
2021,英语,A,70
2021,语文,B,86
2021,数学,B,43
2021,英语,B,39
2022,语文,A,90
2022,数学,A,49
2022,英语,A,80
2022,语文,B,80
2022,数学,B,83
2022,英语,B,97

思路:

提取关键词每年、总成绩、都有提升

解决:

对年份和学生分组，求总成绩。
使用lag()函数开窗，lag用于统计窗口内往上第n行值，取不到就给默认值，我们这里向上取一行，默认值为0，并且对学生分区，对年份排序(跟lag取数的顺序相关)。
每一行的总成绩减去lag函数拿到的上一行成绩，得到的可能是正数也可能是负数，正数代表提升。
使用if()函数对上面的结果进行处理，如果结果大于0给1，小于等于0给0，得到diff，方便统计。
子查询，对学生分区，使用avg()函数对diff进行统计，等于1则说明总成绩每年都在提升。

代码:

select 
	student 
from
	(select
		year,student,
		if(sum_score - lag(sum_score,1,0) over(partition by student order by year) > 0 ,1,0) diff
	from
		(select
			year,student,sum(score) as sum_score
		from
			scores
		group by
			year,student
		order by 
			student
		)
	)
group by student having avg(diff) = 1;

结果:

2020	A	1
2021	A	1
2022	A	1
2020	B	1
2021	B	0
2022	B	1
-------------
A