A window function in SQL is a function that performs a calculation on a set of rows within a window, which is a defined subset of the rows in a table. Window functions can be used to perform a variety of tasks, such as calculating running totals, ranking rows, and finding the average salary of employees in the same department.
Window functions are defined using the OVER clause in SQL. The syntax for the OVER clause is as follows:
SQL
OVER (PARTITION BY partition_column ORDER BY order_column [ROWS BETWEEN preceding_row AND following_row])
Use code with caution. Learn more
The partition_column is a column that is used to divide the rows in the table into partitions. The order_column is a column that is used to order the rows in each partition. The preceding_row and following_row clauses are optional and can be used to specify the range of rows that are included in the window.
Here is an example of a window function:
SQL
SELECT name, SUM(salary) OVER (PARTITION BY department ORDER BY salary DESC) AS total_salary
FROM employees;
Use code with caution. Learn more
This query calculates the total salary for each employee, grouped by department and ordered by salary in descending order.
Here is another example of a window function:
SQL
SELECT name, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;
Use code with caution. Learn more
This query ranks each employee by salary within their department, with the highest-paid employee having a rank of 1.
Window functions are a powerful tool for analyzing data in SQL. By understanding how to use window functions, you can write more complex and informative queries.
