Can you explain the concept of window functions in database management systems?

Window functions in RDBMS allow performing calculations across a set of table rows that are related to the current row, without grouping the results. They are useful for analytics and complex queries where you need to obtain aggregates, rankings, moving sums, etc., while preserving the detail of each row.

Working principle:

• A window (set of rows) is defined using the OVER() clause, where you can specify PARTITION BY (grouping) and ORDER BY (ordering within the group).
• For each row, a function is calculated over the window, such as sum, average, row number, etc.

Example in SQL:

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

Here, the salary rank is calculated for each employee within their department, while each row remains in the result.