An aggregate function is a commonly used function in data analysis. It can summarize a set of data before computing. This type of function can help you extract useful information overview from a large amount of data, such as the total, average, maximum, and minimum. This topic describes how to use aggregate functions in built-in functions.
BI_AVG
BI_AVG is used to calculate the average value of data in a specified dimension.
Syntax | BI_AVG(number) |
Description | number: must be the result returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field calculated by using a common formula. |
Description | Average |
Output data type | N/A |
Examples | BI_AVG([order amount]) Calculate the average value of the order amount based on the dimensions used in the chart. |
BI_COUNT
BI_COUNT is used to count data of a specified dimension.
Syntax | BI_COUNT(expression) |
Description | expression: the result that is returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field that is calculated by using a common formula. |
Description | The count. |
Output data type | N/A |
Examples | BI_COUNT([Order ID]) Aggregates the number of orders based on the dimensions used in the chart. |
BI_COUNTD
BI_COUNTD is used to deduplicate data in a specified dimension.
Syntax | BI_COUNTD(expression) |
Description | expression: the result that is returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field that is calculated by using a common formula. |
Description | DISTINCT |
Output data type | N/A |
Examples | BI_COUNTD([Order ID]) Aggregates the dimensions used in the chart to obtain the quantity after the order ID is deduplication. |
BI_MAX
BI_MIN is used to obtain the maximum value for a specified dimension.
Syntax | BI_MAX(expression) |
Description | expression: the result that is returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field that is calculated by using a common formula. |
Description | The maximum value. |
Output data type | N/A |
Examples | BI_MAX([order amount]) Aggregates the dimensions used in the chart to obtain the maximum order amount. |
BI_MEDIAN
BI_MEDIAN is used to obtain the median of data from a specified dimension.
Syntax | BI_MEDIAN(number) |
Description | number: must be the result returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field calculated by using a common formula. |
Description | Median Value |
Output data type | N/A |
Examples | BI_MEDIAN([Order Amount]) Aggregate based on the dimensions used in the chart to find the median of the order amount. |
Limits | MySQL, AnalyticDB for MySQL 3.0, Hologres, PostgreSQL, and SQL Server data sources are not supported. |
BI_MIN
BI_MIN is used to obtain the minimum value for a specified dimension.
Syntax | BI_MIN(expression) |
Description | expression: the result that is returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field that is calculated by using a common formula. |
Description | The minimum value. |
Output data type | N/A |
Examples | BI_MIN([order amount]) Aggregates the dimensions used in the chart to find the minimum order amount. |
BI_PERCENTILE
BI_PERCENTILE is used to return the value at the percentile corresponding to the specified number from the given expression based on the current analysis dimension.
Syntax | BI_PERCENTILE(expression, number) |
Description |
|
Description | Returns the value at the percentile corresponding to the specified <number> from the given expression. <number> must be between 0 and 1 and must be a numeric constant. |
Output data type | N/A |
Examples | BI_PERCENTILE([Order Amount], 0.9) Calculate the 90% percentile of the order amount based on the dimensions used in the chart. |
Limits | MySQL, AnalyticDB for MySQL 3.0, ClickHouse, and SQL Server data sources are not supported. |
BI_STDEV
BI_STDEV is used to calculate the sample standard deviation for data of a specified dimension.
Syntax | BI_STDEV(number) |
Description | number: must be the result returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field calculated by using a common formula. |
Description | Sample Standard Deviation |
Output data type | N/A |
Examples | BI_STDEV([order amount]) Aggregates the sample standard deviation of the order amount based on the dimensions used in the chart. |
BI_STDEVP
BI_STDEVP is used to calculate the population standard deviation of data from a specified dimension.
Syntax | BI_STDEVP(number) |
Description | number: must be the result returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field calculated by using a common formula. |
Description | Population Standard Deviation |
Output data type | N/A |
Examples | BI_STDEVP([Order Amount]) Aggregates the dimensions used in the chart to obtain the overall standard deviation of the order amount. |
BI_SUM
BI_SUM is used to sum data of a specified dimension.
Syntax | BI_SUM(number) |
Description | number: must be the result returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field calculated by using a common formula. |
Description | Total price |
Output data type | N/A |
Examples | BI_SUM([order amount]) Aggregate based on the dimensions used in the chart to find the sum of the order amounts. |
BI_VAR
BI_VAR is used to calculate the sample variance for data of a specified dimension.
Syntax | BI_VAR(number) |
Description | number: must be the result returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field calculated by using a common formula. |
Description | Population variance |
Output data type | N/A |
Examples | BI_VAR([order amount]) Aggregates the sample variance of the order amount based on the dimensions used in the chart. |
BI_VARP
BI_VARP is used to calculate the population variance for data of a specified dimension.
Syntax | BI_VARP(number) |
Description | number: must be the result returned by a non-aggregate function formula. It can be a dimension field, a measure field, or a result field calculated by using a common formula. |
Description | Population variance |
Output data type | N/A |
Examples | BI_VARP([order amount]) Aggregates the dimensions used in the chart to calculate the overall variance of the order amount. |