Aggregate functions - RisingWave

For details about the supported syntaxes of aggregate expressions, see Aggregate function calls.

General-purpose aggregate functions

`approx_count_distinct`

Added in v2.5.0.

Counts the estimated number of distinct values in a column, using the HyperLogLog algorithm.

This function is only supported for append-only streams, and it is currently in technical preview stage.

approx_count_distinct ( expression ) -> bigint

`array_agg`

Collects all the input values, including nulls, into an array. The ORDER BY clause is optional and specifies the order of rows processed in the aggregation, which determines the order of the elements in the result array.

array_agg ( expression [ ORDER BY sort_expression ] ) -> output_array

`avg`

Returns the average (arithmetic mean) of all non-null input values or null if no non-null values are provided.

avg ( expression ) -> see description

Input types include smallint, int, bigint, numeric, real, and double precision. Return type is numeric for integer inputs and double precision for float point inputs.

`bit_and`

Returns the bitwise AND of all non-null input values or null if no non-null values are provided.

bit_and ( smallint | int | bigint ) -> same as input type

`bit_or`

Returns the bitwise OR of all non-null input values or null if no non-null values are provided.

bit_or ( smallint | int | bigint ) -> same as input type

`bool_and`

Returns true if all non-null input values are true, otherwise false.

bool_and ( boolean ) -> boolean

`bool_or`

Returns true if any non-null input value is true, otherwise false.

bool_or ( boolean ) -> boolean

`count`

Returns the number of non-null input values.

count ( expression ) -> bigint

The input can be of any supported data type.

`count(*)`

Returns the number of rows in the input.

count(*) -> bigint

`jsonb_agg`

Collects all the input values, including nulls, into a JSON array. The ORDER BY clause is optional and specifies the order of rows processed in the aggregation, which determines the order of the elements in the result array.

jsonb_agg ( any_element [ ORDER BY sort_expression ] ) -> jsonb

`jsonb_object_agg`

Aggregates name/value pairs as a JSON object. Values can be null, but keys cannot.

jsonb_object_agg ( key "text" , value "any" ) -> jsonb

`max`

Returns the maximum of the non-null input values, or null if no non-null values are provided.

max ( expression ) -> same as input type

Input can be of any numeric, string, date/time, or interval type, or an array of these types.

`min`

Returns the minimum value of the non-null input values, or null if no non-null values are provided.

min ( expression ) -> same as input type

Input can be of any numeric, string, date/time, or interval type, or an array of these types.

`arg_max`

Added in v2.6.0.

Returns the first argument where the second argument (comparison value) is the largest. If multiple rows have the same maximum value, use ORDER BY to determine which row to return.

arg_max (any, any [ORDER BY ...]) -> any

select arg_max(name, score) from students;
-- Returns the name of the student with the highest score

select arg_max(name, score order by age desc) from students;
-- Returns the name of the oldest student among those with the highest score

`arg_min`

Added in v2.6.0.

Returns the first argument where the second argument (comparison value) is the smallest. If multiple rows have the same minimum value, use ORDER BY to determine which row to return.

arg_min (any, any [ORDER BY ...]) -> any

select arg_min(name, score) from students;
-- Returns the name of the student with the lowest score

select arg_min(name, score order by age) from students;
-- Returns the name of the youngest student among those with the lowest score

`string_agg`

Concatenates non-null input values into a string. Each value after the first is preceded by the corresponding delimiter (if it’s not null). If no non-null values are provided, returns null. The ORDER BY clause is optional and specifies the order of rows processed in the aggregation, which determines the order of the elements in the result array.

string_agg ( value text, delimiter text [ ORDER BY sort_expression ] ) -> output_string

`sum`

Returns the sum of all non-null input values, or null if no non-null values are provided.

sum ( expression )

Input types include smallint, int, bigint, numeric, real, and double precision. Return type is bigint for smallint or int inputs, numeric for bigint inputs, otherwise the same as the input data type.

`first_value`

Returns the first value in an ordered set of values, including nulls.

first_value ( expression ORDER BY order_key ) -> same as input type

order_key is the column or expression used to determine the order of the values. It is required to make the result deterministic.

`last_value`

Returns the last value in an ordered set of values, including nulls.

last_value ( expression ORDER BY order_key ) -> same as input type

Aggregate functions for statistics

`stddev_pop`

Calculates the population standard deviation of the input values. Returns NULL if the input contains no non-null values.

stddev_pop ( expression ) -> output_value

`stddev_samp`

Calculates the sample standard deviation of the input values. Returns NULL if the input contains fewer than two non-null values.

stddev_samp ( expression ) -> output_value

`var_pop`

Calculates the population variance of the input values. Returns NULL if the input contains no non-null values.

var_pop ( expression ) -> output_value

`var_samp`

Calculates the sample variance of the input values. Returns NULL if the input contains fewer than two non-null values.

var_samp ( expression ) -> output_value

Ordered-set aggregate functions

At present, ordered-set aggregate functions support only constant fraction arguments.

`mode`

Computes the mode, which is the most frequent value of the aggregated argument. If there are multiple equally-frequent values, it arbitrarily chooses the first one.

mode () WITHIN GROUP ( ORDER BY sort_expression ) -> same as sort_expression

sort_expression: Must be of a sortable type. This example calculates the mode of the values in column1 from table1.

SELECT mode() WITHIN GROUP (ORDER BY column1) FROM table1;

`percentile_cont`

At present, percentile_cont is not supported for streaming queries yet.

Computes the continuous percentile, which is a value corresponding to the specified fraction within the ordered set of aggregated argument values. It can interpolate between adjacent input items if needed.

percentile_cont ( fraction double precision ) WITHIN GROUP ( ORDER BY sort_expression double precision ) -> double precision

fraction: The fraction value representing the desired percentile. It should be between 0 and 1. This example calculates the median (50th percentile) of the values in column1 from table1.

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY column1) FROM table1;

If NULL is provided, the function will not calculate a specific percentile and return NULL instead.

`percentile_disc`

At present, percentile_disc is not supported for streaming queries yet.

Computes the discrete percentile, which is the first value within the ordered set of aggregated argument values whose position in the ordering equals or exceeds the specified fraction.

percentile_disc ( fraction double precision ) WITHIN GROUP ( ORDER BY sort_expression anyelement ) -> same as sort_expression

fraction: The fraction value representing the desired percentile. It should be between 0 and 1. sort_expression: Must be of a sortable type. This example calculates the 75th percentile of the values in column1 from table1.

SELECT percentile_disc(0.75) WITHIN GROUP (ORDER BY column1) FROM table1;

If NULL is provided, the function will not calculate a specific percentile and return NULL instead.

`approx_percentile`

Returns an approximate value of the specified percentile from a numeric column.

approx_percentile(DOUBLE percentile [, DOUBLE relative_error]) WITHIN GROUP (ORDER BY percentile_column) -> output_value

percentile: The percentile to approximate. It should be between 0 and 1.
relative_error: Optional. Specifies the maximum allowed error in the approximation. Defaults to 0.01 (1%).
percentile_column: The column from which to calculate the percentile. Must be of a numeric type.

This example calculates the 50th percentile of a numeric column with the default relative error:

SELECT approx_percentile(0.5) WITHIN GROUP (ORDER BY column1) FROM table1;

Grouping operation functions

Grouping operation functions are used in conjunction with grouping sets to distinguish result rows. The arguments to the grouping() function are not actually evaluated, but they must exactly match expressions given in the GROUP BY clause of the associated query level.

`grouping`

Returns a bit mask indicating which GROUP BY expressions are not included in the current grouping set. Bits are assigned with the rightmost argument corresponding to the least-significant bit; each bit is 0 if the corresponding expression is included in the grouping criteria of the grouping set generating the current result row, and 1 if it is not included.

Syntax

grouping ( group_by_expression(s) ) → integer

Example

Create a table

CREATE TABLE items_sold (brand varchar, size varchar, sales int);

Insert some data

INSERT INTO items_sold VALUES ('Foo', 'L', 10),('Foo', 'M', 20),('Bar', 'M', 15),('Bar', 'L', '5');

Get grouping results

SELECT brand, size, sum(sales), grouping(brand), grouping(size), grouping(brand,size), count(DISTINCT sales)
FROM items_sold
GROUP BY GROUPING SETS ((brand), (size), ());
------RESULTS
Bar NULL 20 0 1 1 2
Foo NULL 30 0 1 1 2
NULL L 15 1 0 2 2
NULL M 35 1 0 2 2
NULL NULL 50 1 1 3 4

​General-purpose aggregate functions

​approx_count_distinct

​array_agg

​avg

​bit_and

​bit_or

​bool_and

​bool_or

​count

​count(*)

​jsonb_agg

​jsonb_object_agg

​max

​min

​arg_max

​arg_min

​string_agg

​sum

​first_value

​last_value

​Aggregate functions for statistics

​stddev_pop

​stddev_samp

​var_pop

​var_samp

​Ordered-set aggregate functions

​mode

​percentile_cont

​percentile_disc

​approx_percentile

​Grouping operation functions

​grouping

​Example

General-purpose aggregate functions

`approx_count_distinct`

`array_agg`

`avg`

`bit_and`

`bit_or`

`bool_and`

`bool_or`

`count`

`count(*)`

`jsonb_agg`

`jsonb_object_agg`

`max`

`min`

`arg_max`

`arg_min`

`string_agg`

`sum`

`first_value`

`last_value`

Aggregate functions for statistics

`stddev_pop`

`stddev_samp`

`var_pop`

`var_samp`

Ordered-set aggregate functions

`mode`

`percentile_cont`

`percentile_disc`

`approx_percentile`

Grouping operation functions

`grouping`

Example