The Magic of Window Functions in Postgres

Window functions are often used to simplify complex queries and for data analytics. They allow analysis that is normally performed in client applications to be more efficiently processed by the database server.

This presentation explains the many window function facilities and how they can be used to produce useful SQL query results.


In this webinar you will learn:

  • The basics of window functions
  • Window function syntax
  • Window syntax with generic aggregates
  • Window-specific functions
  • Window function examples


Webinar video:


Webinar slides:


Webinar Q&A

Q: ROW_NUMBER() can be used as the work-around for ROWNUM?

A: BJM:  I have seen people use row_number as a work-around, but I don’t think it behaves the same as Oracle’s ROWNUM.


Q: In what kind of scenario - one should use ROWS, I mean to ask this is what’s the use of ROWS if it’s only mean CURRENT ROW.

A: ROWS mode allows you to turn off the default behavior that CURRENT ROW means all rows that are peers.


Q: What is the best practice about the Window keyword. To use it or not when defining Window functions?

A:  When you don’t want to repeat the same window clause multiple times in the query.


Q: Can you explain the difference between dense_rank() with window function vs with WITHIN GROUP?  

A: No.  I have not studied that yet.