LAG window function - MaxCompute SQL-MaxCompute(MaxCompute)-阿里云帮助中心

Syntax

lag(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

Parameters

Parameter	Required	Description
`expr`	Yes	The expression whose value is returned from the row at the specified offset before the current row. Can be a column, column operation, or function call.
`offset`	No	Number of rows back to look (BIGINT constant, ≥ 0). `0` = current row, `1` = previous row. Default: `1`. STRING or DOUBLE values are implicitly converted to BIGINT.
`default`	No	Constant value returned when the offset exceeds the partition boundary. Must be of the same data type as `expr`. Default: NULL. If `expr` is not a constant, the default value is determined from the current row.
`partition_clause`	No	Divides the result set into partitions. LAG operates independently within each partition. Syntax: windowing_definition.
`orderby_clause`	Yes	Defines the row order within each partition. Required because LAG depends on row order. Syntax: windowing_definition.

Return value

Returns a value of the same data type as expr.

Usage notes

Window functions can only appear in SELECT statements.
Window functions cannot be nested, nor can they contain aggregate functions.
Window functions and aggregate functions cannot coexist at the same level in a SELECT statement.

Examples

These examples use the emp table defined in Sample data.

A. Get the previous row's value within each partition

Partition by department, order by salary, and return the previous row's salary within each partition.

SELECT deptno, ename, sal,
       lag(sal, 1) OVER (PARTITION BY deptno ORDER BY sal) AS prev_sal
FROM emp;

The first row in each partition returns NULL because no previous row exists.

deptno	ename	sal	prev_sal
10	TEBAGE	1300	NULL
10	MILLER	1300	1300
10	CLARK	2450	1300
10	WELAN	2450	2450
10	KING	5000	2450
10	JACCKA	5000	5000
20	SMITH	800	NULL
20	ADAMS	1100	800
20	JONES	2975	1100
20	SCOTT	3000	2975
20	FORD	3000	3000
30	JAMES	950	NULL
30	MARTIN	1250	950
30	WARD	1250	1250
30	TURNER	1500	1250
30	ALLEN	1600	1500
30	BLAKE	2850	1600

B. Specify a default value for partition boundaries

Pass a third argument to return 0 instead of NULL when the offset exceeds the partition boundary.

SELECT deptno, ename, sal,
       lag(sal, 1, 0) OVER (PARTITION BY deptno ORDER BY sal) AS prev_sal
FROM emp;

The first row in each partition returns 0 instead of NULL.

deptno	ename	sal	prev_sal
10	TEBAGE	1300	0
10	MILLER	1300	1300
10	CLARK	2450	1300
10	WELAN	2450	2450
10	KING	5000	2450
10	JACCKA	5000	5000
20	SMITH	800	0
20	ADAMS	1100	800
20	JONES	2975	1100
20	SCOTT	3000	2975
20	FORD	3000	3000
30	JAMES	950	0
30	MARTIN	1250	950
30	WARD	1250	1250
30	TURNER	1500	1250
30	ALLEN	1600	1500
30	BLAKE	2850	1600

C. Look back multiple rows

Set offset to 2 to access the salary two rows before the current row.

SELECT deptno, ename, sal,
       lag(sal, 2) OVER (PARTITION BY deptno ORDER BY sal) AS two_rows_back
FROM emp;

The first two rows in each partition return NULL.

deptno	ename	sal	two_rows_back
10	TEBAGE	1300	NULL
10	MILLER	1300	NULL
10	CLARK	2450	1300
10	WELAN	2450	1300
10	KING	5000	2450
10	JACCKA	5000	2450
20	SMITH	800	NULL
20	ADAMS	1100	NULL
20	JONES	2975	800
20	SCOTT	3000	1100
20	FORD	3000	2975
30	JAMES	950	NULL
30	MARTIN	1250	NULL
30	WARD	1250	950
30	TURNER	1500	1250
30	ALLEN	1600	1250
30	BLAKE	2850	1500

Sample data

Create a table named emp 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);

Upload data from a local file emp.txt using Tunnel:

tunnel upload emp.txt emp;

Contents of emp.txt:

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

Related functions

LAG looks backward; LEAD looks forward. Both are part of the Window functions family.