PostgreSQL的window函数整理.docVIP

PostgreSQL的window函数整理A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.?? ? Window Functions in SQL is an OLAP functionality that provides ranking, cumulative computation, and partitioning aggregation. Many commercial RDMBS such like Oracle, MS SQL Server and DB2 have implemented part of this specification, while open source RDMBS including PostgreSQL, MySQL and Firebird doesnt yet. To implement this functionality on PostgreSQL not only helps many users move from those RDBMS to PostgreSQL but encourages OLAP applications such as BI (Business Inteligence) to analyze large data set. This specification is defined first in SQL:2003, and improved in SQL:2008?简言之，聚合函数返回的各个分组的结果，窗口函数则为每一行返回结果,示例如下：?1.建示例表,初始化数据 DROP TABLE IF EXISTS empsalary; CREATE TABLE empsalary( depname varchar, empno bigint, salary int, enroll_date date ); INSERT INTO empsalary VALUES(develop,10, 5200, 2007/08/01); INSERT INTO empsalary VALUES(sales, 1, 5000, 2006/10/01); INSERT INTO empsalary VALUES(personnel, 5, 3500, 2007/12/10); INSERT INTO empsalary VALUES(sales, 4, 4800, 2007/08/08); INSERT INTO empsalary VALUES(sales, 6, 5500, 2007/01/02); INSERT INTO empsalary VALUES(personnel, 2, 3900, 2006/12/23); INSERT INTO empsalary VALUES(develop, 7, 4200, 2008/01/01); INSERT INTO empsalary VALUES(develop, 9, 4500, 2008/01/01); INSERT INTO empsalary VALUES(sales, 3, 4800, 2007/08/01); INSERT INTO empsalary VALUES(develop, 8, 6000, 2006/10/01); INSERT INTO empsalary VALUES(develop, 11, 5200, 2007/08/15); postgres=# select * from empsalary ; depname | empno | salary