Window function queries are characterised by the OVER keyword, following which the set of rows used for the calculation is specified. By default, the set of rows used for the calculation (the "window) is the entire dataset, which can be ordered with the ORDER BY clause. The PARTITION BY clause is used to reduce the window to a particular group within the dataset.
It is also possible to specify which rows to include for the window function (for example, the current row and all preceding rows). See Window Frames for more details.
Scope
Window functions were introduced in SQL:2003, and their definition was expanded in subsequent versions of the standard. The last expansion was in the latest version of the standard, SQL:2011.
Most database products support a subset of the standard, they implement some functions defined as late as in SQL:2011, and at the same time leave some parts of SQL:2008 unimplemented.
MariaDB:
Supports ROWS and RANGE-type frames
All kinds of frame bounds are supported, including RANGE PRECEDING|FOLLOWING n frame bounds (unlike PostgreSQL or MS SQL Server)
Does not yet support DATE[TIME] datatype and arithmetic for RANGE-type frames (MDEV-9727)
Does not support GROUPS-type frames (it seems that no popular database supports it, either)
Does not support frame exclusion (no other database seems to support it, either) (MDEV-9724)
Does not support explicit NULLS FIRST or NULLS LAST.
Does not support nested navigation in window functions (this is VALUE_OF(expr AT row_marker [, default_value) syntax)
First, let's order the records by email alphabetically, giving each an ascending rnum value starting with 1. This will make use of the ROW_NUMBER window function:
SELECT row_number() OVER (ORDER BY email) AS rnum,
email, first_name, last_name, account_type
FROM users ORDER BY email;
+------+------------------------+------------+-----------+--------------+
| rnum | email | first_name | last_name | account_type |
+------+------------------------+------------+-----------+--------------+
| 1 | admin@boss.org | Admin | Boss | admin |
| 2 | bob.carlsen@foo.bar | Bob | Carlsen | regular |
| 3 | eddie.stevens@data.org | Eddie | Stevens | regular |
| 4 | john.smith@xyz.org | John | Smith | regular |
| 5 | root@boss.org | Root | Chief | admin |
+------+------------------------+------------+-----------+--------------
We can generate separate sequences based on account type, using the PARTITION BY clause:
SELECT row_number() OVER (PARTITION BY account_type ORDER BY email) AS rnum,
email, first_name, last_name, account_type
FROM users ORDER BY account_type,email;
+------+------------------------+------------+-----------+--------------+
| rnum | email | first_name | last_name | account_type |
+------+------------------------+------------+-----------+--------------+
| 1 | admin@boss.org | Admin | Boss | admin |
| 2 | root@boss.org | Root | Chief | admin |
| 1 | bob.carlsen@foo.bar | Bob | Carlsen | regular |
| 2 | eddie.stevens@data.org | Eddie | Stevens | regular |
| 3 | john.smith@xyz.org | John | Smith | regular |
+------+------------------------+------------+-----------+--------------+
Given the following structure and data, we want to find the top 5 salaries from each department.
We could do this without using window functions, as follows:
select dept, name, salary
from employee_salaries as t1
where (select count(t2.salary)
from employee_salaries as t2
where t1.name != t2.name and
t1.dept = t2.dept and
t2.salary > t1.salary) < 5
order by dept, salary desc;
+-------------+--------------+--------+
| dept | name | salary |
+-------------+--------------+--------+
| Engineering | Dharma | 3500 |
| Engineering | Binh | 3000 |
| Engineering | Adalynn | 2800 |
| Engineering | Samuel | 2500 |
| Engineering | Cveta | 2200 |
| Sales | Carbry | 500 |
| Sales | Clytemnestra | 400 |
| Sales | Juraj | 300 |
| Sales | Kalpana | 300 |
| Sales | Svantepolk | 250 |
+-------------+--------------+--------+
This has a number of disadvantages:
if there is no index, the query could take a long time if the employee_salary_table is large
Adding and maintaining indexes adds overhead, and even with indexes on dept and salary, each subquery execution adds overhead by performing a lookup through the index.
Let's try achieve the same with window functions. First, generate a rank for all employees, using the RANK function.
Each department has a separate sequence of ranks due to the PARTITION BY clause. This particular sequence of values for rank() is given by the ORDER BY clause inside the window function’s OVER clause. Finally, to get our results in a readable format we order the data by dept and the newly generated ranking column.
Now, we need to reduce the results to find only the top 5 per department. Here is a common mistake:
select
rank() over (partition by dept order by salary desc) as ranking,
dept, name, salary
from employee_salaries
where ranking <= 5
order by dept, ranking;
ERROR 1054 (42S22): Unknown column 'ranking' in 'where clause'
Trying to filter only the first 5 values per department by putting a where clause in the statement does not work, due to the way window functions are computed. The computation of window functions happens after all WHERE, GROUP BY and HAVING clauses have been completed, right before ORDER BY, so the WHERE clause has no idea that the ranking column exists. It is only present after we have filtered and grouped all the rows.
To counteract this problem, we need to wrap our query into a derived table. We can then attach a where clause to it:
