SQL: PARTITION BY

In a nutshell

The PARTITION BY clause lets you divide the result set of a query into partitions based on one or more columns, and then perform calculations against each partition using window functions like SUM(), MAX(), AVG().

Here’s an example use of PARTITION BY to find out how much each product category has sold to date.

SELECT
    Product,
    Category,
    SaleDate,
    SaleAmount,
    SUM(SaleAmount) OVER (PARTITION BY Category ORDER BY SaleDate) AS RunningTotal
FROM
    Sales;

This is what happens as the query is processed.

1. Partitioning. The PARTITION BY Category clause divides the result set into separate partitions where each partition contains only those rows in Sales where the Category column is the same. So you might have one partition for rows where the Category column is "Electrical", another partition where the Category column is “Gardening”, and so on.
2. Ordering. The ORDER BY SaleDate orders the partition rows.
3. Window Function Calculation. After partitioning and ordering, the window function (something like SUM() or AVG() is applied to each row within the partition. The result of the window function will only use the rows within the partition