SQL Window Functions
Introduction
Window functions perform ranking, aggregation, and value functions across a certain window, i.e. set of rows. This is similar to the kind of calculation that an aggregate function may perform. However, using a window function prevents rows from being aggregated into a single output row, unlike ordinary aggregate functions; instead, the rows maintain their individual identities. That means that the window function has access to more rows of the query result than just the current row. Here’s a basic visualization of what we just explained:
Observe how the three rows are combined into one row using the GROUP BY aggregate in the upper part of the figure above. Each row can have an aggregation value output thanks to the window function shown on the lower part of the same figure. By doing this, you could avoid having to do a join after the GROUP BY.
The keyword that we use with window functions is “OVER”. This clause does two things:
- Divides rows into groups called sets. (The phrase "PARTITION BY" is utilized)
- Puts rows inside such divisions in a certain sequence. (The phrase “ORDER BY” is utilized)
The three primary categories of window functions are aggregate, ranking, and value functions. You can see some of the names of the functions included in each category in the figure below.
The table we’ll use in all following examples throughout this tutorial can be seen below (named Table1).
