I understand that window functions work with set of rows called window and in contrast to aggregate functions does not change the size of the sample. But what is window? As I understand, it is a set of rows which are passed to a function.
Imagine we have a simple query:
SELECT age, SUM(salary) as sum FROM workers GROUP BY age
Can we state that grouped rows having the same age are also a window? Because we group workers and for each group we count a sum of their salaries.Сan we say that aggregate function also works with window?
No, it would not be correct. A window is more general than aggregation. I think this will be clear if you look at the definition of the window frame:
over (partition by . . .
order by . . .
range/rows . . .
)
Of course, these are not all needed, but they are part of the definition of a given window.
The PARTITION BY is equivalent to the GROUP BY keys, specifying that a given window only has rows with the same key values. So, it would be quite consistent to say that "group by processes partitions, returning one row per partition".
Note a key point here: GROUP BY also affects the number of rows in the result set, by returning only one row per partition. Window functions have no such effect on the result set, returning a "summarized" value per row.
In addition, a window is broader than just the PARTITION BY and can represent a set of rows or values relative to the current row. That is where ORDER BY and RANGE/ROW come in.