SQL: Window Functions

20th August 2020 at 2:19pm
SQL
Note
Window Function 在我看来过于复杂,未深入研究。需要用时看看 PG 的文档:3.54.2.89.227.2.5

窗口函数(Window Functions),定义:

A window function performs a calculation across a set of table rows that are somehow related to the current row.
- From PostgreSQL Docs

感觉用在数据分析上比较有用。

列出员工工资及其部门平均工资:

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

列出员工工资以及其在部门中的工资排名:

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    10 |   5200 |    2
 develop   |    11 |   5200 |    2
 develop   |     9 |   4500 |    4
 develop   |     7 |   4200 |    5
 personnel |     2 |   3900 |    1
 personnel |     5 |   3500 |    2
 sales     |     1 |   5000 |    1
 sales     |     4 |   4800 |    2
 sales     |     3 |   4800 |    2
(10 rows)

Window function 的语法关键点是 OVER。如果顺序不重要,ORDER BY 可以不要;如果不需要分区(虽然不分区意义不大),PARTITION BY 也可以不要,FROM、WHERE 等筛选出来的全部数据组成一个唯一分区。