Aggregating Windows

All aggregate functions can be used as window functions instead. All you have to do is add a window with an OVER clause. This causes the aggregate function to behave differently than when you use it with GROUP BY.

Review

Let's review a query we saw when we learned about GROUP BY.

SELECT
  company,
  max(salary) AS top_salary
FROM employees
GROUP BY company;

Remember that GROUP BY takes a column(s), looks at the values in each row for that column(s), and groups together the rows where that value is equal. This column(s), is pictured in green.

The rest of the columns from that table, pictured in red, have their values globbed into collections. These cannot be selected directly. Aggregate functions are used here to convert these collections into single values, once per row. You are already acquainted with this usage.

New Usage

When aggregate functions are used with windows, there is no grouping happening on the table you are selecting from, i.e. the input table. Instead, the aggregate function aggregates the window. As with other window functions, it runs once per row and generates 1 value per row of the input table.

Here is an example.

SELECT
  max(salary) OVER (PARTITION BY company) AS company_max,
  *
FROM employees;

ORDER BY is not necessary in this window because max() is not sensitive to ordering. Actually, we have to omit it because it would change the frame, which we'll discuss a bit later.

The key difference here is that whereas GROUP BY aggregates individual employees into groups, aggregating on windows preserves the individual employee rows, and each row "sees" the result of max(salary).

Every employee row can access the max salary of their respective company. This gives us yet another way to solve the problem of getting the top earner's name at each company.

WITH max_salaries AS (
  SELECT
    max(salary) OVER (PARTITION BY company) AS company_max,
    *
  FROM employees
)
SELECT *
FROM max_salaries
WHERE company_max = salary;