lag lead 学习

来源：互联网发布：福州哪里收淘宝模特编辑：程序博客网时间：2024/05/21 09:52

lag lead 语法如下：

Lag和Lead函数可以在一次查询中取出同一字段的前N行的数据和后N行的值

lag(exp_str,offset,defval) over()
exp_str 是要做对比的字段
offset 是exp_str字段的偏移量比如说 offset 为3 则拿exp_str的第一行和第四行对比，第二行和第五行，依次类推，offset的默认值为1！
defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

--lag(exp_str)，如下：

select ename,job,sal ,lag(sal) over(order by sal) last_sal from emp;

ENAMEJOBSALLAST_SAL1SMITHCLERK8002JAMESCLERK9508003ADAMSCLERK11009504WARDSALESMAN125011005MARTINSALESMAN125012506CLARKMANAGER130012507MILLERCLERK130013008TURNERSALESMAN150013009ALLENSALESMAN1600150010BLAKEMANAGER2850160011JONESMANAGER2975285012SCOTTANALYST3000297513FORDANALYST3000300014KINGPRESIDENT50003000--lead如同

select ename,job,sal ,lead(sal) over(order by sal) last_sal from emp;

ENAMEJOBSALLAST_SAL1SMITHCLERK8009502JAMESCLERK95011003ADAMSCLERK110012504WARDSALESMAN125012505MARTINSALESMAN125013006CLARKMANAGER130013007MILLERCLERK130015008TURNERSALESMAN150016009ALLENSALESMAN1600285010BLAKEMANAGER2850297511JONESMANAGER2975300012SCOTTANALYST3000300013FORDANALYST3000500014KINGPRESIDENT5000
--lad(sal,1,100) over(order by sal) sal向前便宜1，null值会被100填充

select ename,job,sal ,lag(sal,1,100) over(order by sal) last_sal from emp;

ENAMEJOBSALLAST_SAL1SMITHCLERK8001002JAMESCLERK9508003ADAMSCLERK11009504WARDSALESMAN125011005MARTINSALESMAN125012506CLARKMANAGER130012507MILLERCLERK130013008TURNERSALESMAN150013009ALLENSALESMAN1600150010BLAKEMANAGER2850160011JONESMANAGER2975285012SCOTTANALYST3000297513FORDANALYST3000300014KINGPRESIDENT50003000
--lead(sal,1,100) over(order by sal) 向后偏移1
select ename,job,sal ,lead(sal,1,100) over(order by sal) last_sal from emp;

ENAMEJOBSALLAST_SAL1SMITHCLERK8009502JAMESCLERK95011003ADAMSCLERK110012504WARDSALESMAN125012505MARTINSALESMAN125013006CLARKMANAGER130013007MILLERCLERK130015008TURNERSALESMAN150016009ALLENSALESMAN1600285010BLAKEMANAGER2850297511JONESMANAGER2975300012SCOTTANALYST3000300013FORDANALYST3000500014KINGPRESIDENT5000100
--偏移2如下
select ename,job,sal ,lag(sal,2) over(order by sal) last_sal from emp;

ENAMEJOBSALLAST_SAL1SMITHCLERK8002JAMESCLERK9503ADAMSCLERK11008004WARDSALESMAN12509505MARTINSALESMAN125011006CLARKMANAGER130012507MILLERCLERK130012508TURNERSALESMAN150013009ALLENSALESMAN1600130010BLAKEMANAGER2850150011JONESMANAGER2975160012SCOTTANALYST3000285013FORDANALYST3000297514KINGPRESIDENT50003000

0 0