窗口函数是OVER(),其中对应子句有PARTITION BY 以及 ORDER BY子句,所以形式有:
- OVER():这时候,是一个空子句,此时的效果和没有使用OVER()函数是一样的,作用的是这个表所有数据构成的窗口。
- OVER(PARTITION BY yyy1,yyy2,yyy3):含有了PARTITION BY 子句,此时就会根据yyy1,yyy2,yyy3这些列构成的整体进行划分窗口,只有这些列构成的整体相同,才会处在同一个窗口中。
- OVER(ORDER BY yyy1,yyy2,yyy3 ASC\DESC):每个窗口中利用ORDER BY子句,这时候将按照yyy1进行对应的升序\降序的顺序进行排序,如果yyy1相同,将根据yyy2排序(和ORDER BY 的用法一样),这时候不仅会进行排序操作,如果是SUM与其连用的话,同时进行了累加的操作,即值是当前行加上前一行对应的值。但是下面的例子中却发现ORDER BY 后面对应的值相同的时候,并不是当前这一行加上以前行的值,例如ORDER BY salary\ORDER BY name的时候。###sum(字段)over() ###eg:SUM(salary) OVER(ORDER BY salary) AS already_paid_salary,相当于salary从小到大排列,然后显示累加值,当前行为前面行的总和加上自身行。
- OVER(PARTITION BY yyy ORDER BY zzz ASC\DESC):根据PARTITION BY ,此时表示根据yyy进行分组,然后在每个窗口中的所有行将利用ORDER BY 子句,将根据zzz进行排序。值得注意的是,如果zzz和yyy相同的时候,这时候作用相当于OVER(PARTITION BY yyy),和没有ORDER BY子句是一样的,因为都处在一个窗口了。
- 而窗口函数可以和SUM()\AVG()\COUNT()\MAX()\MIN()这几个函数一起使用:
其中这些函数有一些特点,如果AVG()\COUNT()\MAX()\MIN()的括号中必须要有参数,用于统计某一列的对应的值,并且这一列中如果含有值为NULL的行,那么就会忽略值NULL的行,而COUNT()则比较特殊,如果是COUNT(*),那么就不会忽略NULL值的行,用来统计这个表中有多少行,否则,如果是COUNT(column),统计某一列column有多少行,那么就会忽略NULL的行。 - OVER ( PARTITION BY <用于分组的列名>
- ORDER BY <按序叠加的列名>
- ROWS <窗口滑动的数据范围> )
-
<窗口滑动的数据范围> 用来限定[ 你要的操作] 所运用的数据的范围,具体有如下这些:
当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following -
举例理解一下:
取当前行和前五行:ROWS between 5 preceding and current row --共6行
取当前行和后五行:ROWS between current row and 5 following --共6行
取前五行和后五行:ROWS between 5 preceding and 5 folowing --共11行