Function List

1. Overview

Guandata BI supports 8 types of functions, including but not limited to aggregate statistics, non-aggregate statistics, date and time, etc.

2. Function List

Note: For convenient practical use, you can directly download the attachment for local storage: Guandata Function Guide.xlsx. The attachment contains various functions and application cases for your reference and learning.

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 the expression or numeric field. Only applicable to numeric fields, null values are not calculated. Example: AVG(sales_amount), returns the average of all non-null values corresponding to the "sales_amount" field.
	COLLECT_LIST	Usage: COLLECT_LIST([field]). Description: Merges multiple rows into one row, returns a non-deduplicated array. COLLECT_LIST is similar to aggregate operations and requires specifying aggregation dimensions, so it can only be used in ETL group aggregation, SQL input, or when creating calculated fields in cards. Field type should be set to text. Example: COLLECT_LIST([ID]), returns [18731, 18634, 18806, 18278, 18663].
	COLLECT_SET	Usage: COLLECT_SET([field]). Description: Merges multiple rows into one row, returns a deduplicated array. COLLECT_SET is similar to aggregate operations and requires specifying aggregation dimensions, so it can only be used in ETL group aggregation, SQL input, or when creating calculated fields in cards. Field type should be set to text. Example: COLLECT_SET([ID]), returns [18731, 18634, 18806, 18278, 18663].
	COUNT	Usage: COUNT(expression/field). Description: Returns the number of data entries for all valid fields in the expression, null values are not calculated. Example: COUNT(sales_amount), returns the number of data entries for all non-null values corresponding to the "sales_amount" field.
	MAX	Usage: MAX(expression/numeric field). Description: Returns the maximum value in the expression or numeric field, only applicable to numeric fields. Example: MAX(sales_amount), returns the maximum value corresponding to the "sales_amount" field.
	MIN	Usage: MIN(expression/numeric field). Description: Returns the minimum value in the expression or numeric field, only applicable to numeric fields. Example: MIN(sales_amount), returns the minimum value corresponding to the "sales_amount" field.
	PERCENTILE	Usage: PERCENTILE([numeric field], percentile). Description: Calculates the median and percentile of numeric type fields. The first parameter is a numeric field; the second parameter range is 0<p<1, such as 0.1, 0.25, 0.75, 0.95. Example: PERCENTILE([math_exam_score], 0.5), returns the median of exam scores.
	SUM	Usage: SUM(expression/numeric field). Description: Returns the sum of all values in the expression or numeric field, only applicable to numeric fields, null values are not calculated. Example: SUM(sales_amount), returns the sum of all non-null values corresponding to the "sales_amount" field.