Window frames in SQL

The frame decides which rows the window function can see from where it stands.

SELECT
    day,
    amount,
    SUM(amount) OVER (
        ORDER BY day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS three_row_sum,
    SUM(amount) OVER (
        ORDER BY day
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS to_date,
    SUM(amount) OVER (
        ORDER BY day
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) AS centered
FROM daily_revenue;

How it works

ROWS counts rows; RANGE counts values of the sort key.
With ties, RANGE pulls in every peer row — ROWS does not.
The default frame is the whole partition up to the current row's peers.

Keywords and builtins used here

AND
AS
BETWEEN
BY
CURRENT
FROM
ORDER
ROW
ROWS
SELECT
SUM
day

The run, in numbers

Lines: 16
Characters to type: 315
Tokens: 67
Three-star pace: 95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 42 seconds.

Type this snippet

Step 5 of 9 in Window functions, step 11 of 24 in Analytics & reporting.

← Previous Next →