SQL Window Functions

Window functions in SQL provide the ability to perform calculations on a set of rows related to the current row. Unlike aggregate functions, which return a single value for each group, window functions return a single value for each row based on the rows in its "window". This allows for more advanced calculations and rankings without collapsing multiple rows into a single output row.

Basics of Window Functions

The primary components of a window function are:

• The window function itself (e.g., ROW_NUMBER(), RANK()).
• An OVER() clause, which defines the window of rows for the function to operate on.
• ORDER BY and PARTITION BY clauses within the OVER() clause to order and segment data.

Example 1: ROW_NUMBER()

                            SELECT 
                                EmployeeName, 
                                Department, 
                                Salary,
                                ROW_NUMBER() OVER (ORDER BY Salary DESC) AS 'Rank'
                            FROM Employees;
                        

This example assigns a unique rank to each employee based on their salary in descending order.

Example 2: RANK() and DENSE_RANK()

                            SELECT 
                                EmployeeName, 
                                Department, 
                                Salary,
                                RANK() OVER (ORDER BY Salary DESC) AS 'Rank',
                                DENSE_RANK() OVER (ORDER BY Salary DESC) AS 'DenseRank'
                            FROM Employees;
                        

In this example, RANK() provides a unique rank for each salary but leaves gaps in ranking for equal salary values. DENSE_RANK(), on the other hand, does not leave gaps.

Example 3: LAG() and LEAD()

                            SELECT 
                                EmployeeName,
                                Salary,
                                LAG(Salary, 1) OVER (ORDER BY Salary) AS 'PreviousSalary',
                                LEAD(Salary, 1) OVER (ORDER BY Salary) AS 'NextSalary'
                            FROM Employees;
                        

LAG() returns the previous row's salary, and LEAD() returns the next row's salary.

Example 4: Cumulative SUM using SUM()

                            SELECT 
                                EmployeeName,
                                Salary,
                                SUM(Salary) OVER (ORDER BY EmployeeName) AS 'CumulativeSalary'
                            FROM Employees;
                        

This example calculates a running total of salaries, ordered by employee names.

Conclusion

Window functions bring a powerful dimension to SQL querying, allowing for more complex analyses on datasets without the need to aggregate or transform the data in cumbersome ways. By understanding and leveraging window functions, developers can achieve a deeper insight into their data and produce more efficient and effective queries.