Window Functions
This page contains window functions supported by e6data.
Ranking Functions
RANK()
Returns the rank of a value compared to all values in the partition. It will produce gaps in the ranking sequence and does not break ties.
DENSE_RANK()
Returns the rank of a value compared to all values in the partition. It will not produce gaps in the ranking sequence and does not break ties.
ROW_NUMBER()
Returns a unique, sequential number for each row, starting with 1
, according to the ordering of rows within the window partition
NTILE( n )
Divides the rows for each window partition into n
, where n is greater than 1 bucket, ranging from 1
to at most n
Value Functions
FIRST_VALUE( <expr> )
Returns the first value of expr
for a group of rows.
LAST_VALUE( <expr> )
Returns the last value of expr
for a group of rows.
LEAD( <expr>, offset )
Returns data in a subsequent row in the same result set without joining the table to itself.
If the offset is null or larger than the window NULL is returned.
LAG( <expr>, offset )
Returns data in a preceding row in the same result set without joining the table to itself.
If the offset is null or larger than the window NULL is returned.
Window Frame Functions
Window Frame functions provide the ability to specify the upper and lower bounds of a window frame.
Note: e6data currently supports only SUM & COUNT functions in Window Frame functions
Syntax:
Cumulative Frames
Sliding Frames
Other Functions
COLLECT_LIST()
The collect_list function enables you to create an ordered list of values from a specified column within a group or window.
It is particularly useful when you need to gather and process multiple values associated with a particular grouping key or window frame.
Usage Examples:
Aggregation Example: This query below aggregates values from the "value_column" and creates a list of those values for each unique "key_column" value.
Windowing Example: This query generates a list of values from the "value_column" within each window defined by the "partition_column." The values are ordered by the "order_column" within each window.
Limitations:
Unsupported Complex Data Types: The collect_list function currently does not support complex data type columns as input. It is designed to work with simple data types such as integers, strings, or dates.
Absence of Sorting Capability: The collect_list function does not allow for sorting operations, such as using the ORDER BY clause. The resulting list is generated based on the order of appearance of the values in the dataset, without the ability to explicitly control the ordering.
Incompatibility with Distinct Operation: The collect_list function cannot be used in conjunction with the DISTINCT operation. It is intended to gather all values within a specified grouping or windowing context, without eliminating duplicates or applying distinctness.
Last updated