PERCENTILE_APPROX

description

Syntax

PERCENTILE_APPROX(expr, DOUBLE p[, DOUBLE compression])

返回第p个百分位点的近似值,p的值介于0到1之间

compression参数是可选项,可设置范围是[2048, 10000],值越大,精度越高,内存消耗越大,计算耗时越长。 compression参数未指定或设置的值在[2048, 10000]范围外,以10000的默认值运行

该函数使用固定大小的内存,因此对于高基数的列可以使用更少的内存,可用于计算tp99等统计值

example

  1. MySQL > select `table`, percentile_approx(cost_time,0.99) from log_statis group by `table`;
  2. +---------------------+---------------------------+
  3. | table | percentile_approx(`cost_time`, 0.99) |
  4. +----------+--------------------------------------+
  5. | test | 54.22 |
  6. +----------+--------------------------------------+
  7. MySQL > select `table`, percentile_approx(cost_time,0.99, 4096) from log_statis group by `table`;
  8. +---------------------+---------------------------+
  9. | table | percentile_approx(`cost_time`, 0.99, 4096.0) |
  10. +----------+--------------------------------------+
  11. | test | 54.21 |
  12. +----------+--------------------------------------+

keywords

PERCENTILE_APPROX,PERCENTILE,APPROX