A window function performs a calculation across a set of table rows that are related to the current row. Window functions preserve all original rows and simply add new computed columns.
Unlike aggregate functions which collapse rows into a single result (like SUM, AVG with GROUP BY ).
Window function = buffet (each dish stays separate, but you still know the total).
Aggregate = making a smoothie (all fruits blended into one drink)
Key Components of Window Functions:
1. OVER Clause: This defines the window of rows to operate on.
2. PARTITION BY: Divides the result set into partitions to which window function is applied.
3. ORDER BY: Defines the order of rows in each partition.
4. ROWS or RANGE: Specifies the frame of rows to include in the calculation.
In SQL, window functions are used to perform calculations across a set of rows that are related to the current row. Window functions work with a “window” or “frame” of rows, which is a subset of the total number of rows in the result set.
A “window specification” that includes the following elements is used to specify the window or frame:
- Partitioning: separates rows into several partitioned groups to which the window function is applied.
- Ordering: specifies the order in which the rows within each partition are processed.
- Frame: specifies the set of rows that should be included in the window calculation.