PARTITION BY Mechanics
Overview
If OVER() calculates math across the entire table, OVER(PARTITION BY col) calculates math inside specific 'buckets', exactly like GROUP BY, but without crushing the rows! It restarts the calculation every time the partitioned column changes. This allows you to say 'Give me the average salary for this specific employee's department, and attach it to their row'.
Syntax
-- Syntax: OVER (PARTITION BY column_name)
SELECT
emp_name,
department,
salary,
-- 1. Global Average (Entire table)
AVG(salary) OVER () AS global_avg,
-- 2. Partitioned Average!
-- This calculates the average strictly for the current row's department.
-- It 'resets' the math when it enters a new department.
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;Common Pitfalls
- Confusing
PARTITION BYwithGROUP BY.GROUP BY departmentwill output exactly one row per department.PARTITION BY departmentwill output exactly the same number of rows you started with, but the math happening in the new column is bucketed by department. - Partitioning by unique IDs. If you
PARTITION BY user_idon an orders table, you are creating a bucket of size 1 for every single row.AVG(amount) OVER(PARTITION BY order_id)will just return the exact same amount as the order, defeating the purpose.
Interview Questions
PARTITION BY and ORDER BY inside the same OVER() clause?The PARTITION BY slices the table into isolated buckets. The ORDER BY then dictates how the rows are mathematically evaluated within that specific bucket. This combination is the foundation for Ranking and Running Totals.
Real-World Example
Finding out what percentage of a department's total budget is consumed by a specific employee.
SELECT
emp_name,
department,
salary,
-- Sums the salary for ONLY this department
SUM(salary) OVER (PARTITION BY department) AS dept_total_budget,
-- Inline Math!
(salary / SUM(salary) OVER (PARTITION BY department)) * 100 AS pct_of_budget
FROM employees;Check Your Knowledge
Test your understanding of PARTITION BY Mechanics with these quick questions.