SQL function description - Simple Log Service - Alibaba Cloud Documentation Center

This topic outlines the functions and operators supported by SQL and the Simple Log Service Processing Language (SPL).

Aggregate functions

```markdown

Function Name	Description	Support for SQL	Support for SPL
arbitrary function	Returns any non-empty value from x.	√	×
avg function	Computes the arithmetic average value of x.	√	×
bitwise_and_agg function	Returns the result of a bitwise AND operation on all values in x.	√	×
bitwise_or_agg function	Returns the result of a bitwise OR operation on all values in x.	√	×
bool_and function	Determines whether all logs satisfy the condition. If so, returns true. The bool_and function is equivalent to the every function.	√	×
bool_or function	Determines whether there exists a log that satisfies the condition. If so, returns true.	√	×
checksum function	Calculates the checksum of x.	√	×
count function	Tallies the number of logs.	√	×
	Tallies the number of logs, equivalent to count(*).	√	×
	Counts the number of logs with a non-null value in x.	√	×
count_if function	Counts the number of logs satisfying the specified condition.	√	×
every function	Determines whether all logs satisfy the condition. If so, returns true. The every function is equivalent to the bool_and function.	√	×
geometric_mean function	Computes the geometric mean of x.	√	×
kurtosis function	Calculates the kurtosis of x.	√	×
map_union function	Returns the union of a series of Map data. If the Map contains the same key, the returned key value is one of the values of the key.	√	×
max function	Finds the largest value in x.	√	×
max function	Finds the top n largest values in x and returns them as an array.	√	×
max_by function	Finds the x value associated with the largest y value.	√	×
max_by function	Finds the x values associated with the top n largest y values and returns them as an array.	√	×
min function	Finds the smallest value in x.	√	×
min function	Finds the top n smallest values in x and returns them as an array.	√	×
min_by function	Finds the x value associated with the smallest y value.	√	×
min_by function	Finds the x values associated with the top n smallest y values and returns them as an array.	√	×
skewness function	Calculates the skewness of x.	√	×
sum function	Calculates the total value of x.	√	×

String functions

Function Name	Description	Supported in SQL	Supported in SPL
chr function	Converts an ASCII code into the corresponding character.	√	√
codepoint function	Converts a character into its ASCII code.	√	√
concat function	Concatenates multiple strings into a single string.	√	√
from_utf8 function	Decodes a binary string into a UTF-8 encoded string, replacing invalid UTF-8 characters with the default character U+FFFD.	√	√
from_utf8 function	Decodes a binary string into a UTF-8 encoded string, replacing invalid UTF-8 characters with a custom string.	√	√
length function	Calculates the length of a string.	√	√
levenshtein_distance function	Calculates the minimum edit distance between x and y.	√	×
lower function	Converts a string to lowercase.	√	√
lpad function	Fills the beginning of a string with specified characters until the specified length is reached, then returns the result string.	√	√
ltrim function	Removes leading spaces from a string.	√	√
normalize function	Formats a string using the NFC format.	√	×
position function	Returns the position of a target substring within a string.	√	×
replace function	Replaces matched characters in a string with other specified characters.	√	√
replace function	Deletes matched characters from a string.	√	√
reverse function	Returns a string in reverse order.	√	√
rpad function	Fills the end of a string with specified characters until the specified length is reached, then returns the result string.	√	√
rtrim function	Removes trailing spaces from a string.	√	√
split function	Splits a string using a specified separator and returns a collection of substrings.	√	√
split function	Splits a string using a specified separator and limits the number of splits, then returns a collection of substrings.	√	√
split_part function	Splits a string using a specified separator and returns the content at a specified position.	√	√
split_to_map function	Splits a string using a specified first separator, then splits again using a specified second separator.	√	√
strpos function	Returns the position of a target substring within a string, equivalent to the position(sub_string in x) function.	√	√
substr function	Returns a substring from a string at a specified position and with a specified length.	√	√
substr function	Returns a substring from a string at a specified position.	√	√
to_utf8 function	Transforms a string into UTF-8 encoding format.	√	√
trim function	Deletes leading and trailing spaces from a string.	√	√
upper function	Converts a string to uppercase.	√	√

Date and time functions

Function Name	Description	Support for SQL	Support for 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 of timestamp type to a specified format.	✓	✓
date_parse function	Converts a date and time string to a datetime expression of timestamp type in a specified format.	✓	✓
from_iso8601_date function	Converts an ISO 8601 date expression to a date type date expression.	✓	×
from_iso8601_timestamp function	Converts an ISO 8601 datetime expression to a timestamp type datetime expression.	✓	×
from_unixtime function	Converts a UNIX timestamp to a datetime expression of timestamp type without a time zone.	✓	✓
	Converts a UNIX timestamp to a datetime expression of timestamp type with a time zone.	✓	×
	Converts a UNIX timestamp to a datetime expression of timestamp type with a time zone, where hours and minutes are the time zone offsets.	✓	×
localtime function	Returns the local time.	✓	×
localtimestamp function	Returns the local date and time.	✓	×
now function	Returns the current date and time. The now function is equivalent to the current_timestamp function.	✓	×
to_iso8601 function	Converts a date or timestamp type datetime expression to an ISO 8601 format datetime expression.	✓	×
to_unixtime function	Converts a timestamp type datetime expression to a UNIX timestamp.	✓	✓
day function	Extracts the day from a datetime expression, calculated by month. The day function is equivalent to the day_of_month function.	✓	×
day_of_month function	Extracts the day from a datetime expression, calculated by month. The day_of_month function is equivalent to the day function.	✓	×
day_of_week function	Extracts the day from a datetime expression, calculated by week. The day_of_week function is equivalent to the dow function.	✓	✓
day_of_year function	Extracts the day from a datetime expression, calculated by year. The day_of_year function is equivalent to the doy function.	✓	✓
dow function	Extracts the day from a datetime expression, calculated by week. The dow function is equivalent to the day_of_week function.	✓	✓
doy function	Extracts the day from a datetime expression, calculated by year. The doy function is equivalent to the day_of_year function.	✓	✓
extract function	Extracts the date or time part from a datetime expression by a specified field.	✓	×
hour function	Extracts the hour from a datetime expression, calculated by a 24-hour clock.	✓	✓
minute function	Extracts the minute from a datetime expression.	✓	✓
month function	Extracts the month from a datetime expression.	✓	✓
quarter function	Calculates the quarter of the target date.	✓	✓
second function	Extracts the second from a datetime expression.	✓	✓
timezone_hour function	Calculates the hour offset of the time zone.	✓	×
timezone_minute function	Calculates the minute offset of the time zone.	✓	×
week function	Calculates the week number of the year for a target date. The week function is equivalent to the week_of_year function.	✓	×
week_of_year function	Calculates the week number of the year for a target date. The week_of_year function is equivalent to the week function.	✓	×
year function	Extracts the year from a target date.	✓	✓
year_of_week function	Extracts the year of the target date in the ISO week calendar. The year_of_week function is equivalent to the yow function.	✓	✓
yow function	Extracts the year of the target date in the ISO week calendar. The yow function is equivalent to the year_of_week function.	✓	✓
date_trunc function	Truncates a datetime expression to a specified time unit and aligns it to millisecond, second, minute, hour, day, month, or year.	✓	×
date_add function	Adds N time units to x.	✓	✓
date_diff function	Returns the time difference between two datetime expressions, such as the number of time units between x and y.	✓	✓
time_series function	Completes the missing data in your query time window.	✓	×

Json functions

Function Name	Description	SQL Support	SPL Support
json_array_contains function	Determines if a specified value is present in a JSON array.	✓	✓
json_array_get function	Retrieves the element at a specified index within a JSON array.	✓	×
json_array_length function	Counts the number of elements in a JSON array.	✓	✓
json_extract function	Extracts a group of JSON values (array or object) from a JSON object or array.	✓	✓
json_extract_scalar function	Extracts a group of scalar values (strings, integers, or Boolean values) from a JSON object or array, similar to the json_extract function.	✓	✓
json_extract_bool function	Extracts Boolean values from a JSON object or array.	✓	×
json_extract_long function	Extracts bigint values from a JSON object or array.	✓	×
json_extract_double function	Extracts double values from a JSON object or array.	✓	×
json_format function	Converts JSON data into a string format.	✓	✓
json_parse function	Transforms a string into JSON data.	✓	✓
json_size function	Calculates the number of elements in a JSON object or array.	✓	✓

Regular expression functions

Function Name	Description	Support for SQL	Support for SPL
regexp_extract_all function	Extracts all substrings from the target string that match the regular expression and returns them as a collection.	✓	×
regexp_extract_all function	Extracts substrings from the target string that match the regular expression and returns a collection of substrings that match the target capturing group.	✓	×
regexp_extract function	Extracts and returns the first substring from the target string that matches the regular expression.	✓	✓
regexp_extract function	Extracts the substring from the target string that matches the regular expression and returns the first substring that matches the target capturing group.	✓	✓
regexp_extract_bool function	Extracts the first substring from the target string that matches the regular expression and converts it to a Boolean type. The conversion is successful only if the substring is "true" or "false" (case-insensitive); otherwise, returns `null`.	✓	×
regexp_extract_bool function	Extracts substrings from the target string that match the regular expression, returns the substring that matches the specified capturing group, and converts it to a Boolean type. The conversion is successful only if the substring is "true" or "false" (case-insensitive); otherwise, returns `null`.	✓	×
regexp_extract_long function	Extracts and returns the substring from the target string that matches the regular expression and converts it to a bigint type. Returns `null` if the conversion fails.	✓	×
regexp_extract_long function	Extracts substrings from the target string that match the regular expression, returns the substring that matches the target capturing group, and converts it to a bigint type. Returns `null` if the conversion fails.	✓	×
regexp_extract_double function	Extracts and returns the substring from the target string that matches the regular expression and converts it to a double type. Returns `null` if the conversion fails.	✓	×
regexp_extract_double function	Extracts substrings from the target string that match the regular expression, returns the substring that matches the target capturing group, and converts it to a double type. Returns `null` if the conversion fails.	✓	×
regexp_like function	Determines whether the target string matches the regular expression.	✓	✓
regexp_replace function	Deletes substrings from the target string that match the regular expression and returns the remaining substrings.	✓	✓
regexp_replace function	Replaces substrings in the target string that match the regular expression and returns the replaced string.	✓	✓
regexp_split function	Splits the target string using the regular expression and returns the resulting collection of substrings.	✓	×

Interval-valued comparison and periodicity-valued comparison functions

```markdown

Function Name	Description	Support for SQL	Support for SPL
compare function	Compares the results of the current period with those from n seconds prior.	✓	×
compare function	Compares the results of the current period with those from n1, n2, and n3 seconds prior.	✓	×
ts_compare function	Compares the results of the current period with those from n seconds prior. Important The ts_compare function requires grouping by the time column (GROUP BY).	✓	×
ts_compare function	Compares the results of the current period with those from n1, n2, and n3 seconds prior.	✓	×

Array functions and operators

Function Name	Description	Supported in SQL	Supported in SPL
Subscript Operator	Returns the element at the xth position in an array.	√	×
array_agg Function	Returns all values in x as an array.	√	×
array_distinct Function	Eliminates duplicate elements from an array.	√	√
array_except Function	Calculates the difference between two arrays.	√	√
array_intersect Function	Calculates the intersection of two arrays.	√	√
array_join Function	Joins the elements of an array into a string using a specified delimiter, ignoring null elements.	√	√
array_join Function	Joins the elements of an array into a string using a specified delimiter, replacing null elements with null_replacement.	√	√
array_max Function	Finds the maximum value within an array.	√	√
array_min Function	Finds the minimum value within an array.	√	√
array_position Function	Returns the index of a specified element in an array, starting at 1, or 0 if the element is not found.	√	√
array_remove Function	Removes a specified element from an array.	√	√
array_sort Function	Sorts array elements in ascending order, placing null elements at the end.	√	√
array_transpose Function	Transposes a matrix, extracting elements with the same index from a two-dimensional array to form a new two-dimensional array.	√	×
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 a single array.	√	×
contains Function	Determines if an array contains a specified element, returning true if it does.	√	×
element_at Function	Returns the element at the yth position in an array.	√	×
filter Function	Filters elements in an array based on a lambda expression, returning those that match.	√	√
flatten Function	Converts a two-dimensional array into a one-dimensional array.	√	×
reduce Function	Adds up elements in an array based on a lambda expression, then returns the result.	√	√
reverse Function	Reverses the order of elements in an array.	√	√
sequence Function	Generates an array of consecutive and incrementing elements within a specified range, with a default increment of 1.	√	√
sequence Function	Generates an array of consecutive and incrementing elements within a specified range, with a custom increment value.	√	√
shuffle Function	Randomizes the order of elements in an array.	√	√
slice Function	Extracts a subset of an array.	√	√
transform Function	Applies a lambda expression to each element in an array.	√	√
zip Function	Merges multiple arrays into a two-dimensional array, with elements at the same index forming a new array.	√	√
zip_with Function	Merges two arrays into one using a lambda expression.	√	×

Map functions and operators

Function Name	Description	Supported in SQL	Supported in SPL
Subscript Operator	Retrieves a map value by key.	√	×
Cardinality Function	Determines the size of a map.	√	×
Element_at Function	Retrieves a map value by key.	√	√
Histogram Function	Groups query and analysis results, returning the result in JSON format.	√	×
Histogram_u Function	Groups query and analysis results, returning the result in multiple rows and columns format.	√	×
Map Function	Returns an empty map.	√	√
Map Function	Transforms two arrays into a single map.	√	√
Map_agg Function	Maps x and y into a map. x is the key in the map, and y is the value. If multiple values exist for a key, a random value is selected as the key value.	√	×
Map_concat Function	Merges multiple maps into one map.	√	√
Map_filter Function	Uses a lambda expression to filter elements in a map.	√	√
Map_keys Function	Extracts all keys from a map and returns them as an array.	√	√
Map_values Function	Extracts all values from a map and returns them as an array.	√	√
Multimap_agg Function	Maps x and y into a map. x is the key in the map, and y is the value in array format. If multiple values exist for a key, all values are extracted as the key value.	√	×

Mathematical calculation functions

Function Name	Description	Support for SQL	Support for SPL
abs function	Calculates the absolute value of x.	√	√
acos function	Calculates the arccosine of x.	√	√
asin function	Calculates the arcsine of x.	√	√
atan function	Calculates the arctangent of x.	√	√
atan2 function	Calculates the arctangent of the quotient of x and y.	√	√
cbrt function	Calculates the cube root of x.	√	√
ceil function	Rounds x up to the nearest integer. The ceil 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 radians to degrees.	√	√
e function	Returns the value of the mathematical constant e.	√	√
exp function	Calculates e raised to the power of x.	√	×
floor function	Rounds x down to the nearest integer.	√	√
from_base function	Converts x from a specified base encoding to a base-10 number.	√	√
ln function	Calculates the natural logarithm of x.	√	√
infinity function	Returns a value representing positive infinity.	√	√
is_nan function	Determines if x is NaN.	√	√
log2 function	Calculates the binary logarithm of x.	√	√
log10 function	Calculates the base-10 logarithm of x.	√	√
log function	Calculates the logarithm of x with base y.	√	×
mod function	Calculates the remainder of dividing x by y.	√	√
nan function	Returns a NaN value.	√	√
pi function	Returns the value of π to 15 decimal places.	√	√
pow function	Calculates x raised to the power of y. The pow function is an alias of the power function.	√	√
power function	Calculates x raised to the power of y.	√	√
radians function	Converts degrees to radians.	√	√
rand function	Returns a random number.	√	√
random function	Returns a random number between 0 (inclusive) and 1 (exclusive).	√	√
random function	Returns a random number between 0 (inclusive) and x (exclusive).	√	√
round function	Rounds x to the nearest integer.	√	√
round function	Rounds x to the specified number of decimal places.	√	√
sign function	Returns the sign of x, represented as 1, 0, or -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 string representation in the specified base encoding.	√	×
truncate function	Truncates the decimal portion of x.	√	√
width_bucket function	Divides a range of values into equal-sized buckets and returns the bucket to which x belongs.	√	×
width_bucket function	Uses an array to specify bucket ranges and returns the bucket to which x belongs.	√	×

Mathematical statistics functions

### Proofread English Translation

Function Name	Description	SQL Support	SPL Support
corr function	Calculates the correlation coefficient between x and y, with a return value ranging from 0 to 1.	√	×
covar_pop function	Calculates the population covariance of x and y.	√	×
covar_samp function	Calculates the sample covariance of x and y.	√	×
stddev function	Calculates the sample standard deviation of x, equivalent to the stddev_samp function.	√	×
stddev_samp function	Calculates the sample standard deviation of x.	√	×
stddev_pop function	Calculates the population standard deviation of x.	√	×
variance function	Calculates the sample variance of x, equivalent to the var_samp function.	√	×
var_samp function	Calculates the sample variance of x.	√	×
var_pop function	Calculates the population variance of x.	√	×
regr_intercept function	Calculates the y-axis intercept of a linear equation fitted to the input points `(x, y)`.	√	×
regr_slope function	Calculates the slope of a linear equation fitted to the input points `(x, y)`.	√	×
beta_cdf function	Calculates the cumulative distribution function (CDF) value for the beta distribution using the formula P(N ≤ v; α, β), where α and β are the shape parameters of the beta distribution.	√	×
binomial_cdf function	Calculates the cumulative distribution function (CDF) value for the binomial distribution using the formula P(N ≤ v), where N represents the number of trials and v represents the number of successful outcomes, given a certain probability of success per trial.	√	×
cauchy_cdf function	Calculates the cumulative distribution function (CDF) value for the Cauchy distribution using the formula P(N ≤ v; x, y), where x represents the location parameter (the peak of the distribution), and y is the scale parameter.	√	×
chi_squared_cdf function	Calculates the cumulative distribution function (CDF) value for the chi-square distribution using the formula P(N ≤ v; k), where k represents the degrees of freedom.	√	×
inverse_beta_cdf function	Calculates the value corresponding to the inverse of the beta cumulative distribution function (CDF). Given a cumulative probability p, this function determines the value of v such that P(N ≤ v; α, β) = p.	√	×
inverse_binomial_cdf function	Calculates the value corresponding to the inverse of the binomial cumulative distribution function (CDF). Given a cumulative probability p, this function determines the value of v such that P(N ≤ v) = p.	√	×
inverse_cauchy_cdf function	Calculates the value corresponding to the inverse of the Cauchy cumulative distribution function (CDF). Given a cumulative probability p, this function determines the value of v such that P(N ≤ v; x, y) = p.	√	×
inverse_chi_squared_cdf function	Calculates the value corresponding to the inverse of the chi-square cumulative distribution function (CDF). Given a cumulative probability p, this function determines the value of v such that P(N ≤ v; k) = p.	√	×
inverse_laplace_cdf function	Calculates the value corresponding to the inverse of the Laplace cumulative distribution function (CDF). Given a cumulative probability p, this function determines the value of v such that P(N ≤ v; μ, b) = p.	√	×
inverse_normal_cdf function	Calculates the value corresponding to the inverse of the normal cumulative distribution function (CDF). Given a cumulative probability p, this function determines the value of v such that P(N ≤ v; x, y) = p.	√	×
inverse_poisson_cdf function	Calculates the value corresponding to the inverse of the Poisson cumulative distribution function (CDF). Given a cumulative probability p, this function determines the value of v such that P(N ≤ v; λ) = p.	√	×
inverse_weibull_cdf function	Calculates the value corresponding to the inverse of the Weibull cumulative distribution function (CDF). Given a cumulative probability p, this function determines the value of v such that P(N ≤ v; x, y) = p.	√	×
laplace_cdf function	Calculates the cumulative distribution function (CDF) value for the Laplace distribution using the formula P(N ≤ v; μ, b), where μ represents the location parameter, and b denotes the scale parameter.	√	×
normal_cdf function	Calculates the cumulative distribution function (CDF) value for the normal distribution using the formula P(N ≤ v; x, y), where x represents the mean of the distribution, and y represents the standard deviation.	√	×
poisson_cdf function	Calculates the cumulative distribution function (CDF) value for the Poisson distribution using the formula P(N ≤ v; λ), where λ represents the mean rate of occurrence.	√	×
weibull_cdf function	Calculates the cumulative distribution function (CDF) value for the Weibull distribution using the formula P(N ≤ v; x, y), where x represents the scale parameter, and y denotes the shape parameter.	√	×

Type conversion functions

Function name	Description	Supported in SQL	Supported in SPL
cast function	Converts the data type of x. If the cast function fails to convert a value, the query that calls this function is terminated.	√	√
try_cast function	Converts the data type of x. If the try_cast function fails to convert a value, the function returns NULL and skips the value to continue processing. Note Logs may contain dirty data. We recommend that you use the try_cast function to prevent conversion failures from causing the entire query and analysis operation to fail.	√	×
typeof function	Returns the data type of x.	√	×

Window functions

Function Name	Description	Supported in SQL	Supported in SPL
Aggregate Functions	All aggregate functions can be used within window functions. For a list of aggregate functions, see aggregate functions.	√	×
cume_dist Function	Computes the cumulative distribution of values within a window partition. It calculates the ratio of the number of rows with values less than or equal to the current value to the total number of rows in the window partition. The return value range is (0,1].	√	×
dense_rank Function	Ranks values within a window partition. Identical values receive the same rank, and ranks are consecutive. For example, if two identical values have a rank of 1, the next value will have a rank of 2.	√	×
ntile Function	Divides the data in a window partition into 'n' groups in order.	√	×
percent_rank Function	Calculates the percentage rank of each row within a window partition.	√	×
rank Function	Ranks values within a window partition. Identical values receive the same rank, but ranks are not consecutive. For example, if two identical values have a rank of 1, the next value will have a rank of 3.	√	×
row_number Function	Assigns a unique ordinal number to each value within a window partition, starting at 1. For example, if three values are identical, they will be numbered 1, 2, and 3 respectively.	√	×
first_value Function	Returns the value of the first row within each window partition.	√	×
last_value Function	Returns the value of the last row within each window partition.	√	×
lag Function	Returns the value from a row at a specified offset before the current row within a window partition. If no such row exists, returns the default_value.	√	×
lead Function	Returns the value from a row at a specified offset after the current row within a window partition. If no such row exists, returns the default_value.	√	×
nth_value Function	Returns the value from the row at a specified offset within a window partition.	√	×

IP functions

Function Name	Description	SQL Support	SPL Support
ip_to_city function	Associates an IP address with its corresponding city. Maps an IP address to its corresponding city, returning the city's name in Chinese.	√	×
ip_to_city function	Identifies the city associated with an IP address. Maps an IP address to its corresponding city, returning the city's administrative division code.	√	×
ip_to_city_geo function	Determines the geographic coordinates of a city associated with an IP address, providing a single set of longitude and latitude values.	√	×
ip_to_country function	Identifies the country or region associated with an IP address. Identifies the country or region associated with an IP address, returning the name in Chinese.	√	×
ip_to_country function	Identifies the country or region associated with an IP address. Identifies the country or region associated with an IP address, returning the corresponding code.	√	×
ip_to_country_code function	Identifies the country or region associated with an IP address. Identifies the country or region associated with an IP address, returning the corresponding code.	√	×
ip_to_domain function	Checks if an IP address is private or public.	√	×
ip_to_geo function	Identifies the geographic coordinates of an IP address's location, providing longitude and latitude values.	√	×
ip_to_provider function	Identifies the Internet service provider (ISP) for a given IP address.	√	×
ip_to_province function	Identifies the or state associated with an IP address. The function retrieves the Chinese name for a given or state.	√	×
ip_to_province function	Identifies the or state associated with an IP address. The function retrieves the administrative region code for a state.	√	×
ip_prefix function	Returns the prefix of an IP address.	√	×
is_prefix_subnet_of function	Verifies if a CIDR block is a subnet of a specified CIDR block.	√	×
is_subnet_of function	Checks if an IP address falls within a specified CIDR block.	√	×
ip_subnet_max function	Provides the largest IP address within a CIDR block.	√	×
ip_subnet_min function	Provides the smallest IP address within a CIDR block.	√	×
ip_subnet_range function	Calculates the range of IP addresses within a CIDR block.	√	×
ipv6_to_city function	Maps an IPv6 address to its corresponding city.	√	×
ipv6_to_city_code function	Identifies the administrative division code of the city associated with an IPv6 address.	√	×
ipv6_to_city_geo function	Maps an IPv6 address to the geographic coordinates of its corresponding city.	√	×
ipv6_to_country function	Identifies the country or region associated with an IPv6 address.	√	×
ipv6_to_country_code function	Identifies the code of the country or region associated with an IPv6 address.	√	×
ipv6_to_domain function	Checks if an IPv6 address is private or public.	√	×
ipv6_to_provider function	Identifies the Internet service provider (ISP) for an IPv6 address.	√	×
ipv6_to_province function	Identifies the province associated with an IPv6 address.	√	×
ipv6_to_province_code function	Identifies the administrative division code of the province associated with an IPv6 address.	√	×

URL functions

Function name	Description	SQL Support	Supports Structured Process Language (SPL)
url_encode function	Encodes URLs.	√	√
url_decode function	Decodes URLs.	√	√
url_extract_fragment function	Extracts the fragment component from a URL.	√	√
url_extract_host function	Extracts the host component from a URL.	√	√
url_extract_parameter function	Extracts the value of a specified parameter from a URL's query string.	√	√
url_extract_path function	Extracts the path component from a URL.	√	√
url_extract_port function	Extracts the port information from a URL.	√	√
url_extract_protocol function	Extracts the protocol information from a URL.	√	√
url_extract_query function	Extracts the query component from a URL.	√	√

Estimation functions

Function Name	Description	Support for SQL	Support for SPL
approx_distinct function	Provides an estimate of the unique value count in x, with a default standard error of 2.3%.	√	×
approx_distinct function	Allows for a custom standard error when estimating the unique value count in xx.	√	×
approx_percentile function	Orders values in x by ascending order and returns the value approximately at the specified percentile.	√	×
	Orders values in x by ascending order and returns the values approximately at the specified percentiles, percentage01 and percentage02.	√	×
	Orders the product of x and weight by ascending order and returns the value approximately at the specified percentile.	√	×
	Orders the product of x and weight by ascending order and returns the values approximately at the specified percentiles, percentage01 and percentage02.	√	×
	Orders the product of x and weight by ascending order and returns the value approximately at the specified percentile, with support for setting the accuracy of the return value.	√	×
numeric_histogram function	Generates an approximate histogram of x with a specified number of buckets (histogram columns), returning a JSON-type result.	√	×
numeric_histogram function	Generates an approximate histogram of x with a specified number of buckets (histogram columns), returning a JSON-type result and allowing for weighted values.	√	×
numeric_histogram_u function	Creates an approximate histogram of x with a specified number of buckets (histogram columns), producing a multi-row and multi-column format result.	√	×

Binary functions

Function Name	Description	SQL Support	SPL Support
from_base64 function	Decodes a Base64-encoded string to binary data.	√	√
from_base64url function	Decodes a Base64-encoded string to binary data using URL-safe characters.	√	×
from_big_endian_64 function	Converts big endian binary data to a number.	√	×
from_hex function	Converts hexadecimal data to binary data.	√	√
length function	Calculates the length of binary data.	√	×
md5 function	Performs MD5 encoding on binary data.	√	√
to_base64 function	Encodes binary data into Base64 format.		√
to_base64url function	Encodes binary data into Base64 format using URL-safe characters.	√	×
to_hex function	Converts binary data to hexadecimal format.	√	√
to_big_endian_64 function	Converts a number to big endian binary data.	√	×
sha1 function	Performs SHA-1 encryption on binary data.	√	√
sha256 function	Performs SHA-256 encryption on binary data.	√	√
sha512 function	Performs SHA-512 encryption on binary data.	√	√
xxhash64 function	Performs xxHash64 encryption on binary data.	√	√

Bitwise functions

Function Name	Description	Supported in SQL	Supported in SPL
bit_count function	Counts the number of '1' bits in x.	√	√
bitwise_and function	Performs a bitwise AND operation on x and y in binary form.	√	√
bitwise_not function	Performs a bitwise NOT operation on all bits of x in binary form.	√	√
bitwise_or function	Performs a bitwise OR operation on x and y in binary form.	√	√
bitwise_xor function	Performs a bitwise exclusive OR operation on x and y in binary form.	√	√

Geospatial functions

Function name	Description	SQL Support	Supports Structured Process Language (SPL)
ST_AsText function	Returns the Well-Known Text (WKT) representation of a geometry.	Yes	No
ST_Boundary function	Returns the boundary of a geometry.	Yes	No
ST_Buffer function	Returns a geometry that represents all points whose distance from a given geometry is less than or equal to a specified distance.	Yes	No
ST_Difference function	Returns a geometry representing the points that are in the first geometry but not in the second geometry.	Yes	No
ST_Envelope function	Returns the minimum bounding rectangle for a geometry.	Yes	No
ST_ExteriorRing function	Returns the exterior ring of a polygon as a line string.	Yes	No
ST_Intersection function	Returns a geometry representing the shared portion of two geometries.	Yes	No
ST_SymDifference function	Returns a geometry representing the symmetric difference between two geometries, which is the set of points belonging to either one but not both.	Yes	No
ST_Contains function	Determines whether the first geometry contains the second geometry (the boundary may intersect). If it does, it returns true.	Yes	No
ST_Crosses function	Determines whether two geometries share any interior points. If they do, it returns true.	Yes	No
ST_Disjoint function	Determines whether two geometries do not share any points. If they do not, it returns true.	Yes	No
ST_Equals function	Determines whether two geometries are exactly the same. If they are, it returns true.	Yes	No
ST_Intersects function	Determines whether the planar projections of two geometries share any points. If they do, it returns true.	Yes	No
ST_Overlaps function	Determines whether two geometries have the same dimension and overlap but neither is contained by the other. If they do, it returns true.	Yes	No
ST_Relate function	Determines whether two geometries are related. If they are, it returns true.	Yes	No
ST_Touches function	Determines whether two geometries share a boundary but do not share any interior points. If they do, it returns true.	Yes	No
ST_Within function	Determines whether the first geometry is completely within the second geometry (without intersecting the boundary). If it is, it returns true.	Yes	No
ST_Area function	Calculates the area of a geometry's projection on a two-dimensional plane using the Euclidean method.	Yes	No
ST_Centroid function	Returns the center point of a geometric entity.	Yes	No
ST_CoordDim function	Returns the coordinate dimension of a geometry.	Yes	No
ST_Dimension function	Returns the inherent dimension of a geometric entity, which must be less than or equal to the coordinate dimension.	Yes	No
ST_Distance function	Calculates the minimum distance between two geometries.	Yes	No
ST_EndPoint function	Returns the last point of a line segment.	Yes	No
ST_IsClosed function	Determines whether a geometry is closed. If it is, it returns true.	Yes	No
ST_IsEmpty function	Determines whether a geometry is empty. If it is, it returns true.	Yes	No
ST_IsRing function	Determines whether a geometry is a closed simple line string (ring). If it is, it returns true.	Yes	No
ST_Length function	Calculates the two-dimensional projected length of a line segment using the Euclidean method. If there are multiple line segments, it returns the sum of all lengths.	Yes	No
ST_NumPoints function	Returns the number of points in a geometry.	Yes	No
ST_NumInteriorRing function	Calculates the number of interior rings in a geometry.	Yes	No
ST_StartPoint function	Returns the first point of a line segment.	Yes	No
ST_X function	Returns the x-axis coordinate of a point.	Yes	No
ST_XMax function	Returns the first maximum x-axis coordinate of a geometry.	Yes	No
ST_XMin function	Returns the first minimum x-axis coordinate of a geometry.	Yes	No
ST_Y function	Returns the y-axis coordinate of a point.	Yes	No
ST_YMax function	Returns the first maximum y-axis coordinate of a geometry.	Yes	No
ST_YMin function	Returns the first minimum y-axis coordinate of a geometry.	Yes	No
bing_tile function	Constructs a Bing tile using x-coordinate, y-coordinate, and zoom level.	Yes	No
bing_tile function	Constructs a Bing tile using a quadkey.	Yes	No
bing_tile_at function	Constructs a Bing tile using latitude, longitude, and zoom level.	Yes	No
bing_tile_coordinates function	Returns the x-coordinate and y-coordinate for a given Bing tile.	Yes	No
bing_tile_polygon function	Returns the polygon format of a given Bing tile.	Yes	No
bing_tile_quadkey function	Returns the quadkey of a given Bing tile.	Yes	No
bing_tile_zoom_level function	Returns the zoom level of a given Bing tile.	Yes	No

Geo functions

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

Color functions

```markdown

Function Name	Description	Supported in SQL	Supported in SPL
bar function	Specifies the width of the entire ANSI bar chart through the width parameter, with the starting color of the ANSI bar chart being red (low_color) and the ending color being green (high_color). Then, a segment of the ANSI bar chart is extracted through the x parameter and returned.	√	×
bar function	Specifies the width of the entire ANSI bar chart through the width parameter, with customizable starting and ending colors. Then, a segment of the ANSI bar chart is extracted through the x parameter and returned.	√	×
color function	Transforms a color string into a color type.	√	×
	Specifies the proportion of low_color and high_color based on the ratio of x between low and high, then returns a color between low_color and high_color.	√	×
	Specifies the proportion of low_color and high_color through y, then returns a color between low_color and high_color.	√	×
render function	Applies color rendering to display results: a green tick for a true Boolean expression, and a red cross for a false one.	√	×
render function	Applies custom color rendering to display results.	√	×
rgb function	Returns a color value from RGB values.	√	×

HyperLogLog functions

Function name	Description	Support for SQL	Support for SPL
approx_set function	Estimates the number of distinct values in x. 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
Basic Operators	Compares x and y, returning true if the condition is satisfied.	√	√
ALL Operator	Returns true if x satisfies all specified conditions.	√	×
ANY Operator	Returns true if x meets any of the given conditions.	√	×
BETWEEN Operator	Returns true if x falls within the range of y and z.	√	√
DISTINCT Operator	Returns true if x is not equal to y (x IS DISTINCT FROM y).	√	×
DISTINCT Operator	(x IS NOT DISTINCT FROM y) Returns true if x is equal to y.	√	×
LIKE Operator	Checks for a match to a specified character pattern in a string, which is case-sensitive.	√	√
SOME Operator	Returns true if x satisfies any of the specified conditions.	√	×
GREATEST Operator	Identifies the largest value among x and y.	√	×
LEAST Operator	Identifies the smallest value among x and y.	√	×
NULL Operator	Returns true if x is null (x IS NULL).	√	×
NULL Operator	(x IS NOT NULL) Returns true if x is not null.	√	×

Logical operators

Operator	Description	Support for SQL	Support for 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.	√	√

Aggregate functions

```markdown

Function name	Description	SQL supported	Supported in Structured Process Language (SPL)
convert_data_size function	This function converts a data size measurement to the most appropriate unit, as determined by the system. It returns the converted value as a string. For instance, 1,024 KB becomes 1 MB, and 1,024 MB becomes 1 GB.	✓	×
convert_data_size function	Converts a measurement from the current unit to a specified unit, returning the result as a string.	✓	×
format_data_size function	This function converts byte measurements into a specified unit, returning the result as a string.	✓	×
parse_data_size function	Transforms a measurement from its current unit into bytes, returning a decimal value.	✓	×
to_data_size_B function	Converts a measurement to bytes from its current unit, returning a value of type double.	✓	×
to_data_size_KB function	This function converts a given measurement to kilobytes (KB), returning a value of type double.	✓	×
to_data_size_MB function	This function converts a given measurement to megabytes (MB), returning a value of type double.	✓	×
to_data_size_GB function	This function converts a given measurement to gigabytes (GB), returning a value of type double.	✓	×
to_data_size_TB function	This function converts a given measurement to terabytes (TB), returning a value of type double.	✓	×
to_data_size_PB function	This function converts a given measurement to petabytes (PB), returning a value of type double.	✓	×
format_duration function	This function formats a time interval in seconds into a readable string format.	✓	×
parse_duration function	Formats a time interval, converting it to the `0 00:00:00.000` format.	✓	×
to_days function	Converts a time interval into days.	✓	×
to_hours function	Converts a time interval into hours.	✓	×
to_microseconds function	Converts a time interval into microseconds.	✓	×
to_milliseconds function	Converts a time interval into milliseconds.	✓	×
to_minutes function	Converts a time interval into minutes.	✓	×
to_most_succinct_time_unit function	This function converts a time interval to the most appropriate unit, as determined by the system, and returns the result.	✓	×
to_nanoseconds function	Converts a time interval into nanoseconds.	✓	×
to_seconds function	Converts a time interval into seconds.	✓	×

Window funnel functions

Function name	Description	Support for SQL	Support for 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 enables the definition of lambda expressions within SQL analytic statements and SPL statements. These expressions can be passed to specific functions, enhancing their expressive capabilities. For more information, see the referenced document.

Conditional expressions

Expression	Description	Supported in SQL	Supported in SPL
CASE WHEN expression	Classifies data based on conditions.	√	√
IF expression	Classifies data based on conditions.	√	√
COALESCE expression	Returns the first non-null value from a list of expressions.	√	√
NULLIF expression	Compares two expressions to determine if they are equal. If they are equal, it returns null; otherwise, it returns the first expression's value.	√	×
TRY expression	Catches abnormal information, allowing the system to continue executing queries and parsing operations.	√	√

Vector calculation functions

Expression	Description	SQL Support	SPL Support
Cosine Similarity Function	Cosine similarity values range from -1 to 1: A value of 1 indicates vectors in the same direction. A value of -1 indicates vectors in opposite directions. A value of 0 indicates orthogonal vectors, which are perpendicular to each other.	Yes	No
Inner Product (Dot Product) Function	Computes the dot product of two vectors.	Yes	No
Euclidean Distance Function	Computes the Euclidean distance between two vectors.	Yes	No
Vector Norm (Length) Function	Calculates the vector norm, which is equivalent to its length.	Yes	No
Vector Element Sum Function	Computes the sum of all elements in a vector.	Yes	No
Vector Element Mean Function	Computes the average value of a vector.	Yes	No
Vector Element Variance Function	Computes the variance of vector elements, indicating their dispersion around the mean.	Yes	No
Vector Element Standard Deviation Function	Computes the standard deviation of vector elements, reflecting how much they deviate from the mean. A higher standard deviation indicates greater spread, while a lower one indicates elements are closer to the mean.	Yes	No
Vector Addition Function	Computes the sum of two vectors. Computes the sum of two vectors. This operation is part of linear transformations that include vector addition, subtraction, and scaling.	Yes	No
Vector Subtraction Function	Computes the difference between two vectors. Computes the difference between two vectors. This operation is part of linear transformations that include vector addition, subtraction, and scaling.	Yes	No
Vector Scaling Function	Enables linear transformations through vector addition, subtraction, and scaling.	Yes	No