本页尚未翻译

本页内容仍为英文原文，源自官方文档 sql/dql/window。中文翻译正在分批进行中。

Window functions

Compute over a set of rows related to the current one, without collapsing them.

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.