Extra Window Functions for PostgreSQL

compatible 9.6–19

This extension provides additional window functions to PostgreSQL. Some of them provide SQL Standard functionality but without the SQL Standard grammar, others extend on the SQL Standard, and still others are novel and hopefully useful to someone.

Installation

This is a PGXS extension, so it builds with the PostgreSQL server's own tooling. From a checkout:

make
make install          # may need sudo, depending on where PostgreSQL lives

make uses the first pg_config on your PATH. To build against a different server, point PG_CONFIG at its pg_config:

make PG_CONFIG=/path/to/pg/bin/pg_config install

Then load it into each database that needs it:

CREATE EXTENSION extra_window_functions;

To run the regression tests against a live server, use make installcheck.

Function reference

Function	Description
Standard functions, extended
lag_ignore_nulls(x [, offset [, default]])	LAG over non-null rows
lead_ignore_nulls(x [, offset [, default]])	LEAD over non-null rows
first_value_ignore_nulls(x [, default])	FIRST_VALUE over non-null rows
last_value_ignore_nulls(x [, default])	LAST_VALUE over non-null rows
nth_value(x, n, default)	NTH_VALUE with an out-of-frame default
nth_value_ignore_nulls(x, n [, default])	NTH_VALUE over non-null rows
nth_value_from_last(x, n [, default])	NTH_VALUE counting from the end
nth_value_from_last_ignore_nulls(x, n [, default])	both at once
Ranking and ties
count_ties()	size of the current peer group
avg_rank()	average (mid) rank of the peer group
avg_percent_rank()	percent_rank() from the average rank
Sequencing and runs
flip_flop(x [, y])	latch: off until x, on until y (or x again)
group_number(x)	group counter, +1 each time x is true
run_length(x)	length of the current run of equal values
run_position(x)	position within that run, counting from 1
Numeric and time series
ema(x, alpha)	exponential moving average
interpolate(x)	fill nulls by linear interpolation
most_common(x)	most frequent value in the frame (the mode)

Simulating Standard SQL

The window functions LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() can skip over null values, and NTH_VALUE() can count from the start or the end of the window frame. PostgreSQL does not implement the syntax for these, but this extension provides functions that give you the same behavior.

Despite the long names, there isn't really any difference in length compared to the excessively verbose SQL Standard syntax.

-- Standard SQL:
NTH_VALUE(x, 3) FROM LAST IGNORE NULLS OVER w

-- This extension:
nth_value_from_last_ignore_nulls(x, 3) OVER w

PostgreSQL 19 adds the standard IGNORE NULLS clause, so the *_ignore_nulls functions are only needed on PostgreSQL 18 and earlier. On 19 and later they emit a warning that points you at the standard syntax. They will be removed from this extension once 18 goes EOL in 2030.

FROM LAST is still not implemented in PostgreSQL 19, so nth_value_from_last and nth_value_from_last_ignore_nulls stay useful on every version.

Extending Standard SQL

LEAD() and LAG() accept a default value for when the requested row falls outside of the partition. FIRST_VALUE(), LAST_VALUE(), and NTH_VALUE() do not have default values for when the requested row is not in the frame. The versions here take an optional trailing default argument that supplies one. The SQL Standard has no syntax for this, so it stays useful even on PostgreSQL 19 and above.

Non-Standard Functions

These have no SQL Standard equivalent.

Flip-flop

flip_flop() implements the "flip floperator". In the first variant, the function returns false until the expression given as an argument returns true. It then keeps returning true until the expression is matched again. The second variant takes two expressions: the first to flip, the second to flop.

Ranking and ties

Like the standard rank() and dense_rank(), these take no arguments and work on the peer group of the current row (the rows tied with it under the window's ORDER BY).

count_ties() returns the size of that peer group: how many rows, including the current one, share its ORDER BY value. It is 1 when the row has no ties.

avg_rank() returns the average rank of the peer group, which is the mean of the ranks its rows occupy. Where rank() gives every peer the group's first position and then skips, avg_rank() puts the group at the middle of the positions it spans:

avg_rank() = rank() + (count_ties() - 1) / 2.0

 score | rank | dense_rank | count_ties | avg_rank
-------+------+------------+------------+----------
    90 |    1 |          1 |          1 |        1
    80 |    2 |          2 |          3 |        3
    80 |    2 |          2 |          3 |        3
    80 |    2 |          2 |          3 |        3
    70 |    5 |          3 |          2 |      5.5
    70 |    5 |          3 |          2 |      5.5
    60 |    7 |          4 |          1 |        7

This is the tie handling used by Excel's RANK.AVG and by the default method="average" in R's rank(), pandas' Series.rank(), and SciPy's rankdata(). Statistics calls it the fractional rank, or midrank. Rank-based methods such as Spearman's correlation and the Wilcoxon and Kruskal-Wallis tests need it; computing them with rank() gives wrong answers whenever there are ties.

avg_percent_rank() is percent_rank() computed from the average rank instead of the first rank, (avg_rank() - 1) / (total_rows - 1). It is the tie-aware plotting position those same tests use.

Sessionization and runs

group_number() returns a number that starts at 1 and goes up by one every time its argument is true, staying the same otherwise, the way row_number() numbers rows. It turns a "this row starts a new group" flag into a stable key, which is the usual way to handle gaps-and-islands and sessionization problems. Window functions cannot be nested, so compute the boundary in a subquery first:

SELECT *, group_number(gap) OVER (ORDER BY ts) AS session
FROM (
    SELECT *, ts - lag(ts) OVER (ORDER BY ts) > interval '30 min' AS gap
    FROM events
) s;

run_length() and run_position() describe runs of consecutive equal values. Unlike count_ties(), which groups by the window's ORDER BY, a run is a maximal block of adjacent rows whose value is not distinct from the current row's, so consecutive nulls form a run. run_length() is the size of that block, and run_position() is the current row's position within it, counting from 1.

Time series

ema(value, alpha) is the exponential moving average with smoothing factor alpha in the range (0, 1]:

ema = alpha * value + (1 - alpha) * previous_ema

A null value counts as a missing observation, so the result is null and the running average does not change.

interpolate(value) fills nulls by linear interpolation between the nearest non-null rows on either side, using row position as the x axis. A null with a non-null neighbor on only one side takes that neighbor's value, so leading and trailing nulls are held rather than extrapolated.

Mode

most_common(value) returns the most frequent non-null value in the window frame, breaking ties by first appearance. This is the mode. PostgreSQL's standard mode() WITHIN GROUP (ORDER BY ...) is an ordered-set aggregate and cannot be a window function, but most_common() can. It scans the frame, so a bounded frame is best.