PARTITION BY Deep Dive

Concepts covered: Window Partitioning

OVER () builds 1 window from every row. PARTITION BY splits it into several, 1 per distinct value of the columns named, and the function runs separately inside each. SUM(salary) OVER (PARTITION BY department) reads the 2 Engineering rows and produces 250,000, the 2 Sales rows and produces 170,000, the 2 Marketing rows and produces 185,000, and every row receives the figure for its own partition. Row count stays at 6. The partition keys do the same work the keys in GROUP BY do, which is why a GROUP BY department query and a PARTITION BY department window always agree on the per-department figures; the difference is what happens to the rows afterwards.

Trace Alice. Her department is Engineering, so her window is the Engineering rows: Alice at 120,000 and Carol at 130,000. SUM over that window is 250,000, AVG is 125,000, and her share of the department is 120,000 divided by 250,000, which is 48 percent. Carol's window is the same 2 rows, so she receives the same 250,000 and 125,000, and a share of 52 percent. Dave in Sales never sees either of them; his window is Dave and Eve, totalling 170,000, and his share of it is 47.1 percent.

SELECT emp_name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees
ORDER BY department, salary DESC

This SELECT holds 3 window functions, all over the same partition, and the engine builds the partition once and reuses it for each. pct_of_dept divides a column by a window result, which is allowed in the SELECT list and the final ORDER BY. A window value cannot appear in WHERE, GROUP BY, HAVING or a join condition, because all of them run before the window is computed. It also cannot be nested inside another aggregate.

The animation plays the AVG version clause by clause: 6 rows arrive from FROM, the engine sorts them into 3 partitions by department, computes 1 average inside each, and writes that average onto every row of the partition it came from.

SELECT
  emp_name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees

Engineering averages 125,000, Sales 85,000, Marketing 92,500. Each figure appears once per row of its partition. That tells a window average from a GROUP BY average at a glance: the GROUP BY version is 3 rows with no names.

Comparing a row to its own group

The everyday use of a partitioned aggregate is a gap from the group figure. salary - AVG(salary) OVER (PARTITION BY department) gives Alice minus 5,000 and Carol plus 5,000, Dave minus 5,000 and Eve plus 5,000, Frank plus 2,500 and Grace minus 2,500. It takes 1 expression and no self-join, and the answer lands on the row where the follow-up question, who is paid below their department's average, will be asked. The same shape gives a product's price against its category average, a server's latency against its region's, and a store's sales against its chain's.

Partitioning on more than 1 column

PARTITION BY takes a list, and a window forms for each distinct combination. PARTITION BY department, hire_year on a larger payroll table makes 1 window per department per hiring cohort, so an employee is compared with the people hired into the same department in the same year. More columns mean smaller partitions, until every row is alone in a partition of 1 and the aggregate returns the row's own value. That is a useful diagnostic: a window total that equals the detail column on every row means the partition is too fine.

The practice table employees in the platform database has the same columns as the running example and many more rows. The blanks ask for the 2 decisions every partitioned window makes: what to compute, and which rows to compute it over.

Among the distractors, ORDER BY department runs without error and returns a number on every row, which is why a wrong window clause is dangerous: the query succeeds, the column looks plausible, and the figures are a running average across departments in alphabetical order rather than a per-department average. PARTITION BY emp_name also runs, and returns each person's own salary as their average, the partition-of-1 symptom from above.

About this lesson

This section is part of the Window Functions: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.