SQL Window Functions

Introduction

Window functions are a powerful feature in SQL that allow you to perform calculations across a set of rows related to the current row without collapsing them into a single group. Unlike aggregate functions such as SUM(), AVG(), or COUNT(), which group rows together and return a single result, window functions operate over a “window” or subset of data defined by the user while still retaining the individual rows in the result set.

This article will explore the concept of window functions, their common uses, and examples to demonstrate their flexibility and importance in modern SQL queries.

What is a Window Function?

A window function performs a calculation across a set of table rows that are somehow related to the current row. The “window” refers to the set of rows that the function can see when it’s applied to a specific row. It doesn’t reduce the result set; instead, it adds additional information to each row, making it useful for tasks like running totals, rank generation, and moving averages.

The syntax for window functions generally follows this structure:

function_name([arguments]) OVER (
    [PARTITION BY expression]
    [ORDER BY expression]
    [ROWS or RANGE specifications]
)

Here’s a breakdown:

• function_name: The window function being used, such as ROW_NUMBER(), RANK(), or an aggregate function like SUM().
• PARTITION BY: Divides the result set into partitions, which are smaller subsets of the data.
• ORDER BY: Determines the order of rows within each partition.
• ROWS or RANGE: Defines the frame of rows to include in the calculation.

Types of Window Functions

1. Ranking Functions

Ranking functions assign a unique rank to each row within a partition based on a specified order. Common ranking functions include:

• ROW_NUMBER(): Assigns a unique row number to each row within a partition.
• RANK(): Assigns a rank to each row, with gaps if there are ties.
• DENSE_RANK(): Similar to RANK(), but without gaps in ranking values when there are ties.

Example: Suppose you have an employees table with columns name, department, and salary:

| id  | name    | department | salary |
|-----|---------|------------|--------|
| 1   | Alice   | Sales      | 70000  |
| 2   | Bob     | Sales      | 80000  |
| 3   | Charlie | Sales      | 60000  |
| 4   | Dave    | HR         | 90000  |
| 5   | Eve     | HR         | 95000  |
| 6   | Frank   | HR         | 85000  |
| 7   | Grace   | IT         | 100000 |
| 8   | Hank    | IT         | 110000 |
| 9   | Ian     | IT         | 95000  |

To rank employees by salary within each department, you could use the following query:

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

This query ranks employees based on their salary within each department. The PARTITION BY department clause ensures that the ranking resets for each department.

Result:

| name    | department | salary | salary_rank |
|---------|------------|--------|-------------|
| Bob     | Sales      | 80000  | 1           |
| Alice   | Sales      | 70000  | 2           |
| Charlie | Sales      | 60000  | 3           |
| Eve     | HR         | 95000  | 1           |
| Dave    | HR         | 90000  | 2           |
| Frank   | HR         | 85000  | 3           |
| Hank    | IT         | 110000 | 1           |
| Grace   | IT         | 100000 | 2           |
| Ian     | IT         | 95000  | 3           |

2. Aggregate Functions

Window functions can also be used with aggregate functions such as SUM(), AVG(), COUNT(), etc., to provide running totals, moving averages, and other cumulative calculations without collapsing the result set.

Example: Suppose you have an employees table with columns name, department, and salary:

| id  | name    | department | salary |
|-----|---------|------------|--------|
| 1   | Alice   | Sales      | 70000  |
| 2   | Bob     | Sales      | 80000  |
| 3   | Charlie | Sales      | 60000  |
| 4   | Dave    | HR         | 90000  |
| 5   | Eve     | HR         | 95000  |
| 6   | Frank   | HR         | 85000  |
| 7   | Grace   | IT         | 100000 |
| 8   | Hank    | IT         | 110000 |
| 9   | Ian     | IT         | 95000  |

To calculate a running total of salaries for each employee in the employees table, you could write the following query:

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total
FROM employees;

This query calculates a running total of salaries within each department, ordered by salary. The SUM() function calculates the cumulative sum for each row based on the order defined in the ORDER BY clause.

Result:

| name    | department | salary | running_total |
|---------|------------|--------|---------------|
| Charlie | Sales      | 60000  | 60000         |
| Alice   | Sales      | 70000  | 130000        |
| Bob     | Sales      | 80000  | 210000        |
| Frank   | HR         | 85000  | 85000         |
| Dave    | HR         | 90000  | 175000        |
| Eve     | HR         | 95000  | 270000        |
| Ian     | IT         | 95000  | 95000         |
| Grace   | IT         | 100000 | 195000        |
| Hank    | IT         | 110000 | 305000        |

3. Offset Functions

Offset functions are used to access rows relative to the current row in the window. These include:

• LAG(): Returns the value from a previous row within the window.
• LEAD(): Returns the value from the next row within the window.
• FIRST_VALUE(): Returns the first value in the window.
• LAST_VALUE(): Returns the last value in the window.

Example: To compare each employee’s salary with the previous employee’s salary (ordered by salary), you can use the LAG() function:

SELECT name, department, salary,
       LAG(salary) OVER (PARTITION BY department ORDER BY salary) AS previous_salary
FROM employees;

This query returns each employee’s salary along with the salary of the previous employee in the same department. If there’s no previous employee, the result will be NULL.

Practical Use Cases for Window Functions

1. Ranking Employees by Performance

If you want to rank employees based on performance metrics while still displaying individual employee records, window functions can be extremely useful. Instead of grouping employees, you can rank them based on their performance score while preserving all relevant data:

SELECT name, performance_score,
       RANK() OVER (ORDER BY performance_score DESC) AS performance_rank
FROM employees;

This query ranks employees based on their performance score and can be used to identify top performers or those needing improvement.

2. Calculating Moving Averages

Window functions make it easy to compute moving averages, which are useful in financial analysis, trend detection, and forecasting.

SELECT date, sales,
       AVG(sales) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales_data;

This query calculates a 5-day moving average for sales, using the ROWS BETWEEN 4 PRECEDING AND CURRENT ROW clause to define the frame of rows to include in the average calculation.

3. Running Totals and Cumulative Sums

Window functions are excellent for calculating running totals or cumulative sums. For example, in financial applications, you might need to calculate a running total of expenses:

SELECT date, expense,
       SUM(expense) OVER (ORDER BY date) AS running_total
FROM expenses;

This query calculates the cumulative expense up to each date.

Window Frame Specification

The ROWS and RANGE clauses allow further refinement of the window by specifying the exact set of rows to include in the calculation for each row.

• ROWS: Defines the window in terms of a physical number of rows before or after the current row.
• RANGE: Defines the window based on a range of values relative to the current row.

Example: The following query calculates a running total using ROWS:

SELECT name, salary,
       SUM(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM employees;

This query specifies that the running total should include all rows from the beginning up to the current row.

Conclusion

Window functions are a crucial feature in SQL, enabling advanced analytics and reporting without the need for complex subqueries or self-joins. They provide flexibility for ranking, aggregation, and accessing rows relative to the current row, making it easier to solve common business problems such as calculating running totals, generating rankings, and computing moving averages.

Understanding and using window functions can significantly enhance your SQL queries, enabling you to write more efficient and powerful data analysis queries.