MEDIAN

Syntax

Aggregate function

double median(double <colname>)
decimal median(decimal <colname>)

Window function

median(<expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Parameters

Return value

Returns DECIMAL or DOUBLE based on the following rules:

• If colname is DECIMAL, returns DECIMAL.

• If colname is any other valid data type, returns DOUBLE.

• If colname is BOOLEAN, that value is excluded from the calculation.

• If colname is NULL, that row is excluded from the calculation.

Usage notes

Window function constraints:

• Window functions are only supported in SELECT statements.

• A window function cannot contain nested window functions.

• A window function cannot contain nested aggregate functions.

• Window functions and aggregate functions of the same level cannot be used together.

MaxCompute V2.0 data type edition:

If your query uses new data types (TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, BINARY), enable the MaxCompute V2.0 data type edition:

• Session level: Add set odps.sql.type.system.odps2=true; before your SQL statement and run them together.

• Project level: Run setproject odps.sql.type.system.odps2=true;. The change takes effect after 10 to 15 minutes. For details, see Project operations and Data type editions.

A worker supports a maximum of 2 million elements. If a query with multiple aggregate functions causes memory overflow, optimize the SQL statement or purchase computing resources based on your business requirements.

Sample data

The examples in this topic use the emp table. Run the following statements to create the table and load the 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 to your data file

The emp.txt file contains the following data:

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 median salary across all employees.

select median(sal) from emp;

Result:

+------------+
| _c0        |
+------------+
| 1600.0     |
+------------+

Example 2: Use GROUP BY to calculate the median salary per department.

select deptno, median(sal) from emp group by deptno;

Result:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2450.0     |
| 20         | 2975.0     |
| 30         | 1375.0     |
+------------+------------+

Example 3: Use MEDIAN as a window function with PARTITION BY to return each row's department-level median salary alongside the individual salary.

select deptno, sal, median(sal) over (partition by deptno) from emp;

Each row returns the median of all salaries in the same department. Result:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2450.0     |   -- This row is the first row of this window. The return value is the median value of the values from the first row to the sixth row.
| 10         | 2450       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 1300       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 2450       | 2450.0     |
| 20         | 3000       | 2975.0     |
| 20         | 3000       | 2975.0     |
| 20         | 800        | 2975.0     |
| 20         | 1100       | 2975.0     |
| 20         | 2975       | 2975.0     |
| 30         | 1500       | 1375.0     |
| 30         | 950        | 1375.0     |
| 30         | 1600       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 2850       | 1375.0     |
+------------+------------+------------+

Related functions

MEDIAN is an aggregate function and a window function.

• For aggregate functions, see Aggregate functions.

• For window functions, see Window functions.