Aggregate functions
Reference for aggregate functions in HotSQL. Names, signatures, and examples match the engine exactly.
approx_distinct
Returns the approximate number of distinct input values calculated using the HyperLogLog algorithm.
approx_distinct(expression)
> SELECT approx_distinct(column_name) FROM table_name;
+-----------------------------------+
| approx_distinct(column_name) |
+-----------------------------------+
| 42 |
+-----------------------------------+
approx_median
Returns the approximate median (50th percentile) of input values. It is an alias of approx_percentile_cont(0.5) WITHIN GROUP (ORDER BY x).
approx_median(expression)
> SELECT approx_median(column_name) FROM table_name;
+-----------------------------------+
| approx_median(column_name) |
+-----------------------------------+
| 23.5 |
+-----------------------------------+
approx_percentile_cont
Returns the approximate percentile of input values using the t-digest algorithm.
approx_percentile_cont(percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
Arguments
percentile: Percentile to compute. Must be a float value between 0 and 1 (inclusive).centroids: Number of centroids to use in the t-digest algorithm. Default is 100. A higher number results in more accurate approximation but requires more memory.
> SELECT approx_percentile_cont(0.75) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+------------------------------------------------------------------+
| approx_percentile_cont(0.75) WITHIN GROUP (ORDER BY column_name) |
+------------------------------------------------------------------+
| 65.0 |
+------------------------------------------------------------------+
> SELECT approx_percentile_cont(0.75, 100) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+-----------------------------------------------------------------------+
| approx_percentile_cont(0.75, 100) WITHIN GROUP (ORDER BY column_name) |
+-----------------------------------------------------------------------+
| 65.0 |
+-----------------------------------------------------------------------+
An alternate syntax is also supported:
> SELECT approx_percentile_cont(column_name, 0.75) FROM table_name;
+-----------------------------------------------+
| approx_percentile_cont(column_name, 0.75) |
+-----------------------------------------------+
| 65.0 |
+-----------------------------------------------+
> SELECT approx_percentile_cont(column_name, 0.75, 100) FROM table_name;
+----------------------------------------------------------+
| approx_percentile_cont(column_name, 0.75, 100) |
+----------------------------------------------------------+
| 65.0 |
+----------------------------------------------------------+
approx_percentile_cont_with_weight
Returns the weighted approximate percentile of input values using the t-digest algorithm.
approx_percentile_cont_with_weight(weight, percentile [, centroids]) WITHIN GROUP (ORDER BY expression)
Arguments
expression: The expression.weight: Expression to use as weight. Can be a constant, column, or function, and any combination of arithmetic operators.percentile: Percentile to compute. Must be a float value between 0 and 1 (inclusive).centroids: Number of centroids to use in the t-digest algorithm. Default is 100. A higher number results in more accurate approximation but requires more memory.
> SELECT approx_percentile_cont_with_weight(weight_column, 0.90) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+---------------------------------------------------------------------------------------------+
| approx_percentile_cont_with_weight(weight_column, 0.90) WITHIN GROUP (ORDER BY column_name) |
+---------------------------------------------------------------------------------------------+
| 78.5 |
+---------------------------------------------------------------------------------------------+
> SELECT approx_percentile_cont_with_weight(weight_column, 0.90, 100) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+--------------------------------------------------------------------------------------------------+
| approx_percentile_cont_with_weight(weight_column, 0.90, 100) WITHIN GROUP (ORDER BY column_name) |
+--------------------------------------------------------------------------------------------------+
| 78.5 |
+--------------------------------------------------------------------------------------------------+
An alternative syntax is also supported:
> SELECT approx_percentile_cont_with_weight(column_name, weight_column, 0.90) FROM table_name;
+--------------------------------------------------+
| approx_percentile_cont_with_weight(column_name, weight_column, 0.90) |
+--------------------------------------------------+
| 78.5 |
+--------------------------------------------------+
array_agg
Returns an array created from the expression elements. If ordering is required, elements are inserted in the specified order. This aggregation function can only mix DISTINCT and ORDER BY if the ordering expression is exactly the same as the argument expression.
array_agg(expression [ORDER BY expression])
> SELECT array_agg(column_name ORDER BY other_column) FROM table_name;
+-----------------------------------------------+
| array_agg(column_name ORDER BY other_column) |
+-----------------------------------------------+
| [element1, element2, element3] |
+-----------------------------------------------+
> SELECT array_agg(DISTINCT column_name ORDER BY column_name) FROM table_name;
+--------------------------------------------------------+
| array_agg(DISTINCT column_name ORDER BY column_name) |
+--------------------------------------------------------+
| [element1, element2, element3] |
+--------------------------------------------------------+
avg
Returns the average of numeric values in the specified column.
avg(expression)
> SELECT avg(column_name) FROM table_name;
+---------------------------+
| avg(column_name) |
+---------------------------+
| 42.75 |
+---------------------------+
bool_and
Returns true if all non-null input values are true, otherwise false.
bool_and(expression)
Arguments
expression: The expression.
> SELECT bool_and(column_name) FROM table_name;
+----------------------------+
| bool_and(column_name) |
+----------------------------+
| true |
+----------------------------+
corr
Returns the coefficient of correlation between two numeric values.
corr(expression1, expression2)
Arguments
expression1: First expression.expression2: Second expression.
> SELECT corr(column1, column2) FROM table_name;
+--------------------------------+
| corr(column1, column2) |
+--------------------------------+
| 0.85 |
+--------------------------------+
count
Returns the number of non-null values in the specified column. To include null values in the total count, use count(*).
count(expression)
> SELECT count(column_name) FROM table_name;
+-----------------------+
| count(column_name) |
+-----------------------+
| 100 |
+-----------------------+
> SELECT count(*) FROM table_name;
+------------------+
| count(*) |
+------------------+
| 120 |
+------------------+
covar_samp
Returns the sample covariance of a set of number pairs.
covar_samp(expression1, expression2)
Arguments
expression1: First expression.expression2: Second expression.
> SELECT covar_samp(column1, column2) FROM table_name;
+-----------------------------------+
| covar_samp(column1, column2) |
+-----------------------------------+
| 8.25 |
+-----------------------------------+
first_value
Returns the first element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
first_value(expression [ORDER BY expression])
> SELECT first_value(column_name ORDER BY other_column) FROM table_name;
+-----------------------------------------------+
| first_value(column_name ORDER BY other_column)|
+-----------------------------------------------+
| first_element |
+-----------------------------------------------+
grouping
Returns 1 if the data is aggregated across the specified column, or 0 if it is not aggregated in the result set.
grouping(expression)
Arguments
expression: Expression to evaluate whether data is aggregated across the specified column. Can be a constant, column, or function.
> SELECT column_name, GROUPING(column_name) AS group_column
FROM table_name
GROUP BY GROUPING SETS ((column_name), ());
+-------------+-------------+
| column_name | group_column |
+-------------+-------------+
| value1 | 0 |
| value2 | 0 |
| NULL | 1 |
+-------------+-------------+
last_value
Returns the last element in an aggregation group according to the requested ordering. If no ordering is given, returns an arbitrary element from the group.
last_value(expression [ORDER BY expression])
> SELECT last_value(column_name ORDER BY other_column) FROM table_name;
+-----------------------------------------------+
| last_value(column_name ORDER BY other_column) |
+-----------------------------------------------+
| last_element |
+-----------------------------------------------+
max
Returns the maximum value in the specified column.
max(expression)
> SELECT max(column_name) FROM table_name;
+----------------------+
| max(column_name) |
+----------------------+
| 150 |
+----------------------+
median
Returns the median value in the specified column.
median(expression)
Arguments
expression: The expression.
> SELECT median(column_name) FROM table_name;
+----------------------+
| median(column_name) |
+----------------------+
| 45.5 |
+----------------------+
min
Returns the minimum value in the specified column.
min(expression)
> SELECT min(column_name) FROM table_name;
+----------------------+
| min(column_name) |
+----------------------+
| 12 |
+----------------------+
nth_value
Returns the nth value in a group of values.
nth_value(expression, n ORDER BY expression)
Arguments
expression: The column or expression to retrieve the nth value from.n: The position (nth) of the value to retrieve, based on the ordering.
> SELECT dept_id, salary, NTH_VALUE(salary, 2) OVER (PARTITION BY dept_id ORDER BY salary ASC) AS second_salary_by_dept
FROM employee;
+---------+--------+-------------------------+
| dept_id | salary | second_salary_by_dept |
+---------+--------+-------------------------+
| 1 | 30000 | NULL |
| 1 | 40000 | 40000 |
| 1 | 50000 | 40000 |
| 2 | 35000 | NULL |
| 2 | 45000 | 45000 |
+---------+--------+-------------------------+
percentile_cont
Returns the exact percentile of input values, interpolating between values if needed.
percentile_cont(percentile) WITHIN GROUP (ORDER BY expression)
Arguments
expression: The expression.percentile: Percentile to compute. Must be a float value between 0 and 1 (inclusive).
> SELECT percentile_cont(0.75) WITHIN GROUP (ORDER BY column_name) FROM table_name;
+----------------------------------------------------------+
| percentile_cont(0.75) WITHIN GROUP (ORDER BY column_name) |
+----------------------------------------------------------+
| 45.5 |
+----------------------------------------------------------+
An alternate syntax is also supported:
> SELECT percentile_cont(column_name, 0.75) FROM table_name;
+---------------------------------------+
| percentile_cont(column_name, 0.75) |
+---------------------------------------+
| 45.5 |
+---------------------------------------+
stddev
Returns the standard deviation of a set of numbers.
stddev(expression)
> SELECT stddev(column_name) FROM table_name;
+----------------------+
| stddev(column_name) |
+----------------------+
| 12.34 |
+----------------------+
stddev_pop
Returns the population standard deviation of a set of numbers.
stddev_pop(expression)
> SELECT stddev_pop(column_name) FROM table_name;
+--------------------------+
| stddev_pop(column_name) |
+--------------------------+
| 10.56 |
+--------------------------+
string_agg
Concatenates the values of string expressions and places separator values between them. If ordering is required, strings are concatenated in the specified order. This aggregation function can only mix DISTINCT and ORDER BY if the ordering expression is exactly the same as the first argument expression.
string_agg([DISTINCT] expression, delimiter [ORDER BY expression])
Arguments
expression: The string expression to concatenate. Can be a column or any valid string expression.delimiter: A literal string used as a separator between the concatenated values.
> SELECT string_agg(name, ', ') AS names_list
FROM employee;
+--------------------------+
| names_list |
+--------------------------+
| Alice, Bob, Bob, Charlie |
+--------------------------+
> SELECT string_agg(name, ', ' ORDER BY name DESC) AS names_list
FROM employee;
+--------------------------+
| names_list |
+--------------------------+
| Charlie, Bob, Bob, Alice |
+--------------------------+
> SELECT string_agg(DISTINCT name, ', ' ORDER BY name DESC) AS names_list
FROM employee;
+--------------------------+
| names_list |
+--------------------------+
| Charlie, Bob, Alice |
+--------------------------+
sum
Returns the sum of all values in the specified column.
sum(expression)
> SELECT sum(column_name) FROM table_name;
+-----------------------+
| sum(column_name) |
+-----------------------+
| 12345 |
+-----------------------+
var
Returns the statistical sample variance of a set of numbers.
var(expression)
Arguments
expression: Numeric expression.
var_pop
Returns the statistical population variance of a set of numbers.
var_pop(expression)
Arguments
expression: Numeric expression.