What is a window function?

A window function is a type of function used in database systems and data processing frameworks that performs calculations across a set of rows related to the current row. Unlike regular aggregate functions that return a single value for a group of rows, window functions return a value for each row in the result set while still allowing access to the individual row data.

Window functions are often used for tasks such as:

• Calculating running totals
• Finding moving averages
• Ranking rows within partitions
• Performing cumulative distributions

In SQL, a window function is defined using the OVER() clause, which specifies the window of rows to consider for the calculation. Here’s an example of a SQL query using a window function:

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_department_salary
FROM 
    employees;

In this example, the AVG(salary) function calculates the average salary for each department while still returning each employee's individual salary.