开窗函数之LAG,LEAD与⾃连接
LAG与LEAD
返回窗⼝中当前⾏之前(之后)给定偏移量的输⼊表达式的值,即在同⼀张表中实现错位查询(在同⼀次查询中取出同⼀字段的前N⾏的数据(Lag)和后N⾏的数据(Lead)作为独⽴的列)。它可以避免使⽤消耗更⼤的⾃连接,从⽽提⾼查询处理速度。小型核反应堆
函数写法:
亲爱的爸爸妈妈LAG ( expression[, offt ] [, default ] ) OVER ( )
expression:字段名;offt:偏移量,上(下)1⾏或N⾏;
default:函数取上N/下N个值,当在表中从当前⾏位置向前数N⾏已经超出了表的范围时,default作为函数的默认返回值,若⽆指定默认值,则返回NULL。
⽰例
原始数据
1.LAG,LEAD
lect uucompany_id
,year_quarter
,spend
,lag(spend,1,0) over(partition by uucompany_id order by year_quarter) as last_spend
,lead(spend,1,0) over(partition by uucompany_id order by year_quarter) as next_spend
,spend - lag(spend,1,0) over(partition by uucompany_id order by year_quarter) as spend_diff
,datediff('quarter',lag(year_quarter,1) over(partition by uucompany_id order by year_quarter),year_quarter)-1 as quarter_diff --⽆消耗季度武汉大学招生简章
台风前
from t_quarter_spend
2.⾃连接
lect q1.uucompany_id
,q1.year_quarter施工项目管理
,q1.spend as spend
,q2.spend as last_spend
,q1.spend-q2.spend as spend_diff
,datediff('quarter',q2.year_ar_quarter)-1 as quarter_diff --⽆消耗季度from t_quarter_spe
nd q1
left join t_quarter_spend q2 on q1.uucompany_id = q2.uucompany_id --上季度
and q1.rk = q2.rk+1
3.LEAD,LAG应⽤
根据A,B列实现⽬标排名(题意:B列从上到下遇到不同的值就加1)
lect t.A
,t.B
,sum(decode(t.B=t.B_lead,true,0,1)) over(order by t.A) as ⽬标值
from (
三英语
lect t.A
, t.B
时间管理四象限, nvl(lag(t.B) over (order by t.A),0) as B_lead
st1 t摄影学习
) t
;