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.000000...