窗口函数作为辅助列在计算中的应用
题目一：存在一张学生成绩表（class）,字段有year、subject、student、score，请查询
1、每年每门学科排名第一的学生
2、每年总成绩都有所提升的学生
回答：
1、

思路一：join

由题意知道需要year 和subject进行分组，最终求的是学生是谁
先求出每年每门学科的最大成绩

select year,subject, max(score)
from class
group by year,subject;

由于要求的是最高成绩对应的学生，所以需要在原始表中找出对应的学生，这时候就相当于数据不全需要补充数据。通过join来与不同表进行关联来获取我们需要的信息，按照year,subject,max(score)来与原表进行关联

select  a.year,a.subject,a.max_score,b.student
from (
select year,subject,max(score) as max_score
from class 
group by year,subject) a 
join class b
on a.year=b.year 
and a.subject =b.subject
and a.max_score=b.score;

思路二：窗口思维

所谓窗口思维就是把数据看成数据集(集合思维)，把一张完整的表找出对应的需要计算的数据分片。数据分片指满足条件的数据范围，或计算时需要的作用阈。

利用窗口函数增加辅助列来计算，很明显本题窗口范围依旧时按照年和科目分组后的数据，可以用分析函数max()对该窗口内的数据进行聚合求出每门课的最高成绩作为辅助列（辅助列往往是作为一种映射，一种对应关系而存在。）

select 
      year,subject,score,student
      ,max(score) over (partition by year,subject) as max_score
from class;

由上面计算结果可以看出，最后一列为max_score列，该列的左边为数据表本身对应的字段值，为了求出每年每门学科最高成绩的学生，我们可以进行过滤通过原表classs中score字段值与辅助列字段一致时筛选出我们需要的结果，见下：

select a.year,a.subject,a.score,a.student
from 
(select 
      year,subject,score,student
      ,max(score) over (partition by year,subject) as max_score
from class) a
where
a.score=a.max_score;--保留与最高分相同的记录

另外，也可用row_number()分析函数进行实现，思路同max（） over（）

select a.year,a.subject,a.score,a.student
from 
(select 
      year,subject,score,student
      ,row_number(score) over (partition by year,subject  order by score desc) as rn
from class) a
where
rn=1;----选出成绩按降序排序后最高的记录

思路三：

采用first_value()分析函数计算。first_value()返回分组排序后，组内第一行某个字段的值--待思考完善

select distinct year,subject,score --去重是因为first_value(student)取出的是窗口内排序后第一条记录的学生值，由于该字段生成是针对每条记录的，因而会有重复，需要去重
,first_value(student) over 
(partition by year,subject 
order by score desc) as student
from class

采用窗口函数进行分析要比join编写的代码简介，而且效率高，通过窗口函数对原纪录增加新列进行辅助计算避免了join操作，该新列的建立是针对每条记录按照条件进行的映射，可以看成标志位，如max_score和rn，然后再根据标志位进行筛选得出最终结果。

2、每年总成绩都有所提升的学生
lag()分析函数可以不用自关联，取除当前行外获取前面指定行某字段的值。因为为了比较每年学生总成绩都有所提升，可以通过该函数获取上一年学生的总成绩与当前行成绩进行比较。
lag() 函数又称行比较分析函数。

分析的主表还是每年每个学生的总成绩表，需要将学生分成一组，按年的升序进行排序，再进行窗口分析。

接着利用lag()函数访问上一行的成绩，利用本行的成绩减去上一行的成绩进行判断，如果差值大于0则设置标签为1说明今年成绩提高，然后按照学生分组，分组后判断flag为1的值的和是否和年份的记录数一致，如果一致则表示每年都在增长。

select student 
from 
(
select year,student,
case when () > 0 
     then 1 
     else 0 end  as flag
     
select student
from     
(select year,student,
case when (sum_score-
lag(sum_score) over(
partition by student order by year
)) > 0 
     then 1 
     else 0 end  as flag
from (
    select year,student,
    sum(score) as sum_score
    from class
    group by year,student) a )b
group by student
having sum(flag)=count(year)