阿里云可观测

Overview

An aggregate function calculates the values of a field and returns a single value. This topic describes the syntax of aggregate functions. This topic also provides examples on how to use aggregate functions.

Functions

The following table describes the aggregate functions supported by Simple Log Service. In the analytics statements of Simple Log Service, strings must be enclosed in single quotation marks ('). Strings that are not enclosed or strings enclosed in double quotation marks (") indicate field names or column names. For example, 'status' indicates the string status, and status or "status" indicates the log field named status.

Function	Function	Function
arbitrary	arbitrary( x )	arbitrary
arbitrary	avg( x )	Returns the arithmetic mean value of the x field.
Returns the arithmetic mean value of the x field.	bitwise_and_agg( x )	eturns the result of the bitwise AND operation on the values of the x field.
bitwise_or_agg	bitwise_or_agg( x )	Returns the result of the bitwise OR operation on the values of the x field.
bool_and	bool_and( boolean expression )	Checks whether all logs meet the specified condition. If yes, the function returns true. This function is equivalent to the every function.
bool_or	bool_or( boolean expression )	Checks whether all logs meet the specified condition. If yes, the function returns true. This function is equivalent to the every function.
checksum	checksum( x )	Returns the checksum of the values of the x field.
count	count(*)
count	count(1)	Returns the total number of logs.。
count	count( x )	Returns the total number of logs.
count_if	count_if( boolean expression )	Returns the number of logs that meet the specified condition.
every	every( boolean expression )	Returns the number of logs that meet the specified condition.
geometric_mean	geometric_mean( x )	Returns the geometric mean of the values of the x field.
kurtosis	kurtosis( x )
map_union	map_union( x )	Returns the union of the specified maps. If multiple input maps have the same key, the value of the key in the returned union is extracted from one of the maps at random.
max	max( x )	Returns the maximum value of the x field.

Examples

arbitrary

The arbitrary function returns a random, not-null value for the x field.

sql

arbitrary(x)

Parameter	Description
x	The parameter value can be of an arbitrary data type.
Return value type	Return value type

Return value type

sql

* | SELECT arbitrary(request_method) AS request_method

avg

The avg function returns the arithmetic mean value of the x field.

sql

avg(x)

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

Description

sql

method: PostLogstoreLogs | SELECT avg(latency) AS avg_latency, Project GROUP BY Project HAVING avg_latency > 1000

bitwise_and_agg

Description

sql

bitwise_and_agg(x)

Parameter	Description
x	Description
Return value type	Description
Bigint (binary)

The bitwise_or_agg function returns the result of the bitwise OR operation on the values of the x field. Query statement:

sql

* | SELECT bitwise_and_agg(status)

bitwise_or_agg

The bitwise_or_agg function returns the result of the bitwise OR operation on the values of the x field.

sql

bitwise_or_agg(x)

Parameter	Description
x	The parameter value is of the bigint type.
Return value type	Description
Bigint (binary)

The parameter value is of the bigint type.

sql

* | SELECT bitwise_or_agg(request_length)

bool_and

The bitwise_or_agg function returns the result of the bitwise OR operation on the values of the x field.

sql

bool_and(boolean expression)

Parameter	Description
boolean expression	The parameter value can be a Boolean expression.
Return value type	Description
Boolean

Boolean

sql

* | SELECT bool_and(request_time < 100)

bool_or

The bool_or function checks whether any log meets the specified condition. If yes, the function returns true.

sql

bool_or(boolean expression)

Parameter	Description
boolean expression	The parameter value can be a Boolean expression.
Return value type	Description
The parameter value can be a Boolean expression.

Example: Check whether any request duration is less than 20 seconds. If yes, the function returns true.

sql

* | SELECT bool_or(request_time < 100)

checksum

Example: Check whether any request duration is less than 20 seconds. If yes, the function returns true.

sql

checksum(x)

Parameter	Description
x	The parameter value can be of an arbitrary data type.
Return value type	Description
String (Base64-encoded)

String (Base64-encoded)

sql

* | SELECT checksum(request_method) AS request_method

count

The count function returns the number of logs.

Parameter	Description
x	The count function returns the number of logs.
Return value type	Description
Integer

Example: Return the total number of logs.

sql

count(*)

Example: Return the total number of logs.

sql

count(1)

Example: Return the number of logs in which the value of the x field is not null.

sql

count(x)

Example 1: Return the page views (PVs) of a website. Query statement:

sql

* | SELECT count(*) AS PV

Example 2: Return the number of logs in which the value of the request_method field is not null. Query statement:

sql

* | SELECT count(request_method) AS count

count_if

The count_if function returns the number of logs that meet the specified condition.

sql

count_if(boolean expression)

Parameter	Description
boolean expression	Example: Return the number of logs in which the value of the request_uri field ends with file-0.
Return value type	Description
Integer

Example: Return the number of logs in which the value of the request_uri field ends with file-0. Query statement:

sql

* | SELECT count_if(request_uri like '%file-0') AS count

geometric_mean

The geometric_mean function returns the geometric mean of the values of the x field.

sql

geometric_mean(x)

Parameter	Description
x	Example: Return the geometric mean of request durations.
Return value type	Description
Double

Example: Return the geometric mean of request durations. Query statement:

sql

* | SELECT geometric_mean(request_time) AS time

every

The every function checks whether all logs meet the specified condition. If yes, the function returns true. This function is equivalent to the bool_and function.

sql

every(boolean expression)

Parameter	Description
boolean expression	The parameter value can be a Boolean expression.
Return value type	Description

示例：判断所有请求的时间是否都小于 100 秒。如果是，则返回 true。 Query statement:

sql

* | SELECT every(request_time < 100)

kurtosis

The every function checks whether all logs meet the specified condition. If yes, the function returns true. This function is equivalent to the bool_and function.

sql

kurtosis(x)

Parameter	Description
x	The parameter value can be of the double or bigint data type.
Return value type	Description
The parameter value can be of the double or bigint data type.

The parameter value can be of the double or bigint data type. Query statement:

sql

*| SELECT kurtosis(request_time)

map_union

The parameter value can be of the double or bigint data type.

sql

map_union(x)

Parameter	Description
x	The parameter value can be of the map data type.
Return value type	Description
map

Perform a union operation on the maps of the etl_context field and randomly return one of the maps. Sample field:

sql

etl_context: {
project:"datalab-148****6461-cn-chengdu"
logstore:"internal-etl-log"
consumer_group:"etl-83****4d1965"
consumer:"etl-b2d40ed****c8d6-291294"
shard_id:"0" }

Query statement:

sql

* | SELECT map_union(try_cast(json_parse(etl_context) AS map(varchar,varchar)))

max

The max function returns the maximum value of the x field.

Return the maximum value of the x field.

sql

max(x)

Query the largest N values of the x field and return an array.

sql

max(x, n)

Parameter	Description
x	PQuery the largest N values of the x field and return an array.
n	The parameter value must be a positive integer.
Return value type	Description
The return value type is the same as that of the parameter value.

Example 1: Return the longest request duration. Query statement:

sql

* | SELECT max(request_time) AS max_request_time

Example 2: Return the 10 longest request durations. Query statement:

sql

* | SELECT max(request_time,10) AS "top 10"

max_by

Example 2: Return the 10 longest request durations.

Return the value of the x field that corresponds to the maximum value of the y field.

sql

max_by(x, y)

Return the values of the x field that correspond to the largest N values of the y field. The function returns an array.

sql

max_by(x, y, n)

Parameter	Description
x	The parameter value can be of an arbitrary data type.
y	The parameter value can be of an arbitrary data type.
n	The parameter value must be a positive integer.
Return value type	Description
The return value type is the same as that of the parameter value.

Example 1: Return the point in time of the order that has the highest value. Query statement:

sql

* | SELECT max_by(UsageEndTime, PretaxAmount) as time

Example 2: Return the request methods of the three requests that have the longest request durations. Query statement:

sql

* | SELECT max_by(request_method,request_time,3) AS method

min

Example 2: Return the request methods of the three requests that have the longest request durations.

Return the minimum value of the x field.

sql

min(x)

Query the smallest N values of the x field and return an array.

sql

min(x,n)

Parameter	Description
x	The parameter value can be of an arbitrary data type.
n	The parameter value must be a positive integer.
Return value type	Description
The return value type is the same as that of the parameter value.

Example 1: Return the shortest request duration. Query statement:

sql

* | SELECT min(request_time) AS min_request_time

Example 2: Return the 10 shortest request durations. Query statement:

sql

* | SELECT min(request_time,10)

min_by

Return the value of the x field that corresponds to the minimum value of the y field.

sql

min_by(x, y)

Return the value of the x field that corresponds to the minimum value of the y field.

sql

min_by(x, y, n)

Parameter	Description
x	The parameter value can be of an arbitrary data type.
y	The parameter value can be of an arbitrary data type.
n	The parameter value can be of an arbitrary data type.
Return value type	Description
The return value type is the same as that of the parameter value.

The skewness function returns the skewness of the values of the x field. Query statement:

sql

* | SELECT min_by(request_method,request_time) AS method

The skewness function returns the skewness of the values of the x field.。 Query statement:

sql

* | SELECT min_by(request_method,request_time,3) AS method

skewness

The skewness function returns the skewness of the values of the x field.

sql

skewness(x)

Parameter	Description
x	The parameter value can be of the double or bigint data type.
Return value type	Description
The parameter value can be of the double or bigint data type.

Example: Return the skewness of request durations. Query statement:

sql

* | SELECT skewness(request_time) AS skewness

sum

The sum function returns the sum of the values of the x field.

sql

sum(x)

Parameter	Description
x	The parameter value can be of the double, bigint, decimal, or real data type.
Return value type	Description
The return value type is the same as that of the parameter value.

Example: Return the daily page views (PVs) of a website. Query statement:

sql

* | SELECT date_trunc('day',__time__) AS time, sum(body_bytes_sent) AS body_bytes_sent GROUP BY time ORDER BY time

Overview ​

Functions ​

Examples ​

arbitrary ​

avg ​

bitwise_and_agg ​

bitwise_or_agg ​

bool_and ​

bool_or ​

checksum ​

count ​

count_if ​

geometric_mean ​

every ​

kurtosis ​

map_union ​

max ​

max_by ​

min ​

min_by ​

skewness ​

sum ​