Window Functions

Tags: postgres

When group by’s limitation of collapsing rows into an aggregate aren’t what you want, but you still need to partition data while keeping rows expanded, reach for a window function.

A window function keeps every row, but adds a computed column based on the rows neighbors.

select id, body, count(*) over (partition by user_id) from posts;

There’s better examples in the docs https://www.postgresql.org/docs/current/tutorial-window.html

over(...) makes a window function.

some_function(args) over (
  partition by <cols>   -- split rows into independent groups
  order by    <cols>    -- sequence rows WITHIN each group
  <frame>               -- which neighbors in the group are visible
)

For ranking rows, use one of:

• row_number()
• rank()
• dense_rank()