Aggregate functions summarize a set of values to compute a single result. They help you extract useful summaries from large datasets, such as totals, averages, maximums, and minimums. This topic describes how to use the built-in aggregate functions.
BI_AVG
BI_AVG calculates the average value of data in a specified dimension.
Syntax | BI_AVG(number) |
Parameter description | number: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Average value |
Output | Numeric value |
Example | BI_AVG([Order amount]) Aggregates by the dimensions used in the chart and returns the average order amount. |
BI_COUNT
BI_COUNT counts the number of values in a specified dimension.
Syntax | BI_COUNT(expression) |
Parameter description | expression: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Count |
Output | Numeric value |
Example | BI_COUNT([Order ID]) Aggregates by the dimensions used in the chart and returns the number of orders. |
BI_COUNTD
BI_COUNTD counts distinct values in a specified dimension.
Syntax | BI_COUNTD(expression) |
Parameter description | expression: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Distinct count |
Output | Numeric value |
Example | BI_COUNTD([Order ID]) Aggregates by the dimensions used in the chart and returns the number of unique order IDs. |
BI_MAX
BI_MAX returns the maximum value in a specified dimension.
Syntax | BI_MAX(expression) |
Parameter description | expression: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Maximum |
Output | Numeric value |
Example | BI_MAX([Order amount]) Aggregates by the dimensions used in the chart and returns the highest order amount. |
BI_MEDIAN
BI_MEDIAN returns the median value in a specified dimension.
Syntax | BI_MEDIAN(number) |
Parameter description | number: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Median |
Output | Numeric value |
Example | BI_MEDIAN([Order amount]) Aggregates by the dimensions used in the chart and returns the median order amount. |
Limits | Not supported for MySQL, AnalyticDB for MySQL 3.0, Hologres, PostgreSQL, SQL Server, Lindorm, Elasticsearch, SLS, IBM DB2 LUW_V10, Gbase 8a, and Vertica data sources. |
BI_MIN
BI_MIN returns the minimum value in a specified dimension.
Syntax | BI_MIN(expression) |
Parameter description | expression: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Minimum |
Output | Numeric value |
Example | BI_MIN([Order amount]) Aggregates by the dimensions used in the chart and returns the lowest order amount. |
BI_PERCENTILE
BI_PERCENTILE returns the value at a specified percentile for a given expression within the current analysis dimensions.
Syntax | BI_PERCENTILE(expression, number) |
Parameter description |
|
Definition | Returns the value at the percentile specified by <number> for the given expression. <number> must be between 0 and 1 and must be a numeric constant. |
Output | Numeric value |
Example | BI_PERCENTILE([Order amount], 0.9) Aggregates by the dimensions used in the chart and returns the value at the 90th percentile of order amounts. |
Limits | Not supported for MySQL, AnalyticDB for MySQL 3.0, ClickHouse, SQL Server, Lindorm, Shentong, IBM DB2 LUW_V10, SLS, Gbase 8a, and Vertica data sources. |
BI_STDEV
BI_STDEV calculates the sample standard deviation of data in a specified dimension.
Syntax | BI_STDEV(number) |
Parameter description | number: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Sample standard deviation |
Output | Numeric value |
Example | BI_STDEV([Order amount]) Aggregates by the dimensions used in the chart and returns the sample standard deviation of order amounts. |
BI_STDEVP
BI_STDEVP calculates the population standard deviation of data in a specified dimension.
Syntax | BI_STDEVP(number) |
Parameter description | number: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Population standard deviation |
Output | Numeric value |
Example | BI_STDEVP([Order amount]) Aggregates by the dimensions used in the chart and returns the population standard deviation of order amounts. |
BI_SUM
BI_SUM calculates the sum of data in a specified dimension.
Syntax | BI_SUM(number) |
Parameter description | number: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Sum |
Output | Numeric value |
Example | BI_SUM([Order amount]) Aggregates by the dimensions used in the chart and returns the total order amount. |
BI_VAR
BI_VAR calculates the sample variance of data in a specified dimension.
Syntax | BI_VAR(number) |
Parameter description | number: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Population variance |
Output | Numeric value |
Example | BI_VAR([Order amount]) Aggregates by the dimensions used in the chart and returns the sample variance of order amounts. |
BI_VARP
BI_VARP calculates the population variance of data in a specified dimension.
Syntax | BI_VARP(number) |
Parameter description | number: The result of a non-aggregate function formula. It can be a dimension field, a measure field, or a field calculated by a regular formula. |
Definition | Population variance |
Output | Numeric value |
Example | BI_VARP([Order amount]) Aggregates by the dimensions used in the chart and returns the population variance of order amounts. |
Limits | Not supported for Lindorm (wide-table engine and multi-model SQL) and Shentong data sources. |