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.

rank() over(order by a)
rank() over(partition by a order by b)

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.

dense_rank() over(order by a)
dense_rank() over(partition by a order by b)

ROW_NUMBER()

Returns a unique, sequential number for each row, starting with 1, according to the ordering of rows within the window partition

row_number() over(order by a)
row_number() over(partition by a order by b)

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

ntile(n) over(order by a)
ntile(n) over(partition by a order by b)

> select colA, ntile(10) over( order by colA desc) as ntile_val from table;

> select colB, ntile(10) over( partition by colB order by colc desc) as ntile_val from table;

Value Functions

FIRST_VALUE( <expr> )

Returns the first value of expr for a group of rows.

> SELECT first_value(col) over (order by colA desc) FROM table;

LAST_VALUE( <expr> )

Returns the last value of expr for a group of rows.

> SELECT last_value(col) over ( partition by colB order by colA desc) FROM table;

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.

> select * from (
    select lead(count(colB),5) over (order by colA)
    from table)
  order by colA DESC;

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.

> select * from (
    select lag(count(colB),5) over (order by colA)
    from table)
  order by colA DESC;

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:

<function> ( <arguments> ) OVER ( PARTITION BY <expr1> ORDER BY <expr2> 
<frame_type> )

• Cumulative Frames

rows between unbounded preceding and current row
rows between current row and unbounded following

• Sliding Frames

rows between <num> { preceding | following } and <num> { preceding | following }
rows between unbounded preceding and <num> { preceding | following }
rows between <num> { preceding | following } and unbounded following

> select
    count(a) over (partition by b order by c rows between unbounded preceding and current row),
    count(a) over (partition by b order by c rows between 2 following and  unbounded following),
    count(a) over (partition by b order by c rows between 2 preceding and  2 following),
    sum(a)   over (partition by b order by c rows between unbounded preceding and current row),
    sum(a) over (partition by b order by c rows between 2 following and  unbounded following),
    sum(a) over (partition by b order by c rows between 2 preceding and  2 following)
  from table
  ;

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:

1. Aggregation Example: This query below aggregates values from the "value_column" and creates a list of those values for each unique "key_column" value.

SELECT key_column, collect_list(value_column) AS value_list
FROM table_name
GROUP BY key_column;

2. 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.

SELECT key_column, collect_list(value_column) OVER (PARTITION BY partition_column ORDER BY order_column) AS value_list
FROM table_name;

Limitations:

1. 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.

2. 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.

3. 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.