Window functions

Window functions are extremely useful and powerful; therefore, they represent a must-know topic for every developer that interacts with a database via SQL. In a nutshell, the best way to quickly overview window functions is to start from a famous diagram representing a comparison between an aggregation function and a window function that highlights the main difference between them, as represented here:

Figure 13.3 – Aggregate functions versus window functions

As you can see, both the aggregate function and the window function calculate something on a set of rows, but a window function doesn't aggregate or group these rows into a single output row. A window function relies on the following syntax:

window_function_name (expression) OVER (
    Partition Order Frame
)

This syntax can be explained as follows:

Obviously, window_function_name represents the window function name, such as ROW_NUMBER(), RANK(...