WINDOW functions and LAG()

Window functions

This is an example taken straight from the PostgreSQL documentation

So what is a window function and why would you use it? Well, window functions all you to perform aggregations for groups while keeping the rows separate. So lets say you want to know the average salary by department and use that to find each employees difference from the average. The average is not stored in you employee salary table (empsalary) so you need to calculate it. You can use the WINDOW function to create a "window" around each deparment, then, the aggregate function will only be applied to that window.


SELECT depname, empno, salary, 
       avg(salary) OVER (PARTITION BY depname)
FROM empsalary;

Returns

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)

Now just use this as a subquery and subtract the average from the salary.

LAG

A useful function with WINDOW is LAG. It returns values from a previous row in the table (to return a value from the next row use the LEAD function).

If you have a purchases table and you want to find the average days between purchases you can use the LAG() function.

SELECT customer_id, AVG(delta) as avg_days_between_purchases
FROM (
    SELECT *, day::DATE - lag(day::DATE, 1) OVER 
        ( PARTITION BY customer_id ORDER BY day::DATE) delta
    FROM customer_purchases
    ORDER BY customer_id) foo
GROUP BY customer_id
ORDER  BY customer_id;

Easy Peasy