An aggregate function is used to calculate a set of values and return a single value. This topic describes the syntax of aggregate functions. This topic also provides examples on how to use the functions.

The following table describes the aggregate functions that are supported by Log Service.

Notice If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.
Function Syntax Description
arbitrary function arbitrary(x) Returns a random, non-null value of x.
avg function avg(x) Calculates the arithmetic mean of the values of x.
bitwise_and_agg function bitwise_and_agg(x) Returns the result of the bitwise AND operation for the values of x.
bitwise_or_agg function bitwise_or_agg(x) Returns the result of the bitwise OR operation for the values of x.
checksum function hecksum(x) Calculates the checksum of x.
count function count(*) Calculates the total number of all log entries.
count(1) Calculates the total number of all log entries. This function is equivalent to count(*).
count(x) Calculates the number of log entries that contain x whose value is not null.
count_if function count_if(boolean expression) Calculates the number of log entries that meet a specified condition.
geometric_mean function geometric_mean(x) Calculates the geometric mean of the values of x.
max function max(x) Queries the maximum value of x.
max_by function max_by(x,y) Returns the value of x associated with y whose value is the maximum value.
max_by(x,y,n) Returns a JSON array that includes the n values of x associated with y whose values are the first n maximum values.
min function min(x) Queries the minimum value of x.
min_by function min_by(x,y) Returns the value of x associated with y whose value is the minimum value.
min_by(x,y,n) Returns a JSON array that includes the n values of x associated with y whose values are the first n minimum values.
sum function sum(x) Calculates the sum of the values of x.

arbitrary function

The arbitrary function is used to return a random, non-null value of x.

Syntax

arbitrary(x)

Parameters

Parameter Description
x The value of this parameter is of the arbitrary data type.

Return value type

The type of the return value is the same as the type of the parameter value.

Examples

Return a random, non-null value of the request_method field.

  • Query statement 
    * | SELECT arbitrary(request_method) AS request_method
  • Query and analysis resultarbitrary function

avg function

The avg function is used to calculate the arithmetic mean of the values of x.

Syntax

avg(x)

Parameters

Parameter Description
x The value of the parameter is of the double, bigint, decimal, or real type.

Return value type

The double type.

Examples

Return the projects whose average latency is greater than 1,000 microseconds.
  • Query statement 
    method: PostLogstoreLogs | SELECT avg(latency) AS avg_latency, Project GROUP BY Project HAVING avg_latency > 1000
  • Query and analysis resultavg function

bitwise_and_agg function

The bitwise_and_agg function is used to return the result of the bitwise AND operation for the values of x.

Syntax

bitwise_and_agg(x)

Parameters

Parameter Description
x The value of this parameter is of the bigint type.

Return value type

The bigint type (binary form).

Examples

Perform a bitwise AND operation on all values of the request_time field.

  • Query statement 
    * | SELECT bitwise_and_agg(status)
  • Query and analysis resultAND

bitwise_or_agg function

The bitwise_or_agg function is used to return the result of the bitwise OR operation for the values of x.

Syntax

bitwise_or_agg(x)

Parameters

Parameter Description
x The value of this parameter is of the bigint type.

Return value type

The bigint type (binary form).

Examples

Perform a bitwise OR operation on all values of the request_time field.
  • Query statement 
    * | SELECT bitwise_or_agg(request_length)
  • Query and analysis resultOR

checksum function

The checksum function is used to calculate the checksum of x.

Syntax

checksum(x)

Parameters

Parameter Description
x The value of this parameter is of the arbitrary data type.

Return value type

The string type (Base64-encoded).

Examples

  • Query statement 
    * | SELECT checksum(request_method) AS request_method
  • Query and analysis resultchecksum

count function

The count function is used to calculate the number of specific log entries.

Syntax

  • To calculate the total number of all log entries, use the following syntax: 
    count(*)
  • To calculate the total number of all log entries, use the following syntax. This function is equivalent to count(*). 
    count(1)
  • To calculate the number of log entries that contain x whose value is not null, use the following syntax: 
    count(x)

Parameters

Parameter Description
x The value of this parameter is of the arbitrary data type.

Return value type

The integer type.

Examples

  • Example 1: Calculate page views (PVs).
    • Query statement 
      * | SELECT count(*) AS PV
    • Query and analysis resultcount function
  • Example 2: Calculate the number of log entries that contain the request_method field whose value is not null.
    • Query statement 
      * | SELECT count(request_method) AS count
    • Query and analysis resultcount function

count_if function

The count_if function is used to calculate the number of log entries that meet a specified condition.

Syntax

count_if(boolean expression)

Parameters

boolean expression: a Boolean expression.

Return value type

The integer type.

Examples

Calculate the number of log entries whose value of the request_uri field ends with %file-7.

  • Query statement 
    * | SELECT count_if(request_uri like '%file-0') AS count
  • Query and analysis resultcount function

geometric_mean function

The geometric_mean function is used to calculate the geometric mean of the values of x.

Syntax

geometric_mean(x)

Parameters

Parameter Description
x The value of the parameter is of the double, bigint, or real type.

Return value type

The double type.

Examples

Calculate the geometric mean of request durations.

  • Query statement 
    * | SELECT geometric_mean(request_time) AS time
  • Query and analysis resultgeometric_mean

max function

The max function is used to query the maximum value of x.

Syntax

max(x)

Parameters

Parameter Description
x The value of this parameter is of the arbitrary data type.

Return value type

The type of the return value is the same as the type of the parameter value.

Examples

Query the longest request duration.
  • Query statement 
    * | SELECT max(request_time) AS max_request_time
  • Query and analysis resultmax function

max_by function

The max_by function supports the following two syntax:

Syntax

  • To return the value of x associated with y whose value is the maximum value, use the following syntax: 
    max_by(x,y)
  • To return a JSON array that includes the n values of x associated with y whose values are the first n maximum values, use the following syntax: 
    max_by(x,y,n)

Parameters

Parameter Description
x The value of this parameter is of the arbitrary data type.
y The value of this parameter is of the arbitrary data type.
n An integer that is greater than 0.

Return value type

The type of the return value is the same as the type of the parameter value.

Examples

  • Example 1: Query the point in time when the highest consumption occurs.
    • Query statement 
      * | SELECT max_by(UsageEndTime, PretaxAmount) as time
    • Query and analysis resultmax_by
  • Example 2: Return three request methods whose request durations are the three longest request durations.
    • Query statement 
      * | SELECT max_by(request_method,request_time,3) AS method
    • Query and analysis resultmax_by

min function

The min function is used to query the minimum value of x.

Syntax

min(x)

Parameters

Parameter Description
x The value of this parameter is of the arbitrary data type.

Return value type

The type of the return value is the same as the type of the parameter value.

Examples

Query the shortest request duration.
  • Query statement 
    * | SELECT min(request_time) AS min_request_time
  • Query and analysis resultmin function

min_by function

The min_by function supports the following two syntax:

Syntax

  • To return the value of x associated with y whose value is the minimum value, use the following syntax: 
    min_by(x,y)
  • To return a JSON array that includes the n values of x associated with y whose values are the first n minimum values, use the following syntax: 
    min_by(x,y,n)

Parameters

Parameter Description
x The value of this parameter is of the arbitrary data type.
y The value of this parameter is of the arbitrary data type.
n An integer that is greater than 0.

Return value type

The type of the return value is the same as the type of the parameter value.

Examples

  • Example 1: Return the request method whose request duration is the shortest duration.
    • Query statement 
      * | SELECT min_by(request_method,request_time) AS method
    • Query and analysis resultmin_by
  • Example 2: Return three request methods whose request durations are the three shortest request durations.
    • Query statement 
      * | SELECT min_by(request_method,request_time,3) AS time
    • Query and analysis resultmax_by

sum function

The sum function is used to calculate the sum of the values of x.

Syntax

sum(x)

Parameters

Parameter Description
x The value of the parameter is of the double, bigint, decimal, or real type.

Return value type

The type of the return value is the same as the type of the parameter value.

Examples

Calculate the size of daily web traffic.
  • Query statement 
    * | SELECT date_trunc('day',__time__) AS time, sum(body_bytes_sent) AS body_bytes_sent GROUP BY time ORDER BY time
  • Query and analysis resultsum function