Window functions in Postgres 8.4

As you can read in this blog post, PostgreSQL 8.4 will contain so called window functions which are part of the SQL 2008 specification. Now that I know how this feature is named, I know what I was missing for the past 10 years. Window functions allow you to produce aggregated values from each row over a specific set of rows (a partition).

Assuming a table staff defined as:

create table staff (
    name    varchar(30),
    dept    varchar(30),
    salaray int
);

we can now show each staff person together with the average salary of the department he/she belongs to using a very simple SQL statement:

select name, dept, salary,
       avg(salary) over (partition by dept)
from staff;

Without using window functions, the SQL statement is considerably more complex and requires ugly subselects and joins:

select s.name, s.dept, s.salary,
       a.avg_salary
from staff s,
     (select dept, avg(salary) as avg_salary
      from staff group by dept) as a
where s.dept = a.dept

Window functions can do a lot more, for example, they can produce cummulative sums easily.