窗户函数应用详解：结合分组（partition by）与排序（order by）的实践操作

开窗函数功能很强大

Oracle数据库是支持这种写法的，mysql的话要8版本或以上才支持

PARTITION 中文是分割的意思，ORDER 是排序的意思，把一组数据按照制定的字段进行分割成各种组，然后组内按照某个字段排序

简而言之就是

分组后对组内数据排序

写法：

select id,name,class_id,score,lag(score,1,0)

over (partition by class_id order by score desc) before_score from t_student;

mysql8.0之前写法：

SELECT
    c.id,
    c.NAME,
    c.class_id,
    c.score,
    c.before_score
FROM
    (
SELECT
IF ( @id = a.class_id, @lagname := @score, @lagname := '' ) AS before_score,
    @id := a.class_id AS aclass_id,
    @score := a.score AS aafter_score,
    a.*
FROM
    ( SELECT * FROM t_student ORDER BY class_id, score DESC ) a,
    ( SELECT @lagname := NULL, @id := 0, @score := NULL ) b
    ) c;

或者，采用子查询，先排序，再分组，找到最值元素

扩展

over (partition by order by）

还可以有这种写法

over (order by），整个数据就是一个大块

配合ROW_NUMBER() 函数可以做到组内数据从1开始编号

ROW_NUMBER()  over (partition by class_id order by score desc)   no       对数据的编号

与OVER(PARTITION BY... ORDER BY...)匹配使用的函数

row_number() over()、rank() over()和dense_rank() over()

在上面的例子里，使用ROW_NUMBER()可以对数据编号，但是有一个问题，例子中的MI_ID是不可以重复的，如果在可以重复的情况下，就有并列的情况，这样就无法取出并列的数据，只能取单一排序的数据。所以这里可以换成 rank() over()和dense_rank() over()来进行编号：（rank() over()和dense_rank() over()的区别如图）

sum() over()，first_value() over()和last_value() over()的使用

sum() over()分组求和

first_value() over()求分组第一条

last_value() over()求分组最后一条

其中用row_number() over()取编号第一条的也可以实现first_value() over()的效果

常用的分析函数如下所列:

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)