- Categories:
Aggregate Functions (General) , Window Functions (Window Frame)
MODE¶
Returns the most frequent value for the values within expr1
. NULL values are ignored. If all the values are
NULL, or there are 0 rows, then the function returns NULL.
Syntax¶
Aggregate function
MODE( <expr1> )
Window function
MODE( <expr1> ) OVER ( [ PARTITION BY <expr2> ] )
Arguments¶
expr1
This expression produces the values that are searched to find the most frequent value. The expression can be of any of the following data types:
BINARY
BOOLEAN
DATE
FLOAT
INTEGER
NUMBER
TIMESTAMP (TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ)
VARCHAR
VARIANT
This function does not support the following data types:
ARRAY
GEOGRAPHY
OBJECT
expr2
The optional expression on which to partition the data into groups. The output contains the most frequent value for each group/partition.
Returns¶
The data type of the returned value is identical to the data type of the input expression.
Usage Notes¶
If there is a tie for most frequent value (two or more values occur as frequently as each other, and more frequently than any other value), MODE returns one of those values.
DISTINCT is not supported for this function.
Even if NULL is the most frequent value, the function does not return NULL (unless all values are NULL).
When used as a window functions:
This function does not support:
ORDER BY sub-clause in the OVER() clause. The order of the values does not matter when choosing the most frequent value.
Window frames.
Examples¶
The following code demonstrates the use of MODE
:
Create a table and data:
create or replace table aggr(k int, v decimal(10,2));Get the
MODE
value for column v. The function returns NULL because there are no rows.select mode(v) from aggr; +---------+ | MODE(V) | |---------| | NULL | +---------+Insert some rows:
INSERT INTO aggr (k, v) VALUES (1, 10), (1, 10), (1, 10), (1, 10), (1, 20), (1, 21);
MODE
returns the most frequent value 10:select mode(v) from aggr; +---------+ | MODE(V) | |---------| | 10.00 | +---------+Insert some more rows:
INSERT INTO aggr (k, v) VALUES (2, 20), (2, 20), (2, 25), (2, 30);Now there are two most frequent values.
MODE
selects the value 10:select mode(v) from aggr; +---------+ | MODE(V) | |---------| | 10.00 | +---------+Insert a row with NULL value:
INSERT INTO aggr (k, v) VALUES (3, null);Get the
MODE
value for each group. Note that because the values in group k = 3 only contains NULL values, the returned value for that group is NULL.select k, mode(v) from aggr group by k order by k; +---+---------+ | K | MODE(V) | |---+---------| | 1 | 10.00 | | 2 | 20.00 | | 3 | NULL | +---+---------+
MODE
can also be used as a basic window function with an OVER() clause:select k, v, mode(v) over (partition by k) from aggr order by k, v; +---+-------+-------------------------------+ | K | V | MODE(V) OVER (PARTITION BY K) | |---+-------+-------------------------------| | 1 | 10.00 | 10.00 | | 1 | 10.00 | 10.00 | | 1 | 10.00 | 10.00 | | 1 | 10.00 | 10.00 | | 1 | 20.00 | 10.00 | | 1 | 21.00 | 10.00 | | 2 | 20.00 | 20.00 | | 2 | 20.00 | 20.00 | | 2 | 25.00 | 20.00 | | 2 | 30.00 | 20.00 | | 3 | NULL | NULL | +---+-------+-------------------------------+