Window frame
A window frame is the subset of rows within a window function's partition that a calculation is applied to, defined with ROWS, RANGE, or GROUPS relative to the current row.
Overview
A window frame narrows the rows that a window function sees for each output row. Instead of aggregating an entire partition, a frame lets you compute things like a trailing 7-day sum, a running total, or a centered moving average by sliding a window of rows relative to the "current row."
A frame is declared inside an OVER clause after PARTITION BY and ORDER BY:
Copy code
SELECT
order_date,
revenue,
SUM(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS trailing_7day_sum
FROM orders;
Frame units: ROWS, RANGE, GROUPS
- ROWS counts physical rows before/after the current row — the most common and predictable choice for running totals and moving averages.
- RANGE groups by value proximity in the
ORDER BYexpression (e.g., all rows within a numeric or time range of the current row's value), treating peer rows (equal order-by values) as one unit. - GROUPS counts peer groups (sets of tied rows) rather than individual rows.
Frame bounds are UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, or UNBOUNDED FOLLOWING.
Default frame
If a query has ORDER BY but no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which is why plain running-total queries with ORDER BY alone still work, but can behave surprisingly with tied order-by values. If there is no ORDER BY at all, the frame defaults to the entire partition.
DuckDB specifics
DuckDB implements the full ANSI frame syntax (ROWS/RANGE/GROUPS, all bound types) and also supports the EXCLUDE clause (EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, EXCLUDE NO OTHERS) to fine-tune which rows near the current row are removed from the frame. DuckDB also supports QUALIFY, which lets you filter on a window function's result without wrapping the query in a subquery:
Copy code
SELECT order_date, revenue,
AVG(revenue) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS centered_avg
FROM orders
QUALIFY centered_avg > 1000;
Related terms
The OVER clause turns an aggregate or ranking function into a window function by defining the partition, order, and frame it operates on, without collapsing the result set.
window functions →Window functions allow you to perform calculations across a set of table rows that are somehow related to the current row, similar to aggregate functions,…
ROW_NUMBER →ROW_NUMBER() is a SQL window function that assigns a unique, sequential integer to each row within a partition based on a specified order, with no ties.
PARTITION BY →PARTITION BY divides rows into independent groups for a window function or an output operation, so calculations reset for each group without collapsing rows the way GROUP BY does.
QUALIFY →QUALIFY filters rows based on the result of a window function, letting you write conditions like 'keep only the top-ranked row per group' without wrapping the query in a subquery.
row group →A row group is a fundamental storage concept in columnar databases like DuckDB that represents a horizontal partition of data containing a fixed number of…
FAQS
ROWS counts a fixed number of physical rows before or after the current row, which is predictable even with duplicate ORDER BY values. RANGE instead groups rows by proximity in the ORDER BY value, treating rows with the same order-by value as a single peer group.
With an ORDER BY clause and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Without an ORDER BY clause, the frame defaults to the full partition.
