@spec any_value(term()) :: Latu.Plan.expression()

Spark's any_value. The trailing argument is optional.

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

Examples (Spark SQL)

> 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

Note: The function is non-deterministic.

@spec any_value(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's any_value. With the optional trailing argument.

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

Examples (Spark SQL)

> 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

Note: The function is non-deterministic.

@spec approx_count_distinct(term()) :: Latu.Plan.expression()

Spark's approx_count_distinct. The trailing argument is optional.

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

Examples (Spark SQL)

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

@spec approx_count_distinct(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's approx_count_distinct. With the optional trailing argument.

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

Examples (Spark SQL)

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

@spec approx_percentile(term(), term()) :: Latu.Plan.expression()

Spark's approx_percentile. Sends Spark's defaults for the rest: 10000.

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.

Examples (Spark SQL)

> 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
> SELECT approx_percentile(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
0-1
> SELECT approx_percentile(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
[0 00:00:01.000000000,0 00:00:02.000000000]

@spec approx_percentile(term(), term(), term()) :: Latu.Plan.expression()

Spark's approx_percentile. Every argument is sent.

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.

Examples (Spark SQL)

> 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
> SELECT approx_percentile(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
0-1
> SELECT approx_percentile(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
[0 00:00:01.000000000,0 00:00:02.000000000]

@spec array_agg(term()) :: Latu.Plan.expression()

Spark's array_agg.

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

Examples (Spark SQL)

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

Note: 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.

@spec avg(term()) :: Latu.Plan.expression()

Spark's avg.

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

Examples (Spark SQL)

> 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

@spec bit_and(term()) :: Latu.Plan.expression()

Spark's bit_and.

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

Examples (Spark SQL)

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

@spec bit_or(term()) :: Latu.Plan.expression()

Spark's bit_or.

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

Examples (Spark SQL)

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

@spec bit_xor(term()) :: Latu.Plan.expression()

Spark's bit_xor.

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

Examples (Spark SQL)

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

@spec bitmap_and_agg(term()) :: Latu.Plan.expression()

Spark's bitmap_and_agg.

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().

Examples (Spark SQL)

> 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

@spec bitmap_construct_agg(term()) :: Latu.Plan.expression()

Spark's bitmap_construct_agg.

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().

Examples (Spark SQL)

> 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

@spec bitmap_or_agg(term()) :: Latu.Plan.expression()

Spark's bitmap_or_agg.

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().

Examples (Spark SQL)

> 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

@spec bool_and(term()) :: Latu.Plan.expression()

Spark's bool_and.

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

Examples (Spark SQL)

> 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

@spec bool_or(term()) :: Latu.Plan.expression()

Spark's bool_or.

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

Examples (Spark SQL)

> 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

@spec collect_list(term()) :: Latu.Plan.expression()

Spark's collect_list.

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

Examples (Spark SQL)

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

Note: 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.

@spec collect_set(term()) :: Latu.Plan.expression()

Spark's collect_set.

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

Examples (Spark SQL)

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

Note: 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.

@spec corr(term(), term()) :: Latu.Plan.expression()

Spark's corr.

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

Examples (Spark SQL)

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

@spec count(term()) :: Latu.Plan.expression()

Spark's count.

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.

Examples (Spark SQL)

> 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

@spec count_distinct(term()) :: Latu.Plan.expression()

Spark's count. Over distinct values: count with is_distinct set.

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.

Examples (Spark SQL)

> 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

@spec count_distinct(term(), [term()]) :: Latu.Plan.expression()

Spark's count. Over distinct values, across several arguments.

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.

Examples (Spark SQL)

> 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

@spec count_if(term()) :: Latu.Plan.expression()

Spark's count_if.

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

Examples (Spark SQL)

> 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

@spec count_min_sketch(term(), term(), term()) :: Latu.Plan.expression()

Spark's count_min_sketch. Draws a random seed, so the plan is not reproducible — pass one to fix it.

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.

Examples (Spark SQL)

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

@spec count_min_sketch(term(), term(), term(), term()) :: Latu.Plan.expression()

Spark's count_min_sketch. With an explicit seed, so the plan is reproducible.

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.

Examples (Spark SQL)

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

@spec covar_pop(term(), term()) :: Latu.Plan.expression()

Spark's covar_pop.

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

Examples (Spark SQL)

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

@spec covar_samp(term(), term()) :: Latu.Plan.expression()

Spark's covar_samp.

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

Examples (Spark SQL)

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

@spec every(term()) :: Latu.Plan.expression()

Spark's every.

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

Examples (Spark SQL)

> 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

@spec first(term()) :: Latu.Plan.expression()

Spark's first. Sends Spark's defaults for the rest: false.

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

Examples (Spark SQL)

> 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

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

@spec first(term(), term()) :: Latu.Plan.expression()

Spark's first. Every argument is sent.

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

Examples (Spark SQL)

> 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

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

@spec first_value(term()) :: Latu.Plan.expression()

Spark's first_value. The trailing argument is optional.

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

Examples (Spark SQL)

> 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

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

@spec first_value(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's first_value. With the optional trailing argument.

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

Examples (Spark SQL)

> 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

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

@spec grouping(term()) :: Latu.Plan.expression()

Spark's grouping.

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.",

Examples (Spark SQL)

> 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

@spec grouping_id([term()]) :: Latu.Plan.expression()

Spark's grouping_id. Variadic in Spark; takes a list here.

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

Examples (Spark SQL)

> 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

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

@spec histogram_numeric(term(), term()) :: Latu.Plan.expression()

Spark's histogram_numeric.

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.

Examples (Spark SQL)

> 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}]

@spec hll_sketch_agg(term()) :: Latu.Plan.expression()

Spark's hll_sketch_agg. The trailing argument is optional.

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.

Examples (Spark SQL)

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

@spec hll_sketch_agg(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's hll_sketch_agg. With the optional trailing argument.

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.

Examples (Spark SQL)

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

@spec hll_union_agg(term()) :: Latu.Plan.expression()

Spark's hll_union_agg. The trailing argument is optional.

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

Examples (Spark SQL)

> 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

@spec hll_union_agg(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's hll_union_agg. With the optional trailing argument.

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

Examples (Spark SQL)

> 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

@spec kll_merge_agg_bigint(term()) :: Latu.Plan.expression()

Spark's kll_merge_agg_bigint. The trailing argument is optional.

@spec kll_merge_agg_bigint(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's kll_merge_agg_bigint. With the optional trailing argument.

@spec kll_merge_agg_double(term()) :: Latu.Plan.expression()

Spark's kll_merge_agg_double. The trailing argument is optional.

@spec kll_merge_agg_double(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's kll_merge_agg_double. With the optional trailing argument.

@spec kll_merge_agg_float(term()) :: Latu.Plan.expression()

Spark's kll_merge_agg_float. The trailing argument is optional.

@spec kll_merge_agg_float(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's kll_merge_agg_float. With the optional trailing argument.

@spec kll_sketch_agg_bigint(term()) :: Latu.Plan.expression()

Spark's kll_sketch_agg_bigint. The trailing argument is optional.

@spec kll_sketch_agg_bigint(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's kll_sketch_agg_bigint. With the optional trailing argument.

@spec kll_sketch_agg_double(term()) :: Latu.Plan.expression()

Spark's kll_sketch_agg_double. The trailing argument is optional.

@spec kll_sketch_agg_double(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's kll_sketch_agg_double. With the optional trailing argument.

@spec kll_sketch_agg_float(term()) :: Latu.Plan.expression()

Spark's kll_sketch_agg_float. The trailing argument is optional.

@spec kll_sketch_agg_float(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's kll_sketch_agg_float. With the optional trailing argument.

@spec kurtosis(term()) :: Latu.Plan.expression()

Spark's kurtosis.

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

Examples (Spark SQL)

> 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

@spec last(term()) :: Latu.Plan.expression()

Spark's last. Sends Spark's defaults for the rest: false.

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

Examples (Spark SQL)

> 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

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

@spec last(term(), term()) :: Latu.Plan.expression()

Spark's last. Every argument is sent.

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

Examples (Spark SQL)

> 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

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

@spec last_value(term()) :: Latu.Plan.expression()

Spark's last_value. The trailing argument is optional.

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

Examples (Spark SQL)

> 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

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

@spec last_value(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's last_value. With the optional trailing argument.

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

Examples (Spark SQL)

> 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

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

@spec listagg(term()) :: Latu.Plan.expression()

Spark's listagg. The trailing argument is optional.

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.

Examples (Spark SQL)

> 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

Note: * 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.

@spec listagg(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's listagg. With the optional trailing argument.

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.

Examples (Spark SQL)

> 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

Note: * 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.

@spec listagg_distinct(term()) :: Latu.Plan.expression()

Spark's listagg. Over distinct values, with an optional delimiter.

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.

Examples (Spark SQL)

> 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

Note: * 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.

@spec listagg_distinct(term(), term()) :: Latu.Plan.expression()

Spark's listagg. Over distinct values, with the delimiter given.

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.

Examples (Spark SQL)

> 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

Note: * 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.

@spec max(term()) :: Latu.Plan.expression()

Spark's max.

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

Examples (Spark SQL)

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

@spec max_by(term(), term()) :: Latu.Plan.expression()

Spark's max_by. The trailing argument is optional.

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.

Examples (Spark SQL)

> 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"]

Note: 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.

@spec max_by(term(), term(), term()) :: Latu.Plan.expression()

Spark's max_by. With the optional trailing argument.

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.

Examples (Spark SQL)

> 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"]

Note: 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.

@spec mean(term()) :: Latu.Plan.expression()

The average. Spark's own name for it on the wire is avg, which is what this sends.

@spec median(term()) :: Latu.Plan.expression()

Spark's median.

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

Examples (Spark SQL)

> 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

@spec min(term()) :: Latu.Plan.expression()

Spark's min.

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

Examples (Spark SQL)

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

@spec min_by(term(), term()) :: Latu.Plan.expression()

Spark's min_by. The trailing argument is optional.

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.

Examples (Spark SQL)

> 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"]

Note: 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.

@spec min_by(term(), term(), term()) :: Latu.Plan.expression()

Spark's min_by. With the optional trailing argument.

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.

Examples (Spark SQL)

> 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"]

Note: 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.

@spec mode(term()) :: Latu.Plan.expression()

Spark's mode. Sends Spark's defaults for the rest: false.

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.

Examples (Spark SQL)

> SELECT mode(col) FROM VALUES (0), (10), (10) AS tab(col);
10
> SELECT mode(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH), (INTERVAL '10' MONTH) AS tab(col);
0-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

@spec mode(term(), term()) :: Latu.Plan.expression()

Spark's mode. Every argument is sent.

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.

Examples (Spark SQL)

> SELECT mode(col) FROM VALUES (0), (10), (10) AS tab(col);
10
> SELECT mode(col) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '10' MONTH), (INTERVAL '10' MONTH) AS tab(col);
0-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

@spec percentile(term(), term()) :: Latu.Plan.expression()

Spark's percentile. Sends Spark's defaults for the rest: 1.

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

Examples (Spark SQL)

> 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]

@spec percentile(term(), term(), term()) :: Latu.Plan.expression()

Spark's percentile. Every argument is sent.

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

Examples (Spark SQL)

> 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]

@spec percentile_approx(term(), term()) :: Latu.Plan.expression()

Spark's percentile_approx. Sends Spark's defaults for the rest: 10000.

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.

Examples (Spark SQL)

> 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
> SELECT percentile_approx(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
0-1
> SELECT percentile_approx(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
[0 00:00:01.000000000,0 00:00:02.000000000]

@spec percentile_approx(term(), term(), term()) :: Latu.Plan.expression()

Spark's percentile_approx. Every argument is sent.

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.

Examples (Spark SQL)

> 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
> SELECT percentile_approx(col, 0.5, 100) FROM VALUES (INTERVAL '0' MONTH), (INTERVAL '1' MONTH), (INTERVAL '2' MONTH), (INTERVAL '10' MONTH) AS tab(col);
0-1
> SELECT percentile_approx(col, array(0.5, 0.7), 100) FROM VALUES (INTERVAL '0' SECOND), (INTERVAL '1' SECOND), (INTERVAL '2' SECOND), (INTERVAL '10' SECOND) AS tab(col);
[0 00:00:01.000000000,0 00:00:02.000000000]

@spec product(term()) :: Latu.Plan.expression()

Spark's product.

@spec regr_avgx(term(), term()) :: Latu.Plan.expression()

Spark's regr_avgx.

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.

Examples (Spark SQL)

> 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

@spec regr_avgy(term(), term()) :: Latu.Plan.expression()

Spark's regr_avgy.

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.

Examples (Spark SQL)

> 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

@spec regr_count(term(), term()) :: Latu.Plan.expression()

Spark's regr_count.

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.

Examples (Spark SQL)

> 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

@spec regr_intercept(term(), term()) :: Latu.Plan.expression()

Spark's regr_intercept.

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.

Examples (Spark SQL)

> 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

@spec regr_r2(term(), term()) :: Latu.Plan.expression()

Spark's regr_r2.

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.

Examples (Spark SQL)

> 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

@spec regr_slope(term(), term()) :: Latu.Plan.expression()

Spark's regr_slope.

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.

Examples (Spark SQL)

> 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

@spec regr_sxx(term(), term()) :: Latu.Plan.expression()

Spark's regr_sxx.

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.

Examples (Spark SQL)

> 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

@spec regr_sxy(term(), term()) :: Latu.Plan.expression()

Spark's regr_sxy.

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.

Examples (Spark SQL)

> 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

@spec regr_syy(term(), term()) :: Latu.Plan.expression()

Spark's regr_syy.

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.

Examples (Spark SQL)

> 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

@spec skewness(term()) :: Latu.Plan.expression()

Spark's skewness.

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

Examples (Spark SQL)

> 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

@spec some(term()) :: Latu.Plan.expression()

Spark's some.

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

Examples (Spark SQL)

> 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

@spec std(term()) :: Latu.Plan.expression()

Spark's std.

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

Examples (Spark SQL)

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

@spec stddev(term()) :: Latu.Plan.expression()

Spark's stddev.

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

Examples (Spark SQL)

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

@spec stddev_pop(term()) :: Latu.Plan.expression()

Spark's stddev_pop.

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

Examples (Spark SQL)

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

@spec stddev_samp(term()) :: Latu.Plan.expression()

Spark's stddev_samp.

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

Examples (Spark SQL)

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

@spec string_agg(term()) :: Latu.Plan.expression()

Spark's string_agg. The trailing argument is optional.

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.

Examples (Spark SQL)

> 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

Note: * 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.

@spec string_agg(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's string_agg. With the optional trailing argument.

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.

Examples (Spark SQL)

> 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

Note: * 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.

@spec string_agg_distinct(term()) :: Latu.Plan.expression()

Spark's string_agg. Over distinct values, with an optional delimiter.

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.

Examples (Spark SQL)

> 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

Note: * 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.

@spec string_agg_distinct(term(), term()) :: Latu.Plan.expression()

Spark's string_agg. Over distinct values, with the delimiter given.

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.

Examples (Spark SQL)

> 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

Note: * 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.

@spec sum(term()) :: Latu.Plan.expression()

Spark's sum.

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

Examples (Spark SQL)

> 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

@spec sum_distinct(term()) :: Latu.Plan.expression()

Spark's sum. Over distinct values: sum with is_distinct set.

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

Examples (Spark SQL)

> 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

@spec theta_intersection_agg(term()) :: Latu.Plan.expression()

Spark's theta_intersection_agg.

@spec theta_sketch_agg(term()) :: Latu.Plan.expression()

Spark's theta_sketch_agg. The trailing argument is optional.

@spec theta_sketch_agg(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's theta_sketch_agg. With the optional trailing argument.

@spec theta_union_agg(term()) :: Latu.Plan.expression()

Spark's theta_union_agg. The trailing argument is optional.

@spec theta_union_agg(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's theta_union_agg. With the optional trailing argument.

@spec try_avg(term()) :: Latu.Plan.expression()

Spark's try_avg.

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

Examples (Spark SQL)

> 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

@spec try_sum(term()) :: Latu.Plan.expression()

Spark's try_sum.

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

Examples (Spark SQL)

> 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

@spec tuple_intersection_agg_double(term()) :: Latu.Plan.expression()

Spark's tuple_intersection_agg_double. The trailing argument is optional.

@spec tuple_intersection_agg_double(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's tuple_intersection_agg_double. With the optional trailing argument.

@spec tuple_intersection_agg_integer(term()) :: Latu.Plan.expression()

Spark's tuple_intersection_agg_integer. The trailing argument is optional.

@spec tuple_intersection_agg_integer(
  term(),
  term()
) :: Latu.Plan.expression()

Spark's tuple_intersection_agg_integer. With the optional trailing argument.

@spec tuple_sketch_agg_double(term(), term()) :: Latu.Plan.expression()

Spark's tuple_sketch_agg_double. Sends Spark's defaults for the rest: 12, "sum".

@spec tuple_sketch_agg_double(term(), term(), term()) :: Latu.Plan.expression()

Spark's tuple_sketch_agg_double. Sends Spark's defaults for the rest: "sum".

@spec tuple_sketch_agg_double(term(), term(), term(), term()) ::
  Latu.Plan.expression()

Spark's tuple_sketch_agg_double. Every argument is sent.

@spec tuple_sketch_agg_integer(term(), term()) :: Latu.Plan.expression()

Spark's tuple_sketch_agg_integer. Sends Spark's defaults for the rest: 12, "sum".

@spec tuple_sketch_agg_integer(term(), term(), term()) :: Latu.Plan.expression()

Spark's tuple_sketch_agg_integer. Sends Spark's defaults for the rest: "sum".

@spec tuple_sketch_agg_integer(term(), term(), term(), term()) ::
  Latu.Plan.expression()

Spark's tuple_sketch_agg_integer. Every argument is sent.

@spec tuple_union_agg_double(term()) :: Latu.Plan.expression()

Spark's tuple_union_agg_double. Sends Spark's defaults for the rest: 12, "sum".

@spec tuple_union_agg_double(term(), term()) :: Latu.Plan.expression()

Spark's tuple_union_agg_double. Sends Spark's defaults for the rest: "sum".

@spec tuple_union_agg_double(term(), term(), term()) :: Latu.Plan.expression()

Spark's tuple_union_agg_double. Every argument is sent.

@spec tuple_union_agg_integer(term()) :: Latu.Plan.expression()

Spark's tuple_union_agg_integer. Sends Spark's defaults for the rest: 12, "sum".

@spec tuple_union_agg_integer(term(), term()) :: Latu.Plan.expression()

Spark's tuple_union_agg_integer. Sends Spark's defaults for the rest: "sum".

@spec tuple_union_agg_integer(term(), term(), term()) :: Latu.Plan.expression()

Spark's tuple_union_agg_integer. Every argument is sent.

@spec var_pop(term()) :: Latu.Plan.expression()

Spark's var_pop.

var_pop(expr) - Returns the population variance calculated from values of a group.

Examples (Spark SQL)

> SELECT var_pop(col) FROM VALUES (1), (2), (3) AS tab(col);
0.6666666666666666

@spec var_samp(term()) :: Latu.Plan.expression()

Spark's var_samp.

var_samp(expr) - Returns the sample variance calculated from values of a group.

Examples (Spark SQL)

> SELECT var_samp(col) FROM VALUES (1), (2), (3) AS tab(col);
1.0

@spec variance(term()) :: Latu.Plan.expression()

Spark's variance.

variance(expr) - Returns the sample variance calculated from values of a group.

Examples (Spark SQL)

> SELECT variance(col) FROM VALUES (1), (2), (3) AS tab(col);
1.0