What is a Window Function?

4 weeks ago

A window function in SQL is a type of function that performs calculations across a specific “window” of rows related to the current row, rather than collapsing multiple rows into a single output (like aggregate functions do). This allows you to compute things like running totals, ranking, and moving averages while still retaining the individual row details.

Key Components of Window Functions

A window function works with the OVER() clause, which defines the window of rows the function operates on.

1. PARTITION BY (Optional)

Divides the result set into partitions (similar to GROUP BY but without collapsing rows). The function is applied separately within each partition.

2. ORDER BY (Optional)

Specifies the order of rows within each partition before the window function is applied.

3. FRAME SPECIFICATION (Optional)

Defines the subset of rows in the partition to include in the calculation (e.g., rows before/after the current row).

Common Window Functions

Here are some commonly used window functions:

1. Ranking Functions

Used to assign ranks to rows.

RANK() – Assigns a unique rank, skipping numbers when there are ties.
DENSE_RANK() – Similar to RANK(), but without gaps in numbering.
ROW_NUMBER() – Assigns a unique row number to each row.

Example:

SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num FROM employees;

This ranks employees within each department based on salary.

2. Aggregate Functions as Window Functions

SUM(), AVG(), MIN(), MAX(), COUNT() can be used as window functions.

Example: Running Total

SELECT customer_id, order_id, order_date, amount, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total FROM orders;

This calculates a running total of the amount spent by each customer.

3. Analytical Functions

LEAD() – Fetches the value from the next row.
LAG() – Fetches the value from the previous row.
FIRST_VALUE() – Gets the first value in the window.
LAST_VALUE() – Gets the last value in the window.

Example: Comparing Sales to the Previous Month

SELECT customer_id, order_date, amount, LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS prev_order_amount, LEAD(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS next_order_amount FROM orders;

This allows you to compare the current order amount to the previous and next orders.

4. FRAME Specification in Window Functions

By default, window functions consider all rows up to the current row, but you can define a custom frame using:

ROWS or RANGE
UNBOUNDED PRECEDING (from the start)
CURRENT ROW (only the current row)
N PRECEDING / N FOLLOWING (relative to the current row)

Example: Moving Average Over 3 Rows

SELECT employee_id, salary, AVG(salary) OVER (ORDER BY employee_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM employees;

This calculates a moving average considering the current row and the previous two rows.

Why Use Window Functions?

Retain Row-Level Detail – Unlike GROUP BY, which collapses rows, window functions keep all rows intact.
Perform Advanced Analytics – Useful for ranking, running totals, and comparisons within partitions.
Flexible and Powerful – They allow complex computations without needing subqueries or self-joins.