/understand-sql-window-functions

📖 Support examples for learning Window Functions in PostgreSQL. Ready to run.

Understand Window functions with Postgres

forthebadge

Piotr Lewandowski, @constjs

Table of content

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

Database set-up

AdventureWorks - Sample database prepared by Microsoft for their trainings.

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?

I don't know