Window Function for PostgreSQL Desgin Overview [v01]

Abstract

Window Function 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 doesn't 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 plan is quite ugly, because for each window a Window node is added with a Sort node.
Probably all of window and sort process is packed into a Window node.
For this current plan, Sort node uses Tuplesort as you expect then Window node uses
Tuplestore to store each Partition tuples. This is supposed to be the worst plan. We are able to
get it better somehow.

Things to be discussed

How are Window functions (e.g. ROW_NUMBER()) to be defined?

How do you know about ranking boundaries in RANK()?

WHERE clause with window evaluation is allowed if and only if the WHERE consists only window evaluation? e.g. SELECT * FROM empsalary WHERE RANK() OVER (PARTITION BY depname) < 3;