Use approximate functions to calculate the approximate number of distinct values and the approximate percentile - AnalyticDB

In scenarios that involve large amounts of data, approximate functions can be used to quickly return calculation results to ensure accuracy and improve data processing efficiency. This topic describes the approximate functions that are supported by AnalyticDB for MySQL.

APPROX_DISTINCT: returns the approximate number of distinct values in a specific column.
APPROX_PERCENTILE: returns the approximate percentile in a specific column at a specific percentage. Values in the column are sorted in ascending order.

APPROX_DISTINCT

APPROX_DISTINCT(column, [standard_error]);

Description: This function returns the approximate number of distinct values in a specific column. The standard_error parameter specifies the standard error. This parameter is optional. Default value: 0.023. Valid values: 0.0040625 to 0.26000. We recommend that you do not modify the value of the standard_error parameter.
Data type of the return value: BIGINT.
Example:
Query the approximate number of distinct values in the age column of the test table.
```
SELECT approx_distinct(age) from test;
```
Sample result:
```
+-----------------------+
| approx_distinct(age)  |
+-----------------------+
|   4                   |
+-----------------------+ 
```
Note
The return value of the APPROX_DISTINCT function is an approximate value. This example is for reference only.

APPROX_PERCENTILE

APPROX_PERCENTILE(column, percentage);

Description: This function returns the approximate percentile in a specific column at a specific percentage. Values in the column are sorted in ascending order. The percentage parameter specifies a percentage. Valid values: 0 to 1. If you set the percentage parameter to 1, the maximum value in the column is returned.
Data type of the return value: BIGINT.

Example:

Query the approximate percentile in the age column of the test table at a percentage of 0.4.

SELECT APPROX_PERCENTILE(age, 0.4) FROM test;

Sample result:

+------------------------------+
| APPROX_PERCENTILE(age, 0.4)  |
+------------------------------+
|   20                         |
+------------------------------+