Overview
Version
FineBI Version | Functional Change |
6.0 | / |
6.1.4 | The PERCENTILE_AGG() function is supported for the extracted data. |
Function Description
Syntax | PERCENTILE_AGG(Array,Percentile) | According to the current analysis dimension, the function can return the value at the percentile corresponding to the specified number from the given expression. The percentile must be a numeric constant ranging from 0 to 1, such as 0.66. |
Parameter | Array | The Array parameter must be the result returned by a non-aggregate function formula, which can be a calculation result of an indicator field, dimension or indicator field and a common formula. Text or date insertion is not supported. |
Notes
The function supports two numeric parameters, and the second numeric parameter must be a constant.
Direct connection only supports the following databases: Sybase IQ, Oracle, PostgreSQL (9.4 or higher versions), ClickHouse, Greenplum, Transwarp Inceptor, Redshift, MaxCompute, Teradata, Hologres, DB2 (11.1 or higher versions), Hive, GaussDB(DWS) (8.1.1 or higher versions), and Dremio.
When the direct database is Redshift, only ordered aggregation calculations (such as Median and Percentile) are supported for the same field at the same time, otherwise an error will occur.
When the direct database is MaxCompute or Hive, ordered aggregation calculations are only supported for integer fields.
Example
Calculating 95% of Sales Volume
If the dimension field in the horizontal axis is Day, the value returned by the vertical axis calculation field PERCENTILE_AGG(Sales Volume,0.95) is 95% of the sales per day.
If the dimension field in the horizontal axis is Month, the value returned by the calculation field PERCENTILE_AGG(Sales Volume,0.95) is 95% of the sales per month, as shown in the following figure.
More Examples
If you are a human resources manager, you can use PERCENTILE_AGG to know the salary distribution of employees.
Formula | Definition | Explanation |
PERCENTILE_AGG(Salary,0.25) | Returns the 25th percentile. | The function indicates that 25% of employees are paid less than this value. The result can be used to know the base salary. |
PERCENTILE_AGG(Salary,0.5) | Returns the 50th percentile. | The function indicates that 50% of employees are paid less than this value. The result is the midpoint of the salary distribution. |
PERCENTILE_AGG(Salary,0.9) | Returns the 90th percentile. | The function indicates that 90% of employees are paid less than this value. The result can be used to know the situation of high income. |
The calculation logic of PERCENTILE_AGG is the same as that of Percentile in Excel.
If the percentile is not a multiple of 1/n, the PERCENTILE_AGG function uses interpolation to determine the final value of the percentile.
Previous：STDEV_AGG - Standard Deviation
