DocsICP FilingConsole
官方文档
首页

MEDIAN

更新时间：
复制 MD 格式
产品详情
我的收藏

The MEDIAN function calculates the median.

Limits

  • Window functions are supported only in SELECT statements.

  • A window function cannot contain nested window functions or nested aggregate functions.

  • Window functions cannot be used together with aggregate functions at the same level.

Precautions

  • If a function involves new data types, such as TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, or BINARY, you must enable MaxCompute V2.0 data types before you use the function. To do this, execute one of the following commands:

    • Session level: Add the SET odps.sql.type.system.odps2=true; statement before the SQL statement. Then, submit both statements together.

    • Project level: A project owner can run the setproject odps.sql.type.system.odps2=true; command to enable MaxCompute V2.0 data types for a project.

    For more information about the setproject command, see Project operations. For more information about the precautions for enabling project-level data types, see Data type editions.

  • The number of elements in a single worker cannot exceed 2,000,000.

  • If you use multiple aggregate functions in a single SQL statement, an out-of-memory (OOM) error may occur if the project has insufficient resources. In this case, you can optimize the SQL statement or purchase more computing resources.

Command format

-- Calculate the median.
DOUBLE MEDIAN(DOUBLE <colname>)
DECIMAL MEDIAN(DECIMAL <colname>)

-- Calculate the median of expr in a window.
MEDIAN(<expr>) OVER ([partition_clause] [orderby_clause] [frame_clause])

Parameters

  • colname: Required. The column can be of the DOUBLE or DECIMAL data type. If it is of the STRING or BIGINT data type, its values are implicitly converted to DOUBLE for the calculation.

  • expr: Required. The expression for which to calculate the median. The expression must evaluate to a value of the DOUBLE or DECIMAL type. You can specify a minimum of 1 and a maximum of 255 numbers.

    If the input value is of the STRING or BIGINT type, it is implicitly converted to the DOUBLE type for the calculation. Other data types cause an error.

  • partition_clause, orderby_clause, and frame_clause: For more information, see windowing_definition.

Return value

  • If a row contains a NULL value for colname, it is not included in the calculation. The rules for other types are as follows:

    Input type

    Return type

    TINYINT

    DOUBLE

    SMALLINT

    DOUBLE

    INT

    DOUBLE

    BIGINT

    DOUBLE

    FLOAT

    DOUBLE

    DOUBLE

    DOUBLE

    DECIMAL

    DECIMAL

  • If expr is of the DECIMAL type, a value of the DECIMAL type is returned. Otherwise, a value of the DOUBLE type is returned. If all values of expr are NULL, this function returns NULL.

Examples

Sample data

  1. Create a table named dept_sal.

    CREATE TABLE IF NOT EXISTS dept_sal (
 deptno BIGINT,
 ename STRING,
 sal BIGINT
 );

  2. Insert test data.

    INSERT INTO dept_sal VALUES
      (10, 'KING',   5000),
      (10, 'CLARK',  2450),
      (10, 'MILLER', 1300),
      (20, 'SCOTT',  3000),
      (20, 'FORD',   3000),
      (20, 'JONES',  2975),
      (30, 'BLAKE',  2850),
      (30, 'ALLEN',  1600),
      (30, 'WARD',   1250);

Example 1: Calculate the median salary of all employees

SELECT MEDIAN(sal) FROM dept_sal;

-- The following result is returned:
+------------+
| _c0        |
+------------+
| 2850.0     |
+------------+

Example 2: Calculate the median salary for each department

Use this function with GROUP BY to group all employees by department (deptno) and calculate the median salary (sal) for each department.

SELECT deptno, MEDIAN(sal) FROM dept_sal GROUP BY deptno;

-- The following result is returned:
+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 2450.0     |
| 20         | 3000.0     |
| 30         | 1600.0     |
+------------+------------+

Example 3: Calculate the median salary within a department window

You can specify the department (deptno) as the window column to calculate the median salary (sal). The function returns the median for the current window, which includes employees with the same deptno.

SELECT deptno, sal, MEDIAN(sal) OVER (PARTITION BY deptno) FROM dept_sal;

-- The following result is returned:
+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 5000       | 2450.0     |
| 10         | 2450       | 2450.0     |
| 10         | 1300       | 2450.0     |
| 20         | 3000       | 3000.0     |
| 20         | 3000       | 3000.0     |
| 20         | 2975       | 3000.0     |
| 30         | 2850       | 1600.0     |
| 30         | 1600       | 1600.0     |
| 30         | 1250       | 1600.0     |
+------------+------------+------------+

Related functions

MEDIAN can be used as an aggregate function or a window function.

  • For more information about aggregate functions that perform calculations on a set of records and return a single value, see Aggregate functions.

  • For more information about window functions that perform calculations across a set of table rows related to the current row, see Window functions.

Previous：MAXNext：MIN