Answer
Window functions operate over a logical window of rows, defined by PARTITION BY and ORDER BY clauses in the OVER() expression.
PARTITION BY:
- Divides the result set into groups (partitions) for independent calculations, similar to grouping in analytics.
- Example:
SUM(SalesAmount) OVER (PARTITION BY CustomerId)gives total sales per customer.
ORDER BY:
- Defines the sequence of rows within each partition.
- Required for ranking and running calculations like
ROW_NUMBER(), cumulative sums, and moving averages.
In summary, PARTITION BY defines the scope and ORDER BY defines the sequence of the window.