Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

Window functions

Prev Next

Applies to VAST Cluster 5.5.

Functions › Aggregate and analytic functions › Window functions. A window function computes a value for each row from other rows of the result, such as the previous row's value or a running total. The VAST Query Engine runs window functions over the rows in the order they reach it, with nothing but a frame in OVER (...): ORDER BY and PARTITION BY in OVER (...) aren't available. This page covers the OVER clause and rank, dense_rank, percent_rank, cume_dist, ntile, lag, lead, first_value, last_value and nth_value, and aggregates used as window functions. row_number has its own page.

Syntax

function(arguments) OVER ()
function(arguments) OVER (ROWS frame)
function(arguments) OVER w ... WINDOW w AS ()
rank() OVER ()
dense_rank() OVER ()
percent_rank() OVER ()
lag(x [, offset [, default]]) OVER ()
lead(x [, offset [, default]]) OVER ()
first_value(x) OVER ()
aggregate(x) OVER (ROWS UNBOUNDED PRECEDING)
Argument Type Description
x Any type The value to read from another row.
offset Integer How many rows back (lag) or ahead (lead). Default 1.
default The type of x The value when the row at offset doesn't exist. Default NULL.
aggregate(x) An aggregate such as sum, count, avg, min or var_pop.

Returns: rank, dense_rank and row_number: BIGINT. percent_rank: DOUBLE. lag, lead and first_value: the type of x. A running aggregate: the aggregate's own type.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_aggregate_window_functions. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of seven daily meter readings. Two readings are the same, and the last day has none:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_aggregate_window_functions"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines (the table's row count, which can still be 0 just after an insert),
    with your database name shown as <bucket>."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line).replace(BUCKET, "<bucket>"))

TABLE = f"{SCHEMA}.meter"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, meter VARCHAR, day DATE, reading BIGINT)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'm1', DATE '2026-10-01', 10), (2, 'm1', DATE '2026-10-02', -20), "
    "(3, 'm2', DATE '2026-10-03', 0), (4, 'm2', DATE '2026-10-04', 10), "
    "(5, 'm3', DATE '2026-10-05', 30), (6, 'm3', DATE '2026-10-06', 5), "
    "(7, 'm3', DATE '2026-10-07', NULL)")

# Most examples read the rows sorted by day, through this subquery.
BY_DAY = f"(SELECT id, meter, day, reading FROM {TABLE} ORDER BY day LIMIT 100)"

[]
[]
[(7,)]

What it does

The OVER clause

OVER (...) turns a function into a window function: it returns one value per row instead of one value for the query. In the VAST Query Engine, OVER (...) can hold nothing, or a ROWS frame, and the rows are taken in the order they reach the window step. With a plain table, that's the order the scan returns them in, which isn't defined. To work in a chosen order, sort the rows in a subquery with ORDER BY ... LIMIT, and apply the window function to the subquery, as the examples on this page do with BY_DAY.

Which forms run:

Function OVER () OVER (ROWS ...) ORDER BY or PARTITION BY in OVER
row_number Runs: 1, 2, 3, ... Runs: the same numbers Fails
rank(), dense_rank() Runs: 1 on every row Runs: 1 on every row Fails
percent_rank() Runs: 0.0 on every row Runs: 0.0 on every row Fails
lag(x), lead(x) Runs: the previous or next row's value Runs: the frame doesn't change the result Fails
first_value(x) Runs: the first row's value Runs: the frame doesn't change the result Fails
cume_dist(), ntile(n), last_value(x), nth_value(x, n) Fails Fails Fails
An aggregate (sum, count, avg, min, var_pop, ...) Fails Only ROWS UNBOUNDED PRECEDING runs: a running value Fails

A form that fails returns The operator 'WINDOW' type=6 is currently not supported. A frame with EXCLUDE fails too. OVER w with WINDOW w AS () is the same as OVER ().

rank, dense_rank and percent_rank

These rank a row by the ORDER BY in its window. Without one, every row ties with every other, so rank() and dense_rank() return 1 and percent_rank() returns 0.0 on every row. They take no arguments.

cume_dist and ntile

cume_dist() and ntile(n) fail in every form in the VAST Query Engine, OVER () included.

lag and lead

lag(x, offset, default) returns x from the row offset rows before the current one; lead from the row offset rows after it. offset defaults to 1, and default, returned when that row doesn't exist, to NULL. x can be any type: numbers, text, dates and lists all work.

first_value, last_value and nth_value

first_value(x) returns x from the first row the window step receives, on every row. last_value(x) and nth_value(x, n) fail in every form; to get the last value, apply first_value to rows sorted the other way.

Aggregates as window functions

An aggregate with OVER (ROWS UNBOUNDED PRECEDING) returns, on each row, the aggregate of that row and every row before it: a running total, count, average, minimum or variance. Any other window on an aggregate fails, OVER () included, so a total repeated on every row isn't available: compute it in a separate aggregate query. FILTER works with the running form.

NULLs and edge cases

Case Result
lag(x) on the first row, lead(x) on the last NULL, or default if given
lag(x, 0) x from the current row
lag(x, -1) The same as lead(x); a negative offset reads the other way
An offset beyond the rows (lag(x, 100)) NULL on every row
A NULL value of x in the row read NULL, not the default: default is only for a row that doesn't exist
lag(x, NULL) Error: The operator 'WINDOW' type=6 is currently not supported.
A default that doesn't convert to the type of x (lag(reading, 1, 'x')) Error: Could not convert string 'x' to INT64
A running aggregate over NULLs NULLs are skipped, as in the aggregate
No rows No rows
No FROM (SELECT lag(42) OVER ()) NULL

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run(f"SELECT day, reading, lag(reading) OVER (), lead(reading) OVER (), "
    f"lag(reading, 2, -1) OVER (), lead(reading, 2, -1) OVER () FROM {BY_DAY}")
run(f"SELECT day, reading, lag(reading, 0) OVER (), lag(reading, -1) OVER (), lag(reading, 100) OVER () "
    f"FROM {BY_DAY}")
run(f"SELECT id, lag(reading, NULL) OVER () FROM {TABLE}")
run(f"SELECT id, lag(reading, 1, 'x') OVER () FROM {TABLE}")
run(f"SELECT day, sum(reading) OVER (ROWS UNBOUNDED PRECEDING), count(reading) OVER (ROWS UNBOUNDED PRECEDING) "
    f"FROM {BY_DAY}")
run(f"SELECT count(*) FROM (SELECT lag(reading) OVER () AS v FROM {TABLE} WHERE id > 100)")
run("SELECT lag(42) OVER (), rank() OVER (), percent_rank() OVER ()")

[(datetime.date(2026, 10, 1), 10, None, -20, -1, 0), (datetime.date(2026, 10, 2), -20, 10, 0, -1, 10), (datetime.date(2026, 10, 3), 0, -20, 10, 10, 30), (datetime.date(2026, 10, 4), 10, 0, 30, -20, 5), (datetime.date(2026, 10, 5), 30, 10, 5, 0, None), (datetime.date(2026, 10, 6), 5, 30, None, 10, -1), (datetime.date(2026, 10, 7), None, 5, None, 30, -1)]
[(datetime.date(2026, 10, 1), 10, 10, -20, None), (datetime.date(2026, 10, 2), -20, -20, 0, None), (datetime.date(2026, 10, 3), 0, 0, 10, None), (datetime.date(2026, 10, 4), 10, 10, 30, None), (datetime.date(2026, 10, 5), 30, 30, 5, None), (datetime.date(2026, 10, 6), 5, 5, None, None), (datetime.date(2026, 10, 7), None, None, None, None)]
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: Conversion Error: Could not convert string 'x' to INT64
[(datetime.date(2026, 10, 1), Decimal('10'), 1), (datetime.date(2026, 10, 2), Decimal('-10'), 2), (datetime.date(2026, 10, 3), Decimal('-10'), 3), (datetime.date(2026, 10, 4), Decimal('0'), 4), (datetime.date(2026, 10, 5), Decimal('30'), 5), (datetime.date(2026, 10, 6), Decimal('35'), 6), (datetime.date(2026, 10, 7), Decimal('35'), 6)]
[(0,)]
[(None, 1, 0.0)]

Examples

The change from the previous day. The subquery sorts the readings by day; lag reads the row before:

run(f"SELECT day, reading, reading - lag(reading) OVER () AS change FROM {BY_DAY}")

[(datetime.date(2026, 10, 1), 10, None), (datetime.date(2026, 10, 2), -20, -30), (datetime.date(2026, 10, 3), 0, 20), (datetime.date(2026, 10, 4), 10, 10), (datetime.date(2026, 10, 5), 30, 20), (datetime.date(2026, 10, 6), 5, -25), (datetime.date(2026, 10, 7), None, None)]

The next day's reading, with 0 after the last day:

run(f"SELECT day, reading, lead(reading, 1, 0) OVER () AS next_reading FROM {BY_DAY}")

[(datetime.date(2026, 10, 1), 10, -20), (datetime.date(2026, 10, 2), -20, 0), (datetime.date(2026, 10, 3), 0, 10), (datetime.date(2026, 10, 4), 10, 30), (datetime.date(2026, 10, 5), 30, 5), (datetime.date(2026, 10, 6), 5, None), (datetime.date(2026, 10, 7), None, 0)]

A running total, count and average, day by day:

run(f"SELECT day, reading, "
    f"sum(reading) OVER (ROWS UNBOUNDED PRECEDING) AS total, "
    f"count(reading) OVER (ROWS UNBOUNDED PRECEDING) AS days_with_reading, "
    f"avg(reading) OVER (ROWS UNBOUNDED PRECEDING) AS average FROM {BY_DAY}")

[(datetime.date(2026, 10, 1), 10, Decimal('10'), 1, 10.0), (datetime.date(2026, 10, 2), -20, Decimal('-10'), 2, -5.0), (datetime.date(2026, 10, 3), 0, Decimal('-10'), 3, -3.3333333333333335), (datetime.date(2026, 10, 4), 10, Decimal('0'), 4, 0.0), (datetime.date(2026, 10, 5), 30, Decimal('30'), 5, 6.0), (datetime.date(2026, 10, 6), 5, Decimal('35'), 6, 5.833333333333333), (datetime.date(2026, 10, 7), None, Decimal('35'), 6, 5.833333333333333)]

Each reading against the first day's, and the last day's reading, from first_value over rows sorted the other way:

run(f"SELECT day, reading, reading - first_value(reading) OVER () AS since_first FROM {BY_DAY}")
run(f"SELECT day, first_value(reading) OVER () AS last_reading "
    f"FROM (SELECT day, reading FROM {TABLE} WHERE reading IS NOT NULL ORDER BY day DESC LIMIT 100) LIMIT 1")

[(datetime.date(2026, 10, 1), 10, 0), (datetime.date(2026, 10, 2), -20, -30), (datetime.date(2026, 10, 3), 0, -10), (datetime.date(2026, 10, 4), 10, 0), (datetime.date(2026, 10, 5), 30, 20), (datetime.date(2026, 10, 6), 5, -5), (datetime.date(2026, 10, 7), None, None)]
[(datetime.date(2026, 10, 6), 5)]

One meter's readings only. WHERE runs before the window step, so lag reads only the rows that pass:

run(f"SELECT day, reading, lag(reading) OVER () AS previous "
    f"FROM (SELECT day, reading FROM {TABLE} WHERE meter = 'm3' ORDER BY day LIMIT 100)")

[(datetime.date(2026, 10, 5), 30, None), (datetime.date(2026, 10, 6), 5, 30), (datetime.date(2026, 10, 7), None, 5)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). A window function is a step in the Query Engine above the scan, and EXPLAIN shows which kind:

  • STREAMING_WINDOW: the forms that run. The step computes each row's value from the rows it receives, in the order it receives them.
  • WINDOW: the forms that fail when the query runs, such as any OVER (ORDER BY ...), sum(x) OVER () and ntile(n) OVER ().

So you can check a window query with EXPLAIN before you run it. lag(reading) OVER () and a running sum plan a STREAMING_WINDOW step; sum(reading) OVER () plans a WINDOW step:

plan(f"SELECT id, lag(reading) OVER () FROM {TABLE}")
plan(f"SELECT id, sum(reading) OVER (ROWS UNBOUNDED PRECEDING) FROM {TABLE}")
plan(f"SELECT id, sum(reading) OVER () FROM {TABLE}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      STREAMING_WINDOW     │
│    ────────────────────   │
│        Projections:       │
│    LAG(reading) OVER ()   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│          reading          │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      STREAMING_WINDOW     │
│    ────────────────────   │
│        Projections:       │
│  sum(reading) OVER (ROWS  │
│      BETWEEN UNBOUNDED    │
│ PRECEDING AND CURRENT ROW)│
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│          reading          │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           WINDOW          │
│    ────────────────────   │
│        Projections:       │
│    sum(reading) OVER ()   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│          reading          │
│                           │
└───────────────────────────┘

With the sorted subquery, a TOP_N step sorts the rows between the scan and the window step:

plan(f"SELECT day, lag(reading) OVER () FROM {BY_DAY}")

┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│            day            │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #0            │
│             #1            │
│             #2            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│      STREAMING_WINDOW     │
│    ────────────────────   │
│        Projections:       │
│    LAG(reading) OVER ()   │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           TOP_N           │
│    ────────────────────   │
│          Top: 100         │
│                           │
│         Order By:         │
│      vastdb."<bucket>     │
│/docs_sql_fn_aggregate_wind│
│ ow_functions".meter."day" │
│             ASC           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│            day            │
│          reading          │
│                           │
└───────────────────────────┘

Make it faster

  • Filter in WHERE. A condition that pushes down is evaluated in VAST DataBase, so fewer rows reach the window step. Put it inside the sorted subquery.
  • Keep the subquery's LIMIT to the rows you need. The TOP_N step keeps only that many rows. Rows beyond the LIMIT are left out of the window.
  • Check the plan. A WINDOW step in EXPLAIN means the query will fail; rewrite it before you run it on a large table.
  • Compute totals separately. A total on every row (sum(x) OVER ()) isn't available; one aggregate query computes it in one pass, and your application can join it to the rows.

Limitations and errors

  • No ORDER BY or PARTITION BY in OVER (...). They fail with The operator 'WINDOW' type=6 is currently not supported. Sort in a subquery instead. For one group, filter in WHERE; for every group at once, or for rankings, use Trino or Spark on the same table.
  • Not available in any form: cume_dist, ntile, last_value, nth_value, an aggregate with OVER () or with a frame other than ROWS UNBOUNDED PRECEDING, and a window over an aggregate (sum(count(*)) OVER ()). The same error.
  • No IGNORE NULLS. lag(x IGNORE NULLS) OVER () fails with the same error; lag(x) IGNORE NULLS OVER () fails with a syntax error at IGNORE. Filter out the NULLs in the subquery instead.
  • Not in WHERE. A window function in WHERE fails with WHERE clause cannot contain window functions!. Filter in an outer query, or with QUALIFY.
  • Wrong argument counts. rank(id) fails with RANK needs 0 parameters, got 1, and nth_value(x) with NTH_VALUE needs 2 parameters, got 1.
  • Some window functions of other SQL dialects don't exist. fill, ratio_to_report and percentile_cont fail with Aggregate Function with name ... does not exist!.
run(f"SELECT id, rank() OVER (ORDER BY reading) FROM {TABLE}")
run(f"SELECT id, lag(reading) OVER (PARTITION BY meter) FROM {TABLE}")
run(f"SELECT id, ntile(3) OVER () FROM {TABLE}")
run(f"SELECT id, last_value(reading) OVER () FROM {TABLE}")
run(f"SELECT id, sum(reading) OVER () FROM {TABLE}")
run(f"SELECT id, sum(reading) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM {TABLE}")
run(f"SELECT sum(count(*)) OVER () FROM {TABLE}")
run(f"SELECT id, lag(reading IGNORE NULLS) OVER () FROM {TABLE}")
run(f"SELECT id FROM {TABLE} WHERE lag(reading) OVER () > 0")
run(f"SELECT count(*) FROM (SELECT id FROM {TABLE} QUALIFY lag(reading) OVER () IS NOT NULL)")
run(f"SELECT id, rank(id) OVER () FROM {TABLE}")
run(f"SELECT id, nth_value(reading) OVER () FROM {TABLE}")
run(f"SELECT id, ratio_to_report(reading) OVER () FROM {TABLE}")

InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: The operator 'WINDOW' type=6 is currently not supported.
InternalError: Binder Error: WHERE clause cannot contain window functions!
[(6,)]
InternalError: Binder Error: RANK needs 0 parameters, got 1
InternalError: Binder Error: NTH_VALUE needs 2 parameters, got 1
InternalError: Catalog Error: Aggregate Function with name ratio_to_report does not exist!

Similar functions

To get In the VAST Query Engine
Row numbers in a chosen order row_number over a sorted subquery
The top rows by a value (instead of rank) ORDER BY value DESC LIMIT n
A total, average or count for the whole result The aggregate on its own, in a separate query: sum, avg, count
A running value The aggregate with OVER (ROWS UNBOUNDED PRECEDING)
The previous or next value lag or lead over a sorted subquery
The first or last value first_value over a subquery sorted one way or the other, or min and max for the smallest and largest

The top three readings with ORDER BY ... LIMIT, and the total as its own aggregate:

run(f"SELECT day, reading FROM {TABLE} ORDER BY reading DESC NULLS LAST LIMIT 3")
run(f"SELECT sum(reading), avg(reading), count(reading) FROM {TABLE}")

[(datetime.date(2026, 10, 5), 30), (datetime.date(2026, 10, 4), 10), (datetime.date(2026, 10, 1), 10)]
[(Decimal('35'), 5.833333333333333, 6)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

Related articles
  • row_number
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Aggregate and analytic functions
  • count
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Aggregate and analytic functions
  • any_value
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Aggregate and analytic functions