Window Functions Quick Reference

Available since: v3.7.0 (full SQL-92 + PostgreSQL window surface, 38 hardening tests). Verified against: v3.19.1.

UVP

Every window function PostgreSQL ships, HeliosDB Nano runs unchanged: ROW_NUMBER, RANK, DENSE_RANK, CUME_DIST, PERCENT_RANK, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, plus any aggregate as a window function. Frame specs (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, RANGE BETWEEN n PRECEDING AND m FOLLOWING) and named windows (WINDOW w AS (…)) with inheritance all work. Same SQL as PostgreSQL — no rewrites, no flags.

The v3.7 release added named windows with inheritance and ORDER BY aggregate sorting (sort by SUM(x) without nesting it in a CTE). Implementation is src/sql/executor/window.rs; 38 regression tests are checked into tests/window_functions_hardening.rs.

The Functions

Ranking

SELECT
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense,
  PERCENT_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS pct,
  CUME_DIST()  OVER (PARTITION BY dept ORDER BY salary DESC) AS cd,
  NTILE(4)     OVER (PARTITION BY dept ORDER BY salary DESC) AS quartile
FROM employees;

Function	Returns	Tie behavior
`ROW_NUMBER()`	Sequential 1, 2, 3, … per partition	Always unique
`RANK()`	Rank with gaps for ties (1, 1, 3, 4)	Same value → same rank, gap
`DENSE_RANK()`	Rank without gaps (1, 1, 2, 3)	Same value → same rank, no gap
`PERCENT_RANK()`	`(rank - 1) / (total - 1)`	Range `[0, 1]`
`CUME_DIST()`	Cumulative distribution `[0, 1]`	Uses ORDER BY keys (fixed in v3.7)
`NTILE(n)`	Bucket number `1..n`	Equal-sized buckets, remainder in early buckets

Offset

SELECT
  LAG(price, 1)   OVER (ORDER BY ts) AS prev_price,
  LAG(price, 1, 0) OVER (ORDER BY ts) AS prev_price_or_zero,
  LEAD(price, 1)  OVER (ORDER BY ts) AS next_price,
  price - LAG(price) OVER (ORDER BY ts) AS delta
FROM ticks;

LAG / LEAD accept (expr, offset, default). Default offset is 1, default default is NULL.

Frame Lookup

SELECT
  FIRST_VALUE(price) OVER (PARTITION BY symbol ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS open,
  LAST_VALUE(price)  OVER (PARTITION BY symbol ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS close,
  NTH_VALUE(price, 3) OVER (PARTITION BY symbol ORDER BY ts
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third
FROM ticks;

The frame spec matters for LAST_VALUE — without UNBOUNDED FOLLOWING, you’ll get the current row (because the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Aggregates as Windows

Any aggregate works in OVER (…):

SELECT
  ts, price,
  SUM(price)   OVER (PARTITION BY symbol ORDER BY ts) AS running_total,
  AVG(price)   OVER (PARTITION BY symbol ORDER BY ts
                     ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sma_7,
  COUNT(*)     OVER (PARTITION BY symbol)             AS partition_size,
  STDDEV(price) OVER (PARTITION BY symbol ORDER BY ts) AS rolling_stddev
FROM ticks;

SUM / AVG / MIN / MAX / COUNT / STDDEV / VARIANCE all work. NULLs propagate per SQL three-valued logic (e.g., all-NULL SUM returns NULL — fixed in v3.7).

Frame Specs

The full surface (src/sql/executor/window.rs::compute_frame_bounds):

-- ROWS frames (physical row count)
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW       -- running aggregate
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- whole partition
ROWS BETWEEN 3 PRECEDING AND CURRENT ROW               -- last 4 rows
ROWS BETWEEN CURRENT ROW AND 5 FOLLOWING               -- next 6 rows
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING               -- centered window of 5

-- RANGE frames (value-based)
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW      -- default with ORDER BY
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING  -- default without ORDER BY

Default frame:

With ORDER BY, no frame clause: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (running aggregate).
Without ORDER BY, no frame clause: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (whole partition).

This is why LAST_VALUE with no frame returns the current row, not the partition’s last — same as PostgreSQL.

Named Windows With Inheritance (v3.7)

Repeating OVER (PARTITION BY symbol ORDER BY ts) ten times is noisy. Define it once:

SELECT
  ts, price,
  SUM(price)        OVER w AS running_total,
  AVG(price)        OVER w AS running_avg,
  ROW_NUMBER()      OVER w AS rn,
  FIRST_VALUE(price) OVER (w ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS open
FROM ticks
WINDOW w AS (PARTITION BY symbol ORDER BY ts);

The fourth column inherits w and adds a frame clause — same partitioning and ordering, narrower frame. The planner handles this in populate_named_windows (src/sql/planner.rs).

You can also chain windows:

WINDOW
  w1 AS (PARTITION BY symbol),
  w2 AS (w1 ORDER BY ts),
  w3 AS (w2 ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)

ORDER BY Aggregate Sorting (v3.7)

This now works without a CTE wrap:

SELECT dept, SUM(salary) AS total
FROM employees
GROUP BY dept
ORDER BY SUM(salary) DESC;     -- sort by aggregate directly

The planner rewrites the ORDER BY aggregate reference to the column alias produced by the HashAggregate operator (Self::rewrite_order_by_aggregates, src/sql/planner.rs). You can also write ORDER BY total DESC or ORDER BY 2 DESC (ordinal positions are SQL-92, fixed in v3.7).

Recipes

Top-N per group

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn <= 3;

Running totals + percentages

SELECT
  ts, amount,
  SUM(amount) OVER (ORDER BY ts) AS cumulative,
  amount * 1.0 / SUM(amount) OVER () AS pct_of_total
FROM transactions
ORDER BY ts;

Gap detection (LAG)

SELECT ts, value, ts - LAG(ts) OVER (ORDER BY ts) AS gap
FROM heartbeats;

Quartiles

SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS spend_quartile
FROM customer_summary;

Moving averages

SELECT
  ts, price,
  AVG(price) OVER w_7  AS sma_7,
  AVG(price) OVER w_30 AS sma_30
FROM ticks
WINDOW
  w_7  AS (PARTITION BY symbol ORDER BY ts ROWS BETWEEN 6  PRECEDING AND CURRENT ROW),
  w_30 AS (PARTITION BY symbol ORDER BY ts ROWS BETWEEN 29 PRECEDING AND CURRENT ROW);

Common Pitfalls

Pitfall	Symptom	Fix
`LAST_VALUE` returns current row	Frame defaults to `… AND CURRENT ROW`	Add `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`
`RANK()` with no ORDER BY	All rows rank 1	Add ORDER BY (no order = no rank semantics)
`SUM` over all-NULL returns 0	Pre-3.7 bug	Fixed — now returns NULL per SQL semantics
`CUME_DIST()` always 1.0	Pre-3.7 bug — used empty args	Fixed — now uses ORDER BY keys
Filtering on window result in WHERE	`WHERE rn <= 3` doesn’t work	Wrap in subquery or use QUALIFY (not yet)
`OVER w` typo (`WINDOW w AS (…)` missing)	Parse error	Define every named window in the `WINDOW` clause

Where Next

EXPLAIN_AND_OPTIMIZATION_TUTORIAL — read window plans, see partition cost.
KEYSET_PAGINATION_QUICKREF — ROW_NUMBER + keyset for stable cursors.
UPSERT_QUICKREF — ON CONFLICT patterns for write paths.

CHANGELOG references: v3.7.0 (window functions surface + named windows + ORDER BY aggregate + 38 hardening tests + CUME_DIST/SUM-over-NULL fixes), v3.19.1 (current verified release).