SQL Window Functions
This lesson introduces you to SQL window functions. They are a powerful kind of function, that computes a result based on a set of rows, instead of computing based on a single row. They are similar to aggregate functions. The term window refers to the set of rows used to calculate the result of the function. This lesson focuses on how window functions work and the syntax to use.
SQL Window functions enable you to calculate functions including:
min on a group of records. The individual records are not collapsed, so you can create a query combining or showing the individual record together with the result of a window function. This ability to combine record level fields with results of functions applied to a group of records is what makes windows functions so powerful.
Understanding How SQL Window Functions Work
Here is an example using the
This example uses window functions to find the salary of each employee and the highest salary in his/her department. The result is the employee name, salary, and the top salary of the department where he/she works.
SELECT full_name, salary, department MAX(salary) OVER (PARTITION BY department) as top_salary FROM employee
PARTITION BY clause in the query creates groups of records (the
window) having the same value in the column department. The
MAX() function finds the maximum value of salary in the window. Here is the result:
Positional SQL Window Functions
When you compare window functions vs aggregate functions, you find a powerful feature on window functions side: positional functions. They can obtain a column value from other records in the same window, which enables you to create complex reports in a few SQL lines. Two of these functions are
lead()returns a column value of the previous record in the window.
lag()returns a column value from the next record in the window.
To use these functions it is important to have the window ordered by any column in the table.
Here is a table (share) to store shares from companies with their market values at some point in time:
Suppose you want a report with the value of every share with the previous value and the variation percentage. You can use the
lead() function to obtain the previous value of the share. Note the importance of having the window ordered by timestamp. If the window is not ordered by timestamp, the results are wrong.
SELECT share_symbol, timestamp, value, lag(value) OVER (PARTITION BY share_symbol ORDER BY timestamp ) AS previous_value, trunc(((value - (lag(value) OVER (PARTITION BY share_symbol ORDER BY timestamp )))*100)/value,2) AS percentage_variation FROM share
Here is the result:
The query result columns previous_value and
percentage_variation are calculated columns using values from different records in the same table, this is really powerful!
There are more window functions such as
last_value(), which are explored later.
SQL Window functions are one of the most unknown parts of the SQL language. As you saw in this article, they are powerful and flexible. There are clauses (for example
PARTITION BY or
WINDOW FRAME) and topics that were not covered in this article. Keep going, learn
SQL and increase your skills!
IN THIS PAGE