mode aggregate function

Returns the most frequent, not NULL, value of expr in a group.

Since: Databricks Runtime 11.2

Syntax

mode(expr) [FILTER ( WHERE cond ) ]

Arguments

  • expr: An expression of any type that can be compared.

  • cond: An optional boolean expression filtering the rows used for aggregation.

Returns

The result type matches the type of the argument.

If the group contains only nulls, the function returns NULL. The result is non-deterministic if there is a tie for the most frequent value.

Examples

> SELECT mode(col) FROM VALUES (NULL), (1), (NULL), (2), (NULL), (3), (3) AS tab(col);
 3

> SELECT mode(col) FROM VALUES (array(1, 2)), (array(1, 2)), (array(2, 3)) AS tab(col);
 [1, 2]

-- The function returns either 1 or 2, but not 3
> SELECT mode(col) FROM VALUES (1), (1), (2), (2), (3) AS tab(col);
 1

> SELECT mode(col) FROM VALUES (NULL), (NULL) AS tab(col);
 NULL