WINDOW FUNCTIONS

Introduction to window functions.

INTRODUCTION

Window functions are used to perform calculations across related rows without collapsing the result into a single value. This is similar to using aggregate functions except rows in output are not grouped together. Window functions enable ranking data, aggregating data, comparing row values and complex analytics.

Get Gracevalerie’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This guide is a walkthrough over different SQL window functions with hands-on practical examples.

OVER CLAUSE

The OVER clause defines the window partition and ordering of a set of rows in a table.

PARTITION BY CLAUSE

This clause is used to group table rows into partitions. It divides a window function result into data subsets where it is applied to each partition separately.

--syntax
OVER (PARTITION BY column_name)

TYPES OF WINDOW FUNCTIONS

There are three types of window functions:

• Aggregate functions: SUM, COUNT, MAX, MIN, AVG
• Value functions: LAG (), LEAD (), FIRST_VALUE ()
• Ranking functions: NTILE () , RANK () , ROW_NUMBER, DENSE_RANK,

RANK ()

This function assigns a rank or relative position to each row within a part of the data. The rank is assigned based on a provided column.

-- syntax
RANK() OVER(PARTITION BY column_name ORDER BY column_two)

-- example -> ranking employees by their salary in every department
SELECT first_name, last_name, salary, department,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as ranking
from employees;

In the above example, we created partitions for every department in our employees table and ranked them by salary. The result is as follows

rank window function result

We can also rank the entire table set without applying partitions as follows.

SELECT first_name, last_name, salary, department,
RANK() OVER (ORDER BY salary DESC) AS ranking
FROM employees;

ranking entire set

Notice how employees with similar salaries tie in the ranking. We can use DENSE RANK to give every row a unique rank.

DENSE_RANK ()

This function works exactly like RANK () but does not leave any gaps when ranking values. The rank of each row is distinct and sequential.

SELECT first_name, last_name, salary, department,
DENSE_RANK() OVER(ORDER BY salary DESC) ASranking
FROM employees;

The above example results to

DENSE_RANK() function result

If two or more rows have similar values, they share the same rank. The next row is assigned the previous rank value plus one. Unlike the RANK () function, no rank values are skipped.

ROW_NUMBER ()

Assigns a sequential value to each row in the dataset. Every following row value is incremented by 1 even if they have similar values. This means no two rows can share the same value.

SELECT first_name, last_name, salary, department,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS ranking
FROM employees;

ROW_NUMBER FUNCTION RESULT

NTILE ()

Divides rows in an ordered partition into specified number of buckets which are assigned a value starting from 1.

-- syntax
NTILE(num_of_groups) OVER (PARTITION BY column_name ORDER BY column_name) alias

-- example 
SELECT first_name, last_name, salary, department,
NTILE(4) OVER(partition by department ORDER BY salary DESC) as ranking
FROM employees;

RESULT

NTILE () WINDOW FUNCTION RESULT

LEAD ()

This function allows you to access a row at a given physical offset that follows the current row. It takes two arguments — the current row value that is a scalar value, and an offset which is the number of rows from the current row.

-- syntax
LEAD (expr, offset) OVER (PARTITION BY column_name ORDER BY column_name)

-- example
SELECT first_name, last_name, salary, department,
LEAD(salary, 1) OVER(partition by department
 ORDER BY salary DESC) as lead_salary
FROM employees;

LEAD FUNCTION RESULT

In this example, we used an offset of 1, which returns the next row salary value as our lead value. The last value in our partition returns NULL as the lead value because there are no succeeding values.

LAG ()

Returns the data from the previous row in the same table without using a self-join. It is similar to LEAD () but it provides access to a row at a given offset that comes before the current row.

-- syntax
LAG (expr, offset) OVER (PARTITION BY column_name ORDER BY column_name)

-- example
SELECT first_name, last_name, salary, department,
LAG(salary, 1) OVER(partition by department ORDER BY salary DESC) as ranking
FROM employees;

LAG () VALUE WINDOW FUNCTION

FIRST_VALUE ()

Returns the first value in an ordered set of rows within a partition

-- syntax
SELECT *, FIRST_VALUE(column_name) 
OVER (PARTITION BY column_name ORDER BY column_name) AS alias_name
FROM table_name;

CONCLUSION

Window functions perform calculations on a set of rows without collapsing them into a single result. They are essential for data analytics tasks within SQL. This article covers practical examples for how you can use window functions.