Understanding Windows Function in SQL

SQL is a powerful language for managing and analyzing data. Windows function in SQL allows to perform calculations across set of table rows related to the current row. This blog will provide an detailed explanation at window functions, including examples and visual explanations.

Unlike aggregate functions, which group rows into a single output, window functions can return multiple rows with the calculated/aggregated value across set of multiple rows. They are useful for tasks such as calculating running totals, moving averages, and ranking data.

Syntax of Window Functions

The basic syntax for a window function is as follows:

function_name (arguments) OVER ([PARTITION BY partition_expression] [ORDER BY sort_expression] [frame_clause])

Key Components

• function_name: The name of the window function (e.g., ROW_NUMBER(), RANK(), SUM(), etc.).

• PARTITION BY: Divides the result set into partitions to which the function is applied.

• ORDER BY: Defines the order of rows in each partition.

• frame_clause: Specifies the subset of rows within the partition to which the function is applied.

Example 1 - ROW_NUMBER():

The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition of a result set.

SELECT employee_id, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num FROM employees;

Output:

employee_id	department	salary	row_num
101	HR	60000	1
102	HR	50000	2
103	IT	90000	1
104	IT	80000	2

Example 2 - RANK() :

The RANK() function provides a ranking of rows within a partition, allowing for gaps in the rank when there are ties.

SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employees;

Output:

employee_id	department	salary	rank
101	HR	60000	1
102	HR	50000	2
103	IT	90000	1
104	IT	80000	2

Example 3 - LAG()

The LAG() function allows you to access data from a previous row in the same result set without using a self-join.

SELECT employee_id, department, salary, LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary DESC) AS previous_salary FROM employees;

Output:

employee_id	department	salary	previous_salary
101	HR	60000	0
102	HR	50000	60000
103	IT	90000	0
104	IT	80000	90000

SQL Databases that Support Window Functions

1. PostgreSQL

2. MySQL (version 8.0 and above)

3. SQL Server (since SQL Server 2005)

4. Oracle (since Oracle 8i, with significant enhancements in later versions)

5. SQLite (version 3.25.0 and above)

6. IBM DB2

7. MariaDB (version 10.2 and above)

8. Amazon Redshift

9. Google BigQuery

10. Apache Hive

SQL Databases that Do Not Support Window Functions

1. MySQL (versions below 8.0): Prior to version 8.0, MySQL did not support window functions.

2. SQLite (versions below 3.25.0): Before version 3.25.0, SQLite did not have support for window functions.

3. Older versions of database systems: Any older versions of the above-mentioned databases that were released before they introduced support for window functions.

Conclusion

Window functions are an essential tool in SQL for performing advanced calculations and data analysis. They offer flexibility and power beyond simple aggregate functions, enabling you to derive valuable insights from your data. By understanding and utilizing window functions, you can efficiently solve complex analytical problems and improve your SQL skills