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 column and return the value of the request_time field that is approximately at the 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 column and return the values of the request_time field that are 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 column based on the product of request_time and weight, and return the value of the request_time field that is approximately at the 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 column based on the product of request_time and weight, and return the values of the request_time field that are approximately at the 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 column based on the product of request_time and weight, and return the value of the request_time field that is approximately at the 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)

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 14 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 15 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 17 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 18 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 22 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 23 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 25 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 26 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)