The window function in SQL with an example

SQL window functions are a powerful feature in SQL that allow you to perform calculations on a set of rows, referred to as a “window”, that are related to the current row. Unlike aggregate functions, window functions do not cause the rows to become grouped into a single output row. They are called window functions because they perform a calculation across a set of table rows that are somehow related to the current row.

The syntax of a window function is as follows:

window_function_name ( expression ) OVER ( partition_clause order_clause frame_clause )

Here’s a brief explanation of each part of the syntax:

• window_function_name: The name of the window function, such as ROW_NUMBER(), RANK(), SUM(), etc.
• expression: The target expression or column on which the window function operates.
• OVER clause: This clause defines window partitions to form the groups of rows and specifies the orders of rows in a partition. It consists of three clauses: partition, order, and frame clauses.

There are several types of window functions in SQL, including:

1. Ranking functions: These functions return a ranking value for each row in a partition. Examples include RANK(), DENSE_RANK(), ROW_NUMBER(), etc.
2. Aggregate functions: These functions perform calculations on a set of rows and return a single output row. Examples include SUM(), AVG(), MIN(), MAX(), etc.
3. Value functions: These functions perform operations on values in the window frame. Examples include FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG(), etc.

Example, returning employee bonus such that next employee bonus equal to the sum of all pervious employees. We can achieve this using a window function in SQL. Here’s how you can do it:

SELECT 
    EmployeeID, 
    Name, 
    Bonus,
    SUM(Bonus) OVER (ORDER BY EmployeeID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Allocated_Bonus
FROM Employees;

This query will return EmployeeID, Name, Bonus, and Allocated_Bonus columns. The Allocated_Bonus for each employee is the sum of the Bonus of all preceding employees (including the current one), sorted by EmployeeID.

Let’s consider the following Employees table:

EmployeeID	Name	Bonus
1	John	1000
2	Alice	2000
3	Bob	3000
4	David	4000

The output of the query would be:

EmployeeID	Name	Bonus	Allocated_Bonus
1	John	1000	1000
2	Alice	2000	3000
3	Bob	3000	6000
4	David	4000	10000

As you can see, the Allocated_Bonus for each employee is the sum of the Bonus of all preceding employees (including the current one).