Aggregate functions

Every Spark SQL aggregate function on Databasin Sail: whether it is supported, how it differs from Apache Spark, and examples with results.

Last updated October 20, 2026

Reading time 32 min read

This page lists every Spark SQL aggregate function: 66 work on Databasin Sail as in Apache Spark, 4 work with a difference noted below and 16 aren't supported. Each function shows its status on Sail, its usage, and examples whose results on Sail match Spark's.

Every supported function also works from PySpark through pyspark.sql.functions when PySpark has a wrapper for it. The Sail SQL reference lists every function page.

Aggregate

any · any_value · approx_count_distinct · approx_percentile · approx_top_k · approx_top_k_accumulate · approx_top_k_combine · array_agg · avg · bit_and · bit_or · bit_xor · bitmap_and_agg · bitmap_construct_agg · bitmap_or_agg · bool_and · bool_or · collect_list · collect_set · corr · count · count_if · count_min_sketch · covar_pop · covar_samp · every · first · first_value · grouping · grouping_id · histogram_numeric · hll_sketch_agg · hll_union_agg · kll_merge_agg_bigint · kll_merge_agg_double · kll_merge_agg_float · kll_sketch_agg_bigint · kll_sketch_agg_double · kll_sketch_agg_float · kurtosis · last · last_value · listagg · max · max_by · mean · measure · median · min · min_by · mode · percentile · percentile_approx · percentile_cont · percentile_disc · regr_avgx · regr_avgy · regr_count · regr_intercept · regr_r2 · regr_slope · regr_sxx · regr_sxy · regr_syy · skewness · some · std · stddev · stddev_pop · stddev_samp · string_agg · sum · theta_intersection_agg · theta_sketch_agg · theta_union_agg · try_avg · try_sum · tuple_intersection_agg_double · tuple_intersection_agg_integer · tuple_sketch_agg_double · tuple_sketch_agg_integer · tuple_union_agg_double · tuple_union_agg_integer · var_pop · var_samp · variance

any

Supported.

any(expr): Returns true if at least one value of expr is true.

Since Spark 3.0.0.

SELECT any(col) FROM VALUES (true), (false), (false) AS tab(col);
-- true

SELECT any(col) FROM VALUES (NULL), (true), (false) AS tab(col);
-- true

SELECT any(col) FROM VALUES (false), (false), (NULL) AS tab(col);
-- false

any_value

Supported.

any_value(expr[, isIgnoreNull]): Returns some value of expr for a group of rows. If isIgnoreNull is true, returns only non-null values.

Note from Spark: The function is non-deterministic.

Since Spark 3.4.0.

SELECT any_value(col) FROM VALUES (10), (5), (20) AS tab(col);
-- 10

SELECT any_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
-- NULL

SELECT any_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
-- 5

approx_count_distinct

Supported. Spark's HyperLogLog++ (values equal Spark, single node and distributed).

approx_count_distinct(expr[, relativeSD]): Returns the estimated cardinality by HyperLogLog++. relativeSD defines the maximum relative standard deviation allowed.

Since Spark 1.6.0.

SELECT approx_count_distinct(col1) FROM VALUES (1), (1), (2), (2), (3) tab(col1);
-- 3

approx_percentile

Supported, differs from Spark. YEAR-MONTH interval input is refused; DAY-TIME interval results are typed INTERVAL DAY TO SECOND (Spark: INTERVAL SECOND). Numeric input, including an array of percentages, matches Spark.

approx_percentile(col, percentage [, accuracy]): Returns the approximate percentile of the numeric or ansi interval column col which is the smallest value in the ordered col values (sorted from least to greatest) such that no more than percentage of col values is less than the value or equal to that value. The value of percentage must be between 0.0 and 1.0. The accuracy parameter (default: 10000) is a positive numeric literal which controls approximation accuracy at the cost of memory. Higher value of accuracy yields better accuracy, 1.0/accuracy is the relative error of the approximation. When percentage is an array, each value of the percentage array must be between 0.0 and 1.0. In this case, returns the approximate percentile array of column col at the given percentage array.

Since Spark 2.1.0.

SELECT approx_percentile(col, array(0.5, 0.4, 0.1), 100) FROM VALUES (0), (1), (2), (10) AS tab(col);
-- [1,1,0]

SELECT approx_percentile(col, 0.5, 100) FROM VALUES (0), (6), (7), (9), (10) AS tab(col);
-- 7

approx_top_k

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

approx_top_k(expr, k, maxItemsTracked): Returns top k items with their frequency. k An optional INTEGER literal greater than 0. If k is not specified, it defaults to 5. maxItemsTracked An optional INTEGER literal greater than or equal to k and has upper limit of 1000000. If maxItemsTracked is not specified, it defaults to 10000.

approx_top_k_accumulate

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

approx_top_k_accumulate(expr, maxItemsTracked): Accumulates items into a sketch. maxItemsTracked An optional positive INTEGER literal with upper limit of 1000000. If maxItemsTracked is not specified, it defaults to 10000.

approx_top_k_combine

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

approx_top_k_combine(state, maxItemsTracked): Combines multiple sketches into a single sketch. maxItemsTracked An optional positive INTEGER literal with upper limit of 1000000. If maxItemsTracked is specified, it will be set for the combined sketch. If maxItemsTracked is not specified, the input sketches must have the same maxItemsTracked value, otherwise an error will be thrown. The output sketch will use the same value from the input sketches.

array_agg

Supported.

array_agg(expr): Collects and returns a list of non-unique elements.

Note from Spark: The function is non-deterministic because the order of collected results depends on the order of the rows which may be non-deterministic after a shuffle.

Since Spark 3.3.0.

SELECT array_agg(col) FROM VALUES (1), (2), (1) AS tab(col);
-- [1,2,1]

avg

Supported.

avg(expr): Returns the mean calculated from values of a group.

Since Spark 1.0.0.

SELECT avg(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 2.0

SELECT avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
-- 1.5

bit_and

Supported.

bit_and(expr): Returns the bitwise AND of all non-null input values, or null if none.

Since Spark 3.0.0.

SELECT bit_and(col) FROM VALUES (3), (5) AS tab(col);
-- 1

bit_or

Supported.

bit_or(expr): Returns the bitwise OR of all non-null input values, or null if none.

Since Spark 3.0.0.

SELECT bit_or(col) FROM VALUES (3), (5) AS tab(col);
-- 7

bit_xor

Supported.

bit_xor(expr): Returns the bitwise XOR of all non-null input values, or null if none.

Since Spark 3.0.0.

SELECT bit_xor(col) FROM VALUES (3), (5) AS tab(col);
-- 6

bitmap_and_agg

Supported. New in Spark 4.1.0.

bitmap_and_agg(child): Returns a bitmap that is the bitwise AND of all of the bitmaps from the child expression. The input should be bitmaps created from bitmap_construct_agg().

SELECT substring(hex(bitmap_and_agg(col)), 0, 6) FROM VALUES (X 'F0'), (X '70'), (X '30') AS tab(col);
-- 300000

SELECT substring(hex(bitmap_and_agg(col)), 0, 6) FROM VALUES (X 'FF'), (X 'FF'), (X 'FF') AS tab(col);
-- FF0000

bitmap_construct_agg

Supported.

bitmap_construct_agg(child): Returns a bitmap with the positions of the bits set from all the values from the child expression. The child expression will most likely be bitmap_bit_position().

Since Spark 3.5.0.

SELECT substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6) FROM VALUES (1), (2), (3) AS tab(col);
-- 070000

SELECT substring(hex(bitmap_construct_agg(bitmap_bit_position(col))), 0, 6) FROM VALUES (1), (1), (1) AS tab(col);
-- 010000

bitmap_or_agg

Supported.

bitmap_or_agg(child): Returns a bitmap that is the bitwise OR of all of the bitmaps from the child expression. The input should be bitmaps created from bitmap_construct_agg().

Since Spark 3.5.0.

SELECT substring(hex(bitmap_or_agg(col)), 0, 6) FROM VALUES (X '10'), (X '20'), (X '40') AS tab(col);
-- 700000

SELECT substring(hex(bitmap_or_agg(col)), 0, 6) FROM VALUES (X '10'), (X '10'), (X '10') AS tab(col);
-- 100000

bool_and

Supported.

bool_and(expr): Returns true if all values of expr are true.

Since Spark 3.0.0.

SELECT bool_and(col) FROM VALUES (true), (true), (true) AS tab(col);
-- true

SELECT bool_and(col) FROM VALUES (NULL), (true), (true) AS tab(col);
-- true

SELECT bool_and(col) FROM VALUES (true), (false), (true) AS tab(col);
-- false

bool_or

Supported.

bool_or(expr): Returns true if at least one value of expr is true.

Since Spark 3.0.0.

SELECT bool_or(col) FROM VALUES (true), (false), (false) AS tab(col);
-- true

SELECT bool_or(col) FROM VALUES (NULL), (true), (false) AS tab(col);
-- true

SELECT bool_or(col) FROM VALUES (false), (false), (NULL) AS tab(col);
-- false

collect_list

Supported.

collect_list(expr): Collects and returns a list of non-unique elements.

Note from Spark: The function is non-deterministic because the order of collected results depends on the order of the rows which may be non-deterministic after a shuffle.

Since Spark 2.0.0.

SELECT collect_list(col) FROM VALUES (1), (2), (1) AS tab(col);
-- [1,2,1]

collect_set

Supported.

collect_set(expr): Collects and returns a set of unique elements.

Note from Spark: The function is non-deterministic because the order of collected results depends on the order of the rows which may be non-deterministic after a shuffle.

Since Spark 2.0.0.

SELECT collect_set(col) FROM VALUES (1), (2), (1) AS tab(col);
-- [1,2]

corr

Supported.

corr(expr1, expr2): Returns Pearson coefficient of correlation between a set of number pairs.

Since Spark 1.6.0.

SELECT corr(c1, c2) FROM VALUES (3, 2), (3, 3), (6, 4) as tab(c1, c2);
-- 0.8660254037844387

count

Supported.

count(*): Returns the total number of retrieved rows, including rows containing null.

count(expr[, expr...]): Returns the number of rows for which the supplied expression(s) are all non-null.

count(DISTINCT expr[, expr...]): Returns the number of rows for which the supplied expression(s) are unique and non-null.

Since Spark 1.0.0.

SELECT count(*) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
-- 4

SELECT count(col) FROM VALUES (NULL), (5), (5), (20) AS tab(col);
-- 3

SELECT count(DISTINCT col) FROM VALUES (NULL), (5), (5), (10) AS tab(col);
-- 2

count_if

Supported.

count_if(expr): Returns the number of TRUE values for the expression.

Since Spark 3.0.0.

SELECT count_if(col % 2 = 0) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
-- 2

SELECT count_if(col IS NULL) FROM VALUES (NULL), (0), (1), (2), (3) AS tab(col);
-- 1

count_min_sketch

Supported.

count_min_sketch(col, eps, confidence, seed): Returns a count-min sketch of a column with the given esp, confidence and seed. The result is an array of bytes, which can be deserialized to a CountMinSketch before usage. Count-min sketch is a probabilistic data structure used for cardinality estimation using sub-linear space.

Since Spark 2.2.0.

SELECT hex(count_min_sketch(col, 0.5d, 0.5d, 1)) FROM VALUES (1), (2), (1) AS tab(col);
-- 0000000100000000000000030000000100000004000000005D8D6AB90000000000000000000000000000000200000000000000010000000000000000

covar_pop

Supported.

covar_pop(expr1, expr2): Returns the population covariance of a set of number pairs.

Since Spark 2.0.0.

SELECT covar_pop(c1, c2) FROM VALUES (1,1), (2,2), (3,3) AS tab(c1, c2);
-- 0.6666666666666666

covar_samp

Supported.

covar_samp(expr1, expr2): Returns the sample covariance of a set of number pairs.

Since Spark 2.0.0.

SELECT covar_samp(c1, c2) FROM VALUES (1,1), (2,2), (3,3) AS tab(c1, c2);
-- 1.0

every

Supported.

every(expr): Returns true if all values of expr are true.

Since Spark 3.0.0.

SELECT every(col) FROM VALUES (true), (true), (true) AS tab(col);
-- true

SELECT every(col) FROM VALUES (NULL), (true), (true) AS tab(col);
-- true

SELECT every(col) FROM VALUES (true), (false), (true) AS tab(col);
-- false

first

Supported.

first(expr[, isIgnoreNull]): Returns the first value of expr for a group of rows. If isIgnoreNull is true, returns only non-null values.

Note from Spark: The function is non-deterministic because its results depends on the order of the rows which may be non-deterministic after a shuffle.

Since Spark 2.0.0.

SELECT first(col) FROM VALUES (10), (5), (20) AS tab(col);
-- 10

SELECT first(col) FROM VALUES (NULL), (5), (20) AS tab(col);
-- NULL

SELECT first(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
-- 5

first_value

Supported.

first_value(expr[, isIgnoreNull]): Returns the first value of expr for a group of rows. If isIgnoreNull is true, returns only non-null values.

Note from Spark: The function is non-deterministic because its results depends on the order of the rows which may be non-deterministic after a shuffle.

Since Spark 2.0.0.

SELECT first_value(col) FROM VALUES (10), (5), (20) AS tab(col);
-- 10

SELECT first_value(col) FROM VALUES (NULL), (5), (20) AS tab(col);
-- NULL

SELECT first_value(col, true) FROM VALUES (NULL), (5), (20) AS tab(col);
-- 5

grouping

Supported. Results match Spark; rows can come back in a different order (add ORDER BY when order matters).

grouping(col): indicates whether a specified column in a GROUP BY is aggregated or not, returns 1 for aggregated or 0 for not aggregated in the result set.

Since Spark 2.0.0.

SELECT name, grouping(name), sum(age) FROM VALUES (2, 'Alice'), (5, 'Bob') people(age, name) GROUP BY cube(name);
-- Alice | 0 | 2
-- Bob | 0 | 5
-- NULL | 1 | 7

grouping_id

Supported. Results match Spark; rows can come back in a different order (add ORDER BY when order matters).

grouping_id([col1[, col2 ..]]): returns the level of grouping, equals to (grouping(c1) << (n-1)) + (grouping(c2) << (n-2)) + ... + grouping(cn)

Note from Spark: Input columns should match with grouping columns exactly, or empty (means all the grouping columns).

Since Spark 2.0.0.

SELECT name, grouping_id(), sum(age), avg(height) FROM VALUES (2, 'Alice', 165), (5, 'Bob', 180) people(age, name, height) GROUP BY cube(name, height);
-- Alice | 0 | 2 | 165.0
-- Alice | 1 | 2 | 165.0
-- NULL | 3 | 7 | 172.5
-- Bob | 0 | 5 | 180.0
-- Bob | 1 | 5 | 180.0
-- NULL | 2 | 2 | 165.0
-- NULL | 2 | 5 | 180.0

histogram_numeric

Supported.

histogram_numeric(expr, nb): Computes a histogram on numeric 'expr' using nb bins. The return value is an array of (x,y) pairs representing the centers of the histogram's bins. As the value of 'nb' is increased, the histogram approximation gets finer-grained, but may yield artifacts around outliers. In practice, 20-40 histogram bins appear to work well, with more bins being required for skewed or smaller datasets. Note that this function creates a histogram with non-uniform bin widths. It offers no guarantees in terms of the mean-squared-error of the histogram, but in practice is comparable to the histograms produced by the R/S-Plus statistical computing packages. Note: the output type of the 'x' field in the return value is propagated from the input value consumed in the aggregate function.

Since Spark 3.3.0.

SELECT histogram_numeric(col, 5) FROM VALUES (0), (1), (2), (10) AS tab(col);
-- [{"x":0,"y":1.0},{"x":1,"y":1.0},{"x":2,"y":1.0},{"x":10,"y":1.0}]

hll_sketch_agg

Supported.

hll_sketch_agg(expr, lgConfigK): Returns the HllSketch's updatable binary representation. lgConfigK (optional) the log-base-2 of K, with K is the number of buckets or slots for the HllSketch.

Since Spark 3.5.0.

SELECT hll_sketch_estimate(hll_sketch_agg(col, 12)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
-- 3

hll_union_agg

Supported.

hll_union_agg(expr, allowDifferentLgConfigK): Returns the estimated number of unique values. allowDifferentLgConfigK (optional) Allow sketches with different lgConfigK values to be unioned (defaults to false).

Since Spark 3.5.0.

SELECT hll_sketch_estimate(hll_union_agg(sketch, true)) FROM (SELECT hll_sketch_agg(col) as sketch FROM VALUES (1) tab(col) UNION ALL SELECT hll_sketch_agg(col, 20) as sketch FROM VALUES (1) tab(col));
-- 1

kll_merge_agg_bigint

Not supported. New in Spark 4.1.2. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_merge_agg_bigint(expr[, k]): Merges binary KllLongsSketch representations and returns the merged sketch. The input expression should contain binary sketch representations (e.g., from kll_sketch_agg_bigint). The optional k parameter controls the size and accuracy of the merged sketch (range 8-65535). If k is not specified, the merged sketch adopts the k value from the first input sketch.

kll_merge_agg_double

Not supported. New in Spark 4.1.2. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_merge_agg_double(expr[, k]): Merges binary KllDoublesSketch representations and returns the merged sketch. The input expression should contain binary sketch representations (e.g., from kll_sketch_agg_double). The optional k parameter controls the size and accuracy of the merged sketch (range 8-65535). If k is not specified, the merged sketch adopts the k value from the first input sketch.

kll_merge_agg_float

Not supported. New in Spark 4.1.2. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_merge_agg_float(expr[, k]): Merges binary KllFloatsSketch representations and returns the merged sketch. The input expression should contain binary sketch representations (e.g., from kll_sketch_agg_float). The optional k parameter controls the size and accuracy of the merged sketch (range 8-65535). If k is not specified, the merged sketch adopts the k value from the first input sketch.

kll_sketch_agg_bigint

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_agg_bigint(expr[, k]): Returns the KllLongsSketch compact binary representation. The optional k parameter controls the size and accuracy of the sketch (default 200, range 8-65535). Larger k values provide more accurate quantile estimates but result in larger, slower sketches.

kll_sketch_agg_double

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_agg_double(expr[, k]): Returns the KllDoublesSketch compact binary representation. The optional k parameter controls the size and accuracy of the sketch (default 200, range 8-65535). Larger k values provide more accurate quantile estimates but result in larger, slower sketches.

kll_sketch_agg_float

Not supported. New in Spark 4.1.0. Sketch family refused on Sail (KLL, tuple and approx_top_k sketches).

kll_sketch_agg_float(expr[, k]): Returns the KllFloatsSketch compact binary representation. The optional k parameter controls the size and accuracy of the sketch (default 200, range 8-65535). Larger k values provide more accurate quantile estimates but result in larger, slower sketches.

kurtosis

Supported.

kurtosis(expr): Returns the kurtosis value calculated from values of a group.

Since Spark 1.6.0.

SELECT kurtosis(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
-- -0.7014368047529627

SELECT kurtosis(col) FROM VALUES (1), (10), (100), (10), (1) as tab(col);
-- 0.19432323191699075

last

Supported.

last(expr[, isIgnoreNull]): Returns the last value of expr for a group of rows. If isIgnoreNull is true, returns only non-null values

Note from Spark: The function is non-deterministic because its results depends on the order of the rows which may be non-deterministic after a shuffle.

Since Spark 2.0.0.

SELECT last(col) FROM VALUES (10), (5), (20) AS tab(col);
-- 20

SELECT last(col) FROM VALUES (10), (5), (NULL) AS tab(col);
-- NULL

SELECT last(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
-- 5

last_value

Supported.

last_value(expr[, isIgnoreNull]): Returns the last value of expr for a group of rows. If isIgnoreNull is true, returns only non-null values

Note from Spark: The function is non-deterministic because its results depends on the order of the rows which may be non-deterministic after a shuffle.

Since Spark 2.0.0.

SELECT last_value(col) FROM VALUES (10), (5), (20) AS tab(col);
-- 20

SELECT last_value(col) FROM VALUES (10), (5), (NULL) AS tab(col);
-- NULL

SELECT last_value(col, true) FROM VALUES (10), (5), (NULL) AS tab(col);
-- 5

listagg

Supported.

listagg(expr[, delimiter])[ WITHIN GROUP (ORDER BY key [ASC | DESC] [,...])]: Returns the concatenation of non-NULL input values, separated by the delimiter ordered by key. If all values are NULL, NULL is returned.

expr: a string or binary expression to be concatenated.
delimiter: an optional string or binary foldable expression used to separate the input values. If NULL, the concatenation will be performed without a delimiter. Default is NULL.
key: an optional expression for ordering the input values. Multiple keys can be specified. If none are specified, the order of the rows in the result is non-deterministic.

Note from Spark: * If the order is not specified, the function is non-deterministic because the order of the rows may be non-deterministic after a shuffle. * If DISTINCT is specified, then expr and key must be the same expression.

Since Spark 4.0.0.

SELECT listagg(col) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- abc

SELECT listagg(col) WITHIN GROUP (ORDER BY col DESC) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- cba

SELECT listagg(col) FROM VALUES ('a'), (NULL), ('b') AS tab(col);
-- ab

SELECT listagg(col) FROM VALUES ('a'), ('a') AS tab(col);
-- aa

SELECT listagg(DISTINCT col) FROM VALUES ('a'), ('a'), ('b') AS tab(col);
-- ab

SELECT listagg(col, ', ') FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- a, b, c

SELECT listagg(col) FROM VALUES (NULL), (NULL) AS tab(col);
-- NULL

max

Supported.

max(expr): Returns the maximum value of expr.

Since Spark 1.0.0.

SELECT max(col) FROM VALUES (10), (50), (20) AS tab(col);
-- 50

max_by

Supported.

max_by(x, y): Returns the value of x associated with the maximum value of y. max_by(x, y, k) - Returns an array of the k values of x associated with the maximum values of y, sorted in descending order by y. Returns NULL if there are no non-NULL ordering values.

Note from Spark: The function is non-deterministic so the output order can be different for those associated the same values of y. The maximum value of k is 100000.

Since Spark 3.0.0.

SELECT max_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
-- b

SELECT max_by(x, y, 2) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
-- ["b","c"]

mean

Supported.

mean(expr): Returns the mean calculated from values of a group.

Since Spark 1.0.0.

SELECT mean(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 2.0

SELECT mean(col) FROM VALUES (1), (2), (NULL) AS tab(col);
-- 1.5

measure

Not supported. New in Spark 4.2.0. Metric-view measures are refused.

measure(expr): this function is used and can only be used to calculate a measure defined in a metric view.

median

Supported.

median(col): Returns the median of numeric or ANSI interval column col.

Since Spark 3.4.0.

SELECT median(col) FROM VALUES (0), (10) AS tab(col);
-- 5.0

SELECT median(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
-- 0-5

min

Supported.

min(expr): Returns the minimum value of expr.

Since Spark 1.0.0.

SELECT min(col) FROM VALUES (10), (-1), (20) AS tab(col);
-- -1

min_by

Supported.

min_by(x, y): Returns the value of x associated with the minimum value of y. min_by(x, y, k) - Returns an array of the k values of x associated with the minimum values of y, sorted in ascending order by y. Returns NULL if there are no non-NULL ordering values.

Note from Spark: The function is non-deterministic so the output order can be different for those associated the same values of y. The maximum value of k is 100000.

Since Spark 3.0.0.

SELECT min_by(x, y) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
-- a

SELECT min_by(x, y, 2) FROM VALUES ('a', 10), ('b', 50), ('c', 20) AS tab(x, y);
-- ["a","c"]

mode

Supported, differs from Spark. YEAR-MONTH interval input is not supported (error); other inputs match Spark, including ties.

mode(col[, deterministic]): Returns the most frequent value for the values within col. NULL values are ignored. If all the values are NULL, or there are 0 rows, returns NULL. When multiple values have the same greatest frequency then either any of values is returned if deterministic is false or is not defined, or the lowest value is returned if deterministic is true. mode() WITHIN GROUP (ORDER BY col) - Returns the most frequent value for the values within col (specified in ORDER BY clause). NULL values are ignored. If all the values are NULL, or there are 0 rows, returns NULL. When multiple values have the same greatest frequency only one value will be returned. The value will be chosen based on sort direction. Return the smallest value if sort direction is asc or the largest value if sort direction is desc from multiple values with the same frequency.

Since Spark 3.4.0.

SELECT mode(col) FROM VALUES (0), (10), (10) AS tab(col);
-- 10

SELECT mode(col) FROM VALUES (0), (10), (10), (null), (null), (null) AS tab(col);
-- 10

SELECT mode(col, false) FROM VALUES (-10), (0), (10) AS tab(col);
-- 0

SELECT mode(col, true) FROM VALUES (-10), (0), (10) AS tab(col);
-- -10

SELECT mode() WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10), (10) AS tab(col);
-- 10

SELECT mode() WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10), (10), (20), (20) AS tab(col);
-- 10

SELECT mode() WITHIN GROUP (ORDER BY col DESC) FROM VALUES (0), (10), (10), (20), (20) AS tab(col);
-- 20

percentile

Supported.

percentile(col, percentage [, frequency]): Returns the exact percentile value of numeric or ANSI interval column col at the given percentage. The value of percentage must be between 0.0 and 1.0. The value of frequency should be positive integral

percentile(col, array(percentage1 [, percentage2]...) [, frequency]): Returns the exact percentile value array of numeric column col at the given percentage(s). Each value of the percentage array must be between 0.0 and 1.0. The value of frequency should be positive integral

Since Spark 2.1.0.

SELECT percentile(col, 0.3) FROM VALUES (0), (10) AS tab(col);
-- 3.0

SELECT percentile(col, array(0.25, 0.75)) FROM VALUES (0), (10) AS tab(col);
-- [2.5,7.5]

SELECT percentile(col, 0.5) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
-- 0-5

SELECT percentile(col, array(0.2, 0.5)) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '10' SECOND) AS tab(col);
-- [0 00:00:02.000000000,0 00:00:05.000000000]

percentile_approx

percentile_approx(col, percentage [, accuracy]): Returns the approximate percentile of the numeric or ansi interval column col which is the smallest value in the ordered col values (sorted from least to greatest) such that no more than percentage of col values is less than the value or equal to that value. The value of percentage must be between 0.0 and 1.0. The accuracy parameter (default: 10000) is a positive numeric literal which controls approximation accuracy at the cost of memory. Higher value of accuracy yields better accuracy, 1.0/accuracy is the relative error of the approximation. When percentage is an array, each value of the percentage array must be between 0.0 and 1.0. In this case, returns the approximate percentile array of column col at the given percentage array.

Since Spark 2.1.0.

SELECT percentile_approx(col, array(0.5, 0.4, 0.1), 100) FROM VALUES (0), (1), (2), (10) AS tab(col);
-- [1,1,0]

SELECT percentile_approx(col, 0.5, 100) FROM VALUES (0), (6), (7), (9), (10) AS tab(col);
-- 7

percentile_cont

Supported, differs from Spark. YEAR-MONTH interval input is not supported (error); numeric input matches Spark.

percentile_cont(percentage) WITHIN GROUP (ORDER BY col): Return a percentile value based on a continuous distribution of numeric or ANSI interval column col at the given percentage (specified in ORDER BY clause).

Since Spark 4.0.0.

SELECT percentile_cont(0.25) WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10) AS tab(col);
-- 2.5

percentile_disc

Supported.

percentile_disc(percentage) WITHIN GROUP (ORDER BY col): Return a percentile value based on a discrete distribution of numeric or ANSI interval column col at the given percentage (specified in ORDER BY clause).

Since Spark 4.0.0.

SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY col) FROM VALUES (0), (10) AS tab(col);
-- 0.0

SELECT percentile_disc(0.25) WITHIN GROUP (ORDER BY col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH) AS tab(col);
-- 0-0

regr_avgx

Supported.

regr_avgx(y, x): Returns the average of the independent variable for non-null pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.3.0.

SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
-- 2.75

SELECT regr_avgx(y, x) FROM VALUES (1, null) AS tab(y, x);
-- NULL

SELECT regr_avgx(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- NULL

SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
-- 3.0

SELECT regr_avgx(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
-- 3.0

regr_avgy

Supported.

regr_avgy(y, x): Returns the average of the dependent variable for non-null pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.3.0.

SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
-- 1.75

SELECT regr_avgy(y, x) FROM VALUES (1, null) AS tab(y, x);
-- NULL

SELECT regr_avgy(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- NULL

SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
-- 1.6666666666666667

SELECT regr_avgy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
-- 1.5

regr_count

Supported.

regr_count(y, x): Returns the number of non-null number pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.3.0.

SELECT regr_count(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
-- 4

SELECT regr_count(y, x) FROM VALUES (1, null) AS tab(y, x);
-- 0

SELECT regr_count(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- 0

SELECT regr_count(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
-- 3

SELECT regr_count(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
-- 2

regr_intercept

Supported.

regr_intercept(y, x): Returns the intercept of the univariate linear regression line for non-null pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.4.0.

SELECT regr_intercept(y, x) FROM VALUES (1, 1), (2, 2), (3, 3), (4, 4) AS tab(y, x);
-- 0.0

SELECT regr_intercept(y, x) FROM VALUES (1, null) AS tab(y, x);
-- NULL

SELECT regr_intercept(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- NULL

SELECT regr_intercept(y, x) FROM VALUES (1, 1), (2, null), (3, 3), (4, 4) AS tab(y, x);
-- 0.0

SELECT regr_intercept(y, x) FROM VALUES (1, 1), (2, null), (null, 3), (4, 4) AS tab(y, x);
-- 0.0

regr_r2

Supported.

regr_r2(y, x): Returns the coefficient of determination for non-null pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.3.0.

SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
-- 0.2727272727272727

SELECT regr_r2(y, x) FROM VALUES (1, null) AS tab(y, x);
-- NULL

SELECT regr_r2(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- NULL

SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
-- 0.7500000000000001

SELECT regr_r2(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
-- 1.0

regr_slope

Supported.

regr_slope(y, x): Returns the slope of the linear regression line for non-null pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.4.0.

SELECT regr_slope(y, x) FROM VALUES (1, 1), (2, 2), (3, 3), (4, 4) AS tab(y, x);
-- 1.0

SELECT regr_slope(y, x) FROM VALUES (1, null) AS tab(y, x);
-- NULL

SELECT regr_slope(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- NULL

SELECT regr_slope(y, x) FROM VALUES (1, 1), (2, null), (3, 3), (4, 4) AS tab(y, x);
-- 1.0

SELECT regr_slope(y, x) FROM VALUES (1, 1), (2, null), (null, 3), (4, 4) AS tab(y, x);
-- 1.0

regr_sxx

Supported.

regr_sxx(y, x): Returns REGR_COUNT(y, x) * VAR_POP(x) for non-null pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.4.0.

SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
-- 2.75

SELECT regr_sxx(y, x) FROM VALUES (1, null) AS tab(y, x);
-- NULL

SELECT regr_sxx(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- NULL

SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
-- 2.0

SELECT regr_sxx(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
-- 2.0

regr_sxy

Supported.

regr_sxy(y, x): Returns REGR_COUNT(y, x) * COVAR_POP(y, x) for non-null pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.4.0.

SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
-- 0.75

SELECT regr_sxy(y, x) FROM VALUES (1, null) AS tab(y, x);
-- NULL

SELECT regr_sxy(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- NULL

SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
-- 1.0

SELECT regr_sxy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
-- 1.0

regr_syy

Supported.

regr_syy(y, x): Returns REGR_COUNT(y, x) * VAR_POP(y) for non-null pairs in a group, where y is the dependent variable and x is the independent variable.

Since Spark 3.4.0.

SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, 2), (2, 3), (2, 4) AS tab(y, x);
-- 0.75

SELECT regr_syy(y, x) FROM VALUES (1, null) AS tab(y, x);
-- NULL

SELECT regr_syy(y, x) FROM VALUES (null, 1) AS tab(y, x);
-- NULL

SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, null), (2, 3), (2, 4) AS tab(y, x);
-- 0.6666666666666666

SELECT regr_syy(y, x) FROM VALUES (1, 2), (2, null), (null, 3), (2, 4) AS tab(y, x);
-- 0.5

skewness

Supported.

skewness(expr): Returns the skewness value calculated from values of a group.

Since Spark 1.6.0.

SELECT skewness(col) FROM VALUES (-10), (-20), (100), (1000) AS tab(col);
-- 1.1135657469022011

SELECT skewness(col) FROM VALUES (-1000), (-100), (10), (20) AS tab(col);
-- -1.1135657469022011

some

Supported.

some(expr): Returns true if at least one value of expr is true.

Since Spark 3.0.0.

SELECT some(col) FROM VALUES (true), (false), (false) AS tab(col);
-- true

SELECT some(col) FROM VALUES (NULL), (true), (false) AS tab(col);
-- true

SELECT some(col) FROM VALUES (false), (false), (NULL) AS tab(col);
-- false

std

Supported.

std(expr): Returns the sample standard deviation calculated from values of a group.

Since Spark 1.6.0.

SELECT std(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 1.0

stddev

Supported.

stddev(expr): Returns the sample standard deviation calculated from values of a group.

Since Spark 1.6.0.

SELECT stddev(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 1.0

stddev_pop

Supported.

stddev_pop(expr): Returns the population standard deviation calculated from values of a group.

Since Spark 1.6.0.

SELECT stddev_pop(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 0.816496580927726

stddev_samp

Supported.

stddev_samp(expr): Returns the sample standard deviation calculated from values of a group.

Since Spark 1.6.0.

SELECT stddev_samp(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 1.0

string_agg

Supported.

string_agg(expr[, delimiter])[ WITHIN GROUP (ORDER BY key [ASC | DESC] [,...])]: Returns the concatenation of non-NULL input values, separated by the delimiter ordered by key. If all values are NULL, NULL is returned.

expr: a string or binary expression to be concatenated.
delimiter: an optional string or binary foldable expression used to separate the input values. If NULL, the concatenation will be performed without a delimiter. Default is NULL.
key: an optional expression for ordering the input values. Multiple keys can be specified. If none are specified, the order of the rows in the result is non-deterministic.

Since Spark 4.0.0.

SELECT string_agg(col) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- abc

SELECT string_agg(col) WITHIN GROUP (ORDER BY col DESC) FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- cba

SELECT string_agg(col) FROM VALUES ('a'), (NULL), ('b') AS tab(col);
-- ab

SELECT string_agg(col) FROM VALUES ('a'), ('a') AS tab(col);
-- aa

SELECT string_agg(DISTINCT col) FROM VALUES ('a'), ('a'), ('b') AS tab(col);
-- ab

SELECT string_agg(col, ', ') FROM VALUES ('a'), ('b'), ('c') AS tab(col);
-- a, b, c

SELECT string_agg(col) FROM VALUES (NULL), (NULL) AS tab(col);
-- NULL

sum

Supported.

sum(expr): Returns the sum calculated from values of a group.

Since Spark 1.0.0.

SELECT sum(col) FROM VALUES (5), (10), (15) AS tab(col);
-- 30

SELECT sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
-- 25

SELECT sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
-- NULL

theta_intersection_agg

Supported. New in Spark 4.1.0.

theta_intersection_agg(expr): Returns the ThetaSketch's Compact binary representation by intersecting all the Theta sketches in the input column.

SELECT theta_sketch_estimate(theta_intersection_agg(sketch)) FROM (SELECT theta_sketch_agg(col) as sketch FROM VALUES (1) tab(col) UNION ALL SELECT theta_sketch_agg(col, 20) as sketch FROM VALUES (1) tab(col));
-- 1

theta_sketch_agg

Supported. New in Spark 4.1.0.

theta_sketch_agg(expr, lgNomEntries): Returns the ThetaSketch compact binary representation. lgNomEntries (optional) is the log-base-2 of nominal entries, with nominal entries deciding the number buckets or slots for the ThetaSketch.

SELECT theta_sketch_estimate(theta_sketch_agg(col, 12)) FROM VALUES (1), (1), (2), (2), (3) tab(col);
-- 3

theta_union_agg

Supported. New in Spark 4.1.0.

theta_union_agg(expr, lgNomEntries): Returns the ThetaSketch's Compact binary representation. lgNomEntries (optional) the log-base-2 of Nominal Entries, with Nominal Entries deciding the number buckets or slots for the ThetaSketch.

SELECT theta_sketch_estimate(theta_union_agg(sketch)) FROM (SELECT theta_sketch_agg(col) as sketch FROM VALUES (1) tab(col) UNION ALL SELECT theta_sketch_agg(col, 20) as sketch FROM VALUES (1) tab(col));
-- 1

try_avg

Supported.

try_avg(expr): Returns the mean calculated from values of a group and the result is null on overflow.

Since Spark 3.3.0.

SELECT try_avg(col) FROM VALUES (1), (2), (3) AS tab(col);
-- 2.0

SELECT try_avg(col) FROM VALUES (1), (2), (NULL) AS tab(col);
-- 1.5

SELECT try_avg(col) FROM VALUES (interval '2147483647 months'), (interval '1 months') AS tab(col);
-- NULL

try_sum

Supported.

try_sum(expr): Returns the sum calculated from values of a group and the result is null on overflow.

Since Spark 3.3.0.

SELECT try_sum(col) FROM VALUES (5), (10), (15) AS tab(col);
-- 30

SELECT try_sum(col) FROM VALUES (NULL), (10), (15) AS tab(col);
-- 25

SELECT try_sum(col) FROM VALUES (NULL), (NULL) AS tab(col);
-- NULL

SELECT try_sum(col) FROM VALUES (9223372036854775807L), (1L) AS tab(col);
-- NULL