Table Summary Mode

1.1 Version

1.2 Application scenarios

The indicator fields in the table can be set for the aggregation method, and through simple clicks, it is convenient for users to quickly obtain the commonly used data calculation results.

1.3 Function introduction

The quick summary method supports the setting of "sum, average, median, maximum, minimum, standard deviation, and variance", as shown in the figure below:

Scope of application: only supported by group tables and cross tables.

Note: For calculation indicators, first summarize the indicators according to the summary method set in the calculation indicator field, and then calculate according to the formula set for the calculation indicators.

Sample data: Use the DEMO "FRDemo_ORDERS".

2. Sum

The default summary method of indicator fields is "sum", and the statistics are the sum of indicatores grouped by dimension fields.

Take the following figure as an example, the "FRDemo_ORDERS" column shows the sum of order amounts for different shipmethods.

3. Average

Averag is to average the indicators grouped according to the dimension field. To facilitate understanding, we drag in "AMOUNT", "Counter", and "AMOUNT" in the indicator fields. And calculate the average of the last "AMOUNT" field.

As shown in the figure below, you can get the "amount (average) = amount (sum) / counter", and the result is the average order amount for each shipmethod.

4. Median

The median is the number in the middle after all the values of the indicators grouped by the dimension field are sorted. Sort the orders whose shipmethod is service Emery according to the amount, and the median is 62,000.

The median is a good way to help users observe the situation in the middle, and the distribution of the data is clearer in general.

5. Maximum/Minimum

Find the maximum/minimum value, group the indicator fields according to the dimension field, and take the grouped maximum/minimum value in the group.

As shown in the figure below, in the shipmethod "Emery", the order with the largest amount is 135,600 and the smallest amount is 25,500.

6. Percentile (only support real-time data)

"Percentile" calculates the data corresponding to the percentile of each value after the indicator fields are grouped according to the dimension field.

For example, you can calculate the data at the "quartile" position and the data at the "median" position.

6.1 Database system that supports settings

Click House, Pivotal Greenplum Database, Oracle, Postgresql (9.4 or above), REDSHIFT, PRESTO, sybaseiq, VERTICA, Alibaba Cloud Max Compute.

Note: When the database system is REDSHIFT, you can only perform aggregation operations that need to be sorted on the same field at the same time (such as deduplication count, median, percentile, approximate deduplication count), and at the same time for 2 or more Fields do this kind of aggregation operation will cause an error.

6.2 Example

The user needs to analyze the battery quality and find the value at the 95th percentile of the remaining capacity under different battery types. The original data is shown in the figure below:

Set to divide the "Battery Type" according to dimensions, and calculate the 95% percentile value of each type of battery, as shown in the figure below:

At the same time, indicators such as 50% percentile and 5% percentile can be calculated, as shown in the figure below:

7. Standard Deviation

To find the standard deviation, take the arithmetic square root of the variance of the indicator grouped by the dimension field.

Both variance and standard deviation are used to measure discrete trends. When the user wants to know whether the difference between the amount of each shipmethod is big, then the variance and standard deviation can be used. As shown in the figure below, the standard deviation of the "DHL" is the largest, which means that in this set of data of the "DHL", the data is relatively discrete, and the gap between the amount is large.

Take the average of the squared value of the difference between each value of the indicator and the average of all values after grouping by the dimension field.

The usage scenario is the same as the standard deviation.