Understand Window functions with Postgres

Piotr Lewandowski, @constjs

---

Table of content

0. Warm'up
1. What's the syntax?
2. Ranking functions
3. Windowing + bonus examples

Database set-up

AdventureWorks - Sample database prepared by Microsoft for their trainings.

• Installation for PostgreSQL

Key points of window functions?

• Aggregate function with OVER () clause
• Don't need GROUP BY
• Can access data from other rows / frames

Use cases

• Data manipulation
• Moving averages
• Cumulative sum
• Division of set (ranks or buckets)

Support

PostgreSQL, MS SQL Server, Oracle, DB2, H2

Why doesn't MySQL have window functions?