SQL Window Functions
-
SQL Window functions are used to perform certain calculation on the current row of the table with the help of other rows.
-
Windows functions are mostly of two types
1. Ranking Window Function
2. Value Window Function
row_number()
ROW_NUMBER is a rank window function.
SYNTAX
SELECT
column_1,
column_2,
ROW_NUMBER() OVER() AS alias_name
FROM
table_name
-
ROW_NUMBER function will give us the rank and OVER is an mandatory clause where we are going to write over which window the method is going to run.
-
If we don't pass any paramter inside row_number than it will take whole column.
EXAMPLE
SELECT
emp_no,
salary,
ROW_NUMBER() OVER() AS row_num
FROM
salaries
-
We can use the over to partition by some parameters.
EXAMPLE
SELECT
emp_no,
salary,
ROW_NUMBER() OVER(PARTITION BY emp_no) AS row_num
FROM
salaries
-
We can even sort the values inside the over.
EXAMPLE
SELECT
emp_no,
salary,
ROW_NUMBER() OVER(PARTITION BY emp_no order by salary) AS row_num
FROM
salaries
-
Alternative Syntax to use the window functions.
EXAMPLE
SELECT
emp_no,
salary,
ROW_NUMBER() OVER w AS row_num
FROM
salaries
window w as (PARTITION BY emp_no order by salary DESC);
-
This syntax is beneficial if we want to use more than one window function with same partition values.
RANK()
Suppose we have person whose salary is same. so row_number function won't be able to identify it but rank will helps to do that.
rank() function also make sure to keep the count of number of rows present in that window.
EXAMPLE
SELECT
emp_no,
salary,
RANK() OVER w AS rank_num
FROM
salaries
WHERE emp_no = 13559
window w as (PARTITION BY emp_no order by salary DESC);
DENSE_RANK()
We can use dense_rank() function when we don't want to keep the count of the number of rows in the window.
EXAMPLE
SELECT
emp_no,
salary,
DENSE_RANK() OVER w AS rank_num
FROM
salaries
WHERE emp_no = 13559
window w as (PARTITION BY emp_no order by salary DESC);
LAG()
LAG() function is an value function, it will return an value unlike above functions which were returning an rank.
LAG() function will return the previous value.
EXAMPLE
SELECT
emp_no,
salary,
LAG() OVER w AS rank_num
FROM
salaries
WHERE emp_no = 13559
window w as (order by salary DESC);
LEAD()
LEAD() function is also an value function.
LEAD() function will return the next value.