LAG与LEAD分析函数可以在同一次查询中获取同一字段的前N行的数据(LAG)或者后N行的数据(LEAD)作为独立列。
LAG
LAG它同时提供对多行表的访问,而不需要自连接。给定从查询返回的一系列行和游标的位置,LAG
可以访问位于该位置之前给定物理偏移量的行。您可以给偏移参数指定一个大于零的整数。如果不指定偏移量,则其默认值为 1。如果偏移量超出窗口的范围,则返回可选值。如果不指定默认值,则其默认值为 NULL
。
语法:
LAG { (value_expr [,offset [,default]]) [RESPECT|IGNORE] NULLS
|(value_expr [RESPECT | IGNORE] NULLS [,offset [,default] ]) }
OVER([query_partition_clause] order_by_clause)
参数:
value_expr:是要做比对的字段。您不能使用 LAG
函数或其他分析函数来嵌套 value_expr。
offset:是 value_expr 的可选参数偏移量。
default:如果未指定默认值,则其默认值为 NULL
。如果在 LEAD
没有显式的设置 default 值的情况下,返回值为 NULL
。
{RESPECT | IGNORE} NULLS:表示是否忽略 NULL
值。默认值为 RESPECT NULLS
,考虑 NULL
值。
OVER:使用 OVER 子句定义窗口进行计算。
返回值:数据类型不限
示例:
创建 emp_msg 表,并向里面插入数据。执行以下语句:
CREATE TABLE emp_msg(deptno INT, ename varchar(30),sal INT);
INSERT INTO emp_msg VALUES(20,'ADAMS',1400);
INSERT INTO emp_msg VALUES(30,'ALLEN',1900);
INSERT INTO emp_msg VALUES(30,'BLAKE',3135);
INSERT INTO emp_msg VALUES(10,'CLARK',2750);
INSERT INTO emp_msg VALUES(20,'FORD',3300);
INSERT INTO emp_msg VALUES(30,'JAMES',1250);
INSERT INTO emp_msg VALUES(20,'JONES',3275);
INSERT INTO emp_msg VALUES(10,'KING',5300);
INSERT INTO emp_msg VALUES(30,'MARTIN',1550);
INSERT INTO emp_msg VALUES(10,'MILLER',1600);
INSERT INTO emp_msg VALUES(20,'SCOTT',3300);
INSERT INTO emp_msg VALUES(20,'SWITH',1100);
INSERT INTO emp_msg VALUES(30,'TURNER',1800);
INSERT INTO emp_msg VALUES(30,'WARD',1550);
查询 emp_msg 表,前 5 个值用 Jane 代替,从第 6 个值开始追加按 ename 字段升序排列的值。执行以下语句:
SELECT deptno, ename, sal, LAG(ename,5,'Jane') OVER (ORDER BY ename) AS new_ename
FROM emp_msg;
查询结果:
+--------+--------+------+-----------+
| DEPTNO | ENAME | SAL | NEW_ENAME |
+--------+--------+------+-----------+
| 20 | ADAMS | 1400 | Jane |
| 30 | ALLEN | 1900 | Jane |
| 30 | BLAKE | 3135 | Jane |
| 10 | CLARK | 2750 | Jane |
| 20 | FORD | 3300 | Jane |
| 30 | JAMES | 1250 | ADAMS |
| 20 | JONES | 3275 | ALLEN |
| 10 | KING | 5300 | BLAKE |
| 30 | MARTIN | 1550 | CLARK |
| 10 | MILLER | 1600 | FORD |
| 20 | SCOTT | 3300 | JAMES |
| 20 | SWITH | 1100 | JONES |
| 30 | TURNER | 1800 | KING |
| 30 | WARD | 1550 | MARTIN |
+--------+--------+------+-----------+
LEAD
LEAD提供了对表多行的访问,而无需进行自我连接。给定从查询返回的一些列行和光标的位置,LEAD
提供超出该位置的物理偏移量的行的访问。
语法:
LEAD { (value_expr [,offset [,default]]) [RESPECT|IGNORE] NULLS
|(value_expr [RESPECT|IGNORE] NULLS [,offset [,default]]) }
OVER([query_partition_clause] order_by_clause)
参数:同LAG
返回值类型:数据类型不限
示例:同LAG
查询 emp_msg 表,最后 5 个值用 Jane 代替,从倒数第 6 个值开始追加按 ename 列升序排列的值。
SELECT deptno, ename, sal, LEAD(ename,5,'Jane') OVER (ORDER BY ename) AS new_ename
FROM emp_msg;
查询结果:
+--------+--------+------+-----------+
| DEPTNO | ENAME | SAL | NEW_ENAME |
+--------+--------+------+-----------+
| 20 | ADAMS | 1400 | JAMES |
| 30 | ALLEN | 1900 | JONES |
| 30 | BLAKE | 3135 | KING |
| 10 | CLARK | 2750 | MARTIN |
| 20 | FORD | 3300 | MILLER |
| 30 | JAMES | 1250 | SCOTT |
| 20 | JONES | 3275 | SWITH |
| 10 | KING | 5300 | TURNER |
| 30 | MARTIN | 1550 | WARD |
| 10 | MILLER | 1600 | Jane |
| 20 | SCOTT | 3300 | Jane |
| 20 | SWITH | 1100 | Jane |
| 30 | TURNER | 1800 | Jane |
| 30 | WARD | 1550 | Jane |
+--------+--------+------+-----------+