OPAL Window Functions

Window functions are like 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.
array_union_agg	Returns an array containing the multiset union of input arrays.
avg	Calculate the arithmetic average of the input expression across the group, or of the scalar arguments if more than one.
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.
delta	Calculates the value difference of the argument in each time bin for each group.
delta_monotonic	Calculates the amount of difference in a column in each time bin for each group.
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.
ewma	Calculates the exponentially weighted moving average of a value.
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.
hash_agg	Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows.
hash_agg_distinct	Returns an aggregate signed 64-bit hash value over the (unordered) set of distinct input rows.
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 a following row across an ordered group.
lead_not_null	Return the non-null value of one column in a following row across an ordered group.
max	Returns the maximum value of a group of inputs.
median	Return the fast approximate median value of one column.
median_exact	Return the exact median value of one column.
min	Returns the minimum value of a group of inputs.
object_agg	Extract aggregated fields and values from a group of rows into a new JSON object.
percentile	Returns an approximated value for the specified percentile of the input expression across the group.
percentile_cont	Assuming a continuous distribution, return the value for the specified percentile of the input expression across the group.
percentile_disc	Assuming a discrete distribution, return 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.
tdigest_agg	Generate a single t-digest state out of an arbitrary number of numeric values.
tdigest_combine	Combine (merge) multiple t-digest values together This function is purely an aggregate function.
topk_agg	Returns an approximation of the top K most frequent values in the input, along with their approximate frequencies.