PERCENTILE

Usage notes

• MaxCompute V2.0 provides new extended functions. If the functions you use involve new data types, including TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY, you must execute a statement to enable the MaxCompute V2.0 data type edition:

  • Session level: Add SET odps.sql.type.system.odps2=true; before the SQL statement that you want to execute, and commit and execute them together.

  • Project level: The project owner can enable the MaxCompute V2.0 data type edition for the project based on the project requirements. Statement:

    setproject odps.sql.type.system.odps2=true;

    The configuration takes effect after 10 to 15 minutes.

    For more information about setproject, see Project commands. For more information about the precautions you must take when enabling the MaxCompute V2.0 data type edition at the project level, see Data type version guide.

• If you use an SQL statement that includes multiple aggregate functions and the project resources are insufficient, memory overflow may occur. We recommend that you optimize the SQL statement or purchase additional computing resources as needed.

Syntax

--Returns a single exact percentile.
double percentile(bigint <colname>, <p>)

--Return multiple exact percentiles as an array. 
array percentile(bigint <colname>, array(<p1> [, <p2>...]))

Description

The value of percentile is calculated from the sequence number 0. For example, the values in a column are 100, 200, and 300 and the values are numbered 0, 1, and 2. If you use this function to calculate the 0.3rd percentile of the column, the value of percentile is 0.6, which is calculated by using the following formula: 2 x 0.3 = 0.6. The value is between the sequence numbers 0 and 1. The result is calculated by using the following formula: 100 + (200 - 100) × 0.6 = 160.

Parameters

• colname: required. A column of the BIGINT type.

• p: required. The percentile. Valid values: [0.0,1.0].

Return value

A value of the DOUBLE or ARRAY type is returned.

Sample data

CREATE TABLE IF NOT EXISTS emp
   (empno BIGINT,
    ename STRING,
    job STRING,
    mgr BIGINT,
    hiredate DATETIME,
    sal BIGINT,
    comm BIGINT,
    deptno BIGINT);
tunnel upload emp.txt emp;   --Replace emp.txt with the actual path (path and name) to which you upload the data file

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

Examples

• Example 1: Calculate the 0.3rd percentile in the sal column.

  Sample statement:

  select percentile(sal, 0.3) from emp;

  The following result is returned:

  +------------+
  | _c0        |
  +------------+
  | 1290.0     |
  +------------+

• Example 2: Use this function with GROUP BY to group all employees based on the deptno column and calculate the 0.3rd percentile of employees in each group in the sal column.

  Sample statement:

  select deptno, percentile(sal, 0.3) from emp group by deptno;

  The following result is returned:

  +------------+------------+
  | deptno     | _c1        |
  +------------+------------+
  | 10         | 1875.0     |
  | 20         | 1475.0     |
  | 30         | 1250.0     |
  +------------+------------+

• Example 3: Use this function with GROUP BY to group all employees based on the deptno column and calculate the 0.3rd, 0.5th, and 0.8th percentiles of employees in each group in the sal column.

  Sample statements:

  set odps.sql.type.system.odps2=true;
  select deptno, percentile(sal, array(0.3, 0.5, 0.8)) from emp group by deptno;

  The following result is returned:

  +------------+------------+
  | deptno     | _c1        |
  +------------+------------+
  | 10         | [1875.0,2450.0,5000.0] |
  | 20         | [1475.0,2975.0,3000.0] |
  | 30         | [1250.0,1375.0,1600.0] |
  +------------+------------+

Implementation logic

The implementation of the PERCENTILE function is similar to the interpolation calculation, which involves the process of sorting, position mapping, calculation of the destination position based on the percentage, and conversion of the destination position into a numerical value.

• Sample statement

  select percentile(x, 0.6) from values(200), (50), (100), (300) as t(x);

• Procedure

  1. Sort column values in ascending order.

     50, 100, 200, 300

  2. Map the column values to sequence numbers that start from 0.

     50(0), 100(1), 200(2), 300(3)

  3. Use the input percentile value to calculate the sorting position of a number.

     (3-0)*0.6=1.8
     -- In this example, 1.8 can be regarded as the number ranked 1.8.

  4. Use the mean interpolation algorithm to calculate the final result.

     --1.8 lies between the first and second positions.
     100+(1.8-1)*(200-100)=180

Related functions

PERCENTILE is an aggregate function. For more information about the functions that are used to calculate the average value of multiple input records and to aggregate parameters, see Aggregate Functions Overview.