PARTITION BY

The PARTITION BY clause is one of the three available clauses used in the OVER() statement when implementing any window function. It is used to define the subset of rows on which the window function can operate.

To demonstrate it's use let's query a sample employee data and use the ROW_NUMBER function to order the salaries. Within the same query, we will implement the PARTITION BY clause and examine the differences in output

SELECT
    first_name, 
    last_name, 
    department_id, 
    salary, 
    ROW_NUMBER() OVER( ORDER BY salary DESC) AS overall_rank, 
    ROW_NUMBER() OVER( PARTITION BY department_id ORDER BY salary DESC) as partitioned_rank  
FROM employees 
WHERE salary IS NOT NULL
LIMIT 30;

OUTPUTfirst_name | last_name | department_id | salary | overall_rank | partitioned_rank ------------+------------+---------------+----------+--------------+------------------ Kevin | Gonzalez | 3 | 98000.00 | 1 | 1 Michael | Brown | 3 | 95000.00 | 2 | 2 Gregory | Peterson | 3 | 94000.00 | 3 | 3 Adam | Wood | 3 | 92000.00 | 4 | 4 Matthew | Evans | 3 | 91000.00 | 5 | 5 Robert | Taylor | 3 | 89000.00 | 6 | 6 Samantha | Richardson | 5 | 88000.00 | 7 | 1 Scott | Price | 5 | 87000.00 | 8 | 2 Kimberly | Morris | 5 | 86000.00 | 9 | 3 Vanessa | James | 5 | 85000.00 | 10 | 4 Elizabeth | Perry | 5 | 83000.00 | 11 | 5 Nicole | Phillips | 5 | 83000.00 | 12 | 6 Sarah | Johnson | 2 | 82000.00 | 13 | 1 Justin | Bailey | 3 | 81000.00 | 14 | 7 Nicholas | Ward | 1 | 79000.00 | 15 | 1 Tyler | Kelly | 3 | 78000.00 | 16 | 8 Christopher | Garcia | 1 | 77000.00 | 17 | 2 Ashley | Cook | 1 | 76000.00 | 18 | 3 Benjamin | Ross | 1 | 75000.00 | 19 | 5 John | Smith | 1 | 75000.00 | 20 | 4 Anthony | Collins | 1 | 74000.00 | 21 | 6 Jonathan | Cox | 7 | 73000.00 | 22 | 1 Amanda | Rodriguez | 5 | 72000.00 | 23 | 7 David | Wilson | 1 | 71000.00 | 24 | 7 Patrick | Ramirez | 7 | 71000.00 | 25 | 2 Daniel | Campbell | 7 | 70000.00 | 26 | 3 Megan | Stewart | 2 | 69000.00 | 27 | 2 Emily | Davis | 4 | 68000.00 | 28 | 1 Melissa | Barnes | 7 | 68000.00 | 29 | 4 Stephanie | Parker | 6 | 67000.00 | 30 | 1 (30 rows)

We can notice the difference in the partitioned_rank column that the rank is order by the department id. In this way, it offers the ranked paid salary for each employee by department id.

The PARTITION BY clause is a useful grouby feature that allows for performance of aggregation at a colum category level. More complex computations can be performed this way