Window functions

Applies to: Embedded ❌, Enterprise ✅ A window function computes across a set of rows related to the current one and returns a value per row. That is the difference from aggregation, which collapses the rows it covers.

SELECT
    title,
    score,
    row_number() OVER (PARTITION BY category ORDER BY score DESC) AS rank
FROM documents

Every row survives; each one gains its rank within its category.

The window

OVER (
    [PARTITION BY expr [, ...]]
    [ORDER BY expr [, ...]]
    [ROWS | RANGE BETWEEN frame_start AND frame_end]
)

PARTITION BY splits the rows into independent groups. ORDER BY orders them inside each partition. Supply an ordering for useful ranks, and a stable tie-breaker when selecting one row from several equal values. The frame clause narrows the window further, which is how running totals and moving averages are expressed:

SELECT
    created_at,
    sum(amount) OVER (ORDER BY created_at ROWS UNBOUNDED PRECEDING) AS running_total,
    avg(amount) OVER (ORDER BY created_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS trailing_7
FROM transactions

A WINDOW clause names a definition when several functions share one:

SELECT
    row_number() OVER w,
    lag(score) OVER w
FROM documents
WINDOW w AS (PARTITION BY category ORDER BY score DESC)

The functions

Registered in DataFusion 54. Ranking

row_number  rank  dense_rank  percent_rank  cume_dist  ntile

Offset and positional

lag  lead  first_value  last_value  nth_value

Aggregates Any aggregate function used with OVER becomes a window function. This is how running totals and moving averages are written.

Deduplicating with a window

The common practical use: keep one row per key, chosen by some ordering. A window ranks, and an outer query filters:

WITH ranked AS (
    SELECT *, row_number() OVER (PARTITION BY doc_id ORDER BY version DESC) AS rn
    FROM revisions
)
SELECT * FROM ranked WHERE rn = 1

The outer query is needed because a window function cannot appear in WHERE: windows are computed after filtering, so the predicate would have nothing to read.

Window functions sort within each partition, so on a large table they are among the more expensive things you can ask for. Filter first, and check the cost with Explain Analyze.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Window functions

The window

The functions

Deduplicating with a window

​The window

​The functions

​Deduplicating with a window

The window

The functions

Deduplicating with a window