In SQL Server we have different categories of window functions
- Aggregate functions – AVG, SUM, COUNT, MIN, MAX etc..
- Ranking functions – RANK, DENSE_RANK, ROW_NUMBER etc..
- Analytic functions – LEAD, LAG, FIRST_VALUE, LAST_VALUE etc…
OVER Clause defines the partitioning and ordering of a rows (i.e a window) for the above functions to operate on. Hence these functions are called window functions. The OVER clause accepts the following three arguments to define a window for these functions to operate on.
- ORDER BY : Defines the logical order of the rows
- PARTITION BY : Divides the query result set into partitions. The window function is applied to each partition separately.
- ROWSor RANGE clause : Further limits the rows within the partition by specifying start and end points within the partition.
