Overview
MySQL 8 introduced a series of powerful window functions that have greatly expanded the database’s repertoire of analytical operations. Among these are the FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() functions. This guide will take you through each function, explain how they work, and provide practical examples so you can start using them like a pro.
Window Functions in MySQL
To understand these new functions, it’s crucial first to comprehend window functions in MySQL. A window function performs a calculation across a set of table rows that are related to the current row, akin to an aggregate but not limited to group clauses. They provide a way to apply functions to specific frames or ‘windows’ of rows related to the current row, creating dynamic aggregations where the input rows can differ for each row of output.
FIRST_VALUE() Function
The FIRST_VALUE() window function returns the first value in an ordered set of values. This can be highly useful when you want to compare other row values to the first row value in the same partition.
SELECT
employee_name,
salary,
FIRST_VALUE(salary) OVER (ORDER BY salary ASC) AS lowest_salary
FROM
employees;
In the query above, every row will include the smallest salary due to the FIRST_VALUE function applied to the ordered list of salaries.
LAST_VALUE() Function
The LAST_VALUE() function complements FIRST_VALUE(), retrieving the last value in the window frame. Due to MySQL’s framing behavior, to get the expected results, you typically need to define the frame with RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
SELECT
employee_name,
salary,
LAST_VALUE(salary) OVER (
ORDER BY salary ASC
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS highest_salary
FROM
employees;
This code retrieves the highest salary within the partition.
NTH_VALUE() Function
More versatile than the previous two functions, NTH_VALUE() retrieves the N-th value from a set where N is a positive integer. Just like LAST_VALUE(), you need to take framing into account.
SELECT
employee_name,
salary,
NTH_VALUE(salary, 3) OVER (
ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
) AS third_highest_salary
FROM
employees;
Here, you would find the third highest salary in each window frame.
Practical Examples
Let’s put these functions into practical scenarios using a simple employee’s table containing ID, employee name, and salary.
Example 1: Rewarding Highest Performers
Suppose you want to determine relative bonuses at the end of the year. Such a calculation might entail finding out how an employee’s salary compares to the highest within their department.
SELECT
department,
employee_name,
salary,
LAST_VALUE(salary) OVER (
PARTITION BY department
ORDER BY salary DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS department_highest_salary
FROM
employees
ORDER BY
department, salary DESC;
Example 2: Time Series Analysis
In cases where you have a time series dataset, like stock prices, FIFO (First-In-First-Out) calculations become feasible using first and last values.
SELECT
trade_day,
stock_price,
FIRST_VALUE(stock_price) OVER (
ORDER BY trade_day ASC
) AS opening_price,
LAST_VALUE(stock_price) OVER (
ORDER BY trade_day ASC
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS closing_price
FROM
stock_prices;
This query would return the opening price and closing price of the stock for each trade_day.
Conclusion
MySQL 8’s FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() window functions provide SQL developers with more flexibility to craft analytical queries that operate over sets of rows rather than aggregated totals. Through the practical application of window functions, complex data trends can be more easily analyzed directly within your database environment. Familiarize yourself with them to leverage their full potential.
