--- title: "Window functions" slug: "dev-database-reference-sql-functions-aggregate-window-functions" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-aggregate-window-functions" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # Window functions *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate) › 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](/develop/docs/dev-database-reference-sql-functions-aggregate-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)](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), 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, "") 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 .""" 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, "")) 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](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause) 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](/develop/docs/dev-database-reference-sql-functions-aggregate-window-functions#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](/develop/docs/dev-database-reference-sql-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." │ │/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](/develop/docs/dev-database-integrations-trino) or [Spark](/develop/docs/dev-database-integrations-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](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-aggregate-sum), [avg](/develop/docs/dev-database-reference-sql-functions-aggregate-avg), [count](/develop/docs/dev-database-reference-sql-functions-aggregate-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](/develop/docs/dev-database-reference-sql-functions-aggregate-min) and [max](/develop/docs/dev-database-reference-sql-functions-aggregate-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 - [row_number](/develop/docs/dev-database-reference-sql-functions-aggregate-row-number) and [Aggregate and analytic functions](/develop/docs/dev-database-reference-sql-functions-aggregate). - [FILTER (WHERE ...)](/develop/docs/dev-database-reference-sql-functions-aggregate-filter-clause), with the running form of an aggregate. - [SELECT](/develop/docs/dev-database-reference-sql-select), [EXPLAIN](/develop/docs/dev-database-reference-sql-explain) and [not supported](/develop/docs/dev-database-reference-sql-not-supported).