Window functions (part3) - new functions
In the first and the second part of window
function series, I talked about the
OVER clause, with/without partitions and with/without ordering. Till then, no new function had been
introduced, so it has about the already existing aggregate functions.
Now, new window-only functions has been introduced. I'll separate them in two groups: ranking and navigational. Both set of functions can be used with/without partition/ordering, but them does not make much sense without ordering.
Ranking functions (DENSE_RANK, RANK and ROW_NUMBER)
With these functions, one can create different type of incremental counters. Think about
SUM(1) OVER (ORDER BY SALARY), these functions
does this type of thing, but all of them in different ways. Lets see an example query, also comparing with the
select id, salary, dense_rank() over (order by salary), rank() over (order by salary), row_number() over (order by salary), sum(1) over (order by salary) from employee order by salary;
And the result set:
As you see, the functions differs when repeated values are found in the order key (the salary of 10.00). With
DENSE_RANK, no gaps are
created and all repeated values receive the same counter. With
RANK, the initial counter is used for all repeated values, but it make
gaps so the next non-repeating value (the salary of 12.00) does not consider the previously repeated values as in different positions.
ROW_NUMBER, each line receives an incremental value. And
SUM(1) is very like
RANK, but the value is computed after all repeated
values are summed, so the gap is before the repeated values.
Navigational functions (LAG and LEAD)
With these functions, an expression could get the value of a previously (
LAG) or a posterior (
LEAD) row of the query. Follow the
select id, salary, lag(salary) over (order by salary), lead(salary) over (order by salary) from employee order by salary;
And the result set:
The functions have a second argument, that is implicitly 1, which means the number of previously/posterior rows will be queried. If this
row does not exist,
<null> is returned.
And of course, you can use these functions as expressions and compute, for example, the difference of an employee salary from the one greater and/or lesser.