窗口函数 - 旧游无处不堪寻

窗口函数是 SQL 的一个相对较新的补充，在SQL:2003 被引入。与聚合函数通过多行数据返回单个值不同，窗口函数使用来自一个或多个行的值来为每一行返回一个值。

窗口函数总有一个 OVER 子句，任何没有 OVER 子句的函数都不是窗口函数。

over

基础语法如下:

over(...)

SELECT
  first_name,
  last_name,
  salary,  
  AVG(salary) OVER()
FROM employee;

AVG(salary) 表示查看平均工资，后面的 OVER() 意味着在查询该公司所有员工的平均工资。

SELECT
  first_name,
  last_name,
  salary,  
  AVG(salary) OVER()
FROM employee
WHERE department_id = 3;

当窗口函数存在 where 子句时，窗口函数所扫描的只是过滤之后的数据，也就是说，窗口函数总是在 where 子句之后执行。即上述语句获取的是部门编号为 3 的员工的平均工资。

注意: 不能将窗口函数放入 where 子句中，原因如上。

partition by order by

基础语法如下:

OVER([PARTITION BY column1, column2 ... column_n] [ORDER BY  column1, column2 ... column_n])

partition by 与 group by非常类似，以其后出现的列进行分组，只不过 partition by 不折叠行。

rank function

基础语法如下:

RANK() OVER(ORDER BY ...)
DENSE_RANK() OVER(ORDER BY ...)
ROW_NUMBER() OVER(ORDER BY ...)

rank 函数和 dense_rank 函数区别是，rank 函数不是密集的而 dense_rank 函数是密集的。例如，如果存在 3 个相同排名的记录，那么使用 rank 函数时，第四名的 rank 为 4，而使用 dense_rank，rank 值为 2。row_number 函数则是每一行都有自己的唯一编号，即使具有相同 rank 值的行也会获得连续的数字。