# Aggregate Functions in Confluent Cloud for Apache Flink Confluent Cloud for Apache Flink® provides these built-in functions to aggregate rows in Flink SQL queries: | [AVG](#flink-sql-avg-function) | [COLLECT](#flink-sql-collect-function) | [COUNT](#flink-sql-count-function) | [CUME_DIST](#flink-sql-cume-dist-function) | |----------------------------------------------|--------------------------------------------------|--------------------------------------|----------------------------------------------| | [DENSE_RANK](#flink-sql-dense-rank-function) | [FIRST_VALUE](#flink-sql-first-value-function) | [LAG](#flink-sql-lag-function) | [LAST_VALUE](#flink-sql-last-value-function) | | [LEAD](#flink-sql-lead-function) | [LISTAGG](#flink-sql-listagg-function) | [MAX](#flink-sql-max-function) | [MIN](#flink-sql-min-function) | | [NTILE](#flink-sql-ntile-function) | [PERCENT_RANK](#flink-sql-percent-rank-function) | [RANK](#flink-sql-rank-function) | [ROW_NUMBER](#flink-sql-row-number-function) | | [STDDEV_POP](#flink-sql-stddev-pop-function) | [STDDEV_SAMP](#flink-sql-stddev-samp-function) | [SUM](#flink-sql-sum-function) | [VAR_POP](#flink-sql-var-pop-function) | | [VAR_SAMP](#flink-sql-var-samp-function) | [VARIANCE](#flink-sql-variance-function) | | | The aggregate functions take an expression across all the rows as the input and return a single aggregated value as the result. ## AVG Syntax : ```sql AVG([ ALL | DISTINCT ] expression) ``` Description : By default or with keyword `ALL`, returns the average (arithmetic mean) of `expression` over all input rows.
Use `DISTINCT` to return one unique instance of each value. Example : ```sql -- returns 1.500000 SELECT AVG(my_values) FROM (VALUES (0.0), (1.0), (2.0), (3.0)) AS my_values; ``` ## COLLECT Syntax : ```sql COLLECT([ ALL | DISTINCT ] expression) ``` Description : By default or with the `ALL` keyword, returns a multiset of `expression` over all input rows.
NULL values are ignored.
Use `DISTINCT` to return one unique instance of each value. ## COUNT Syntax : ```sql COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*) ``` Description : By default or with `ALL`, returns the number of input rows for which expression isn’t NULL.
Use `DISTINCT` to return one unique instance of each value.
Use `COUNT(*)` or `COUNT(1)` to return the number of input rows. Example : ```sql -- returns 4 SELECT COUNT(my_values) FROM (VALUES (0), (1), (2), (3)) AS my_values; ``` ## CUME_DIST Syntax : ```sql CUME_DIST() ``` Description : Returns the cumulative distribution of a value in a group of values. The result is the number of rows preceding or equal to the current row in the partition ordering divided by the number of rows in the window partition. ## DENSE_RANK Syntax : ```sql DENSE_RANK() ``` Description : Returns the rank of a value in a group of values.
The result is one plus the previously assigned rank value.
Unlike the [RANK](#flink-sql-rank-function) function, `DENSE_RANK` doesn’t produce gaps in the ranking sequence. Related function : - [RANK](#flink-sql-rank-function) ## FIRST_VALUE Syntax : ```sql FIRST_VALUE(expression) ``` Description : Returns the first value in an ordered set of values. Example : ```sql -- returns first SELECT FIRST_VALUE(my_values) FROM (VALUES ('first'), ('second'), ('third')) AS my_values; ``` Related function : - [LAST_VALUE](#flink-sql-last-value-function) ## LAG Syntax : ```sql LAG(expression [, offset] [, default]) ``` Description : Returns the value of expression at the offsetth row *before* the current row in the window.
The default value of `offset` is *1*, and the default value of the `default` argument is NULL. Example : The following example shows how to use the LAG function to see player scores changing over time.
```mysql SELECT $rowtime AS row_time , player_id , game_room_id , points , LAG(points, 1) OVER (PARTITION BY player_id ORDER BY $rowtime) previous_points_value FROM gaming_player_activity; ```
For the full code example, see [Compare Current and Previous Values in a Data Stream](../../how-to-guides/compare-current-and-previous-values.md#flink-sql-compare-values-query). Related function : - [LEAD](#flink-sql-lead-function) ## LAST_VALUE Syntax : ```sql LAST_VALUE(expression) ``` Description : Returns the last value in an ordered set of values. Example : ```sql -- returns third SELECT LAST_VALUE(my_values) FROM (VALUES ('first'), ('second'), ('third')) AS my_values; ``` Related function : - [FIRST_VALUE](#flink-sql-first-value-function) ## LEAD Syntax : ```sql LEAD(expression [, offset] [, default]) ``` Description : Returns the value of the expression at the offsetth row *after* the current row in the window.
The default value of `offset` is *1*, and the default value of the `default` argument is NULL. Related function : - [LAG](#flink-sql-lag-function) ## LISTAGG Syntax : ```sql LISTAGG(expression [, separator]) ``` Description : Concatenates the values of string expressions and inserts separator values between them.
The separator isn’t added at the end of string.
The default value of separator is `','`. Example : ```sql -- returns first,second,third SELECT LISTAGG(my_values) FROM (VALUES ('first'), ('second'), ('third')) AS my_values; ``` ## MAX Syntax : ```sql MAX([ ALL | DISTINCT ] expression) ``` Description : By default or with the `ALL` keyword, returns the maximum value of `expression` over all input rows.
Use `DISTINCT` to return one unique instance of each value. Examples : ```sql -- returns 3 SELECT MAX(my_values) FROM (VALUES (0), (1), (2), (3)) AS my_values; ```
The following example shows how to use the MAX function to find the highest player score in a tumbling window.
```mysql SELECT window_start, window_end, SUM(points) AS total, MIN(points) as min_points, MAX(points) as max_points FROM TUMBLE(TABLE gaming_player_activity_source, DESCRIPTOR($rowtime), INTERVAL '10' SECOND) GROUP BY window_start, window_end; ```
For the full code example, see [Aggregate a Stream in a Tumbling Window](../../how-to-guides/aggregate-tumbling-window.md#flink-sql-aggregate-tumbling-window-declare-table). Related function : - [MIN](#flink-sql-min-function) ## MIN Syntax : ```sql MIN([ ALL | DISTINCT ] expression ) ``` Description : By default or with the `ALL` keyword, returns the minimum value of `expression` across all input rows.
Use `DISTINCT` to return one unique instance of each value. Examples : ```sql -- returns 0 SELECT MIN(my_values) FROM (VALUES (0), (1), (2), (3)) AS my_values; ```
The following example shows how to use the MIN function to find the lowest player score in a tumbling window.
```mysql SELECT window_start, window_end, SUM(points) AS total, MIN(points) as min_points, MAX(points) as max_points FROM TUMBLE(TABLE gaming_player_activity_source, DESCRIPTOR($rowtime), INTERVAL '10' SECOND) GROUP BY window_start, window_end; ```
For the full code example, see [Aggregate a Stream in a Tumbling Window](../../how-to-guides/aggregate-tumbling-window.md#flink-sql-aggregate-tumbling-window-declare-table). Related function : - [MAX](#flink-sql-max-function) ## NTILE Syntax : ```sql NTILE(n) ``` Description : Divides the rows for each window partition into `n` buckets ranging from *1* to at most `n`.
If the number of rows in the window partition doesn’t divide evenly into the number of buckets, the remainder values are distributed one per bucket, starting with the first bucket.
For example, with *6* rows and *4* buckets, the bucket values would be:
```none 1 1 2 2 3 4 ``` ## PERCENT_RANK Syntax : ```sql PERCENT_RANK() ``` Description : Returns the percentage ranking of a value in a group of values.
The result is the rank value minus one, divided by the number of rows in the partition minus one.
If the partition only contains one row, the `PERCENT_RANK` function returns *0*. ## RANK Syntax : ```sql RANK() ``` Description : Returns the rank of a value in a group of values.
The result is one plus the number of rows preceding or equal to the current row in the partition ordering.
The values produce gaps in the sequence. Related functions : - [DENSE_RANK](#flink-sql-dense-rank-function) - [ROW_NUMBER](#flink-sql-row-number-function) ## ROW_NUMBER Syntax : ```sql ROW_NUMBER() ``` Description : Assigns a unique, sequential number to each row, starting with one, according to the ordering of rows within the window partition.
The `ROW_NUMBER` and `RANK` functions are similar. `ROW_NUMBER` numbers all rows sequentially, for example, `1, 2, 3, 4, 5`. `RANK` provides the same numeric value for ties, for example `1, 2, 2, 4, 5`. Related functions : - [RANK](#flink-sql-rank-function) - [DENSE_RANK](#flink-sql-dense-rank-function) ## STDDEV_POP Syntax : ```sql STDDEV_POP([ ALL | DISTINCT ] expression) ``` Description : By default or with the `ALL` keyword, returns the population standard deviation of `expression` over all input rows.
Use `DISTINCT` to return one unique instance of each value. Example : ```sql -- returns 0.986154 SELECT STDDEV_POP(my_values) FROM (VALUES (0.5), (1.5), (2.2), (3.2)) AS my_values; ``` Related function : - [STDDEV_SAMP](#flink-sql-stddev-samp-function) ## STDDEV_SAMP Syntax : ```sql STDDEV_SAMP([ ALL | DISTINCT ] expression) ``` Description : By default or with the `ALL` keyword, returns the sample standard deviation of `expression` over all input rows.
Use `DISTINCT` to return one unique instance of each value. Example : ```sql -- returns 1.138713 SELECT STDDEV_SAMP(my_values) FROM (VALUES (0.5), (1.5), (2.2), (3.2)) AS my_values; ``` Related function : - [STDDEV_POP](#flink-sql-stddev-pop-function) ## SUM Syntax : ```sql SUM([ ALL | DISTINCT ] expression) ```
By default or with the `ALL` keyword, returns the sum of `expression` across all input rows.
Use `DISTINCT` to return one unique instance of each value. Examples : ```sql -- returns 6 SELECT SUM(my_values) FROM (VALUES (0), (1), (2), (3)) AS my_values; ```
The following example shows how to use the SUM function to find the total of player scores in a tumbling window.
```mysql SELECT window_start, window_end, SUM(points) AS total, MIN(points) as min_points, MAX(points) as max_points FROM TUMBLE(TABLE gaming_player_activity_source, DESCRIPTOR($rowtime), INTERVAL '10' SECOND) GROUP BY window_start, window_end; ```
For the full code example, see [Aggregate a Stream in a Tumbling Window](../../how-to-guides/aggregate-tumbling-window.md#flink-sql-aggregate-tumbling-window-declare-table). ## VAR_POP Syntax : ```sql VAR_POP([ ALL | DISTINCT ] expression) ``` Description : By default or with the `ALL` keyword, returns the population variance, which is the square of the population standard deviation, of `expression` over all input rows.
Use `DISTINCT` to return one unique instance of each value. Example : ```sql -- returns 0.972500 SELECT VAR_POP(my_values) FROM (VALUES (0.5), (1.5), (2.2), (3.2)) AS my_values; ``` Related function : - [VAR_SAMP](#flink-sql-var-samp-function) ## VAR_SAMP Syntax : ```sql VAR_SAMP([ ALL | DISTINCT ] expression) ``` Description : By default or with the `ALL` keyword, returns the sample variance, which is the square of the sample standard deviation, of `expression` over all input rows.
Use `DISTINCT` to return one unique instance of each value.
The `VARIANCE` function is equivalent to `VAR_SAMP`. Example : ```sql -- returns 1.296667 SELECT VAR_SAMP(my_values) FROM (VALUES (0.5), (1.5), (2.2), (3.2)) AS my_values; ``` Related functions : - [STDDEV_POP](#flink-sql-stddev-pop-function) - [VARIANCE](#flink-sql-variance-function) ## VARIANCE Syntax : ```sql VARIANCE([ ALL | DISTINCT ] expression) ``` Description : Equivalent to [VAR_SAMP](#flink-sql-var-samp-function). ## Other built-in functions - [Aggregate Functions](#flink-sql-aggregate-functions) - [Changelog Conversion Functions](changelog-conversion.md#flink-sql-changelog-conversion-functions) - [Collection Functions](collection-functions.md#flink-sql-collection-functions) - [Comparison Functions](comparison-functions.md#flink-sql-comparison-functions) - [Conditional Functions](conditional-functions.md#flink-sql-conditional-functions) - [Datetime Functions](datetime-functions.md#flink-sql-datetime-functions) - [Hash Functions](hash-functions.md#flink-sql-hash-functions) - [JSON Functions](json-functions.md#flink-sql-json-functions) - [ML Preprocessing Functions](ml-preprocessing-functions.md#flink-sql-ml-preprocessing-functions) - [Model Inference Functions](model-inference-functions.md#flink-sql-model-inference-functions) - [Numeric Functions](numeric-functions.md#flink-sql-numeric-functions) - [Search Functions](search-functions.md#flink-sql-search-functions) - [String Functions](string-functions.md#flink-sql-string-functions) - [Table API Functions](table-api-functions.md#flink-table-api-functions) ## Related content - [User-defined Functions](../../concepts/user-defined-functions.md#flink-sql-udfs) - [Create a User Defined Function](../../how-to-guides/create-udf.md#flink-sql-create-udf) #### NOTE This website includes content developed at the [Apache Software Foundation](https://www.apache.org/) under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).