Generally, to achieve an aggregation calculation, you need to use aggregate functions to summarize the values of a set of data and then use the summarized values to perform a further calculation.
What is the total sales of the company (Sum: SUM_AGG(Sales))?
What is the average price of the product (Average: AVG_AGG(Price))?
How many members in total (Distinct Count: COUNTD_AGG(Members))?
What is the maximum order amount of yesterday's orders (Maximum: MAX_AGG(Order Amount))?
As you switch analysis dimensions, calculation fields automatically adjust to the changed dimensions. For example, if the dimension dragged into the field is Month, then the calculation is:
What is the total sales for each month of the company?
What is the average price for each month of the product?
You can also obtain these summary values through the summary modes for tables, but using functions allows you to further calculate these summarized values: when you want to continue writing the total value into a formula, you can only use aggregate functions.
Different aggregate functions correspond to different summary modes, including sum, average, median, maximum, minimum, standard deviation, variance, distinct count, count.
Aggregate functions are generally used for flexible analysis in components, as shown in the following figure.
Type
Function
Usage
Example
Scale
SUM_AGG
Calculating the sum value.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field SUM_AGG(sales) is the total sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field SUM_AGG(sales) is the total sales for each month.
AVG_AGG
Calculate the average.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field AVG_AGG(sales) is the average sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field AVG_AGG(sales) is the average sales for each month.
COUNT_AGG
Calculating the count.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field COUNT_AGG(sales) is the count of sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field COUNT_AGG(sales) is the count of sales for each month.
COUNTD_AGG
Calculating the distinct count.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field COUNTD_AGG(sales) is the distinct count of sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field COUNTD_AGG(sales) is the distinct count of sales for each month.
Describing the whole from a part.
MIN_AGG
Returns the minimum value in the parameter list.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field MIN_AGG(sales) is the minimum sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field MIN_AGG(sales) is the minimum sales for each month.
MAX_AGG
Returns the maximum value in the parameter list.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field MAX_AGG(sales) is the maximum sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field MAX_AGG(sales) is the maximum sales for each month.
MEDIAN_AGG
Calculating the median value.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field MEDIAN_AGG(sales) is the median sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field MEDIAN_AGG(sales) is the median sales for each month.
Calculating the discreteness.
VAR_AGG
Calculating the variance.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field VAR_AGG(sales) is the variance of sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field VAR_AGG(sales) is the variance of sales for each month.
STDEV_AGG
Calculating the standard deviation.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field STDEV_AGG(sales) is the standard deviation of sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field STDEV_AGG(sales) is the standard deviation of sales for each month.
PERCENTILE_AGG
Calculating the percentage.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field PERCENTILE_AGG(sales,0.95) is 95% of the sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field PERCENTILE_AGG(sales,0.95) is 95% of the sales for each month.
APPROX_COUNTD_AGG
Calculating the approximate distinct count.
1. If the dimension field in the lateral axis is Day, the value returned by the calculation field APPROX_COUNTD_AGG() is the approximate distinct count of sales for each day.
2. If the dimension field in the lateral axis is Month, the value returned by the calculation field APPROX_COUNTD_AGG() is the approximate distinct count of sales for each month.
滑鼠選中內容，快速回饋問題
滑鼠選中存在疑惑的內容，即可快速回饋問題，我們將會跟進處理。
不再提示
10s後關閉
Submitted successfully
Network busy