SQL Window Function

1. 窗口函数

窗口函数(window (analytical) functions): 简单的说， 就是在每一个窗口上进行分析操作。 窗口就是一个行的集合。分析操作就是一些聚合操作。

2. 语法

<Function> OVER ( [PARTITION BY <col...>]
                   ORDER BY <col...>
                  [ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING]) 

2.1 分析函数（Analytical function）

Three types of analytical functions:

• Analytic aggregate functions：SUM(), AVG(), COUNT(), MAX(), MIN()

• Analytic navigation functions：FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG()

• FIRST_VALUE()/LAST_VALUE(): 窗口第一条/最后一条LEAD()/LAG()：随后一条/前面一条

• Analytic numbering functions: RANK(), DENSE_RANK(), ROW_NUMBER()

RANK(), DENSE_RANK() 存在并列，ROW_NUMBER()不存在并列，前两者的区别是RANK()会跳过相同的排位，即排名会不连续，如1,1,3.

2.2 PARTITION BY & ORDER BY

PARTITION BY: 决定横向分组，类似与GROUP BY的作用，即按照某个属性分为一类。非必须，如果没有则把整个当成一个partition。

ORDER BY: 决定纵向排序，即在每一个partition中如何排序。

2.4 ROWS

ROWS：窗口框架(Window Framework Clauses), 即在每一个窗口中再限定范围。

比如：

• ROWS 2 PRECEDING: 前两行
• ROWS 2 FOLLOWING: 后两行
• ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING: 前后两行
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：当前partition中所有行。

3. 用法举例

3.1 GROUP BY vs OVER(PARTITION BY)

GROUP BY: 相当于汇总， 只返回一条数据。
PARTITION BY: 为每一行返回一个窗口内分析/聚合函数的值。

GROUP BY

mysql> SELECT AVG(salary)
    -> FROM salaries;
+-------------+
| AVG(salary) |
+-------------+
|  64904.4945 |
+-------------+
1 row in set (0.00 sec)

mysql> SELECT emp_no, AVG(salary)
    -> FROM salaries
    -> GROUP BY emp_no;
+--------+-------------+
| emp_no | AVG(salary) |
+--------+-------------+
|  10001 |  75388.9412 |
|  10002 |  72527.0000 |
|  10003 |  43030.2857 |
|  10004 |  56512.2500 |
|  10005 |  87275.7692 |
|  10006 |  43311.0000 |
|  10007 |  70826.7143 |
|  10008 |  49307.6667 |
|  10009 |  63437.6667 |
+--------+-------------+
9 rows in set (0.00 sec)

PARTITION BY

mysql> SELECT AVG(salary) OVER()
    -> FROM salaries;
+--------------------+
| AVG(salary