What is a Window Function?

Concepts covered: Aggregate Window Fns, Execution Order

An aggregate function does 2 jobs at once: it computes a value over a set of rows, and it collapses that set into 1 output row. SUM(salary) with nothing else folds the whole table into a single row, and SUM(salary) with GROUP BY department folds it into 1 row per department. The OVER keyword separates the 2 jobs. SUM(salary) OVER () computes the same total, and every input row survives into the output with that total attached as a new column. The set of rows the function reads is its window, which is where the name comes from, and any aggregate becomes a window function the moment OVER follows it.

The lesson follows 1 table through all 5 sections: employees, 6 people in 3 departments, each with a salary and a hire date. Total payroll is 605,000. The table is small enough to add up by hand on purpose, so every window result below can be predicted before the query runs.

The table can answer 3 different questions with SUM, and the clause around it decides which one. Plain SUM(salary) answers how much the company pays in total and returns 1 row. SUM(salary) with GROUP BY department answers what each department costs and returns 3 rows. SUM(salary) OVER () answers how each person's pay sits against the company total and returns 6 rows, one per employee, with 605,000 on every one of them. The 3 tabs below run each version; the row count is the thing to watch.

Plain SUM

Collapses all 6 rows into 1 total.

SELECT SUM(salary) AS total_payroll
FROM employees

GROUP BY

Collapses the 6 rows into 1 per department, 3 in all.

SELECT department, SUM(salary) AS dept_total
FROM employees
GROUP BY department
ORDER BY department

SUM OVER ()

Keeps all 6 rows and attaches the company total to each.

SELECT emp_name, department, salary,
       SUM(salary) OVER () AS total_payroll
FROM employees
ORDER BY employee_id

The repeated 605,000 in the third tab looks wrong to a spreadsheet eye, but it follows directly from how the window is defined. The window for an empty OVER () is every row the query holds at that point, so SUM reads all 6 salaries once, arrives at 605,000, and the engine writes that value into the new column on all 6 rows. Each row's value is computed from the window that row belongs to, and no row borrows the figure from a neighbour. Once the total is on the row, ordinary arithmetic can use it: 100.0 * salary / SUM(salary) OVER () gives Carol 21.5 percent of payroll and Dave 13.2 percent, in the same SELECT list, with no second query.

The window's place in clause order

A query runs its clauses in a fixed order, and window functions have a fixed place in it: after FROM, WHERE, GROUP BY and HAVING have produced their rows, and before the final ORDER BY and LIMIT arrange and trim them. The window therefore holds only the rows that survived WHERE: add WHERE department = 'Sales' and SUM(salary) OVER () returns 170,000 on the 2 Sales rows, because the other 4 were gone before the window formed. A window function also cannot appear in WHERE or HAVING, because those clauses run before its value exists. Filtering on a window result needs an outer query, which section 2 shows with the top-earner-per-department pattern.

• FROM and joins assemble the rows
• WHERE drops rows that fail the condition
• GROUP BY and HAVING collapse and filter groups
• Window functions compute over whatever remains
• SELECT expressions, ORDER BY and LIMIT shape the output

The orders table holds 1,200 rows across 4 regions, and exactly 900 of them have an amount above 100. How many rows does this query return? SELECT order_id, amount, SUM(amount) OVER () AS total FROM orders WHERE amount > 100

• 1 row
• 4 rows
• 900 rows
• 1,200 rows

The answer depends on where OVER () runs. If it were a second pass over the whole table, the answer would be 1,200; it runs on the rows WHERE left behind, so the answer is 900. This window also needs no sort: 1 pass totals the rows the engine already holds.

GROUP BY

• 1 output row per group
• Non-grouped columns must be aggregated
• Totals only; the detail rows are gone
• Result can be filtered with HAVING

OVER

• Every input row stays
• Any column can sit beside the result
• Detail and total on the same row
• Result filtered only from an outer query

The choice between the 2 is the shape of the answer wanted. A report listing departments and their totals is a GROUP BY. A report listing people, each against a total, is a window function. The older route to the second report was a GROUP BY subquery of department totals joined back to employees on department, which costs a second scan of the table. MySQL added OVER in 8.0 and SQLite in 3.25, both in 2018, so every major engine now supports it.

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.