MIN aggregate and window function to calculate the minimum value - MaxCompute

MIN

Returns the minimum value in a column or within a window partition. NULL values are ignored.

Syntax

Aggregate function

min(<colname>)

Window function

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

Parameters

Parameter	Required	Description
`colname`	Yes	The column to evaluate (aggregate form). Accepts any data type except BOOLEAN. NULL rows are excluded from the calculation.
`expr`	Yes	The expression to evaluate (window form). Accepts any data type except BOOLEAN. NULL rows are excluded from the calculation.
`partition_clause`, `orderby_clause`, `frame_clause`	No	Window definition clauses. For details, see windowing_definition.

Return value

Returns the same data type as colname (aggregate form) or expr (window form).
If colname is of type BOOLEAN, it cannot be used in the calculation.

Usage notes

MIN as a window function is supported only in SELECT statements.
Window functions cannot contain nested window functions or nested aggregate functions.
Window functions cannot be used together with aggregate functions at the same query level.
When ORDER BY is omitted from the OVER clause, the window frame defaults to all rows in the partition, and MIN returns the same minimum value for every row in that partition.
When ORDER BY is specified, MIN returns the running minimum from the first row to the current row within each partition.

Sample data

The examples in this section use a table named emp. 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 rows:

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: Window minimum without ORDER BY

Partition by deptno with no ORDER BY. Because the frame covers all rows in each partition, MIN returns the same minimum sal value for every row in the same department.

SELECT deptno, sal, MIN(sal) OVER (PARTITION BY deptno) FROM emp;

Result:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |
| 10         | 2450       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 1300       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 2450       | 1300       |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 30         | 1500       | 950        |
| 30         | 950        | 950        |
| 30         | 1600       | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

Example 2: Running minimum with ORDER BY

Partition by deptno with ORDER BY sal. MIN returns the running minimum from the first row to the current row within each partition.

SELECT deptno, sal, MIN(sal) OVER (PARTITION BY deptno ORDER BY sal) FROM emp;

Result:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |
| 10         | 1300       | 1300       |
| 10         | 2450       | 1300       |
| 10         | 2450       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 5000       | 1300       |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 30         | 950        | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 1500       | 950        |
| 30         | 1600       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

Example 3: Global minimum

Return the lowest salary across all employees.

SELECT MIN(sal) FROM emp;

Result:

+------------+
| _c0        |
+------------+
| 800        |
+------------+

Example 4: Minimum per group

Group employees by department and return the lowest salary in each department.

SELECT deptno, MIN(sal) FROM emp GROUP BY deptno;

Result:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 10         | 1300       |
| 20         | 800        |
| 30         | 950        |
+------------+------------+

Related functions

For aggregate functions that compute averages and other summaries across multiple rows, see Aggregate functions.
For window functions that compute running totals, rankings, and other ordered calculations, see Window functions.