extra_window_functions
extra_window_functions copied to clipboard
Extra Window Functions for PostgreSQL
Extra Window Functions for PostgreSQL
compatible 9.6–15
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.
Simulating Standard SQL
The window functions LEAD()
, LAG()
, FIRST_VALUE()
, LAST_VALUE()
, and
NTH_VALUE()
can skip over null values. PostgreSQL does not implement the
syntax required for that feature but this extension provides additional
functions that give you the same behavior.
In addition to this, NTH_VALUE()
can count from the start or the end of the
window frame.
Functions provided:
-
lag_ignore_nulls(expression[, offset[, default]])
-
lead_ignore_nulls(expression[, offset[, default]])
-
first_value_ignore_nulls(expression)
-
last_value_ignore_nulls(expression)
-
nth_value_from_last(expression, offset)
-
nth_value_ignore_nulls(expression, offset)
-
nth_value_from_last_ignore_nulls(expression, offset)
Despite these functions having 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
Extending Standard SQL
The functions LEAD()
and LAG()
accept a default value for when the
requested row falls outside of the partition. However, the functions
FIRST_VALUE()
, LAST_VALUE()
, and NTH_VALUE()
do not have default values
for when the requested row is not in the frame.
Functions provided:
-
first_value_ignore_nulls(expression, default)
-
last_value_ignore_nulls(expression, default)
-
nth_value_from_last(expression, offset, default)
-
nth_value_ignore_nulls(expression, offset, default)
-
nth_value_from_last_ignore_nulls(expression, offset, default)
Non-Standard Functions
This extension introduces a new partition-level window function flip_flop()
and 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.
Functions provided:
-
flip_flop(expression[, expression])