Simple Log Service:SQL Functions

Classification

Description

Common Functions

Aggregate functions

Perform summary calculations on the target dataset to generate a single statistical result.

• avg function

• count function

• max function

• min function

• sum function

String functions

Process text data, including search, replace, substring, concatenate, format, and more.

• concat function

• length function

• position function

• replace function

• split function

Date and time functions

Perform format conversion, grouping, and aggregation on dates and times in logs.

• current_date function

• date function

• day function

• date_trunc function

• time_series function

JSON functions

Process JSON objects, including extraction, transformation, and statistics.

• json_array_contains function

• json_array_get function

• json_array_length function

• json_extract function

• json_extract_scalar function

Regular expression functions

Pattern matching and text processing.

• regexp_extract_all function

• regexp_extract function

• regexp_extract_bool function

• regexp_replace function

• regexp_split function

Year-on-year and month-on-month functions

Calculate relative changes in time series data.

• compare function

• ts_compare function

Array functions and operators

Perform addition, deletion, modification, query, traversal, and transformation on arrays.

• Subscript operator

• array_distinct function

• array_intersect function

• array_join function

• reverse function

Map mapping functions and operators

Operate on key-value pairs.

• cardinality function

• element_at function

• histogram function

• histogram_u function

• map function

Mathematical calculation functions

Numerical calculations, rounding, random numbers, trigonometric functions, and more.

• abs function

• ceil function

• log function

• mod function

• random function

Mathematical statistics functions

Data distribution analysis and numerical calculations.

• corr function

• covar_pop function

• regr_intercept function

• beta_cdf function

• inverse_chi_squared_cdf function

Type conversion functions

Handle conversions between data types.

• cast function

• try_cast function

• typeof function

Window functions

Aggregation or sorting based on data windows.

• Aggregate functions

• dense_rank function

• lag function

IP functions

Parse and calculate IP addresses.

• ip_to_city function

• ip_prefix function

• ipv6_to_city function

URL functions

Parse URL structures.

• url_encode function

• url_decode function

• url_extract_fragment function

• url_extract_path function

• url_extract_query function

Estimation functions

Predict data or fill in missing values.

• approx_distinct function

• approx_percentile function

• numeric_histogram function

• numeric_histogram_u function

Binary functions

Process binary data types.

• from_base64 function

• from_big_endian_64 function

• from_hex function

• to_hex function

• sha256 function

Bitwise operation functions

Directly operate on binary bits.

• bit_count function

• bitwise_and function

• bitwise_not function

• bitwise_or function

• bitwise_xor function

Spatial geometry functions

Process spatial geometries.

• ST_AsText function

• ST_GeometryFromText function

• ST_Boundary function

• ST_Buffer function

• ST_Contains function

Geographic functions

Geographic location analysis and map calculations.

• geohash function

Color functions

Color representation and conversion.

• bar function

• color function

• render function

• rgb function

HyperLogLog functions

Perform statistical processing on large datasets, sacrificing accuracy to save memory.

• approx_set function

• cardinality function

• empty_approx_set function

• merge function

Comparison operators

Determine the size relationship of parameters, applicable to any comparable data type (double, bigint, varchar, timestamp, and date).

• Basic operators

• ALL operator

• ANY operator

• BETWEEN operator

• DISTINCT operator

Logical operators

Combine multiple Boolean conditions to control logical flow.

• AND operator

• OR operator

• NOT operator

Unit conversion functions

Convert units of data size or time intervals.

• convert_data_size function

• format_duration function

Window funnel functions

Analyze user behavior, app traffic, product goal conversion, and other data.

• window_funnel function

Lambda expressions

Define lambda expressions in SQL analytic statements and SPL statements, and pass the expressions to specified functions to enrich the expression of functions.

• filter function

• reduce function

• transform function

Conditional expressions

Return different values based on conditional branches.

• CASE WHEN expression

• IF expression

• COALESCE expression

• NULLIF expression

• TRY expression

AI machine learning functions

Implement predefined operations or methods for specific algorithms or models. Used in various stages such as data preprocessing, feature engineering, model training, evaluation, and prediction.

• Vector calculation functions

• Time series analysis functions

• Cluster analysis functions

• Regression analysis functions

• Classification analysis functions

• Load balancing measurement functions

• Multivariate pattern recognition functions