AVG

Computes the average of all non-null values of an attribute in a group.

SYNTAX

avg()

OUTPUT

double

EXAMPLE

GROUP avg(age) AS result BY name

→

COLLECT_LIST

Collects all non-null values of an attribute in a group. If the input dataset is ordered, then the resulting list is also ordered. WARNING - using this function with a large number of records can result in errors. Please consider using collect_set().

SYNTAX

collect_list()

OUTPUT

{'type': 'array', 'elementType': 'any'}

EXAMPLE

GROUP collect_list(employee) AS employees BY department

→

COLLECT_SET

Collects all distinct, non-null values of an attribute in a group. The resulting list is not ordered.

SYNTAX

collect_set()

OUTPUT

{'type': 'array', 'elementType': 'any'}

EXAMPLE

GROUP collect_set(employee) AS employees BY department

→

COUNT

Counts the number of non-null items in a group.

SYNTAX

count()

OUTPUT

long

EXAMPLE

GROUP count(event) AS num_events BY region

→

COUNT_DISTINCT

Counts the number of distinct non-null items in a group.

SYNTAX

count_distinct()

OUTPUT

long

EXAMPLE

GROUP count_distinct(event) AS num_events BY region

→

DENSE_RANK

Assigns to each row its position according to the ORDER BY expression. Unlike rank(), dense_rank() values increase sequentially, one-by-one, regardless of ties.

SYNTAX

dense_rank()

OUTPUT

int

EXAMPLE

WINDOW DENSE_RANK() AS eventRank BY region ORDER BY date

FIRST

Returns the first value in a attribute for each partition defined in an aggregation.

SYNTAX

first()

OUTPUT

any

EXAMPLE

GROUP first(A) AS new_A BY B

→

LAG

Computes an expression on a row that is behind the current one in the sort order.

SYNTAX

lag(input, offset)

• input
  • Type: any
  • Description: The expression to compute on a previous row
• offset
  • Type: int
  • Description: The number of rows previous to lag. Must be a positive integer constant.

OUTPUT

any

EXAMPLE

WINDOW event, LAG(event, 1) AS previousEvent BY region ORDER BY date

LAST

Returns the last value in a attribute for each partition defined in an aggregation.

SYNTAX

last()

OUTPUT

any

EXAMPLE

GROUP last(A) AS new_A BY B

→

LEAD

Computes an expression on a row that is ahead of the current one in the sort order.

SYNTAX

lead(input, offset)

• input
  • Type: any
  • Description: The expression to compute on an upcoming row
• offset
  • Type: int
  • Description: The number of rows following to skip. Must be a positive integer constant.

OUTPUT

any

EXAMPLE

WINDOW event, LEAD(event, 1) AS nextEvent BY region ORDER BY date

MAX

Computes the greatest value of an attribute or expression in a group.

SYNTAX

max()

OUTPUT

any

EXAMPLE

GROUP max(age) AS result BY name

→

MAX_SIZE

Computes the maximum value of an attribute or expression based on the bit size of the elements contained in a group. For arrays, the bit size of its length is added to the sum of the bit size of each of its elements.

SYNTAX

max_size()

OUTPUT

any

EXAMPLES

GROUP max_size(name) AS result BY age

→

GROUP max_size(cities) AS result BY state

→

GROUP max_size(special_case) AS result BY some_value

→

GROUP max_size(special_case_2) AS result BY some_value

→

MEAN

Computes the mean of all non-null values of an attribute in a group. This is a synonym of the AVG function.

SYNTAX

mean()

OUTPUT

double

EXAMPLE

GROUP mean(age) AS result BY name

→

MIN

Computes the least value of an attribute or expression in a group.

SYNTAX

min()

OUTPUT

any

EXAMPLE

GROUP min(age) AS result BY name

→

MIN_SIZE

Computes the minimum value of an attribute or expression based on the bit size of the elements contained in a group. For arrays, the bit size of its length is added to the sum of the bit size of each of its elements.

SYNTAX

min_size()

OUTPUT

any

EXAMPLES

GROUP min_size(name) AS result BY age

→

GROUP min_size(cities) AS result BY state

→

GROUP min_size(special_case) AS result BY some_value

→

GROUP min_size(special_case_2) AS result BY some_value

→

MODE

Computes the mode (most frequent value) of an attribute in a group. Ignores nulls. In the case of a tie, the least value will be returned.

SYNTAX

mode()

OUTPUT

any

EXAMPLE

GROUP mode(age) AS result BY name

→

RANK

Assigns to each row its position in the partition according to the ORDER BY expression.

SYNTAX

rank()

OUTPUT

int

EXAMPLE

WINDOW RANK() AS eventRank BY region ORDER BY date

ROW_NUMBER

Assigns a unique sequential ID number to each row in a partition, starting with 1.

SYNTAX

row_number()

OUTPUT

int

EXAMPLE

WINDOW ROW_NUMBER() AS eventId BY region ORDER BY date

STDDEV_POP

Computes the population standard deviation of all non-null values of an attribute in a group.

SYNTAX

stddev_pop()

OUTPUT

double

EXAMPLE

GROUP stddev_pop(age) AS result BY name

→

STDDEV_SAMP

Computes the corrected sample standard deviation of all non-null values of an attribute in a group.

SYNTAX

stddev_samp()

OUTPUT

double

EXAMPLE

GROUP stddev(age) AS result BY name

→

SUM

Computes the sum of all non-null values of an attribute in a group.

SYNTAX

sum()

OUTPUT

number

EXAMPLE

GROUP sum(age) AS result BY name

→

TOP

Computes n most frequently occurring distinct values per group.

SYNTAX

top(input, n)

• input
  • Type: any
  • Description: The attribute on which to compute top n values.
• n
  • Type: int
  • Description: The number of values to return per group. Must be a positive integer.

OUTPUT

{'type': 'array', 'elementType': 'any'}

EXAMPLE

GROUP top(age, 2) AS result BY name

→

VAR_POP

Computes the population variance of all non-null values of an attribute in a group.

SYNTAX

var_pop()

OUTPUT

double

EXAMPLE

GROUP var_pop(age) AS result BY name

→

VAR_SAMP

Computes the corrected sample variance of all non-null values of an attribute in a group.

SYNTAX

var_samp()

OUTPUT

double

EXAMPLE

GROUP var_samp(age) AS result BY name

→