SQL Window Functions

Summary: in this tutorial, you will learn about SQL window functions that solve complex query challenges in easy ways.

Introduction to SQL Window Functions

The aggregate functions perform calculations across a set of rows and return a single output row.

The following query uses the SUM() aggregate function to calculate the total salary of all employees in the company:

SELECT 
    SUM(salary) sum_salary
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

As shown clearly in the output, all rows from the employees table are grouped into a single row.

Similar to an aggregate function, a window function calculates on a set of rows. However, a window function does not cause rows to become grouped into a single output row.

The following query uses the SUM() as a window function. It returns the sum salary of all employees along with the salary of each individual employee:

SELECT
    first_name,
    last_name,
    salary,
    SUM(salary) OVER() sum_salary
FROM
    employees;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

sql window functions - SUM window function example

In this example, the OVER() clause signals that the SUM() function is used as a window function.

The following picture illustrates the main difference between aggregate functions and window functions:

SQL window function syntax

The syntax of the window functions is as follows:

window_function_name ( expression ) OVER (
    partition_clause
    order_clause
    frame_clause
)
Code language: SQL (Structured Query Language) (sql)

window_function_name

The name of the supported window function such as ROW_NUMBER(), RANK(), and SUM().

expression

The target expression or column on which the window function operates.

OVER clause

The OVER clause defines window partitions to form the groups of rows specifies the orders of rows in a partition. The OVER clause consists of three clauses: partition, order, and frame clauses.

The partition clause divides the rows into partitions to which the window function applies. It has the following syntax:

PARTITION BY expr1, expr2, ...
Code language: SQL (Structured Query Language) (sql)

If the PARTITION BY clause is not specified, then the whole result set is treated as a single partition.

The order clause specifies the orders of rows in a partition on which the window function operates:

ORDER BY 
    expression [ASC | DESC]  [NULL {FIRST| LAST}]
    ,...
Code language: SQL (Structured Query Language) (sql)

A frame is the subset of the current partition. To define the frame, you use one of the following syntaxes:

{ RANGE | ROWS } frame_start
{ RANGE | ROWS } BETWEEN frame_start AND frame_end  
Code language: SQL (Structured Query Language) (sql)

where frame_start is one of the following options:

N PRECEDING
UNBOUNDED PRECEDING
CURRENT ROW
Code language: SQL (Structured Query Language) (sql)

and frame_end is one of the following options:

CURRENT ROW
UNBOUNDED FOLLOWING
N FOLLOWING
Code language: SQL (Structured Query Language) (sql)

The following picture illustrates a frame and its options:

UNBOUNDED PRECEDING: the frame starts at the first row of the partition.
N PRECEDING: the frame starts at Nth rows before the current row.
CURRENT ROW: means the current row that is being evaluated.
UNBOUNDED FOLLOWING: the frame ends at the final row in the partition.
N FOLLOWING: the frame ends at the Nh row after the current row.

The ROWS or RANGE specifies the type of relationship between the current row and frame rows.

ROWS: the offsets of the current row and frame rows are row numbers.
RANGE: the offset of the current row and frame rows are row values.

SQL window function types

The window functions are divided into three types value window functions, aggregation window functions, and ranking window functions:

Value window functions

Ranking window functions

Aggregate window functions

AVG()
COUNT()
MAX()
MIN()
SUM()