In a nutshell
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
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.
- Partitioning. The
PARTITION BY Categoryclause divides the result set into separate partitions where each partition contains only those rows in
Categorycolumn is the same. So you might have one partition for rows where the
"Electrical", another partition where the
Categorycolumn is “Gardening”, and so on.
- Ordering. The
ORDER BY SaleDateorders the partition rows.
- Window Function Calculation. After partitioning and ordering, the window function (something like
AVG() is applied to each row within the partition. The result of the window function will only use the rows within the partition