SQL Window Function Examples
This lesson presents SQL window function examples. SQL Window functions are powerful functions that compute their result based on a set of rows (the
window), instead of computing based on a single row. They are similar to SQL aggregate functions.
One important difference between
window functions and
aggregation functions is when you use aggregate functions with the GROUP BY clause, you lose the individual row. This prevents you from mixing attributes of the individual row with results of the aggregate function. Window functions do not have this restriction, enabling you to mix the results with record level fields. This is really good news for SQL developers.
Using SQL Window Functions
In the SQL Window Functions lesson you learned the syntax and clauses of window functions. This lesson focuses on examples. One important point to note about window functions is the placement in the SQL query. You can invoke a window function in the
SELECT list statement or in the ORDER BY clause of a query, but not in the
GROUP BY or
HAVING clauses. All of these examples have a window function in the column list.
Here is an example using the table
RANK() function is one of the simplest window functions. It returns the position of any row inside the partition. To obtain the rank salary for each department:
SELECT RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank, department, employee_id, full_name, salary FROM employee;
Here is the resulting department employee ranking by salary:
Think about how to change the query if you want the same report but with all number 1 ranking employees first, then all number 2 employees, and so on. This change is left to the reader as practice.
Another interesting example is a query to obtain a metric for every employee about how close they are from the top salary of their department. Here is the formula to obtain this metric:
employee_salary / max_salary_in_dept
This query orders all employees by the calculated metric, sorted by employees with the lowest salary related to the max salary of their department.
SELECT employee_id, full_name, department, salary, salary/MAX(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS salary_metric FROM employee
SQL Data Queries Oriented To Window Functions
These two query examples showcase the real power of window functions. The examples are based on a simple database table train_schedule:
The first task is to add a new column called time_to_next_station. You can calculate this value by subtracting the station times for pairs of contiguous stations. Calculating this value without window functions can be very complicated. Using the window function
LEAD simplifies the task. Here is the query to obtain the time of the next station using the
SELECT train_id, station, time as "station_time", lead(time) OVER (PARTITION BY train_id ORDER BY time) - time AS time_to_next_station FROM train_schedule ORDER BY 1 , 3;
LEAD() window function is used to obtain the value of a column for the next row in the window. Note the calculation of the metric is done using an expression combining an individual column(time) with a window function(lead). This combination is not valid with aggregate functions.
Here are the results:
In this example, you add a new
elapsed_travel_time column representing the elapsed time of the trip until the current station. You use the
MIN() window function to obtain the starting time of the trip and subtract the current station time.
SELECT train_id, station, time as "station_time", time - min(time) OVER (PARTITION BY train_id ORDER BY time) AS elapsed_travel_time, lead(time) OVER (PARTITION BY train_id ORDER BY time) - time AS time_to_next_station FROM train_schedule;
Here is the result:
Window functions are one of the least known features of the SQL language, though among the most powerful and flexible. This article touches on the basics of window functions. There are clauses, such as
PARTITION BY and
WINDOW FRAME, and topics to continue to explore. Keep going, learn SQL and increase your skills!
IN THIS PAGE