This topic describes the functions and operators that are involved in SQL analysis.
Aggregate functions
Function | Description |
---|---|
arbitrary function | Returns a random not-null value of the x field. |
avg function | Returns the average of the values of the x field. |
bitwise_and_agg function | Returns the result of the bitwise AND operation on the values of the x field. |
bitwise_or_agg function | Returns the result of the bitwise OR operation on the values of the x field. |
bool_and function | Checks whether all logs meet the specified condition. If all logs meet the specified
condition, 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 a log that meets the specified condition exists, the function returns true. |
checksum function | Returns the checksum of the values of the x field. |
count function | Returns the number of logs. |
Returns the number of logs. This function is equivalent to the count(*) function. | |
Returns the number of logs whose value of the x field is not null. | |
count_if function | Returns the number of logs that meet the specified condition. |
every function | Checks whether all logs meet the specified condition. If all logs meet the specified
condition, the function returns true.
This function is equivalent to the bool_and function. |
geometric_mean function | Returns the geometric mean of the values of the x field. |
kurtosis function | Returns the excess kurtosis of the values of the x field. |
map_union function | Returns the union of the specified maps. If multiple input maps have the same key, the value of the key in the returned union is extracted from one of the maps at random. |
max function | Returns the maximum value of the x field. |
Returns the n largest values of the x field. The function returns an array. | |
max_by function | Returns the value of the x field that corresponds to the maximum value of the y field. |
Returns the values of the x field that correspond to the n largest values of the y field. The function returns an array. | |
min function | Returns the minimum value of the x field. |
Returns the n smallest values of the x field. The function returns an array. | |
min_by function | Returns the value of the x field that corresponds to the minimum value of the y field. |
Returns the values of the x field that correspond to the n smallest values of the y field. The function returns an array. | |
skewness function | Returns the skewness of the values of the x field. |
sum function | Returns the sum of the values of the x field. |
String functions
Function | Description |
---|---|
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. |
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 | Normalizes a string to normalization form C (NFC). |
position function | Returns the position of a specified substring in a string. |
replace function | Replaces the matched characters in a string with specified characters. |
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. |
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. |
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 |
---|---|
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 timestamp expression to a datetime expression in a specified format. |
date_parse function | Converts a datetime string to a timestamp expression in a specified format. |
from_iso8601_date function | Converts a date expression in the ISO 8601 format to a common date expression. |
from_iso8601_timestamp function | Converts a datetime expression in the ISO 8601 format to a timestamp expression. |
from_unixtime function | Converts a UNIX timestamp to a timestamp expression that does not contain a time zone. |
Converts a UNIX timestamp to a timestamp expression that contains a time zone. | |
Converts a UNIX timestamp to a timestamp expression that contains a time zone. In the timestamp 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 date expression or a timestamp expression to a datetime expression in the ISO 8601 format. |
to_unixtime function | Converts a timestamp expression 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 a 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 of a specified date from a time expression that is specified in the
ISO week date system.
This function is equivalent to the yow function. |
yow function | Returns the year of a specified date from a time expression that is specified 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 a specified time unit. |
time_series function | Adds a value to the field that has no value returned in the specified time window. |
JSON functions
Function | Description |
---|---|
json_array_contains function | Checks whether a JSON array contains a specified value. |
json_array_get function | Returns the element that corresponds to an index in a JSON array. |
json_array_length function | Returns the number of elements in a JSON array. |
json_extract function | Returns a set of JSON values from a JSON object or a JSON array. |
json_extract_scalar function | Returns 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 | Returns the number of elements in a JSON object or a JSON array. |
Regular expression functions
Function | Description |
---|---|
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. |
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. |
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 | Removes the substrings that match a specified regular expression from a specified string and returns the substrings that remain. |
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 |
---|---|
compare function | Compares the calculation result of the current time period with the calculation result of a time period n seconds before. |
Compares the calculation result of the current time period with the calculation results of 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.
Notice The query and analysis results of the ts_compare function must be grouped by the time
column by using the GROUP BY clause.
|
Compares the calculation result of the current time period with the calculation results of time periods n1, n2, and n3 seconds before. |
Array functions and operators
Function | Description |
---|---|
Subscript operator | Is used to retrieve the element at position x from an array. |
array_agg function | Returns an array that is created from all values in x. |
array_distinct function | Removes duplicate elements from an array. |
array_except function | Returns the difference of two arrays. |
array_intersect function | Returns the intersection of two arrays. |
array_join function | Concatenates the elements of an array by using a specified delimiter and returns a string. If the array contains a null element, the null element is ignored. |
Concatenates the elements of an array by using a specified delimiter and returns a string. If the array contains a null element, the null element is replaced with null_replacement. | |
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 position of a specified element in an array. Positions start 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 | Returns an array of elements that are sorted 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 is created from the elements in the matrix. The elements are located by using the same indexes. |
array_union function | Returns the union of two arrays. |
cardinality function | Returns the number of elements in an array. |
concat function | Returns the concatenation of multiple arrays. |
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 at position y in an array. |
filter function | Filters elements in an array based on a lambda expression and returns elements that meet the expression. |
flatten function | Transforms a two-dimensional array into a one-dimensional array. |
reduce function | Sums each element in an array by using a lambda expression and returns the result. |
reverse function | Returns an array of elements that are sorted in reverse order. |
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. |
Returns an array of elements within a specified range. The elements are incremental. The incremental step is a custom value. | |
shuffle function | Returns a random permutation of 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 position in the input arrays form a new array in the two-dimensional array. |
zip_with function | Merges two arrays into a single array by using a lambda expression. |
Map functions and operators
Function | Description |
---|---|
Subscript operator | Is used to retrieve 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. |
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 |
---|---|
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). |
Returns a random number in the range [0,x). | |
round function | Rounds x to the nearest integer. |
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. |
Returns the bucket number of x in the range of buckets that are specified by an array. |
Mathematical statistics functions
Function | Description |
---|---|
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. |
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.
|
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. |
Data type conversion functions
Function | Description |
---|---|
cast function | Converts x from one data type to another.
If the cast function fails to convert a value, the query that calls this function is terminated. |
try_cast function | Converts x from one data type to another.
If the try_cast function fails to convert a value, the function returns NULL. The query that calls this function can process the NULL value and continue to run. Note A log may contain dirty data. When you query logs, we recommend that you use the try_cast
function. This way, conversion failures do not cause your queries to fail.
|
typeof function | Returns the data type of x. |
Security check functions
Function | Description |
---|---|
security_check_ip function | Checks whether an IP address is secure. |
security_check_domain function | Checks whether a domain name is secure. |
security_check_url function | Checks whether a URL is secure. |
Window functions
Function | Description |
---|---|
Aggregate functions | You can use all aggregate functions as window functions. For more information about aggregate functions, see Aggregate function. |
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 smaller 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, they are assigned the same rank. The ranks are consecutive. For example, if two values are 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 specified order. |
percent_rank function | Calculates the percentage ranking 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, they are assigned the same rank. The ranks are not consecutive. For example, if two values are 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, they 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 default_value 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 default_value 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 |
---|---|
ip_to_city function | Identifies the city to which an IP address belongs.
The function returns the Chinese name of a city. |
Identifies the city to which an IP address belongs.
The function returns the administrative region 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. |
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 a public address. |
ip_to_geo function | Identifies the longitude and latitude of the location to which an IP address belongs. |
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. |
Identifies the state to which an IP address belongs.
The function returns the administrative region 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. |
URL functions
Function | Description |
---|---|
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 |
---|---|
approx_distinct function | Estimates the number of distinct values in the x field. The standard error is 2.3%, which is the default value. |
Estimates the number of distinct values in the x field. The standard error is a custom value. | |
approx_percentile function | Returns the approximate histogram of x based on the number of histogram columns that are specified by the bucket parameter. The returned result is of the JSON type. |
Returns the approximate histogram of x based on the number of histogram columns that are specified by the bucket parameter. The returned result is of the JSON type. You can specify a weight for x. | |
numeric_histogram function | Returns the approximate histogram of x based on the number of histogram columns that are specified by the bucket parameter. The returned result contains multiple rows and columns. |
approx_percentile function | Sorts the values of x in ascending order and returns the value that is approximately at the specified percentile. |
Sorts the values of x in ascending order and returns the values that are approximately at percentile01 and percentile02. | |
Sorts the values of x in ascending order based on the products of the values of x and the weight and returns the value of x that is approximately at the specified percentile. | |
Sorts the values of x in ascending order based on the products of the values of x and the weight and returns the value of x that are approximately at percentile01 and percentile02. | |
Sorts the values of x in ascending order based on the products of the values of x and the weight and returns the value of x that is approximately at the specified percentile. You can configure the accuracy of the returned results. |
Binary functions
Function | Description |
---|---|
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 | Converts a binary number in big endian to a bigint value. |
from_hex function | Converts a hexadecimal number to 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 | Converts a binary number to 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 |
---|---|
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 |
---|---|
ST_AsText function | Returns the well-known text (WKT) representation of a geometry. |
ST_GeometryFromText function | Returns a geometry from the specified WKT representation. |
ST_LineFromText function | Returns a line string from the specified WKT representation. |
ST_Polygon function | Returns a polygon from the specified WKT representation. |
ST_Point function | Returns a point from the specified WKT representation. |
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 the 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 the 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. |
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 functions
Function | Description |
---|---|
geohash function | Encodes latitudes and longitudes by using the Geohash algorithm. |
Color functions
Function | Description |
---|---|
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 and 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 the x parameter to specify the length of the part that is returned by the 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 the custom colors for the chart. In addition, you can configure the x parameter 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. |
Returns results by using custom color rendering. | |
rgb function | Returns a color value based on an RGB value. |
HyperLogLog functions
Function | Description |
---|---|
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 |
---|---|
Relational operators | Is used to compare x with y. If the specified 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 | If x is not equal to y, true is returned. |
If x is equal to y, true is returned. | |
like operator | Is used to match 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 | Is used to obtain the greater value of x and y. |
least operator | Is used to obtain the smaller value of x and y. |
null operator | If x is null, true is returned. |
If x is not null, true is returned. |
Logical operators
Operator | Description |
---|---|
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 |
---|---|
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. |
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 functions
Function | Description |
---|---|
window_funnel function | Searches the event chain in the sliding time window and counts the maximum number of consecutive events that occurred in the event chain. |
Lambda expressions
Log Service allows you to define a lambda expression in an analytic statement and pass the expression to a specified function. For more information, see Lambda expressions.
Conditional expressions
Expression | Description |
---|---|
CASE WHEN statement | Categorizes data by using conditions. |
IF function | Categorizes data by using conditions. |
COALESCE function | Returns the first not-null value based on multiple expressions. |
NULLIF function | Compares the values of two expressions. If the values of the two expressions are equal, null is returned. Otherwise, the value of the first expression is returned. |
TRY function | Evaluates an expression to capture errors. This helps the system continue to query and analyze data. |