ROW_NUMBER()
Assigns a unique sequential number to each row within a result set.
Example:
sql
SELECT name, ROW_NUMBER() OVER (ORDER BY age) as row_num
FROM students;
RANK()
Assigns a unique rank to each row within a result set based on specified criteria.
Example:
sql
SELECT name, RANK() OVER (ORDER BY score DESC) as ranking
FROM players;
DENSE_RANK()
Similar to RANK(), but does not leave gaps between rank values when there are ties.
Example:
sql
SELECT name, DENSE_RANK() OVER (ORDER BY score DESC) as dense_rank
FROM players;
NTILE(n)
Divides the result set into 'n' roughly equal parts and assigns a group number to each row.
Example:
sql
SELECT name, score, NTILE(4) OVER (ORDER BY score) as quartile
FROM students;
SUM() OVER()
Calculates a running total of a specified column within a result set.
Example:
sql
SELECT date, revenue, SUM(revenue) OVER (ORDER BY date) as running_total
FROM sales;
AVG() OVER()
Calculates a running average of a specified column within a result set.
Example:
sql
SELECT date, revenue, AVG(revenue) OVER (ORDER BY date) as running_avg
FROM sales;
LEAD()
Provides access to subsequent rows from the current row within the result set.
Example:
sql
SELECT name, age, LEAD(age, 1) OVER (ORDER BY age) as next_age
FROM students;
LAG()
Provides access to previous rows from the current row within the result set.
Example:
sql
SELECT name, age, LAG(age, 1) OVER (ORDER BY age) as prev_age
FROM students;
FIRST_VALUE()
Returns the value of a specified column from the first row of the result set.
Example:
sql
SELECT department, salary,
FIRST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) as lowest_salary
FROM employees;
LAST_VALUE()
Returns the value of a specified column from the last row of the result set.
Example:
sql
SELECT department, salary,
LAST_VALUE(salary) OVER (PARTITION BY department ORDER BY salary) as highest_salary
FROM employees;
Comments