OPAL Window Functions

Window functions are similar to aggregate functions in that they summarize input values from multiple input rows. However, each input row generates a corresponding output value, with the window potentially sliding along the list of rows.

For example, a “trailing 5 minute average” function uses a window back for 5 minutes and produce one output row per input row. But each output row looks back across many input rows in history.

Function	Description
any	Return any value of one column across a group
any_not_null	Return any non-null value of one column across a group.
avg	Calculate the arithmetic average of the input expression across the group.
count	Count the number of non-null items in the group.
count_distinct	Estimate the approximate number of distinct values in the input using hyper-log-log.
count_distinct_exact	Count the exact number of distinct values in the input using complete enumeration (slower than count_distinct).
dense_rank	Returns the dense rank within an ordered group of values.
deriv	Calculate the average per-second derivative of the argument across the group.
first	Return the first value of one column across an ordered group.
first_not_null	Return the first non-null value of one column across an ordered group.
lag	Return the value of one column in a previous row across an ordered group.
lag_not_null	Return the non-null value of one column in a previous row across an ordered group.
last	Return the last value of one column across an ordered group.
last_not_null	Return the last non-null value of one column across an ordered group.
lead	Return the value of one column in the following row across an ordered group.
lead_not_null	Return the non-null value of one column in the following row across an ordered group.
max	Compute the maximum of one column across a group (with one argument) or the scalar greatest value of its arguments (with more than
median	Return the fast approximate median value of one column.
median_exact	Return the exact median value of one column.
min	Compute the minimum of one column across a group (with one argument) or the scalar least value of its arguments (with more than
object_agg	Returns one OBJECT per group.
percentile	Returns an approximated value for the specified percentile of the input expression across the group.
percentile_cont	Assuming a continuous distribution, it returns the value for the specified percentile of the input expression across the group.
percentile_disc	Assuming a discrete distribution, it returns the value for the specified percentile of the input expression across the group.
rank	Returns the rank within an ordered group of values.
rate	Calculate the average per-second rate of the argument across the group.
row_number	Return the window index of the row within its groupby, when ordered by the orderby.
stddev	Calculate the standard deviation across the group.
sum	Calculate the sum of the argument across the group, or of the scalar arguments if more than one.