Stackvora.tech

Window Functions Quick Reference

SQL window functions operate on a set of rows related to the current row, known as a "window," without collapsing the rows into a single output row as…

SQL window functions operate on a set of rows related to the current row, known as a "window," without collapsing the rows into a single output row as aggregate functions do. This capability allows for complex analytical queries that would otherwise require self-joins or subqueries, often with significantly improved performance and readability. They are a cornerstone for advanced data analysis in modern relational databases, including PostgreSQL, MySQL (8.0+), SQL Server, and Oracle.

The core syntax for a window function is FUNCTION() OVER ([PARTITION BY expr1, ...] [ORDER BY expr2 [ASC|DESC], ...] [ROWS | RANGE BETWEEN frame_start AND frame_end]). The OVER() clause defines the window. PARTITION BY divides the result set into logical groups, similar to GROUP BY but retaining individual rows. ORDER BY within the OVER() clause sorts rows within each partition. The optional frame clause (ROWS or RANGE) further restricts the rows within a partition that are included in the window calculation.

Understanding the OVER Clause

The OVER clause is what transforms a regular aggregate function into a window function, or defines the scope for specialized window functions. It dictates how the "window" of rows is defined for each row in the result set.

PARTITION BY

PARTITION BY divides the input rows into groups, and the window function is applied independently to each group. This is analogous to a GROUP BY clause but, critically, it does not aggregate the results into a single row per group. Each original row remains in the output, augmented with the window function's result for its respective partition.

SELECT
    employee_id,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary
FROM
    employees;

In this example, AVG(salary) OVER (PARTITION BY department) calculates the average salary for each department. Every employee row will then show their individual salary alongside the average salary of their department.

ORDER BY

The ORDER BY clause within OVER() specifies the logical order of rows within each partition. This is crucial for "ordered" window functions like ROW_NUMBER(), RANK(), LAG(), LEAD(), or cumulative aggregations. Without ORDER BY, the order of rows within a partition is non-deterministic for functions that rely on it, and for aggregate window functions, it implies an unordered window, meaning the aggregate is calculated over the entire partition.

SELECT
    order_id,
    customer_id,
    order_date,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total_amount
FROM
    orders;

Here, SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) calculates a running total of order amounts for each customer, ordered by their order dates. The default frame for an ordered window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (or ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for some functions), which means it sums all amounts from the start of the customer's orders up to the current order date.

Window Frame (ROWS | RANGE)

The window frame further refines the set of rows within a partition on which the window function operates. It is defined using ROWS or RANGE, followed by BETWEEN frame_start AND frame_end. If an ORDER BY clause is present, a default frame is usually applied if not explicitly stated:

  • For ROWS: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  • For RANGE: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

If no ORDER BY clause is present, the default frame is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, meaning the entire partition is considered for each row.

ROWS Frame: Defines the frame based on a fixed number of rows relative to the current row.

  • UNBOUNDED PRECEDING: Starts from the first row of the partition.
  • N PRECEDING: N rows before the current row.
  • CURRENT ROW: The current row.
  • N FOLLOWING: N rows after the current row.
  • UNBOUNDED FOLLOWING: Ends at the last row of the partition.
-- Calculate a 3-day moving average of sales, including current day and 2 previous days
SELECT
    sale_date,
    region,
    amount,
    AVG(amount) OVER (PARTITION BY region ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3day
FROM
    sales;

RANGE Frame: Defines the frame based on a range of values relative to the current row's ordering column(s). This is most useful for date/time or numeric values where you want to include all rows within a certain value range, even if they are not consecutive in terms of row count.

-- Calculate the sum of sales for transactions within a 7-day period (including current day)
SELECT
    transaction_id,
    transaction_date,
    amount,
    SUM(amount) OVER (ORDER BY transaction_date RANGE BETWEEN INTERVAL '6 DAY' PRECEDING AND CURRENT ROW) AS weekly_sales_sum
FROM
    transactions;

Note: RANGE usually requires a single, numeric or date/time ordering column.

Types of Window Functions

Aggregate Window Functions

These are standard aggregate functions (SUM(), AVG(), COUNT(), MIN(), MAX()) used with an OVER() clause. They calculate aggregates over the defined window.

SELECT
    product_id,
    sale_date,
    units_sold,
    SUM(units_sold) OVER (PARTITION BY product_id ORDER BY sale_date) AS cumulative_units,
    AVG(units_sold) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS 7_day_avg_units
FROM
    daily_sales;

Ranking Window Functions

These functions assign a rank to each row within its partition based on the ORDER BY clause.

  • ROW_NUMBER(): Assigns a unique, sequential integer to each row within its partition, starting from 1. If there are ties in the ORDER BY criteria, ROW_NUMBER() will still assign unique numbers.
  • RANK(): Assigns a rank to each row within its partition. If rows have identical values in the ORDER BY clause, they receive the same rank, and the next rank is skipped. (e.g., 1, 1, 3).
  • DENSE_RANK(): Similar to RANK(), but no ranks are skipped in case of ties. (e.g., 1, 1, 2).
  • NTILE(N): Divides the rows in a partition into N approximately equal groups and assigns a group number (from 1 to N) to each row.
SELECT
    player_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS overall_rank_row_num,
    RANK() OVER (ORDER BY score DESC) AS overall_rank_rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS overall_rank_dense_rank,
    NTILE(4) OVER (ORDER BY score DESC) AS score_quartile
FROM
    tournament_scores;

The specific example from the prompt: ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_seq assigns a sequential number to each order for a given user, based on their order date.

Analytic Window Functions

These functions perform calculations across a set of table rows that are related to the current row. They provide values from preceding or succeeding rows.

  • LAG(expression, offset, default_value): Returns the value of expression from a row at offset rows before the current row within the partition. If no such row exists, default_value is returned.
  • LEAD(expression, offset, default_value): Returns the value of expression from a row at offset rows after the current row within the partition. If no such row exists, default_value is returned.
  • FIRST_VALUE(expression): Returns the value of expression from the first row of the window frame.
  • LAST_VALUE(expression): Returns the value of expression from the last row of the window frame.
  • NTH_VALUE(expression, N): Returns the value of expression from the Nth row of the window frame.
SELECT
    trade_id,
    trade_time,
    price,
    LAG(price, 1, 0.0) OVER (PARTITION BY symbol ORDER BY trade_time) AS prev_price,
    (price - LAG(price, 1, 0.0) OVER (PARTITION BY symbol ORDER BY trade_time)) AS price_change
FROM
    stock_trades;

The prompt's example: LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_amount retrieves the amount of the user's previous order.

Example Use Cases

Window functions are incredibly versatile. Here are a few common scenarios:

  • Calculating Running Totals/Balances: As seen with SUM(...) OVER (ORDER BY ...), crucial for financial reporting or tracking progress.
  • Moving Averages/Sums: Using frame clauses like ROWS BETWEEN N PRECEDING AND CURRENT ROW to smooth out data or identify trends.
  • Comparing Current Row to Previous/Next: LAG() and LEAD() are invaluable for detecting changes, calculating differences, or comparing consecutive events.
  • Finding Top N per Group: Using ROW_NUMBER(), RANK(), or DENSE_RANK() within partitions to identify the best-selling products per category, highest-paid employees per department, etc.
  • Gap Detection: Identifying missing sequences in data by comparing current row values to expected values based on previous rows.
-- Advanced example: Calculate percentage of sales contribution by product within its category
-- and also compare product sales to the previous product's sales in the category (ordered by sales amount).
WITH ProductSales AS (
    SELECT
        product_id,
        category_id,
        SUM(sales_amount) AS total_product_sales
    FROM
        sales_data
    GROUP BY
        product_id, category_id
),
CategorySales AS (
    SELECT
        category_id,
        SUM(total_product_sales) AS total_category_sales
    FROM
        ProductSales
    GROUP BY
        category_id
)
SELECT
    ps.product_id,
    ps.category_id,
    ps.total_product_sales,
    cs.total_category_sales,
    (ps.total_product_sales * 100.0 / cs.total_category_sales) AS pct_of_category_sales,
    LAG(ps.total_product_sales, 1, 0) OVER (PARTITION BY ps.category_id ORDER BY ps.total_product_sales DESC) AS prev_product_sales,
    RANK() OVER (PARTITION BY ps.category_id ORDER BY ps.total_product_sales DESC) AS rank_in_category
FROM
    ProductSales ps
JOIN
    CategorySales cs ON ps.category_id = cs.category_id
ORDER BY
    ps.category_id, rank_in_category;

Common Pitfalls

  • Misunderstanding Default Frame: An ORDER BY clause in OVER() implies a default frame (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for SUM()). If you want to aggregate over the entire partition even with an ORDER BY, you must explicitly specify ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. This is a very common source of incorrect cumulative sums when not intended.
  • RANGE vs. ROWS: They behave differently, especially with ties in the ORDER BY column. ROWS counts physical rows, while RANGE includes all rows with the same value as the current row in the ordering column(s) that fall within the specified value range. Use ROWS for a fixed number of preceding/following records, RANGE for a value-based interval.
  • Performance with Large Partitions: While generally efficient, window functions still process data. Very large partitions or complex ORDER BY clauses can impact performance, as they often require sorting the data. Indexing on PARTITION BY and ORDER BY columns can help, but the window function itself might still involve significant memory or disk spills.
  • NULL Handling: Be mindful of how NULL values in your ordering columns affect ranking or how LAG()/LEAD() might return NULL if no preceding/succeeding row exists and no default value is provided.
  • Database-Specific Implementations: While the core concepts are standard SQL (SQL:2003 and later), specific frame options (e.g., GROUPS frame in PostgreSQL 11+) or the exact behavior of default frames can vary slightly between database systems (e.g., MySQL 8.0 vs. PostgreSQL vs. SQL Server). Always consult the documentation for your specific RDBMS version.

Back to the knowledge base · Ask the AI assistant