Window functions in MySQL:
Window functions in MySQL are used to perform calculations across a set of rows related to the current row. They allow you to work with aggregate functions (like SUM, AVG, ROW_NUMBER) without collapsing rows, which is particularly useful for calculations over specific partitions or sequences within the data. Here are some common window functions in MySQL:
1. ROW_NUMBER():
Assigns a unique sequential integer to rows within a partition, ordered by a specified column.
Example:
select *,row_number() over(partition by st_medium order by st_marks desc) as rnk from students;
2. RANK():
Assigns a rank to each row within a partition, with gaps in ranks if there are ties.
Example:
select *,rank() over(partition by st_medium order by st_marks desc) as rnk from students;
3. DENSE_RANK():
Similar to RANK(), but without gaps in the ranking.
select *,dense_rank() over(partition by st_medium order by st_marks desc) as rnk from students;
4. LEAD() and LAG():
Allows access to subsequent (LEAD) or previous (LAG) rows within the same partition.
SELECT Year, Product, Sale, LEAD(Sale,1) OVER ( PARTITION BY Year ORDER BY Country) AS Next_Sale FROM sales_table;
SELECT Year, Product, Sale, LAG(Sale, 1, 0) OVER ( PARTITION BY Year ORDER BY Country) AS Previous_Sale_LAG FROM sales_table;