SQL Window Functions

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

Introduction to SQL Window Functions #

The aggregate functions perform calculations across 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) total_salary
FROM
  employees;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 total_salary
--------------
    322400.00Code language: plaintext (plaintext)

The output indicates that query group rows from the employees table into a single row.

Like an aggregate function, a window function operates on a set of rows. However, a window function does not group rows into a single row.

For example, the following query uses the SUM() function as a window function:

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

Try it

It returns the salary of each individual employee along with the total salary of all employees:

 first_name  |  last_name  |  salary  | sum_salary
-------------+-------------+----------+------------
 Steven      | King        | 24000.00 |  322400.00
 Neena       | Kochhar     | 17000.00 |  322400.00
 Lex         | De Haan     | 17000.00 |  322400.00
 Alexander   | Hunold      |  9000.00 |  322400.00
 Bruce       | Ernst       |  6000.00 |  322400.00
...

In this example, the OVER() clause makes the SUM() function a window function.

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

SQL window function syntax #

Here’s the basic syntax of a window function:

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

window_function_name

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

expression

The expression or column on which the window function operates.

OVER clause

The OVER clause defines a window or a partition. The OVER clause consists of three clauses:

Partition By clause
Order By clause
Frame clause

The PARTITION BY 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 you don’t use the PARTITION BY clause, the window function treats the whole result set as a single partition.

The ORDER BY specifies the orders of rows in each partition:

ORDER BY 
    sort_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 ROWCode language: SQL (Structured Query Language) (sql)

and frame_end is one of the following options:

CURRENT ROW
UNBOUNDED FOLLOWING
N FOLLOWINGCode 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 the nth row 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 #

There are three types of window functions including value window functions, aggregation window functions, and ranking window functions.

Value window functions #

Ranking window functions #

Aggregate window functions #

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