Aggregate Functions

Function List

approx_distinct

This function estimates the number of unique values in x.

• Estimates the number of unique values in x. By default, the standard error is 2.3%.

  Syntax: approx_distinct(x)

• Estimates the number of unique values in x. You can customize the standard error.

  Syntax: approx_distinct(x, e)

Return value type: bigint

• Example 1: Use the approx_distinct function to count the unique values of the clientIp field, with the standard error of 2.3%.

  Table 3 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT approx_distinct(clientIp)	1

• Example 2: Use the approx_distinct function to count the unique values of the clientIp field, with the custom standard error of 10%.

  Table 4 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT approx_distinct(clientIp, 0.1)	1

approx_percentile

This function sorts the values of x in ascending order and returns the value at a specified percentile position (percentage).

• Sorts x in ascending order and returns the value of x at the specified percentile position (percentage) as a double.

  Syntax: approx_percentile(x, percentage)

• Sorts x in ascending order and returns the values of x at the specified percentile positions (percentage01 and percentage02), with the result presented as array(double,double).

  Syntax: approx_percentile(x, array[percentage01, percentage02)]

• Computes the product of each element in x with the corresponding weight, sorts these products in ascending order, and returns the value of x at the specified percentile position (percentage) as a double.

  Syntax: approx_percentile(x, weight, percentage)

• Computes the product of each element in x with the corresponding weight, sorts the products in ascending order, and returns the values of x at the specified percentile positions (percentage01 and percentage02), with the result presented as array(double,double).

  Syntax: approx_percentile(x, weight, array[percentage01, percentage02...])

• Computes the product of each element in x with the corresponding weight, sorts these products in ascending order, and returns the value of x at the specified percentile position (percentage) as a double. The accuracy of the returned result can be set.

  Syntax: approx_percentile(x, weight, percentage, accuracy)

Return value type: double or array(double,...,double)

• Example 1: Sort the request_time values and return the value at the approximate 50% position.

  Table 6 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT approx_percentile(request_time, 0.5)	45.0

• Example 2: Sort the request_time values and return the values at the 10%, 20%, and 70% positions.

  Table 7 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT approx_percentile(request_time,array[0.1,0.2,0.7])	[17.0, 24.0, 59.0]

• Example 3: Sort the request_time values based on the product of request_time and weight, and return the value at the approximate 50% position. The weight is 60.

  Table 8 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT approx_percentile(request_time, 60, 0.5)	45.0

• Example 4: Sort the request_time values based on the product of request_time and weight, and return the values at the approximate 80% and 90% positions. The weight is 60.

  Table 9 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT approx_percentile(request_time, 60, array[0.8, 0.9])	[66.0,73.0]

• Example 5: Sort the request_time values based on the product of request_time and weight, and return the value at the approximate 50% position. The weight is 60 and the accuracy is 0.2.

  Table 10 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT approx_percentile(request_time, 60, 0.5, 0.2)	45.0

arbitrary

This function returns a random non-null value in x.

Syntax: arbitrary(x)

Return value type: same as the data type of the parameter value

Example: select arbitrary(region)

bool_and

bool_and is an aggregate function of Trino. It is used to perform the AND aggregation operation on a group of Boolean values. It returns a Boolean value. If all non-null values are TRUE, TRUE is returned. If any non-null value is FALSE or all values in the input are null, FALSE is returned. This function is used to determine whether all conditions are met. It is commonly used in service logic such as data verification, status check, and rule matching.

Syntax: bool_and(x)

Return type: Boolean

• If all non-null values are TRUE, TRUE is returned.
• If at least one non-null value is FALSE, FALSE is returned.
• If all values are null, FALSE is returned.

Example: * | select method , bool_and(status >= 500) as all_error group by method

max_by

This function queries the x value for the maximum y value or the x values for n maximum y values.

• Queries the x value corresponding to the maximum y value.

  Syntax: max_by(x, y)

• Queries the x values corresponding to the top n maximum y values.

  Syntax: max_by(x, y, n)

Return value type: same as the data type of the parameter value

• Example 1: Return the request method for the longest request.

  Table 16 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT max_by(request_method, request_time)	GET

• Example 2: Return the request methods for the top 3 longest requests.

  Table 17 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT max_by(request_method, request_time, 3)	["GET","GET","GET"]

min_by

This function queries the x value for the minimum y value or the x values for n minimum y values.

• Queries the x value corresponding to the minimum y value.

  Syntax: min_by(x, y)

• Queries the x values corresponding to the top n minimum y values.

  Syntax: min_by(x, y, n)

Return value type: same as the data type of the parameter value

• Example 1: Return the request method for the fastest request.

  Table 19 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT min_by(request_method, request_time)	POST

• Example 2: Return the request methods for the top 3 fastest requests.

  Table 20 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT min_by(request_method, request_time, 3)	["POST","POST","POST"]

count

This function is used for counting.

• Counts all log events.

  Syntax: COUNT(*)

• Counts all log events. It is equivalent to count(*).

  Syntax: COUNT(1)

• Counts the number of log events whose values are not NULL in x.

  Syntax: COUNT(x)

Return value type: Int

Example: select COUNT(*)

max

This function queries the maximum value in x.

• Queries the maximum value in x.

  Syntax: max(x)

• Queries the n maximum values in x and returns them as an array.

  Syntax: max(x, n)

Return value type: same as the data type of the parameter value

• Example 1: Query the maximum value in x.

  Table 24 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT max(x)	99.0

• Example 2: Query the two maximum values in x.

  Table 25 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT max(x, 2)	[99.0, 99.0]

min

This function queries the minimum value in x.

• Queries the minimum value in x.

  Syntax: min(x)

• Queries the n minimum values in x and returns them as an array.

  Syntax: min(x, n)

Return value type: same as the data type of the parameter value

• Example 1: Query the minimum value in x.

  Table 27 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT min(x)	10.0

• Example 2: Query the two minimum values in x.

  Table 28 Query and analysis results

  Type	Query Statement	Returned Result
  Scenario	SELECT min(x, 2)	[10.0, 10.0]

avg

This function calculates the arithmetic average value of x.

Syntax: AVG(x)

Return value type: double

Example: select AVG(value)