Built-in Aggregate Functions for Statistical Analysis - Quick BI - Alibaba Cloud - Quick BI

Aggregate functions perform calculations on a set of values and return a single summary result, such as a sum, average, maximum, or minimum. Use these built-in aggregate functions to summarize large datasets.

BI_AVG

The BI_AVG function calculates the average of a set of values.

Syntax	`BI_AVG(number)`
Arguments	`number`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a numeric value.
Description	average
Return value	Number
Example	`BI_AVG([Order Amount])` Calculates the average of [Order Amount] based on the dimensions used in the chart.

BI_COUNT

The BI_COUNT function returns the count of items in a group.

Syntax	`BI_COUNT(expression)`
Arguments	`expression`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field.
Description	count
Return value	Number
Example	`BI_COUNT([Order ID])` Calculates the total number of orders based on the dimensions used in the chart.

BI_COUNTD

The BI_COUNTD function returns the number of distinct (unique) items in a group.

Syntax	`BI_COUNTD(expression)`
Arguments	`expression`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field.
Description	distinct count
Return value	Number
Example	`BI_COUNTD([Order ID])` Calculates the number of unique order IDs based on the dimensions used in the chart.

BI_MAX

The BI_MAX function returns the maximum value in a set of values.

Syntax	`BI_MAX(expression)`
Arguments	`expression`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a comparable value.
Description	maximum value
Return value	Number
Example	`BI_MAX([Order Amount])` Calculates the maximum [Order Amount] based on the dimensions used in the chart.

BI_MEDIAN

The BI_MEDIAN function returns the median value of a set of values.

Syntax	`BI_MEDIAN(number)`
Arguments	`number`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a numeric value.
Description	median
Return value	Number
Example	`BI_MEDIAN([Order Amount])` Calculates the median of [Order Amount] based on the dimensions used in the chart.
Limitations	This function is not supported by the following data sources: MySQL, AnalyticDB for MySQL 3.0, Hologres, PostgreSQL, SQL Server, Lindorm, Elasticsearch, Simple Log Service (SLS), IBM DB2 LUW_V10, Gbase 8a, and Vertica.

BI_MIN

The BI_MIN function returns the minimum value in a set of values.

Syntax	`BI_MIN(expression)`
Arguments	`expression`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a comparable value.
Description	minimum value
Return value	Number
Example	`BI_MIN([Order Amount])` Calculates the minimum [Order Amount] based on the dimensions used in the chart.

BI_PERCENTILE

The BI_PERCENTILE function returns the value from an expression at a specified percentile.

Syntax	`BI_PERCENTILE(expression, number)`
Arguments	`expression`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a numeric value. `number`: A numeric constant between 0 and 1, inclusive.
Description	Returns the value at the specified percentile from the dataset.
Return value	Number
Example	`BI_PERCENTILE([Order Amount], 0.9)` Calculates the 90th percentile value for [Order Amount] based on the dimensions used in the chart.
Limitations	This function is not supported by the following data sources: MySQL, AnalyticDB for MySQL 3.0, SQL Server, Lindorm, Shentong, IBM DB2 LUW_V10, Simple Log Service (SLS), Gbase 8a, and Vertica.

BI_STDEV

The BI_STDEV function calculates the sample standard deviation of a set of values.

Syntax	`BI_STDEV(number)`
Arguments	`number`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a numeric value.
Description	sample standard deviation
Return value	Number
Example	`BI_STDEV([Order Amount])` Calculates the sample standard deviation of [Order Amount] based on the dimensions used in the chart.

BI_STDEVP

The BI_STDEVP function calculates the population standard deviation of a set of values.

Syntax	`BI_STDEVP(number)`
Arguments	`number`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a numeric value.
Description	population standard deviation
Return value	Number
Example	`BI_STDEVP([Order Amount])` Calculates the population standard deviation of [Order Amount] based on the dimensions used in the chart.

BI_SUM

The BI_SUM function returns the sum of a set of values.

Syntax	`BI_SUM(number)`
Arguments	`number`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a numeric value.
Description	sum
Return value	Number
Example	`BI_SUM([Order Amount])` Calculates the sum of [Order Amount] based on the dimensions used in the chart.

BI_VAR

The BI_VAR function calculates the sample variance of a set of values.

Syntax	`BI_VAR(number)`
Arguments	`number`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a numeric value.
Description	sample variance
Return value	Number
Example	`BI_VAR([Order Amount])` Calculates the sample variance of [Order Amount] based on the dimensions used in the chart.

BI_VARP

The BI_VARP function calculates the population variance of a set of values.

Syntax	`BI_VARP(number)`
Arguments	`number`: Must be the result of a non-aggregate expression. It can be a dimension field, measure field, or a calculated field that returns a numeric value.
Description	population variance
Return value	Number
Example	`BI_VARP([Order Amount])` Calculates the population variance of [Order Amount] based on the dimensions used in the chart.
Limitations	This function is not supported by the following data sources: Lindorm (wide table engine, multi-model SQL) and Shentong.