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 |
---|---|
Return any value of one column across a group |
|
Return any non-null value of one column across a group. |
|
Calculate the arithmetic average of the input expression across the group. |
|
Count the number of non-null items in the group. |
|
Estimate the approximate number of distinct values in the input using hyper-log-log. |
|
Count the exact number of distinct values in the input using complete enumeration (slower than [count_distinct](. |
|
Returns the dense rank within an ordered group of values. |
|
Calculate the average per-second derivative of the argument across the group. |
|
Return the first value of one column across an ordered group. |
|
Return the first non-null value of one column across an ordered group. |
|
Return the value of one column in a previous row across an ordered group. |
|
Return the non-null value of one column in a previous row across an ordered group. |
|
Return the last value of one column across an ordered group. |
|
Return the last non-null value of one column across an ordered group. |
|
Return the value of one column in a following row across an ordered group. |
|
Return the non-null value of one column in a following row across an ordered group. |
|
Compute the maximum of one column across a group (with one argument) or the scalar greatest value of its arguments (with more than |
|
Return the fast approximate median value of one column. |
|
Return the exact median value of one column. |
|
Compute the minimum of one column across a group (with one argument) or the scalar least value of its arguments (with more than |
|
Returns one OBJECT per group. |
|
Returns an approximated value for the specified percentile of the input expression across the group. |
|
Assuming a continuous distribution, it returns the value for the specified percentile of the input expression across the group. |
|
Assuming a discrete distribution, it returns the value for the specified percentile of the input expression across the group. |
|
Returns the rank within an ordered group of values. |
|
Calculate the average per-second rate of the argument across the group. |
|
Return the window index of the row within its groupby, when ordered by the orderby. |
|
Calculate the standard deviation across the group. |
|
Calculate the sum of the argument across the group, or of the scalar arguments if more than one. |