SQL › Meet the Window

OVER, PARTITION BY and ORDER BY

A window function works out a value from a set of rows, the window, but unlike GROUP BY it keeps every row. OVER turns an aggregate such as SUM into a window function.

Oracle calls them analytic functions. PostgreSQL, SQL Server, Oracle and MySQL (from 8.0) all have them.

Syntax

Order of execution: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → ORDER BY

Windows only see the rows that are left after WHERE and GROUP BY.

The full order, now that every clause has been taught: FROM and joins → WHERE → GROUP BY → HAVING → window functions → SELECT → DISTINCT → ORDER BY → LIMIT / OFFSET. That's why a window function can't go in WHERE, GROUP BY or HAVING, but can in ORDER BY. With UNION, each SELECT goes through these steps up to DISTINCT, and ORDER BY and LIMIT apply to the combined result.

Code
SELECT column,
       AGG(column) OVER () AS all_rows,
       AGG(column) OVER (PARTITION BY group_column) AS per_group,
       AGG(column) OVER (PARTITION BY group_column ORDER BY sort_column) AS running
FROM table_name;

Examples

GROUP BY vs OVER

GROUP BY returns one row per group; OVER keeps the rows.

Code
SELECT method, SUM(amount) AS method_total FROM payments GROUP BY method;
 
SELECT payment_id, method, amount, SUM(amount) OVER () AS all_payments
FROM payments
ORDER BY payment_id
LIMIT 5;

A window per group

PARTITION BY gives each method its own total, shown on each of its rows.

Code
SELECT payment_id, method, amount,
       SUM(amount) OVER (PARTITION BY method) AS method_total
FROM payments
WHERE order_id <= 10
ORDER BY method, payment_id;

Running values

With ORDER BY inside OVER, each row sees the rows up to itself.

Code
SELECT order_id, order_date, total_amount,
       SUM(total_amount) OVER (ORDER BY order_date, order_id) AS spent_so_far
FROM orders
WHERE customer_id = 3
ORDER BY order_date, order_id;

Running values per group

Combine both: the running count starts again for each product.

Code
SELECT product_id, review_id, rating,
       COUNT(*) OVER (PARTITION BY product_id ORDER BY review_id) AS review_number
FROM reviews
ORDER BY product_id, review_id;

Windows come after WHERE

Code
-- Fails: window functions can't be used in WHERE
SELECT order_id FROM orders WHERE SUM(total_amount) OVER () > 100000;
 
-- Filter on a window value from the outer query instead
SELECT order_id, share
FROM (SELECT order_id, ROUND(100 * total_amount / SUM(total_amount) OVER (), 2) AS share
      FROM orders) t
WHERE share > 1
ORDER BY share DESC, order_id;

Naming a window

Reuse one window definition for several functions.

Code
SELECT product_name, price,
       MIN(price) OVER w AS cheapest_in_category,
       MAX(price) OVER w AS priciest_in_category
FROM products
WHERE category_id = 3
WINDOW w AS (PARTITION BY category_id)
ORDER BY product_id;

Remember

• A window function keeps every row; GROUP BY collapses them.
• PARTITION BY splits the rows into separate windows.
• ORDER BY inside OVER makes the value running; without it, every row of the window gets the same value.
• Windows run after WHERE; to filter on a window value, wrap the query and filter outside.

Practice

1. Each Product vs Its Category Average (Easy)
2. Each Customer's Order Number (Easy)
3. Each Order's Share of Customer Spend (Medium)