Approximate functions trade exact precision for speed, making them practical for large-scale queries where full scans of distinct values or precise percentile calculations would be too slow. AnalyticDB for MySQL supports two approximate aggregate functions: APPROX_DISTINCT and APPROX_PERCENTILE.
APPROX_DISTINCT
APPROX_DISTINCT(column[, standard_error])Returns the approximate number of distinct values in a column — the approximate equivalent of COUNT(DISTINCT column).
Parameters
| Parameter | Required | Description |
|---|---|---|
column | Yes | The column to evaluate. |
standard_error | No | The acceptable standard error for the result. Valid values: 0.0040625–0.26000. Default: 0.023. |
Return type: BIGINT
Usage notes
The standard_error parameter controls accuracy. We recommend that you do not modify the value of the standard_error parameter.
Example
Get the approximate number of distinct values in the age column of the test table:
SELECT approx_distinct(age) FROM test;Result:
+-----------------------+
| approx_distinct(age) |
+-----------------------+
| 4 |
+-----------------------+The return value of the APPROX_DISTINCT function is an approximate value. This example is for reference only.
APPROX_PERCENTILE
APPROX_PERCENTILE(column, percentage)Returns the approximate value at the specified percentile in a column. Values are ranked in ascending order before the percentile is computed.
Parameters
| Parameter | Required | Description |
|---|---|---|
column | Yes | The column to evaluate. |
percentage | Yes | The target percentile expressed as a decimal. Valid values: 0–1. For example, 0.5 returns the median, 0.95 returns the 95th percentile, and 1 returns the maximum value. |
Return type: BIGINT
Example
Get the approximate value at the 40th percentile of the age column in the test table:
SELECT APPROX_PERCENTILE(age, 0.4) FROM test;Result:
+------------------------------+
| APPROX_PERCENTILE(age, 0.4) |
+------------------------------+
| 20 |
+------------------------------+