Window Function in SQL

A window function in SQL performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row:

• The row for which function evaluation occurs is called the current row.
• The query rows related to the current row over which function evaluation occurs comprise the window for the current row.

Window functions apply to aggregate and ranking functions over a particular window (set of rows). OVER clause is used with window functions to define that window. OVER clause does two things : 

• Partition rows to form a set of rows. (PARTITION BY clause is used) 
• Orders rows within those partitions into a particular order. (ORDER BY clause is used) 

Note: If partitions aren’t done, then ORDER BY orders all rows of the table. 

Syntax : 

SELECT column_name1,  window_function(column_name2)

 OVER([PARTITION BY column_name1] [ORDERBY column_name3]) AS new_column

FROM table_name;

 

• Aggregate Window Function

Various aggregate functions such as SUM(), COUNT(), AVERAGE(), MAX(), and MIN() applied over a particular window (set of rows) are called aggregate window functions.

Consider the following employee table :

Example – 

• Calculate the running total of sales for each product:

SELECT product_id, sales_amount,

    SUM(sales_amount) OVER (PARTITION BY product_id ORDER BY order_date) AS running_total

FROM sales_data ;

• Find the average salary for each department:

SELECT department_id, salary, 

AVG(salary) OVER (PARTITION BY department_id) AS avg_salary FROM employees;

• Calculate the cumulative sum of sales over time:

SELECT order_date, sales_amount,

         SUM(sales_amount) OVER (ORDER BY order_date) AS    cumulative_sales

FROM sales_data ; 

 

For Free, Demo classes Call: 020 7117 1500

Registration Link: Click Here!

 

• Ranking Window Functions:

These functions assign a rank or number to each row within the window.

ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.

Examples : 

• Assign a sequential number (row_num) for each employee based on their age.

SELECT department, employee_name, salary,

       ROW_NUMBER() OVER(PARTITION BY department ORDER BY salary DESC) AS row_num

FROM employees;

RANK(): Assigns a rank to each row within a partition, with gaps if there are ties.

Examples : 

• Assigns a unique row number within each department, ordered by salary in descending order.

SELECT department, employee_name, salary,

       RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS rank

FROM employees;

 

DENSE_RANK(): Similar to RANK(), but without gaps for tied rows.

SELECT department, employee_name, salary, 

DENSE_RANK() OVER(PARTITION BY department ORDER BY salary DESC) AS dense_rank

 FROM employees;

 

• Offset Window Functions:

Offset window functions in MySQL are window functions that return values from other rows relative to the current row within the window. They’re helpful for calculating differences between rows or accessing prior and subsequent values in a partitioned set.

There are three main offset window functions in MySQL:

LAG(): Retrieves data from a preceding row in the window.

Example:

• Find the difference between the current employee’s salary and the previous employee’s salary within the same department:

SELECT department_id, employee_name, salary, 

salary – LAG(salary) OVER (PARTITION BY department_id ORDER BY salary) AS salary_diff 

FROM employees;

• Previous day’s sales for each row (default offset = 1) 

SELECT order_date, region, sales, 

LAG(sales) OVER (ORDER BY order_date) AS prev_day_sales 

FROM sales;

 

LEAD(): Retrieves data from the following row in the window.

Example : 

• Find the salary of the next employee in the list.

SELECT employee_id, department, salary, 

LEAD(salary) OVER (ORDER BY employee_id) AS next_salary

FROM employees;

 

•  Find the employee ID of the person with the next highest salary within each department, which could indicate a possible promotion path.

SELECT employee_id, department, salary, 

LEAD(employee_id) OVER (PARTITION BY department ORDER BY salary DESC) AS promoted_employee_id

 FROM employees;

 

Do watch our Channel to learn more: Click Here

 

Author:

Mahesh Kankrale

Call the Trainer and Book your free demo Class For SQL Call now!!!
| SevenMentor Pvt Ltd.

© Copyright 2021 | SevenMentor Pvt Ltd.