AnalyticDB for MySQL:Approximate functions

• APPROX_DISTINCT: returns the approximate number of distinct values in a specified column.

• APPROX_PERCENTILE: returns the approximate percentile in a specified column at a specified 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 specified column. standard_error 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;

  The following information is returned:

  +-----------------------+
  | 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 specified column at a specified percentage. Values in the column are sorted in ascending order. percentage 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;

  The following information is returned:

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