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.

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

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

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.