Common Spark Functions

Before You Start

The Guandata computing engine is built on Spark, so SQL functions supported by Spark can be used on the Guandata platform. See the Spark official documentation.

The following sections list some commonly used functions.

Applicable Scenarios

Applicable to Guan-index datasets, ETL output datasets, and Excel/CSV datasets.

For direct connection database datasets, SQL standard support varies across database types.

---

Numeric

Function	Meaning	Usage	Description	Example
ABS	Absolute value	ABS([Field])	Returns the absolute value.	ABS(-3) returns 3.
CEIL	Round up	CEIL(value/[Field])	Returns the smallest integer not less than the value or field.	CEIL(4.12) returns 5.
FLOOR	Round down	FLOOR(value/[Field])	Returns the largest integer not greater than the value or field.	FLOOR(4.12) returns 4.
INT	Convert to integer	INT([Field])	Returns the largest integer not greater than the value or field. Converts String format to numeric or field type.	INT(1.5) returns 1.
LOG	Logarithm	LOG(value/[Field]A, [value/[Field]B])	Calculates the logarithm of B with base A. If omitted, the natural logarithm base is used by default.	LOG(2,4) returns 2.0.
POW	Power	POW(value/[Field]A, value/[Field]B)	Calculates value or field A raised to the power of value or field B.	POW(4,2) returns 16.0.
RAND	Random number	RAND()	Returns a random decimal greater than 0 and less than 1.	RAND() returns a random number.
ROUND	Round	ROUND(value/[Field]A[, integer D])	Returns value or field A rounded to D decimal places. If omitted, D is 0.	ROUND(4.12,1) returns 4.1.
SQRT	Square root	SQRT(value/[Field])	Returns the square root of the value or field. The value must be greater than or equal to zero.	SQRT(4) returns 2.0.

String

Function	Meaning	Usage	Description	Example
CONCAT	Concatenate strings	CONCAT([Field 1], [Field 2]...)	Returns a string concatenated in order.	CONCAT([Product ID], [Type ID]) returns the concatenated string of product ID and type ID.
FORMAT_STRING	Formatted output	format_string(specified format,[Field])	Formats and outputs the string in the specified form.	format_string('%06d',82343) returns 082343.
INSTR	Find string position	INSTR([Text Field], [Search Field])	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.	INSTR([Name], ',') returns the position of the first occurrence of , in the Name field.
LENGTH	String length	LENGTH([Field])	Returns the length of the string.	LENGTH([Product Name]) returns the product name length.
LOWER	Lowercase	LOWER([Field])	Returns a string where all values in the expression or field are lowercase.	LOWER(ABC) returns abc.
REGEXP_EXTRACT	Parse string with a regular expression	REGEXP_EXTRACT(string, regular expression, index)	Returns the regular expression parsing result. index is the returned result: 0 returns the full result, and 1 returns the first match in the regular expression.	REGEXP_EXTRACT([Product ID], '\\d+', 0) returns the numeric part of the product ID.
REGEXP_REPLACE	Replace string by regular expression	REGEXP_REPLACE(string A, regular expression, string B)	Returns the result after replacing the part of string A that matches the regular expression with string B.	REGEXP_REPLACE([Product Name], '\\d+', '') replaces the numeric part in the product name with an empty string.
REPEAT	Repeat string	REPEAT([Field], numeric value)	Returns a new string generated by repeating the string the specified number of times.	REPEAT([Product Name], 2) returns the product name repeated twice, such as [Product Name][Product Name].
REVERSE	Reverse string	REVERSE([Field])	Returns a new string after reversing the string.	REVERSE([Type ID]) returns CBA when the input is ABC.
SUBSTR	Extract substring	SUBSTR([Field], start position[, length])	Returns a substring of the specified length starting from the start position. Length is optional. A negative index counts characters backward from the end.	SUBSTR([Product Type], 4) returns the substring from index 4 to the end of product type. SUBSTR([Product Type], -4) returns the last 4 characters of product type.
TRIM	Remove spaces	TRIM([Field])	Removes spaces from both sides of data in the expression or field.	TRIM(" ABC ") returns ABC.
UPPER	Uppercase	UPPER([Field])	Returns a string where all values in the expression or field are uppercase.	UPPER("abc") returns ABC.

Aggregate Statistics

Function	Meaning	Usage	Description	Example
AVG	Average	AVG([Field])	Returns the average of all values in the field. Only applies to numeric fields. NULL values are not calculated.	AVG(Sales Amount) returns the average of all non-null values in the Sales Amount field.
COUNT	Count	COUNT([Field])	Returns the number of valid data entries in the expression or field. NULL values are not calculated.	COUNT(Sales Amount) returns the number of non-null entries in the Sales Amount field.
COUNT_DISTINCT	Distinct count	COUNT(DISTINCT([Field]))	Counts distinct values and returns the number of different valid data entries in the expression or field. Null values are not calculated.	COUNT(DISTINCT(Sales Amount)) returns the number of distinct non-null entries in the Sales Amount field.
MAX	Maximum	MAX([Field])	Returns the maximum value in the expression or numeric field. Only applies to numeric fields.	MAX(Sales Amount) returns the maximum value in the Sales Amount field.
MIN	Minimum	MIN([Field])	Returns the minimum value in the expression or numeric field. Only applies to numeric fields.	MIN(Sales Amount) returns the minimum value in the Sales Amount field.
PERCENTILE	Median/percentile	PERCENTILE([Field], percentile)	Calculates the median and percentiles of a numeric type. The first parameter is numeric. The second parameter ranges from 0 to 1, such as 0.1, 0.25, 0.75, and 0.95.	PERCENTILE([Math Exam Score], 0.5) returns the median exam score.
STDDEV	Sample standard deviation	STDDEV([Field])	Returns the sample standard deviation.	STDDEV([Sales Amount])
STDDEV_POP	Population standard deviation	STDDEV_POP([Field])	Returns the population standard deviation.	STDDEV_POP([Sales Amount])
SUM	Sum	SUM([Field])	Returns the sum of all values in the expression or numeric field. Only applies to numeric fields. Null values are not calculated.	SUM(Sales Amount) returns the sum of all non-null values in the Sales Amount field.
VAR_POP	Population variance	VAR_POP([Field])	Returns the population variance.	VAR_POP([Sales Amount])
VARIANCE	Sample variance	VARIANCE([Field])	Returns the sample variance.	VARIANCE([Sales Amount])

Non-Aggregate Statistics

Function	Meaning	Usage	Description	Example
GREATEST	Compare maximum	GREATEST([Field 1], [Field 2]...)	Compares values across columns in the same row and returns the largest value.	GREATEST([Department 1 Sales], [Department 2 Sales]) returns the larger value between Department 1 Sales and Department 2 Sales in the row.
LEAST	Compare minimum	LEAST([Field 1], [Field 2]...)	Compares values across columns in the same row and returns the smallest value.	LEAST([Department 1 Sales], [Department 2 Sales]) returns the smaller value between Department 1 Sales and Department 2 Sales in the row.

Logical

Function	Meaning	Usage	Description	Example
CASE WHEN	Judgment function	CASE WHEN(expression 1) THEN result 1 WHEN (expression 2) THEN result 2 ... ELSE result n END	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.	CASE WHEN([Score]>=60 and [Score]<70) THEN 'Pass' WHEN ([Score]>=70 and [Score]<80) THEN 'Average' WHEN ([Score]>=80 and [Score]<90) THEN 'Good' WHEN ([Score]>=90) THEN 'Excellent' ELSE 'Failed' END
COALESCE	Return non-null value	COALESCE([Field 1], [Field 2], 'Default')	Returns the first non-null value in a set of fields. If all values are NULL, returns NULL.	COALESCE([Name 1], [Name 2], 'Anonymous') returns the first non-null name among the parameters. If all are null, returns Anonymous.
IF	Judgment function	IF(expression, result 1, result 2)	Returns result 1 if the expression is met; otherwise returns result 2.	IF([Score]>80,'Qualified','Unqualified')