Frames, Running Totals and Moving Averages

Concept

The frame is the slice of the partition the function actually reads for the current row. Without ORDER BY the frame is the whole partition; with ORDER BY it defaults to everything up to and including this row. Stating it explicitly is how you build running totals and moving averages.

Syntax

{ROWS | RANGE} BETWEEN frame_start AND frame_end

-- frame_start / frame_end can be:
UNBOUNDED PRECEDING     -- the first row of the partition
n PRECEDING             -- n rows (ROWS) or n units of value (RANGE) before
CURRENT ROW
n FOLLOWING
UNBOUNDED FOLLOWING     -- the last row of the partition

Example: running total

SELECT order_date,
       total,
       SUM(total) OVER (ORDER BY order_date, id
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       COUNT(*)   OVER (ORDER BY order_date, id
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS orders_so_far
FROM   orders
ORDER BY order_date, id;

Example: moving average

SELECT order_date,
       total,
       ROUND(AVG(total) OVER (ORDER BY order_date, id
                              ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3,
       MIN(total) OVER (ORDER BY order_date, id
                        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)           AS min_of_3
FROM   orders
ORDER BY order_date, id;

The first two rows have fewer than three rows behind them, so the average is taken over one and then two rows. That is usually what you want; if it is not, filter them out or require a full window with a row count check.

ROWS vs RANGE

-- ROWS: counts physical rows. Two rows with the same date are separate.
SUM(total) OVER (ORDER BY order_date ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- RANGE: counts by VALUE. All rows with the same order_date are included together.
SUM(total) OVER (ORDER BY order_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

With ties in the ordering column the two give different answers: RANGE includes every peer of the current row, so all orders on the same date share one running total. When in doubt use ROWS - it is predictable and usually faster.

Percent of total and cumulative share

SELECT first_name,
       dept_id,
       salary,
       ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY dept_id), 1) AS pct_of_dept,
       ROUND(100.0 * salary / SUM(salary) OVER (), 1)                     AS pct_of_company
FROM   employees
WHERE  dept_id IS NOT NULL
ORDER BY dept_id, salary DESC;

Window functions vs GROUP BY

Use GROUP BY when you want a summary. Use a window function when you want the detail and the summary on the same row - or when you need two different groupings side by side.

Important rules

• A frame clause requires ORDER BY inside OVER.
• With ORDER BY and no frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
• ROWS counts rows; RANGE groups peers with equal ordering values.
• Frames apply to aggregate window functions, FIRST_VALUE, LAST_VALUE and NTH_VALUE - not to LAG, LEAD or the ranking functions.
• MySQL 8, MariaDB 10.2+, PostgreSQL, SQL Server 2012+ and Oracle all support frames; GROUPS mode is newer and less widely available.

Common mistakes

• Relying on the default frame and getting a running total when a partition total was wanted.
• Using RANGE with a tied ordering column and silently including peer rows.
• Ordering by a non unique column so the running total order is not deterministic - add a tie breaker.
• Computing a percentage with integer division instead of 100.0 *.

Best practices

• Write the frame explicitly whenever ORDER BY appears inside OVER.
• Prefer ROWS unless you specifically want peer grouping.
• Always end the window ORDER BY with a unique column.
• Name reused windows with the WINDOW clause.

Practice

1. Produce a running revenue total over orders by date.
2. Compute a three order moving average of order totals per customer.
3. Show each employee's salary as a percentage of both their department payroll and the company payroll, in one query.