SQL Window Functions

Properties2

Type	Concept
Note created	Feb 17, 2025

Window functions are arguably one of SQL’s more powerful features, allowing us to run powerful aggregations in a very compact syntax, that would require several queries otherwise. According to PostgreSQL documentation:

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. But unlike regular aggregate functions, use of a window function does not cause rows to become grouped into a single output row — the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

Examples

The most basic example is to run a total:

select
	task_id,
	task_group
	duration_seconds,
  sum(duration_seconds) over (
		partition by task_group
		order by start_time
	) as running_time_per_task
from example_schema.example_relation

In this example, we can see how the window function is defined by:

• A partition by clause, that states how to segregate each individual group with columns or expressions,
• An order by clause, that states how to sort the samples for the aggregation.
• A frame clause that defines bounds for the window.

Resources

• SQL Window Functions cheat sheet