OVER clause

Overview

The OVER clause is what distinguishes a window function from a regular aggregate function. Adding OVER (...) after a function like SUM(), AVG(), or ROW_NUMBER() tells the engine to compute the value per row over a defined "window" of related rows, rather than collapsing all rows into one.

Copy code
SELECT
  order_id,
  customer_id,
  revenue,
  SUM(revenue) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

The OVER clause has three optional pieces:

1. PARTITION BY — which rows belong together.
2. ORDER BY — how rows are ordered within a partition (required for ranking functions and frames).
3. A frame clause (ROWS/RANGE/GROUPS BETWEEN ...) — which rows within the ordered partition are included.

An empty OVER () applies the function across the entire result set as one partition.

Named windows

When several window functions in a query share the same partition/order/frame, standard SQL lets you define a named window once with a WINDOW clause and reference it by name:

Copy code
SELECT
  customer_id, order_date, revenue,
  SUM(revenue) OVER w AS running_total,
  AVG(revenue) OVER w AS running_avg
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);

DuckDB specifics

DuckDB supports the full OVER clause syntax, named WINDOW definitions, and the EXCLUDE frame modifier. It also supports QUALIFY, which filters rows based on a window function's output — the natural complement to OVER, since WHERE cannot reference window function results directly:

Copy code
SELECT customer_id, revenue, SUM(revenue) OVER w AS running_total
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date)
QUALIFY running_total > 10000;