Function List

Overview

Guandata BI supports eight function types, including but not limited to aggregate statistics, non-aggregate statistics, and date and time functions.

Function List

:::note[Note]

For easier hands-on use, you can download the attachment and archive it locally: Guandata Function Handbook.xlsx. The attachment contains various function types and application cases for learning and reference.

:::

The function list is as follows:

Function Type	Function	Usage and Description
Aggregate functions	AVG	Usage: AVG(expression/numeric field). Description: Returns the average of all values in an expression or numeric field. Only applies to numeric fields. Null values are not calculated. Example: AVG(Sales Amount) returns the average of all non-null values in the "Sales Amount" field.
Aggregate functions	COLLECT_LIST	Usage: COLLECT_LIST([Field]). Description: Merges multiple rows into one row and returns an array without deduplication. COLLECT_LIST is an approximate aggregate operation and requires an aggregation dimension. Therefore, it can only be used in ETL group aggregation, SQL Input, or a calculated field in a card. Set the field type to Text. Example: COLLECT_LIST([ID]) returns [18731, 18634, 18806, 18278, 18663].
Aggregate functions	COLLECT_SET	Usage: COLLECT_SET([Field]). Description: Merges multiple rows into one row and returns a deduplicated array. COLLECT_SET is an approximate aggregate operation and requires an aggregation dimension. Therefore, it can only be used in ETL group aggregation, SQL Input, or a calculated field in a card. Set the field type to Text. Example: COLLECT_SET([ID]) returns [18731, 18634, 18806, 18278, 18663].
Aggregate functions	COUNT	Usage: COUNT(expression/field). Description: Returns the number of valid data entries for the expression. Null values are not calculated. Example: COUNT(Sales Amount) returns the number of non-null entries in the "Sales Amount" field.
Aggregate functions	MAX	Usage: MAX(expression/numeric field). Description: Returns the maximum value in an expression or numeric field. Only applies to numeric fields. Example: MAX(Sales Amount) returns the maximum value in the "Sales Amount" field.
Aggregate functions	MIN	Usage: MIN(expression/numeric field). Description: Returns the minimum value in an expression or numeric field. Only applies to numeric fields. Example: MIN(Sales Amount) returns the minimum value in the "Sales Amount" field.
Aggregate functions	PERCENTILE	Usage: PERCENTILE([Numeric Field], percentile). Description: Calculates the median and percentiles of a numeric field. The first parameter is a numeric field. The second parameter range is 0 < p < 1, such as 0.1, 0.25, 0.75, and 0.95. Example: PERCENTILE([Math Exam Score], 0.5) returns the median exam score.
Aggregate functions	SUM	Usage: SUM(expression/numeric field). Description: Returns the sum of all values in an expression or numeric field. Only applies to numeric fields. Null values are not calculated. Example: SUM(Sales Amount) returns the sum of all non-null values in the "Sales Amount" field.
Non-aggregate functions	GREATEST	Usage: GREATEST(expression1/field1, expression2/field2, ...). Description: Compares values across columns in the same row and returns the largest value. Example: GREATEST([Department 1 Sales], [Department 2 Sales]) returns the larger value between the two sales fields in the row.
Non-aggregate functions	LEAST	Usage: LEAST(expression1/field1, expression2/field2, ...). Description: Compares values across columns in the same row and returns the smallest value. Example: LEAST([Department 1 Sales], [Department 2 Sales]) returns the smaller value between the two sales fields in the row.
Date and time	ADD_MONTHS	Usage: ADD_MONTHS(start date, numeric value). Description: Returns the date after the number of months specified by the numeric value or field, starting from the start date. The numeric value must be an integer and can be positive or negative. Example: ADD_MONTHS(2021-01-01,12) returns 2022/1/1.
Date and time	CURRENT_DATE	Usage: CURRENT_DATE(). Description: Returns the current system date. No parameter is required. Set the field type to Date. Example: If the system date is 2021-12-17, CURRENT_DATE() returns 2021-12-17.
Date and time	DATEDIFF	Usage: DATEDIFF(end date field, start date field). Description: Returns the number of days between the start date field and the end date field. Only date fields can be passed in. Example: DATEDIFF([Termination Date], [Hire Date]) returns the number of days between "Hire Date" and "Termination Date" in the same row.
Date and time	DATE_ADD	Usage: DATE_ADD(start date, numeric field). Description: Returns the date after the number of days specified by the numeric field, starting from the start date. Example: DATE_ADD([Inbound Date], 1) returns the date one day after goods are put into storage.
Date and time	DATE_SUB	Usage: DATE_SUB(start date, numeric field). Description: Returns the date before the number of days specified by the numeric field, starting from the start date. Example: DATE_SUB([Outbound Date], 1) returns the date one day before goods are shipped out.
Date and time	DATE_TRUNC	Usage: DATE_TRUNC(format, date field). Description: Returns the truncated time/date for the time or date. Supported format inputs include year, week, day, and hour. Example: DATE_TRUNC(week, [Order Time]) returns the first day of the week for the "Order Time" value in the row.
Date and time	DAYOFMONTH	Usage: DAYOFMONTH(date field). Description: Returns the day-of-month value for the date. Only date fields can be passed in. Example: DAYOFMONTH([Order Time]) returns the day value for the "Order Time" field in the row.
Date and time	DAYOFWEEK	Usage: DAYOFWEEK(date/field). Description: Returns which day of the week the date is. Sunday returns 1, Monday returns 2, and so on. Example: DAYOFWEEK(2021-01-01) returns 6, indicating Friday.
Date and time	FROM_UNIXTIME	Usage: FROM_UNIXTIME(timestamp field). Description: Converts a UNIX timestamp to a date. Second-level timestamps are supported. The input parameter is a UNIX timestamp. Example: FROM_UNIXTIME([Timestamp Field]), FROM_UNIXTIME(1441509383).
Date and time	HOUR	Usage: HOUR(date field). Description: Returns the hour value for the date. Only date fields can be passed in. Example: HOUR([Order Time]) returns the hour value for the "Order Time" field in the row.
Date and time	LAST_DAY	Usage: LAST_DAY(field). Description: Returns the last day of the month for the date. Only date fields can be passed in. Example: LAST_DAY([Order Time]) returns 2016-03-31 if the order time in the row is 2016-03-15.
Date and time	MINUTEDIFF	Usage: MINUTEDIFF(end date field, start date field). Description: Returns the number of minutes between the start date field and the end date field. Date or Timestamp fields can be passed in. Example: MINUTEDIFF([Termination Date], [Hire Date]) returns the number of minutes between "Hire Date" and "Termination Date" in the same row.
Date and time	MONTH	Usage: MONTH(date field). Description: Returns the month for the date. Only date fields can be passed in. Example: MONTH([Order Time]) returns the month for the "Order Time" field in the row.
Date and time	MONTHS_BETWEEN	Usage: MONTHS_BETWEEN(date field 1, date field 2). Description: Returns the number of months between two dates. If the interval is less than one month, a decimal value is calculated by days. Only date fields can be passed in. Example: MONTHS_BETWEEN([Termination Date], [Hire Date]) returns the number of months between "Hire Date" and "Termination Date" in the same row.
Date and time	NOW	Usage: NOW(). Description: Returns the current system time. No parameter is required. Because data is cached, results accurate to hours, minutes, and seconds may be inaccurate. Example: NOW().
Date and time	QUARTER	Usage: QUARTER(date field). Description: Returns the quarter of the year for the date. Only date fields can be passed in. Example: QUARTER([Hire Date]) returns the quarter of the year for the hire date.
Date and time	SECONDDIFF	Usage: SECONDDIFF(end date field, start date field). Description: Returns the number of seconds between the start date field and the end date field. Only date or Timestamp fields can be passed in. Example: SECONDDIFF([Termination Date], [Hire Date]) returns the number of seconds between "Hire Date" and "Termination Date" in the same row.
Date and time	TO_DATE	Usage: TO_DATE(date field/text field). Description: Returns the year-month-day part of a date field, or converts a text field to a date type in year-month-day format. Example: TO_DATE([Inbound Date]) returns the year-month-day part of the inbound date.
Date and time	WEEKOFYEAR	Usage: WEEKOFYEAR(date field). Description: Returns the week number of the year for the date. Only date fields can be passed in. Example: WEEKOFYEAR([Hire Date]) returns which week of the year the hire date falls in.
Date and time	YEAR	Usage: YEAR(date field). Description: Returns the year for the date. Only date fields can be passed in. Example: YEAR([Order Time]) returns the year for the "Order Time" field in the row.
String	BASE64	Usage: BASE64(field). Description: Returns the Base64 encoded value. Example: BASE64([Field]) returns the Base64 encoded value.
String	CONCAT	Usage: CONCAT(field1, field2...). Description: Returns a string formed by concatenating each parameter in order. Example: CONCAT([Product ID], [Type ID]) returns the concatenated string of product ID and type ID.
String	CONCAT_WS	Usage: CONCAT_WS(specified separator, [field], [field], ...). Description: Concatenates strings with the specified separator. Example: CONCAT_WS(-,A,B,C,D,E) returns A-B-C-D-E.
String	INSTR	Usage: INSTR(text field, search string). Description: Returns the position of the first occurrence of the search string in the text field value. The result is an integer greater than 0. If not found, returns 0. Example: INSTR([Name], "r") returns the position of the first occurrence of "r" in the Name field.
String	LENGTH	Usage: LENGTH(string). Description: Returns the length of the string. Example: LENGTH([Product Name]) returns the length of the product name.
String	LOWER	Usage: LOWER(expression/field). Description: Returns a string in which all values of the expression or field are converted to lowercase. Example: LOWER("ABC") returns "abc".
String	REGEXP_EXTRACT	Usage: REGEXP_EXTRACT(string, regular expression, index). Description: Returns the regular expression parsing result of the string. Note that \ in a regular expression needs to be escaped with \. For example, to match digits, use \\d instead of \d. The "index" is the returned result: 0 returns the full result, and 1 returns the first matching group in the string. Example: If field [Date] is DATE type and the date is 2020-12-12, REGEXP_EXTRACT([Date],(\\d+)-(\\d+)-(\\d+),1) returns 2020; REGEXP_EXTRACT([Date],(\\d+)-(\\d+)-(\\d+),0) returns 2020-12-12.
String	REGEXP_REPLACE	Usage: REGEXP_REPLACE(string A, regular expression, string B). Description: Returns the part of string A that matches the regular expression parsing result and replaces it with string B. Note that \ in a regular expression needs to be escaped with \. For example, to match digits, use \\d instead of \d. Example: If a product name contains a digit, such as app3le, REGEXP_REPLACE([Product Name],(\\d+)+,A) replaces the numeric part of the product name with character A, such as appAle.
String	REPEAT	Usage: REPEAT(string, numeric value). Description: Returns a new string generated by repeating the string the specified number of times. Example: REPEAT([Product Name], 2) returns the product name repeated twice.
String	REVERSE	Usage: REVERSE(string). Description: Returns a new string with the characters reversed. Example: REVERSE([Type ID]) returns the reversed string of the type ID.
String	SPLIT	Usage: SPLIT([Field], regular expression, limit). Description: Splits a string by a delimiter or regular expression and returns an array. Delimiters support regular expressions. limit controls the number of elements after splitting. If omitted, all parts are split. Set the field type to Text. Example: SPLIT(A-B-C-D-E,-) returns [A, B, C, D, E].
String	SUBSTR	Usage: SUBSTR(string, start position[, length]). Description: Returns a substring of the specified length starting from the start position. Length is optional. A negative index counts characters backward from the end. Example: SUBSTR([Product Type], 4) returns the substring from index 4 to the end of the product type. SUBSTR([Product Type], -4) returns the last 4 characters of the product type.
String	TRIM	Usage: TRIM(expression/field). Description: Removes spaces from both sides of data in the expression or field. Example: TRIM(" ABC ") returns "ABC".
String	UNBASE64	Usage: UNBASE64(field). Description: Returns the byte array of the Base64 decoded value. Example: UNBASE64([Field]) returns the byte array of the Base64 decoded value. You can use String() to convert the byte array to the decoded value, that is: String(UNBASE64([Field])).
String	UPPER	Usage: UPPER(expression/field string). Description: Returns a string in which all values of the expression or field are converted to uppercase. Example: UPPER("abc") returns "ABC".
Logical	CASE_WHEN	Usage: CASE WHEN(expression1) THEN result1 ELSE resultn END. Description: Returns the corresponding result when an expression is met. If none are met, returns the default result after ELSE. The END keyword is required at the end. Example: CASE WHEN([Score]>=60 and [Score]<70) THEN Pass WHEN([Score]>=70 and [Score]<90) THEN Good WHEN([Score]>=90) THEN Excellent ELSE Failed END.
Logical	COALESCE	Usage: COALESCE(field1, field2, default). Description: Returns the first non-null value among the parameters. If all values are NULL, returns NULL. Example: COALESCE([Name1], [Name2], Anonymous) returns the first non-null name among the parameters.
Logical	IF	Usage: IF(expression, result1, result2). Description: IF is a judgment function. The expression is a comparison or calculation statement. If the expression evaluates to true, result1 is returned; otherwise, result2 is returned. Example: IF([Order Count] > 500, "Qualified", "Unqualified"). If the value of the "Order Count" field in the row is greater than 500, "Qualified" is returned; otherwise, "Unqualified" is returned.
Numeric	ABS	Usage: ABS(numeric value). Description: Returns the absolute value of the numeric value. Example: ABS(-3) returns 3.
Numeric	CEIL	Usage: CEIL(numeric value). Description: Returns the smallest integer not less than numeric value a. Example: CEIL(4.12) returns 5.
Numeric	FLOOR	Usage: FLOOR(numeric value). Description: Returns the largest integer not greater than numeric value a. Example: FLOOR(4.12) returns 4.
Numeric	LOG	Usage: LOG(numeric value A, [numeric value B]). Description: Calculates the logarithm of B with base A. If omitted, the natural logarithm base is used by default. Example: LOG(2,4) returns 2.0.
Numeric	POW	Usage: POW(numeric value A, numeric value B). Description: Calculates numeric value A raised to the power of numeric value B. Example: POW(4,2) returns 16.0.
Numeric	RAND	Usage: RAND(). Description: Returns a random decimal greater than 0 and less than 1. Example: RAND() returns a random number.
Numeric	ROUND	Usage: ROUND(numeric value A, [integer D]). Description: Returns numeric value A rounded to D decimal places. If omitted, D is 0. Example: ROUND(4.12,1) returns 4.1.
Numeric	SQRT	Usage: SQRT(numeric value). Description: Returns the square root of the numeric value, which must be greater than or equal to zero. Example: SQRT(4) returns 2.0.
Array	EXPLODE	Usage: EXPLODE(expr). Description: Splits one row into multiple rows. This may produce a Cartesian product, so pay attention to changes in the number of data rows when using it. Example: EXPLODE(ARRAY(10, 20)) returns two rows: 10 and 20.
Array	SEQUENCE	Usage: SEQUENCE(start, stop, step). Description: Generates an array of elements from start to stop, inclusive, with step increments. The returned element type is the same as the parameter expression type. Example: SEQUENCE(1, 5) returns [1,2,3,4,5]. SEQUENCE(TO_DATE(2018-01-01), TO_DATE(2018-03-01), interval 1 month) returns [17532, 17563, 17591].
Array	SORT_ARRAY	Usage: SORT_ARRAY(ARRAY[, ascendingOrder]). Description: Sorts elements in an array. You can sort an array after concatenating it with collect_list or collect_set. Example: SORT_ARRAY(ARRAY(b, d, null, c, a), true) returns [null,"a","b","c","d"]. SORT_ARRAY(COLLECT_SET[Date]) returns [17532, 17563, 17591].