Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

AdvancedAdvanced PostgreSQL

Theory: What are Window Functions?

The query

SQL
-- Q: Explain Window Functions vs Aggregate Functions.
-- A: Aggregate functions (like SUM, COUNT) group multiple rows into a 
-- single output row. 
-- Window functions perform calculations across a set of rows related 
-- to the current row, but they DO NOT group them into a single row. 
-- The original rows retain their separate identities.
Tested against PostgreSQL 16

Note

“Uses the OVER() clause. Examples: RANK(), DENSE_RANK(), ROW_NUMBER(), LEAD(), LAG().