Functions and operators supported in SQL and SPL - Simple Log Service

This topic describes the functions and operators that are supported in the Structured Query Language (SQL) and Simple Log Service Processing Language (SPL).

Functions and operators

Note

You can view the usage of functions and operators based on the features and languages that you want to use. In the following tables, ticks (√) indicate supported, and crosses (×) indicate not supported.

SQL is a widely used query and analysis language. You can use SQL in analysis scenarios such as filtering, transformation, statistics collection, and aggregation of structured data. You can use SQL statements to query and analyze logs in Simple Log Service.
SPL is a pipeline query and processing language. You can use SPL in scenarios such as scan-based query and streaming consumption. You can use SPL statements to preprocess and query data.

Aggregate functions

Function	Description	Supported in SQL	Supported in SPL
arbitrary function	Returns a random, non-null value of the x field.	√	×
avg function	Calculates the average of the values in the x field.	√	×
bitwise_and_agg function	Returns the result of the bitwise AND operation on the values in the x field.	√	×
bitwise_or_agg function	Returns the result of the bitwise OR operation on the values in the x field.	√	×
bool_and function	Checks whether all logs meet the specified condition. If yes, the function returns true. This function is equivalent to the every function.	√	×
bool_or function	Checks whether a log that meets the specified condition exists. If yes, the function returns true.	√	×
checksum function	Calculates the checksum of the values in the x field.	√	×
count function	Counts the number of logs.	√	×
	Counts the number of logs. This function is equivalent to the count(*) function.	√	×
	Counts the number of logs whose value of the x field is not NULL.	√	×
count_if function	Counts the number of logs that meet the specified condition.	√	×
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.	√	×
geometric_mean function	Calculates the geometric mean of the values in the x field.	√	×
kurtosis function	Calculates the kurtosis of the values in the x field.	√	×
map_union function	Returns the result of the union operation on the specified maps. If a key exists in multiple input maps, the function randomly returns one of the values of the key.	√	×
max function	Queries the largest value of the x field.	√	×
max function	Queries the n largest values in the x field. The function returns an array.	√	×
max_by function	Queries the value of x that is associated with the largest value of the y field.	√	×
max_by function	Queries the values in x that are associated with the n largest values in the y field. The function returns an array.	√	×
min function	Queries the smallest value of the x field.	√	×
min function	Queries the n smallest values in the x field. The function returns an array.	√	×
min_by function	Queries the value of x that is associated with the smallest value of the y field.	√	×
min_by function	Queries the values in x that are associated with the n smallest values in the y field. The function returns an array.	√	×
skewness function	Calculates the skewness of the values in the x field.	√	×
sum function	Calculates the sum of the values in the x field.	√	×

String functions

Function	Description	Supported in SQL	Supported in SPL
chr function	Converts an ASCII code to characters.	√	√
codepoint function	Converts characters to an ASCII code.	√	√
concat function	Concatenates multiple strings into one string.	√	√
from_utf8 function	Decodes a binary string into a UTF-8-encoded string. Invalid UTF-8 sequences are replaced with the default replacement character U+FFFD.	√	√
from_utf8 function	Decodes a binary string into a UTF-8 encoded string. Invalid UTF-8 sequences are replaced with a custom string.	√	√
length function	Returns the length of a string.	√	√
levenshtein_distance function	Returns the minimum edit distance between x and y.	√	×
lower function	Converts the characters in a string to lowercase letters.	√	√
lpad function	Left pads a string to a specified length by using a specified character and returns the result string.	√	√
ltrim function	Removes spaces from the start of a string.	√	√
normalize function	Transforms a string by using the NFC normalization form.	√	×
position function	Returns the position of a specified substring in a string.	√	×
replace function	Replaces the matched characters in a string with specified characters.	√	√
replace function	Removes the matched characters from a string.	√	√
reverse function	Returns a string in reverse order.	√	√
rpad function	Right pads a string to a specified length by using a specified character and returns the result string.	√	√
rtrim function	Removes spaces from the end of a string.	√	√
split function	Splits a string by using a specified delimiter and returns a set of substrings.	√	√
split function	Splits a string by using a specified delimiter and returns a set of substrings. The number of substrings that can be generated is specified by limit.	√	√
split_part function	Splits a string by using a specified delimiter and returns the substring at a specified position.	√	√
split_to_map function	Splits a string by using the first specified delimiter, and then splits the string by using the second specified delimiter.	√	√
strpos function	Returns the position of a specified substring in a string. This function is equivalent to the position(sub_string in x) function.	√	√
substr function	Returns the substring at a specified position in a string. The length of the substring is specified.	√	√
substr function	Returns the substring at a specified position in a string.	√	√
to_utf8 function	Converts a string to a UTF-8 representation.	√	√
trim function	Removes spaces from the start and end of a string.	√	√
upper function	Converts the characters in a string to uppercase letters.	√	√

Date and time functions

Function	Description	Supported in SQL	Supported in SPL
current_date function	Returns the current date.	√	×
current_time function	Returns the current time and time zone.	√	×
current_timestamp function	Returns the current date, time, and time zone.	√	×
current_timezone function	Returns the current time zone.	√	×
date function	Returns the date part of a datetime expression.	√	×
date_format function	Converts a datetime expression that can return a timestamp value to a datetime expression in a specified format.	√	√
date_parse function	Converts a datetime string to a datetime expression that can return a timestamp value and is in a specified format.	√	√
from_iso8601_date function	Converts a date expression in the ISO 8601 format to a date expression that can return a date value.	√	×
from_iso8601_timestamp function	Converts a datetime expression in the ISO 8601 format to a datetime expression that can return a timestamp value.	√	×
from_unixtime function	Converts a UNIX timestamp to a datetime expression that can return a timestamp value and does not contain a time zone.	√	√
	Converts a UNIX timestamp to a datetime expression that can return a timestamp value and contains a time zone.	√	×
	Converts a UNIX timestamp to a datetime expression that can return a timestamp value and contains a time zone. In the datetime expression, the values for hours and minutes indicate the offset of the time zone.	√	×
localtime function	Returns the local time.	√	×
localtimestamp function	Returns the local date and time.	√	×
now function	Returns the current date and time. This function is equivalent to the current_timestamp function.	√	×
to_iso8601 function	Converts a datetime expression that can return a date or timestamp value to a datetime expression in the ISO 8601 format.	√	×
to_unixtime function	Converts a datetime expression that can return a timestamp value to a UNIX timestamp.	√	√
day function	Returns the day of the month from a datetime expression. This function is equivalent to the day_of_month function.	√	×
day_of_month function	Returns the day of the month from a datetime expression. This function is equivalent to the day function.	√	×
day_of_week function	Returns the day of the week from a datetime expression. This function is equivalent to the dow function.	√	√
day_of_year function	Returns the day of the year from a datetime expression. This function is equivalent to the doy function.	√	√
dow function	Returns the day of the week from a datetime expression. This function is equivalent to the day_of_week function.	√	√
doy function	Returns the day of the year from a datetime expression. This function is equivalent to the day_of_year function.	√	√
extract function	Returns the specified field from a datetime expression. The field can be a date or time.	√	×
hour function	Returns the hour of the day from a datetime expression. The 24-hour clock is used.	√	√
minute function	Returns the minute of the hour from a datetime expression.	√	√
month function	Returns the month of the year from a datetime expression.	√	√
quarter function	Returns the quarter of the year on which a specified date falls.	√	√
second function	Returns the second of the minute from a datetime expression.	√	√
timezone_hour function	Returns the offset of the time zone in hours.	√	×
timezone_minute function	Returns the offset of the time zone in minutes.	√	×
week function	Returns the week of the year on which a specified date falls. This function is equivalent to the week_of_year function.	√	×
week_of_year function	Returns the week of the year on which a specified date falls. This function is equivalent to the week function.	√	×
year function	Returns the year of a specified date.	√	√
year_of_week function	Returns the year on which a specified date falls in the ISO week date system. This function is equivalent to the yow function.	√	√
yow function	Returns the year on which a specified date falls in the ISO week date system. This function is equivalent to the year_of_week function.	√	√
date_trunc function	Truncates a datetime expression based on the time unit that you specify. The expression can be truncated based on the millisecond, second, minute, hour, day, month, or year.	√	×
date_add function	Adds N to the value of the x field based on the unit that you specify.	√	√
date_diff function	Returns the difference between two time expressions. For example, you can calculate the difference between x and y based on the time unit that you specify.	√	√
time_series function	Adds a value to the field that has no value returned in the specified time window.	√	×

JSON functions

Function	Description	Supported in SQL	Supported in SPL
json_array_contains function	Checks whether a JSON array contains a specified value.	√	√
json_array_get function	Obtains the element that corresponds to an index in a JSON array.	√	×
json_array_length function	Obtains the number of elements in a JSON array.	√	√
json_extract function	Obtains a set of JSON values from a JSON object or a JSON array. The JSON values can be of the array or object type.	√	√
json_extract_scalar function	Obtains a set of scalar values from a JSON object or a JSON array. The scalar values can be of the string, integer, or Boolean type. This function is similar to the json_extract function.	√	√
json_format function	Converts JSON data to a string.	√	√
json_parse function	Converts a string to JSON data.	√	√
json_size function	Obtains the number of elements in a JSON object or a JSON array.	√	√

Regular expression functions

Function	Description	Supported in SQL	Supported in SPL
regexp_extract_all function	Extracts the substrings that match a specified regular expression from a specified string and returns an array of all matched substrings.	√	×
regexp_extract_all function	Extracts the substrings that match a specified regular expression from a specified string and returns an array of substrings that match the nth capturing group in the regular expression.	√	×
regexp_extract function	Extracts the first substring that matches a specified regular expression from a specified string and returns the substring.	√	√
regexp_extract function	Extracts the substrings that match a specified regular expression from a specified string and returns the first substring that matches the nth capturing group in the regular expression.	√	√
regexp_like function	Checks whether a specified string matches a specified regular expression.	√	√
regexp_replace function	Deletes the substrings that match a specified regular expression from a specified string and returns the substrings that remain.	√	√
regexp_replace function	Replaces the substrings that match a specified regular expression in a specified string and returns the result string.	√	√
regexp_split function	Splits a specified string into multiple substrings by using a specified regular expression and returns an array of the substrings.	√	×

Interval-valued comparison and periodicity-valued comparison functions

Function	Description	Supported in SQL	Supported in SPL
compare function	Compares the calculation result of the current time period with the calculation result of a time period n seconds before.	√	×
compare function	Compares the calculation result of the current time period with the calculation results of multiple time periods n1, n2, and n3 seconds before.	√	×
ts_compare function	Compares the calculation result of the current time period with the calculation result of a time period n seconds before. Important The query and analysis results of the ts_compare function must be grouped by the time column by using the GROUP BY clause.	√	×
ts_compare function	Compares the calculation result of the current time period with the calculation results of multiple time periods n1, n2, and n3 seconds before.	√	×

Array functions and operators

Function	Description	Supported in SQL	Supported in SPL
Subscript operator	Returns the element whose index is x in an array.	√	×
array_agg function	Returns an array that consists of all values in the x field.	√	×
array_distinct function	Removes duplicate elements from an array.	√	√
array_except function	Calculates the difference of two arrays.	√	√
array_intersect function	Calculates the intersection of two arrays.	√	√
array_join function	Concatenates the elements of an array into a string by using a specified delimiter. If the array contains a null element, the null element is ignored.	√	√
array_join function	Concatenates the elements of an array into a string by using a specified delimiter. If the array contains a null element, the null element is replaced with the value of the null_replacement parameter.	√	√
array_max function	Returns the maximum value in an array.	√	√
array_min function	Returns the minimum value in an array.	√	√
array_position function	Returns the index of a specified element in an array. The index starts from 1. If the specified element does not exist, the function returns 0.	√	√
array_remove function	Removes a specified element from an array.	√	√
array_sort function	Sorts the elements in an array in ascending order. If the array contains a null element, the null element is placed at the end.	√	√
array_transpose function	Transposes a matrix and returns a new two-dimensional array that consists of the elements in the matrix. The elements are located by using the same indexes.	√	×
array_union function	Calculates the union of two arrays.	√	×
cardinality function	Counts the number of elements in an array.	√	√
concat function	Concatenates multiple arrays into one array.	√	×
contains function	Checks whether an array contains a specified element. If the array contains the specified element, the function returns true.	√	×
element_at function	Returns the element whose index is y in an array.	√	×
filter function	Filters elements in an array based on a lambda expression and returns elements that match the lambda expression.	√	√
flatten function	Transforms a two-dimensional array into a one-dimensional array.	√	×
reduce function	Returns the sum of the elements in an array based on a lambda expression.	√	√
reverse function	Reverses the elements in an array.	√	√
sequence function	Returns an array of elements within a specified range. The elements are consecutive and incremental. The incremental step is 1, which is the default value.	√	√
sequence function	Returns an array of elements within a specified range. The elements are consecutive and incremental. The incremental step is a custom value.	√	√
shuffle function	Shuffles the elements in an array.	√	√
slice function	Returns a subset of an array.	√	√
transform function	Transforms each element in an array by using a lambda expression.	√	√
zip function	Merges multiple arrays into a two-dimensional array. Elements that have the same index in the input arrays form a new array in the two-dimensional array.	√	√
zip_with function	Merges two arrays into one array by using a lambda expression.	√	×

Map functions and operators

Function	Description	Supported in SQL	Supported in SPL
Subscript operator	Retrieves the value of a key from a map.	√	×
cardinality function	Returns the size of a map.	√	×
element_at function	Returns the value of a key in a map.	√	×
histogram function	Groups query and analysis results and returns data in the JSON format.	√	×
histogram_u function	Groups query and analysis results and returns data in multiple rows and multiple columns.	√	×
map function	Returns an empty map.	√	×
map function	Returns a map that is created by using two arrays.	√	×
map_agg function	Returns a map that is created by using x and y. x is the key in the map. y is the value of the key in the map. If y has multiple values, a random value is extracted as the value of the key.	√	×
map_concat function	Returns the union of multiple maps.	√	×
map_filter function	Filters elements in a map based on a lambda expression and returns a new map.	√	×
map_keys function	Returns an array that consists of all the keys of a map.	√	×
map_values function	Returns an array that consists of all the values of a map.	√	×
multimap_agg function	Returns a multimap that is created by using x and y. x is the key in the multimap. y is the value of the key in the multimap, and the value is of the array type. If y has multiple values, all the values are extracted as the values of the key.	√	×

Mathematical calculation functions

Function	Description	Supported in SQL	Supported in SPL
abs function	Calculates the absolute value of x.	√	√
acos function	Calculates the arc cosine of x.	√	√
asin function	Calculates the arc sine of x.	√	√
atan function	Calculates the arc tangent of x.	√	√
atan2 function	Calculates the arc tangent of x divided by y.	√	√
cbrt function	Calculates the cube root of x.	√	√
ceil function	Rounds x up to the nearest integer. This function is an alias of the ceiling function.	√	√
ceiling function	Rounds x up to the nearest integer.	√	√
cos function	Calculates the cosine of x.	√	√
cosh function	Calculates the hyperbolic cosine of x.	√	√
cosine_similarity function	Calculates the cosine similarity between x and y.	√	×
degrees function	Converts an angle in radians to its equivalent in degrees.	√	√
e function	Returns the value of e, which is the base of the natural logarithm.	√	√
exp function	Raises e to the power of x.	√	×
floor function	Rounds x down to the nearest integer.	√	√
from_base function	Converts x to a base-y number.	√	√
ln function	Calculates the natural logarithm of x.	√	√
infinity function	Returns a value that represents positive infinity.	√	√
is_nan function	Determines whether x is Not a Number (NaN).	√	√
log2 function	Calculates the base-2 logarithm of x.	√	√
log10 function	Calculates the base-10 logarithm of x.	√	√
log function	Calculates the base-y logarithm of x.	√	×
mod function	Calculates the remainder of x divided by y.	√	√
nan function	Returns a value that is NaN.	√	√
pi function	Returns the value of π to 15 decimal places.	√	√
pow function	Raises x to the power of y. This function is an alias of the power function.	√	√
power function	Raises x to the power of y.	√	√
radians function	Converts an angle in degrees to its equivalent in radians.	√	√
rand function	Returns a random number.	√	√
random function	Returns a random number in the range [0,1).	√	√
random function	Returns a random number in the range [0,x).	√	√
round function	Rounds x to the nearest integer.	√	√
round function	Rounds x to the nearest decimal with n decimal places.	√	√
sign function	Returns the sign of x. Valid values: 1, 0, and -1.	√	×
sin function	Calculates the sine of x.	√	√
sqrt function	Calculates the square root of x.	√	√
tan function	Calculates the tangent of x.	√	√
tanh function	Calculates the hyperbolic tangent of x.	√	√
to_base function	Converts x to a base-y string.	√	×
truncate function	Removes the fractional part of x.	√	√
width_bucket function	Divides a numeric range into buckets of equal width and returns the bucket number of x.	√	×
width_bucket function	Returns the bucket number of x in the range of buckets that are specified by an array.	√	×

Mathematical statistics functions

Function	Description	Supported in SQL	Supported in SPL
corr function	Returns the coefficient of correlation between x and y. The return value is in the range of [0,1].	√	×
covar_pop function	Returns the population covariance of x and y.	√	×
covar_samp function	Returns the sample covariance of x and y.	√	×
stddev function	Returns the sample standard deviation of x. This function is equivalent to the stddev_samp function.	√	×
stddev_samp function	Returns the sample standard deviation of x.	√	×
stddev_pop function	Returns the population standard deviation of x.	√	×
variance function	Returns the sample variance of x. This function is equivalent to the var_samp function.	√	×
var_samp function	Returns the sample variance of x.	√	×
var_pop function	Returns the population variance of x.	√	×
regr_intercept function	Returns the y-intercept of the line for the linear equation that is determined by the `(x,y)` pair.	√	×
regr_slope function	Returns the slope of the line for the linear equation that is determined by the `(x,y)` pair.	√	×
beta_cdf function	Returns a value for the beta distribution. The function uses the following formula: P(N <= v; α, β) where α and β are parameters for the beta CDF.	√	×
binomial_cdf function	Returns a value for the binomial distribution. The function uses the following formula: P(N <= v; x, y) where x indicates the number of trials, and y indicates the probability of success (POS) of a trial.	√	×
cauchy_cdf function	Returns a value for the Cauchy distribution. The function uses the following formula: P(N <= v; x, y) where x is the location parameter indicating the peak of the distribution, and y is the scale parameter.	√	×
chi_squared_cdf function	Returns a value for the chi-square distribution. The function uses the following formula: P(N <= v; k) where k indicates the degree of freedom.	√	×
inverse_beta_cdf function	Returns a value for the inverse of the beta distribution. p indicates the result of the beta CDF, which uses the P(N <= v; α, β) formula. The inverse inverse_beta_cdf function calculates v.	√	×
inverse_binomial_cdf function	Returns a value for the inverse of the binomial distribution. p indicates the result of the binomial CDF, which uses the P(N <= v; x, y) formula. The inverse inverse_binomial_cdf function calculates v.	√	×
inverse_cauchy_cdf function	Returns a value for the inverse of the Cauchy distribution. p indicates the result of the Cauchy CDF, which uses the P(N <= v; x, y) formula. The inverse inverse_cauchy_cdf function calculates v.	√	×
inverse_chi_squared_cdf function	Returns a value for the inverse of the chi-square distribution. p indicates the result of the chi-square CDF, which uses the P(N <= v; k) formula. The inverse inverse_chi_squared_cdf function calculates v.	√	×
inverse_laplace_cdf function	Returns a value for the inverse of the Laplace distribution. p indicates the result of the Laplace CDF, which uses the P(N <= v; μ, b) formula. The inverse inverse_laplace_cdf function calculates v.	√	×
inverse_normal_cdf function	Returns a value for the inverse of the normal distribution. p indicates the result of the normal CDF, which uses the P(N < v; x, y) formula. The inverse inverse_normal_cdf function calculates v.	√	×
inverse_poisson_cdf function	Returns a value for the inverse of the Poisson distribution. p indicates the result of the Poisson CDF, which uses the P(N <= v; λ) formula. The inverse inverse_poisson_cdf function calculates v.	√	×
inverse_weibull_cdf function	Returns a value for the inverse of the Weibull distribution. p indicates the result of the Weibull CDF, which uses the P(N <= v; x, y) formula. The inverse inverse_weibull_cdf function calculates v.	√	×
laplace_cdf function	Returns a value for the Laplace distribution. The function uses the following formula: P(N <= v; μ, b) where μ is the location parameter, and b is the scale parameter.	√	×
normal_cdf function	Returns a value for the normal distribution. The function uses the following formula: P(N < v; x, y) where x indicates the mean value for the normal distribution, and y indicates the standard deviation for the normal distribution.	√	×
poisson_cdf function	Returns a value for the Poisson distribution. The function uses the following formula: P(N <= v; λ) where λ indicates the average probability of random events.	√	×
weibull_cdf function	Returns a value for the Weibull distribution. The function uses the following formula: P(N <= v; x, y) where x is the scale parameter, and y is the shape parameter.	√	×

Type conversion functions

Function	Description	Supported in SQL	Supported in SPL
cast function	Converts the values in the x field to a specified data type. If the cast function fails to convert a value, the query statement that uses this function fails.	√	√
try_cast function	Converts the values in the x field to a specified data type. If the try_cast function fails to convert a value, the function returns NULL. The query statement that uses this function skips the value and continues to run. Note Logs may contain dirty data. When you query logs, we recommend that you use the try_cast function. This way, conversion failures will not cause your query statement to fail.	√	×
typeof function	Returns the data type of the x field.	√	×

Window functions

Function	Description	Supported in SQL	Supported in SPL
Aggregate functions	You can use all aggregate functions in window functions. For more information, see Aggregate functions.	√	×
cume_dist function	Calculates the cumulative distribution of each value in a partition. The result is obtained by using division. The numerator is the number of rows whose field values are less than or equal to the field value of the specified row. The specified row is also counted. The denominator is the total number of rows in the partition. The calculation is based on the order of the rows in the partition. The return value is in the range of (0,1].	√	×
dense_rank function	Calculates the rank of each value of a specified field in a partition. If two values are the same, the values are assigned the same rank. The ranks are consecutive. For example, if two values are the same and assigned the same rank of 1, the next rank is 2.	√	×
ntile function	Divides the rows in each partition into n groups based on a specifeid order.	√	×
percent_rank function	Calculates the percentage rank of each row in a partition.	√	×
rank function	Calculates the rank of each value of a specified field in a partition. If two values are the same, the values are assigned the same rank. The ranks are not consecutive. For example, if two values are the same and assigned the same rank of 1, the next rank is 3.	√	×
row_number function	Calculates the rank of each value of a specified field in a partition. Each value is assigned a unique rank. The ranks start from 1. For example, if three values are the same, the values are assigned the ranks of 1, 2, and 3.	√	×
first_value function	Returns the value of a specified field in the first row of each partition.	√	×
last_value function	Returns the value of a specified field in the last row of each partition.	√	×
lag function	Returns the value of a specified field in the row that is at the specified offset before the current row in a partition. If no row exists at the specified offset before the current row, the value that is specified by the default_value parameter is returned.	√	×
lead function	Returns the value of a specified field in the row that is at the specified offset after the current row in a partition. If no row exists at the specified offset after the current row, the value that is specified by the default_value parameter is returned.	√	×
nth_value function	Returns the value of a specified field in the row that is at the specified offset from the beginning of a partition.	√	×

IP functions

Function	Description	Supported in SQL	Supported in SPL
ip_to_city function	Identifies the city to which an IP address belongs. The function returns the Chinese name of a city.	√	×
ip_to_city function	Identifies the city to which an IP address belongs. The function returns the administrative division code of a city.	√	×
ip_to_city_geo function	Identifies the longitude and latitude of the city to which an IP address belongs. The function returns the longitude and latitude of a city. Each city has only one set of coordinates.	√	×
ip_to_country function	Identifies the country or region to which an IP address belongs. The function returns the Chinese name of a country or region.	√	×
ip_to_country function	Identifies the country or region to which an IP address belongs. The function returns the code of a country or region.	√	×
ip_to_country_code function	Identifies the country or region to which an IP address belongs. The function returns the code of a country or region.	√	×
ip_to_domain function	Checks whether an IP address is a private or public address.	√	×
ip_to_geo function	Identifies the longitude and latitude of the location of an IP address.	√	×
ip_to_provider function	Identifies the Internet service provider (ISP) of an IP address.	√	×
ip_to_province function	Identifies the state to which an IP address belongs. The function returns the Chinese name of a state.	√	×
ip_to_province function	Identifies the state to which an IP address belongs. The function returns the administrative division code of a state.	√	×
ip_prefix function	Returns the prefix of an IP address.	√	×
is_prefix_subnet_of function	Checks whether a CIDR block is a subnet of a specified CIDR block.	√	×
is_subnet_of function	Checks whether an IP address is in a specified CIDR block.	√	×
ip_subnet_max function	Returns the largest IP address in a CIDR block.	√	×
ip_subnet_min function	Returns the smallest IP address in a CIDR block.	√	×
ip_subnet_range function	Returns the range of a CIDR block.	√	×
ipv6_to_city function	Identifies the city to which an IPv6 address belongs.	√	×
ipv6_to_city_code function	Identifies the administrative division code of the city to which an IPv6 address belongs.	√	×
ipv6_to_city_geo function	Identifies the longitude and latitude of the city to which an IPv6 address belongs.	√	×
ipv6_to_country function	Identifies the country or region to which an IPv6 address belongs.	√	×
ipv6_to_country_code function	Identifies the code of the country or region to which an IPv6 address belongs.	√	×
ipv6_to_domain function	Checks whether an IPv6 address is a private or public address.	√	×
ipv6_to_provider function	Identifies the ISP of an IPv6 address.	√	×
ipv6_to_province function	Identifies the province to which an IPv6 address belongs.	√	×
ipv6_to_province_code function	Identifies the administrative division code of the province to which an IPv6 address belongs.	√	×

URL functions

Function	Description	Supported in SQL	Supported in SPL
url_encode function	Encodes a URL.	√	√
url_decode function	Decodes a URL.	√	√
url_extract_fragment function	Extracts the fragment from a URL.	√	√
url_extract_host function	Extracts the host from a URL.	√	√
url_extract_parameter function	Extracts the value of a specified parameter in the query string from a URL.	√	√
url_extract_path function	Extracts the path from a URL.	√	√
url_extract_port function	Extracts the port number from a URL.	√	√
url_extract_protocol function	Extracts the protocol from a URL.	√	√
url_extract_query function	Extracts the query string from a URL.	√	√

Approximate functions

Function	Description	Supported in SQL	Supported in SPL
approx_distinct function	Estimates the number of distinct values in x. The default standard error is 2.3%.	√	×
approx_distinct function	Estimates the number of distinct values in x. You can specify a custom standard error.	√	×
approx_percentile function	Sorts the values in x in ascending order, and returns the value at the approximate position specified by percentage.	√	×
	Sorts the values in x in ascending order, and returns the values at the approximate positions specified by percentage01 and percentage02.	√	×
	Sorts the values in x in ascending order based on the products of the values in x and the weight, and returns the value at the approximate position specified by percentage.	√	×
	Sorts the values in x in ascending order based on the products of the values in x and the weight, and returns the values at the approximate positions specified by percentage01 and percentage02.	√	×
	Sorts the values in x in ascending order based on the products of the values in x and the weight, and returns the value at the approximate position specified by percentage. You can specify the accuracy of the return value.	√	×
numeric_histogram function	Returns the estimated histogram of x based on the number of histogram columns. The number is specified by bucket. The return value is of the JSON type.	√	×
numeric_histogram function	Returns the estimated histogram of x based on the number of histogram columns. The number is specified by bucket. The return value is of the JSON type. You can specify a weight for the values in x.	√	×
numeric_histogram_u function	Returns the estimated histogram of x based on the number of histogram columns. The number is specified by bucket. The return value contains multiple rows and columns.	√	×

Binary functions

Function	Description	Supported in SQL	Supported in SPL
from_base64 function	Decodes a Base64-encoded string into a binary number.	√	√
from_base64url function	Decodes a Base64-encoded string into a binary number by using URL reserved characters.	√	×
from_big_endian_64 function	Decodes a binary number in big endian into a bigint value.	√	×
from_hex function	Decodes a hexadecimal number into a binary number.	√	√
length function	Returns the length of a binary number.	√	×
md5 function	Computes the MD5 hash value for a binary number.	√	√
to_base64 function	Encodes a binary number into a Base64 string representation.		√
to_base64url function	Encodes a binary number into a Base64 string representation by using URL reserved characters.	√	×
to_hex function	Encodes a binary number into a hexadecimal number.	√	√
to_big_endian_64 function	Encodes a bigint value into a binary number in big endian.	√	×
sha1 function	Computes the SHA-1 hash value for a binary number.	√	√
sha256 function	Computes the SHA-256 hash value for a binary number.	√	√
sha512 function	Computes the SHA-512 hash value for a binary number.	√	√
xxhash64 function	Computes the xxhash64 hash value for a binary number.	√	√

Bitwise functions

Function	Description	Supported in SQL	Supported in SPL
bit_count function	Returns the number of bits 1 in x in binary representation.	√	√
bitwise_and function	Returns the result of the bitwise AND operation on x and y in binary representation.	√	√
bitwise_not function	Returns the result of the bitwise NOT operation on x in binary representation.	√	√
bitwise_or function	Returns the result of the bitwise OR operation on x and y in binary representation.	√	√
bitwise_xor function	Returns the result of the bitwise XOR operation on x and y in binary representation.	√	√

Geospatial functions

Function	Description	Supported in SQL	Supported in SPL
ST_AsText function	Returns the WKT representation of a geometry.	√	×
ST_Boundary function	Returns the closure of the combinatorial boundary of a geometry.	√	×
ST_Buffer function	Returns a geometry that represents all points whose distance from the specified geometry is less than or equal to the specified distance.	√	×
ST_Difference function	Returns a geometry that represents the point set difference of two specified geometries.	√	×
ST_Envelope function	Returns the bounding rectangular polygon of a geometry.	√	×
ST_ExteriorRing function	Returns a line string that represents the exterior ring of a geometry.	√	×
ST_Intersection function	Returns a geometry that represents the point set intersection of two specified geometries.	√	×
ST_SymDifference function	Returns a geometry that represents the point set symmetric difference of two specified geometries.	√	×
ST_Contains function	Returns true if no points of the second geometry lie in the exterior of the first geometry and at least one point of the interior of the first geometry lies in the interior of the second geometry.	√	×
ST_Crosses function	Returns true if two specified geometries have several interior points in common.	√	×
ST_Disjoint function	Returns true if two specified geometries do not share any portion of two-dimensional space.	√	×
ST_Equals function	Returns true if two specified geometries represent the same geometry.	√	×
ST_Intersects function	Returns true if two specified geometries share a portion of two-dimensional space.	√	×
ST_Overlaps function	Returns true if two specified geometries share space and have the same dimension but are not completely contained by each other.	√	×
ST_Relate function	Returns true if two specified geometries have a spatial relationship.	√	×
ST_Touches function	Returns true if two specified geometries have at least one point in common but their interiors do not intersect.	√	×
ST_Within function	Returns true if the first geometry is completely inside the second geometry.	√	×
ST_Area function	Calculates the projected area of a geometry on a two-dimensional plane by using the Euclidean distance method.	√	×
ST_Centroid function	Returns the point value that represents the mathematical centroid of a geometry.	√	×
ST_CoordDim function	Returns the coordinate dimension of a geometry.	√	×
ST_Dimension function	Returns the inherent dimension of a geometry. The inherent dimension must be less than or equal to the coordinate dimension.	√	×
ST_Distance function	Returns the minimum distance between two geometries.	√	×
ST_EndPoint function	Returns the last point of a line string.	√	×
ST_IsClosed function	Returns true if the start point of a line string coincides with the end point.	√	×
ST_IsEmpty function	Returns true if a geometry is empty.	√	×
ST_IsRing function	Returns true if a line string is closed and simple.	√	×
ST_Length function	Calculates the projected length of a line string on a two-dimensional plane by using the Euclidean distance method. If multiple line strings exist, the function returns the sum of the lengths of the multiple line strings.	√	×
ST_NumPoints function	Returns the number of points in a geometry.	√	×
ST_NumInteriorRing function	Returns the number of interior rings in a geometry.	√	×
ST_StartPoint function	Returns the first point of a line string.	√	×
ST_X function	Returns the X-coordinate of a specified point.	√	×
ST_XMax function	Returns the maximum first X-coordinate of a geometry.	√	×
ST_XMin function	Returns the minimum first X-coordinate of a geometry.	√	×
ST_Y function	Returns the Y-coordinate of a specified point.	√	×
ST_YMax function	Returns the maximum first Y-coordinate of a geometry.	√	×
ST_YMin function	Returns the minimum first Y-coordinate of a geometry.	√	×
bing_tile function	Returns a Bing tile based on the X-coordinate, Y-coordinate, and zoom level.	√	×
bing_tile function	Returns a Bing tile based on the quadtree key.	√	×
bing_tile_at function	Returns a Bing tile based on the latitude, longitude, and zoom level.	√	×
bing_tile_coordinates function	Returns the X- and Y-coordinates of a Bing tile.	√	×
bing_tile_polygon function	Returns the polygon format of a Bing tile.	√	×
bing_tile_quadkey function	Returns the quadtree key of a Bing tile.	√	×
bing_tile_zoom_level function	Returns the zoom level of a Bing tile.	√	×

Geo function

Function	Description	Supported in SQL	Supported in SPL
geohash function	Encodes latitudes and longitudes by using the Geohash algorithm.	√	×

Color functions

Function	Description	Supported in SQL	Supported in SPL
bar function	Returns a part of an ANSI bar chart. You can configure the width parameter to specify the width of the ANSI bar chart. However, you cannot configure the high_color or low_color parameter to specify the colors for the chart. The default values of the high_color and low_color parameters are used. The default value of the low_color parameter is red, and the default value of the high_color parameter is green. In addition, you can configure x to specify the length of the part that is returned by the function.	√	×
bar function	Returns a part of an ANSI bar chart. You can configure the width parameter to specify the width of the ANSI bar chart. You can also configure the high_color and low_color parameters to specify custom colors for the chart. In addition, you can configure x to specify the length of the part that is returned by the function.	√	×
color function	Converts a color string to a color type.	√	×
	Returns a color between high_color and low_color based on the portions of high_color and low_color. The portions are determined by the proportion of x between high and low.	√	×
	Returns a color between high_color and low_color based on the portions of high_color and low_color. The portions are determined by y.	√	×
render function	Returns results by using color rendering. If the Boolean expression evaluates to true, the function returns a green tick. If the Boolean expression evaluates to false, the function returns a red cross.	√	×
render function	Returns results by using custom color rendering.	√	×
rgb function	Returns a color value based on an RGB value.	√	×

HyperLogLog functions

Function	Description	Supported in SQL	Supported in SPL
approx_set function	Estimates the number of distinct values in the x field. The maximum standard error is 0.01625, which is the default value.	√	×
cardinality function	Converts HyperLogLog data to bigint data.	√	×
empty_approx_set function	Returns a null value of the HyperLogLog type. The maximum standard error is 0.01625, which is the default value.	√	×
merge function	Aggregates all HyperLogLog values.	√	×

Comparison operators

Operator	Description	Supported in SQL	Supported in SPL
Relational operators	Compares x and y. If the condition is met, true is returned.	√	√
ALL operator	If x meets all conditions, true is returned.	√	×
ANY operator	If x meets one of the conditions, true is returned.	√	×
BETWEEN operator	If x is between y and z, true is returned.	√	√
DISTINCT operator	x IS DISTINCT FROM y: If x is not equal to y, true is returned.	√	×
DISTINCT operator	x IS NOT DISTINCT FROM y: If x is equal to y, true is returned.	√	×
LIKE operator	Matches a specified character pattern in a string. The string is case-sensitive.	√	√
SOME operator	If x meets one of the conditions, true is returned.	√	×
GREATEST operator	Obtains the greater value of x and y.	√	×
LEAST operator	Obtains the smaller value of x and y.	√	×
NULL operator	x IS NULL: If x is null, true is returned.	√	×
NULL operator	x IS NOT NULL: If x is not null, true is returned.	√	×

Logical operators

Operator	Description	Supported in SQL	Supported in SPL
AND operator	If both x and y evaluate to true, true is returned.	√	√
OR operator	If either x or y evaluates to true, true is returned.	√	√
NOT operator	If x evaluates to false, true is returned.	√	√

Unit conversion functions

Function	Description	Supported in SQL	Supported in SPL
convert_data_size function	Converts a measurement from the current unit to the optimal unit. The system automatically determines the optimal unit and returns a measurement in the optimal unit. The returned result is of the string type. For example, you can convert 1,024 KB to 1 MB and 1,024 MB to 1 GB.	√	×
convert_data_size function	Converts a measurement from the current unit to a specified unit. The returned result is of the string type.	√	×
format_data_size function	Converts a measurement in byte to a measurement in a specified unit. The returned result is of the string type.	√	×
parse_data_size function	Converts a measurement from the current unit to a measurement in byte. The returned result is of the decimal type.	√	×
to_data_size_B function	Converts a measurement from the current unit to a measurement in byte. The returned result is of the double type.	√	×
to_data_size_KB function	Converts a measurement from the current unit to a measurement in KB. The returned result is of the double type.	√	×
to_data_size_MB function	Converts a measurement from the current unit to a measurement in MB. The returned result is of the double type.	√	×
to_data_size_GB function	Converts a measurement from the current unit to a measurement in GB. The returned result is of the double type.	√	×
to_data_size_TB function	Converts a measurement from the current unit to a measurement in TB. The returned result is of the double type.	√	×
to_data_size_PB function	Converts a measurement from the current unit to a measurement in PB. The returned result is of the double type.	√	×
format_duration function	Converts a time interval in seconds to a readable string.	√	×
parse_duration function	Converts a time interval to a time interval in the `0 00:00:00.000` format.	√	×
to_days function	Converts a time interval to a time interval in days.	√	×
to_hours function	Converts a time interval to a time interval in hours.	√	×
to_microseconds function	Converts a time interval to a time interval in microseconds.	√	×
to_milliseconds function	Converts a time interval to a time interval in milliseconds.	√	×
to_minutes function	Converts a time interval to a time interval in minutes.	√	×
to_most_succinct_time_unit function	Converts a time interval from the current unit to the optimal unit. The system automatically determines the optimal unit and returns a time interval in the optimal unit.	√	×
to_nanoseconds function	Converts a time interval to a time interval in nanoseconds.	√	×
to_seconds function	Converts a time interval to a time interval in seconds.	√	×

Window funnel function

Function	Description	Supported in SQL	Supported in SPL
window_funnel function	Searches for an event chain in a sliding time window and counts the maximum number of consecutive events in the event chain.	√	×

Lambda expressions

Simple Log Service allows you to define a lambda expression in an SQL statement or an SPL statement and pass the expression to a specified function. For more information, see Lambda expressions.

Conditional expressions

Expression	Description	Supported in SQL	Supported in SPL
CASE WHEN expression	Classifies data based on specified conditions.	√	√
IF expression	Classifies data based on specified conditions.	√	√
COALESCE expression	Returns the first non-null value of multiple expressions.	√	√
NULLIF expression	Evaluates whether the values of two expressions are the same. If the values are the same, null is returned. Otherwise, the value of the first expression is returned.	√	×
TRY expression	Captures errors that occur during the invocation of an expression and ensures that Simple Log Service can continue to query and analyze data even if errors occur.	√	√

Vector calculation functions

Function	Description	Supported in SQL	Supported in SPL
cosine_similarity function	Calculates the cosine similarity of two vectors. Valid values of the cosine similarity: [-1, 1]. If the value of the cosine similarity between two vectors is 1, the vectors are in the same direction. If the value of the cosine similarity between two vectors is -1, the vectors are in the opposite directions. If the value of the cosine similarity between two vectors is 0, one vector is vertical to the other vector.	√	×
inner_product or dot_product function	Calculates the inner product of two vectors.	√	×
l2_distance or euclidean_distance function	Calculates the Euclidean distance between two vectors.	√	×
vector_norm function	Calculates the norm of a vector. The norm of a vector is equivalent to the magnitude of the vector.	√	×
vector_sum function	Calculates the sum of all elements of a vector.	√	×
vector_mean function	Calculates the mean of a vector.	√	×
vector_variance function	Calculates the variance of a vector. Variance measures the dispersion of the elements of a vector. Variance is the average of the squared distances from each element of the vector to the mean.	√	×
vector_std_dev function	Calculates the standard deviation of a vector. The standard deviation of a vector is the positive square root of its variance. Variance measures how far each data point in a dataset is from the mean. A high variance value indicates that the data points are significantly spread out from the mean. A low variance value indicates that the data points are close to the mean.	√	×
vector_add function	Calculates the sum of two vectors. You can perform arbitrary linear transformation based on vector addition, vector subtraction, and vector scaling functions.	√	×
vector_subtract function	Calculates the difference between two vectors. You can perform arbitrary linear transformation based on vector addition, vector subtraction, and vector scaling functions.	√	×
vector_scale function	Calculates the result of vector scaling. You can perform arbitrary linear transformation based on vector addition, vector subtraction, and vector scaling functions.	√	×