OVER的定义
OVER用于为行定义一个窗口,它对一组值进行操作,不需要使用GROUP BY子句对数据进行分组,能够在同一行中同时返回基础行的列和聚合列。
OVER的语法
OVER ( [ PARTITION BY column ] [ ORDER BY culumn ] )
PARTITION BY 子句进行分组;
ORDER BY 子句进行排序。
窗口函数OVER()指定一组行,开窗函数计算从窗口函数输出的结果集中各行的值。
开窗函数不需要使用GROUP BY就可以对数据进行分组,还可以同时返回基础行的列和聚合列。
OVER的用法
OVER开窗函数必须与聚合函数或排序函数一起使用,聚合函数一般指SUM(),MAX(),MIN,COUNT(),AVG()等常见函数。排序函数一般指RANK(),ROW_NUMBER(),DENSE_RANK(),NTILE()等。
OVER在聚合函数中使用的示例
我们以SUM和COUNT函数作为示例来给大家演示。
SUM后的开窗函数
结果如下:
其中开窗函数的每个含义不同,我们来具体解读一下:
SUM(Salary) OVER (PARTITION BY Groupname)
只对PARTITION BY后面的列Groupname进行分组,分组后求解Salary的和。
SUM(Salary) OVER (PARTITION BY Groupname ORDER BY ID)
对PARTITION BY后面的列Groupname进行分组,然后按ORDER BY 后的ID进行排序,然后在组内对Salary进行累加处理。
SUM(Salary) OVER (ORDER BY ID)
只对ORDER BY 后的ID内容进行排序,对排完序后的Salary进行累加处理。
SUM(Salary) OVER ()
对Salary进行汇总处理
COUNT后的开窗函数
后面的每个开窗函数就不再一一解读了,可以对照上面SUM后的开窗函数进行一一对照。
OVER在排序函数中使用的示例
我们对4个排序函数一一演示
ROW_NUMBER()
定义:ROW_NUMBER()函数作用就是将SELECT查询到的数据进行排序,每一条数据加一个序号,他不能用做于学生成绩的排名,一般多用于分页查询,比如查询前10个 查询10-100个学生。ROW_NUMBER()必须与ORDER BY一起使用,否则会报错。
对学生成绩排序
结果如下:
这里的PARTITION BY和ORDER BY的作用与我们在上面看到的聚合函数的作用一样,都是用来进行分组和排序使用的。