Enterprise only
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.
The window
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:
WINDOW clause names a definition when several functions share one:
The functions
Registered in DataFusion 54. RankingOVER 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: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.